Thursday, November 1, 2007

AUTOTRACE Option in sqlplus

Tracing Statements
------------------

You can automatically get a report on the execution path used by the SQL
optimizer and the statement execution statistics. The report is generated
after successful SQL DML (Data Manipulation Language - that is, SELECT, DELETE,
UPDATE and INSERT) statements. It is useful for monitoring and tuning the
performance of these statements.


Controlling the Report
----------------------

You can control the report by setting the AUTOTRACE system variable.

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer
execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the
optimizer execution path and the SQL
statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.

To use this feature, you must have the PLUSTRACE role granted to you and a
PLAN_TABLE table created in your schema. For more information on the PLUSTRACE
role and PLAN_TABLE table, see the AUTOTRACE variable of the SET command in
Chapter 6 of the SQL*Plus Guide.


Execution Plan
--------------

The Execution Plan shows the SQL optimizer's query execution path.
Each line of the Execution Plan has a sequential line number. SQL*Plus also
displays the line number of the parent operation.
The Execution Plan consists of four columns displayed in the following order:

Column Name Description
------------------------------------------------------------------------

ID_PLUS_EXP Shows the line number of each execution step.
PARENT_ID_PLUS_EXP Shows the relationship between each step and its
parent. This column is useful for large reports.
PLAN_PLUS_EXP Shows each step of the report.
OBJECT_NODE_PLUS_EXP Shows the database links or parallel query servers
used.

The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter:

SQL> COLUMN PARENT_ID_PLUS_EXP NOPRINT

The default formats can be found in the site profile (for example, glogin.sql).

The Execution Plan output is generated using the EXPLAIN PLAN command. For
information about interpreting the output of EXPLAIN PLAN, see the
Oracle7 Server Tuning guide.

The following is an example of tracing statements for performance statistics and
query execution path.

If the SQL buffer contains the following statement:

SQL> SELECT D.DNAME, E.ENAME, E.SAL, E.JOB
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO

The statement can be automatically traced when it is run:

SQL> SET AUTOTRACE ON
SQL> /

DNAME ENAME SAL JOB
-------------- ---------- ---------- ---------
ACCOUNTING CLARK 2450 MANAGER
ACCOUNTING KING 5000 PRESIDENT
ACCOUNTING MILLER 1300 CLERK
RESEARCH SMITH 800 CLERK
RESEARCH ADAMS 1100 CLERK
RESEARCH FORD 3000 ANALYST
RESEARCH SCOTT 3000 ANALYST
RESEARCH JONES 2975 MANAGER
SALES ALLEN 1600 SALESMAN
SALES BLAKE 2850 MANAGER
SALES MARTIN 1250 SALESMAN
SALES JAMES 950 CLERK
SALES TURNER 1500 SALESMAN
SALES WARD 1250 SALESMAN

14 rows selected.

Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'DEPT'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'EMP'

Statistics
----------------------------------------------------------
148 recursive calls
4 db block gets
24 consistent gets
6 physical reads
43 redo size
591 bytes sent via SQL*Net to client
256 bytes received via SQL*Net from client
33 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed

Note: The output may vary depending on the version of the server to
which you are connected and the configuration of the server.


References
-----------------------------------------------------------

SQL*PLus User's Guide and Reference Release 3.3.
Autotrace documented on Page 6-77. Part No. A42562-1.

Oracle7 Server Tuning Release 7.3. Part No. A32537-1.
Explain Plan documented on Page 6-18.

10 comments:

Anonymous said...

Can anyone recommend the best Managed Service program for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central performance management
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

[url=http://hairtyson.com]online Phen375[/url] are tablets that forbear trim league weight. Everybody of these tabs has to be enchanted with drinking-water, almost 20 minutes ahead of a collation, twice a day.

Anonymous said...

[url=http://www.payloansonline.com]payday advance[/url]
This is the best way to get all your health products online like green coffee, african mango, phen375 and others. Visit now

[url=http://www.free-press-release.com/news-melatrol-my-honest-review-on-melatrol-natural-sleeping-aids-1355211539.html]Melatrol[/url]

Anonymous said...

Pretty! This has been an incredibly wonderful article.
Many thanks for providing this info.

Also visit my blog ... Order Equinox

Anonymous said...

As the name suggests, natural bodybuilding is naturally to build one's body. There's a clever little
line in the Jimmy Buffet lyrics "Fruitcakes" when his 'lady' is lamenting: "I treat my body like a temple, You treat yours like a tent".
Not only can an elliptical build muscle, but it
will also help you burn calories and fat.

My web-site :: Power Pump XL Reviews

Anonymous said...

I’m not that much of a internet reader to be honest but your blogs really nice, keep it up!
I'll go ahead and bookmark your website to come back later on. Cheers

Check out my blog - Lift and Glow Pro Skin Care

Anonymous said...

Aw, this was a really nice post. Spending some time and actual effort to produce
a really good article… but what can I say… I hesitate a lot and don't manage to get nearly anything done.

Here is my webpage :: what is garcinia cambogia

Anonymous said...

Wonderful work! That is the type of info that should be shared across the web.
Disgrace on the search engines for now not positioning
this post higher! Come on over and discuss with my web site .
Thank you =)

My page :: Pur Essence Reviews

Anonymous said...

Greetings! Quick question that's completely off topic. Do you know how to make your site mobile friendly? My web site looks weird when browsing from my apple iphone. I'm trying to find a template or plugin that might
be able to resolve this issue. If you have any recommendations, please
share. Thanks!

Here is my homepage - Enduros Male Enhancement Review

Anonymous said...

Pretty! This was an incredibly wonderful post. Many thanks
for providing this info.

My blog - Green coffee cleanse review