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

Saturday, September 20, 2008

DBMS_ADVISOR Package

The DBMS_ADVISOR package is new to Oracle 10g. It is used to provide the PL/SQL application programming interface (API), which manages all the advisors. The DBMS_ADVISOR package is also used to create and execute any advisor tasks, including SQL Access Advisor tasks. There are many procedures available for the package, as given below:

Procedure name Description
CREATE_TASK Creates a new advisor task in the repository.
SET_TASK_PARAMETER Modifies a user parameter within a task (a task can only be modified if it is in the initial state).
EXECUTE_TASK Executes the specified task (analysis task in the asked case).
GET_TASK_REPORT Creates and returns an XML report for the specified task.
DELETE_TASK Deletes the specified advisor task.
CANCEL_TASK Cancels a currently executing task.
INTERRUPT_TASK Stops the currently executing task, with normal exit.
RESUME_TASK Resumes an interrupted task.
RESET_TASK Resets a task to its original state.
UPDATE_TASK_ATTRIBUTES Changes various attributes of a task.
MARK_RECOMMENDATION Accepts, rejects, or ignores a recommendation.
GET_TASK_SCRIPT Creates a SQL*Plus-compatible SQL script of all the recommendations that are accepted from a specified task.
CREATE_FILE Creates an external file from a PL/SQL CLOB variable, used for creating scripts and reports.
QUICK_TIME Analyzes and generates recommendations for a single SQL statement.


Oracle10g introduced several intelligent utilities that produce performance tuning advices and suggestions based on historical data stored in the AWR. These utilities are called advisors. The dbms_advisor package provides PL/SQL API to access the following advisors:

The Automatic Database Diagnostic Monitor (ADDM) provides Oracle DBAs with performance data gathered by the AWR. The ADDM also identifies root causes of performance bottlenecks and generates a report containing recommendations or findings on how to improve database performance.

The SQLAccess Advisor provides analysis and recommendations about indexes and materialized views which may improve system performance.

Summary of DBMS_ADVISOR Subprograms


Subprogram
Description
Used in

ADD_SQLWKLD_REF Procedure
Adds a workload reference to an Advisor task
SQL Access Advisor only


ADD_SQLWKLD_STATEMENT Procedure
Adds a single statement to a workload
SQL Access Advisor only

ADD_STS_REF Procedure
Establishes a link between the current SQL Access Advisor task and a SQL Tuning Set
SQL Access Advisor only

CANCEL_TASK Procedure
Cancels a currently executing task operation
All Advisors

COPY_SQLWKLD_TO_STS Procedure
Copies the contents of a SQL workload object to a SQL Tuning Set
SQL Access Advisor

CREATE_FILE Procedure
Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports
All Advisors

CREATE_OBJECT Procedure
Creates a new task object
All Advisors

CREATE_SQLWKLD Procedure
Creates a new workload object
SQL Access Advisor only

CREATE_TASK Procedures
Creates a new Advisor task in the repository
All Advisors

DELETE_SQLWKLD Procedure
Deletes an entire workload object
SQL Access Advisor only

DELETE_SQLWKLD_REF Procedure
Deletes an entire workload object
SQL Access Advisor only

DELETE_SQLWKLD_STATEMENT Procedures
Deletes one or more statements from a workload
SQL Access Advisor only

DELETE_STS_REF Procedure
Removes a link between the current SQL Access Advisor task and a SQL Tuning Set object
SQL Access Advisor only

DELETE_TASK Procedure
Deletes the specified task from the repository
All Advisors

EXECUTE_TASK Procedure
Executes the specified task
All Advisors

GET_REC_ATTRIBUTES Procedure
Retrieves specific recommendation attributes from a task
All Advisors

GET_TASK_REPORT Function
Creates and returns a report for the specified task
All Advisors

GET_TASK_SCRIPT Function
Creates and returns an executable SQL script of the Advisor task's recommendations in a buffer
All Advisors

IMPLEMENT_TASK Procedure
Implements the recommendations for a task
All Advisors

IMPORT_SQLWKLD_SCHEMA Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_SQLCACHE Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_STS Procedure
Imports data into a workload from a SQL Tuning Set into a SQL workload data object
SQL Access Advisor only

IMPORT_SQLWKLD_SUMADV Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

IMPORT_SQLWKLD_USER Procedure
Imports data into a workload from the current SQL cache
SQL Access Advisor only

INTERRUPT_TASK Procedure
Stops a currently executing task, ending its operations as it would at a normal exit, so that the recommendations are visible
All Advisors

MARK_RECOMMENDATION Procedure
Sets the annotation_status for a particular recommendation
All Advisors

QUICK_TUNE Procedure
Performs an analysis on a single SQL statement
All Advisors

RESET_TASK Procedure
Resets a task to its initial state
All Advisors

SET_DEFAULT_SQLWKLD_PARAMETER Procedures
Imports data into a workload from schema evidence
SQL Access Advisor only

SET_DEFAULT_TASK_PARAMETER Procedures
Modifies a default task parameter
All Advisors

SET_SQLWKLD_PARAMETER Procedures
Sets the value of a workload parameter
SQL Access Advisor only

SET_TASK_PARAMETER Procedure
Sets the specified task parameter value
All Advisors

TUNE_MVIEW Procedure
Shows how to decompose a materialized view into two or more materialized views or to restate the materialized view in a way that is more advantageous for fast refresh and query rewrite
SQL Access Advisor only

UPDATE_OBJECT Procedure
Updates a task object
All Advisors

UPDATE_REC_ATTRIBUTES Procedure
Updates an existing recommendation for the specified task
All Advisors

UPDATE_SQLWKLD_ATTRIBUTES Procedure
Updates a workload object
SQL Access Advisor only

UPDATE_SQLWKLD_STATEMENT Procedure
Updates one or more SQL statements in a workload
SQL Access Advisor only

UPDATE_TASK_ATTRIBUTES Procedure
Updates a task's attributes
All Advisors

No comments:

Blog Archive