Oracle DBA Interview Questions focusing on Performance Tuning

Situational ( Performance Tuning )
Q. Customer reports a application slowness issue, and you need to evaluate database performance. What do you look at for 9i and for 11g.

Possible answer: On oracle 9i, look at the statspack report , on oracle 11g look at the AWR report. In both reports look top sqls listed in elapsed time or cpu time. At sqlplus look sql_text from v$sql where disk_reads is high.

Q. You have found a long running sql in your evaluation of system health of database, what do you look for to determine why sql is slow?

Possible answer: Use explain plan to determine the execution plan of the sql. When looking at execution plan look for indexes being used, full table scans on large tables.

Q. You have a windows service is crashing, how can you determine the sqls related to the windows service?

Possible answer: Use sql trace to trace the username and program associated with the trace file. Use tkprof to analyze the sql trace and determine the long running sqls.

sql trace and tkprof

Sql trace and tkprof are useful tools for understanding what sqls are being generated by a poorly performing report or application.

To invoke sql trace you can either alter your session using sql command
ALTER SESSION SET sql_trace = true;

To invoke sql trace in other users/application sessions you can use plsql package
dbms_system.set_sql_trace_in_session ( sid, serial#, TRUE 0 );

To find out the users sid and serial we select from v$session
select sid, serial# , status from v$session where program = < ' The Relevant EXE ' >;

example:
select sid, serial# , status from v$session where program = 'BbTSMain.exe' ;

If there are multiple sessions as there are for BbTSMain.exe then I can dynamically build a sql trace for this

example
spool trace.sql
select 'EXECUTE dbms_system.set_sql_trace_in_session (' ||sid || ','|| serial# || ', TRUE);' from v$session where program='BbTSMain.exe'
/
spool off

This will generate a script like

EXECUTE dbms_system.set_sql_trace_in_session (396,55252, TRUE);

@trace.sql
Now that sql tracing is turned on, it is time to look for our trace directory and tracefile
show parameter user_dump_dest

sort the directory for the *.trc files being actively modified

Now lets add some value to the sql trace by running the tracefile through the tkprof utility

tkprof explain=envision/@envision

example: tkprofe bbts_ora_2160.trc k.out explain=envision/123@envision

edit the outfile to see the sqls and the execution plans

You have now successfully mined the underlying sqls for the application

View Oracle Bind Variables version 10g 11g

Using grid control and v$sql you can see sql statements but to see the bind varibles in 10g and 11g you need to query the view v$sql_bind_capture.

select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b using (sql_id)
where
b.value_string is not null
and sql_id='&mysqlid'
/

Find Oracle Trace File

When looking for an oracle trace file here are a 2 sqls that will help you out. First ascertain the SID then run sql 1 to get the ospid. Then run sql 2 to get the trace file.

Sql 1
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
Then user the spid ( server process identifier ) to get the location of the tracefile.
SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS where pid= &spid

To turn on trace event 10046 you can use the oradebug

connect / as sysdba
oradebug setospid
oradebug unlimit
oradebug event 10046 trace name context forever,level 12