A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:
*Improved selectivity
Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.
*Reduced I/O
If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.
A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
Note:This is no longer the case with index skip scans. See "Index Skip Scans".
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:
CREATE INDEX comp_ind
ON table1(x, y, z);
*x, xy, and xyz combinations of columns are leading portions of the index
*yz, y, and z combinations of columns are not leading portions of the index
Choosing Keys for Composite Indexes
Follow these guidelines for choosing keys for composite indexes:
*Consider creating a composite index on keys that are used together frequently in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.
*If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.
Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections.
Ordering Keys for Composite Indexes
Follow these guidelines for ordering keys in composite indexes:
*Create the index so the keys used in WHERE clauses make up a leading portion.
*If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.
*If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.
Oracle 10 DBA Interview questions and answers
1. Is the following SQL statement syntactically correct? If not, please rewrite it correctly.
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk '{print }'
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
SELECT col1 FROM tableA WHERE NOT IN (SELECT col1 FROM tableB);
Ans. SQL is incorrect.
Correct SQL : SELECT col1 FROM tableA WHERE col1 NOT IN (SELECT col1 FROM tableB);
2. What is a more efficient way to write this query, to archive the same set?
Ans: SELECT col1 from tableA minus SELECT col1 from tableB
3.How would you determine that the new query is more efficient than the original query?
Ans: Run explain plan on both query and see the result .
4.How can we find the location of the database trace files from within the data dictionary?
Ans: Generally trace file on the database server machine is located in one of two locations:
1. If you are using a dedicated server connection, the trace file will be generated in the directory specified by
the USER_DUMP_DEST parameter.
2.If you are using a shared server connection, the trace file will be generated in the directory specified by the
BACKGROUND_DUMP_DEST parameter.
you can run sqlplus>SHOW PARAMETER DUMP_DEST
or
select name, value
from v$parameter
where name like '%dump_dest%'
5. What is the correct syntax for a UNIX endless WHILE loop?
while :
do
commands
done
6. Write the SQL statement that will return the name and size of the largest datafile in the database.
SQL> select name,bytes from v$datafile where bytes=(select max(bytes) from v$datafile);
7. What are the proper steps to changing the Oracle database block size?
cold backup all data files and backup controlfile to trace, recreate your database
with the new block size using the backup control file, and restore. It may be easier
with rman. You can not change datbase block size on fly.
8. Using awk, write a script to print the 3rd field of every line.
Ans:
awk
awk '{print $3}
awk '{print $3}
9.Under what conditions, is a nested loop better than a merge join?
Ans:
Optimizer uses nested loop when we are joining tables containing small number of rows with an efficient driving
condition.
It is important to have an index on column of inner join table as this table is probed every time for a new value
from outer table.
Optimizer may not use nested loop in case:
1. No of rows of both the table is quite high
2. Inner query always results in same set of records
3. The access path of inner table is independent of data coming from outer table.
merge join is used to join two independent data sources. They perform better than nested loop when the volume of
data is big in tables
but not as good as hash joins in general.
10.Which database views would you use to ascertain the number of commits a user's session has performed?
Joining V$SESSTAT ,V$STATNAME
select * from V$SESSTAT a ,V$STATNAME b where b.CLASS=a.STATISTIC# and b.NAME='user commits' and a.sid=
11.What does the #!bin/ksh at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an interpreter script, the "#!", is the name of a program which should be used to
interpret the contents of the file.
For instance, if the first line contains "#! /bin/ksh", then the contents of the file are executed as a korn shell
script.
12.What command is used to find the status of Oracle 10g Clusterware (CRS) and the various components it manages
(ONS, VIP, listener, instances, etc.)?
Ans:
$ocrcheck
13.Describe a scenario in which a vendor clusterware is required, in addition to the Oracle 10g Clusterware.
If you choose external redundancy for the OCR and voting disk, then to enable redundancy, the disk subsystem must be configurable for RAID mirroring/vendor clusterware. Otherwise, your system may be vulnerable because the OCR and voting disk are single points of failure.
14.How would you find the interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command to display online help for OIFCFG. The elements of OIFCFG commands, some of which are
optional depending on the command, are:
*nodename—Name of the Oracle Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the interface is configured in the system
*subnet—Subnet address of the interface
*if_type—Type of interface: public or cluster_interconnect
You can use OIFCFG to list the interface names and the subnets of all of the interfaces available on the local node
by executing the iflist keyword as shown in this example:
oifcfg iflist
hme0 139.185.141.0
qfe0 204.152.65.16
You can also retrieve specific OIFCFG information with a getif command using the following syntax:
oifcfg getif [ [-global | -node nodename] [-if if_name[/subnet]] [-type if_type] ]
To store a new interface use the setif keyword. For example, to store the interface hme0, with the subnet
139.185.141.0, as a global interface (to be used as an interconnect for all of the RAC instances in your cluster),
you would use the command:
oifcfg setif -global hme0/139.185.141.0:cluster_interconnect
For a cluster interconnect that exists between only two nodes, for example rac1 and rac2, you could create the cms0
interface with the following commands, assuming 139.185.142.0 is the subnet addresses for the interconnect on rac1
and rac2 respectively:
oifcfg setif -global cms0/139.185.142.0:cluster_interconnect
Use the OIFCFG delif command to delete the stored configuration for global or node-specific interfaces. A specific
node-specific or global interface can be deleted by supplying the interface name, with an optional subnet, on the
command line. Without the -node or -global options, the delif keyword deletes either the given interface or all of
the global and node-specific interfaces on all of the nodes in the cluster. For example, the following command
deletes the global interface named qfe0 for the subnet 204.152.65.0:
oifcfg delif -global qfe0/204.152.65.0
On the other hand, the next command deletes all of the global interfaces stored with OIFCFG:
oifcfg delif -global
15.What is the Purpose of the voting disk in Oracle 10g Clusterware?
Voting disk record node membership information. Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster.
16.What is the purpose of the OCR in Oracle 10g Clusterware?
Ans: Oracle Cluster Registry (OCR) is a component in 10g RAC used to store the cluster configuration information. It is a shared disk component, typically located in a shared raw volume that must be accessible to all nodes in the cluster.
The daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.
17. In Oracle Streams archived log downstream capture, which database view can be used to determine which archived
logs are no longer needed by the capture process?
Ans: V$ARCHIVE_DEST_STATUS
Monday, October 20, 2008
Subscribe to:
Post Comments (Atom)
Blog Archive
-
▼
2008
(277)
-
▼
October
(14)
- Using Function-based Indexes for Performance
- Choosing Composite Indexes
- Choosing Columns and Expressions to Index
- Finding Locks and Lock Holders
- TM enqueue
- HW enqueue
- ST enqueue
- TX enqueue
- sar - Top 20 DBA unix commands
- iostat - Top 20 DBA unix commands
- vmstat - Top 20 DBA Unix commands
- Find how much memory is in your unix machine/server?
- Difference between Session, Connection and Process
- Moving data files
-
▼
October
(14)
Posts
No comments:
Post a Comment