|
A Roadmap To Query Tuning
For each SQL statement, there are different approaches that could be used to
retrieve the required data. Optimization is the process of choosing the most
efficient way to retrieve this data based upon the evaluation of a number of
different criteria. The CBO bases optimization choices on pre-gathered table and
index statistics while the RBO makes it's decisions based on a set of rules and
does not rely on any statistical information. CBO's reliance on statistics makes
it vastly more flexible than the RBO since as long as up to date statistics are
maintained, it will accurately reflect real data volumes. The RBO is
de-supported in Oracle10g.
To gather 10046 trace at the session level:
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
Features of DBMS_SUPPORT Package
In this article I have described undocumented feature within Oracle there is no
guarantee that the results will be exactly as described for all releases .
Installing DBMS Package
-----------------------
[oracle@localhost admin]$ ls -ltr *supp*
-rw-r----- 1 oracle oracle 1546 Feb 27 2001 dbmssupp.sql
-rw-r----- 1 oracle oracle 1198 Sep 19 2005 prvtsupp.plb
SQL> @$ORACLE_HOME/rdbms/admin/dbmssupp
---------------------------------------------
-- run your select(s) --
SQL> exec DBMS_SUPPORT.START_TRACE;
PL/SQL procedure successfully completed.
SQL> /* Execute your query */
SQL> exec DBMS_SUPPORT.STOP_TRACE;
PL/SQL procedure successfully completed.
Trace Output:
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-53.el5xen
Version: #1 SMP Sat Nov 10 19:46:12 EST 2007
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 4947, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2008-01-21 12:00:25.204
*** SERVICE NAME:(SYS$USERS) 2008-01-21 12:00:25.204
*** SESSION ID:(158.3) 2008-01-21 12:00:25.204
=====================
PARSING IN CURSOR #6 len=198 dep=1 uid=0 oct=3 lid=0 tim=1172745727738352 hv=4125641360
ad='6c2b8cc0'
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2
from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
END OF STMT
PARSE #6:c=0,e=620,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1172745727738344
=====================
PARSING IN CURSOR #3 len=121 dep=2 uid=0 oct=3 lid=0 tim=1172745727740552 hv=3150898423
ad='6c1ddee0'
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1
and intcol#=:2 and row#=:3 order by bucket
END OF STMT
PARSE #3:c=0,e=587,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=1172745727740544
EXEC #3:c=0,e=2148,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=1172745727742876
=====================
--More--(80%)
==------------------------------------------------------------------------------------------------
How to interpret the internal trace
output
STAT Lines report explain plan statistics for the numbered <CURSOR>.
PARSE Parse a statement
EXEC Execute a pre-parsed statement.
FETCH Fetch rows from a cursor.
-
-
-
This is a very short brief explanation for Interpreting and investigating query
parsing ,wait events.
|
