niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
autotrace
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
Author Message
prasath.rao@gmail.com
*nix forums beginner


Joined: 16 Nov 2005
Posts: 19

PostPosted: Mon Jul 17, 2006 10:42 am    Post subject: autotrace Reply with quote
Why does the statistics report in the explain plan take more time to be
generated?

When I use "set autotrace traceonly explain", it takes zero seconds for
the plan to be generated. If the autotrace is set to "set autotrace
traceonly explain statistics" then it takes 14 seconds. Any idea why
this happens?


SQL> desc test_p
Name Null? Type
----------------------------------------- -------- -----------------
MYCOL1 NUMBER

SQL> truncate table test_p;

Table truncated.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly explain
SQL> select * from test_p;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_P'



SQL> set autotrace traceonly explain statistics
SQL> /

no rows selected

Elapsed: 00:00:14.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_P'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
140 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Back to top
sybrandb
*nix forums beginner


Joined: 13 Jul 2006
Posts: 22

PostPosted: Mon Jul 17, 2006 11:22 am    Post subject: Re: autotrace Reply with quote
Prasath wrote:
Quote:
Why does the statistics report in the explain plan take more time to be
generated?

When I use "set autotrace traceonly explain", it takes zero seconds for
the plan to be generated. If the autotrace is set to "set autotrace
traceonly explain statistics" then it takes 14 seconds. Any idea why
this happens?


SQL> desc test_p
Name Null? Type
----------------------------------------- -------- -----------------
MYCOL1 NUMBER

SQL> truncate table test_p;

Table truncated.

Elapsed: 00:00:00.00
SQL> set autotrace traceonly explain
SQL> select * from test_p;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_P'



SQL> set autotrace traceonly explain statistics
SQL> /

no rows selected

Elapsed: 00:00:14.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST_P'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
140 bytes sent via SQL*Net to client
232 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


If you would think one sec about it, you would realize those statistics
need to be SELECTed from your session statistics. Obviously this takes
time.

--
Sybrand Bakker
Senior Oracle DBA
Back to top
prasath.rao@gmail.com
*nix forums beginner


Joined: 16 Nov 2005
Posts: 19

PostPosted: Mon Jul 17, 2006 11:28 am    Post subject: Re: autotrace Reply with quote
I agree that there might be a slight overhead in collecting the
statisitcs of a query. But I am concerned with the amount of time
taken. There must be something wrong with the session. I am looking
for some clues.
Back to top
Gints Plivna
*nix forums beginner


Joined: 21 Jun 2006
Posts: 27

PostPosted: Mon Jul 17, 2006 11:55 am    Post subject: Re: autotrace Reply with quote
Prasath wrote:
Quote:
I agree that there might be a slight overhead in collecting the
statisitcs of a query. But I am concerned with the amount of time
taken. There must be something wrong with the session. I am looking
for some clues.

Oracle docs are your friend
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/autotrac.htm

SET AUTOTRACE ON EXPLAIN
The AUTOTRACE report shows only the optimizer execution path.

Only plan, no actual execute of query, therefore such a difference.

Gints Plivna
http://www.gplivna.eu/
Back to top
Michel Cadot
*nix forums Guru


Joined: 24 Apr 2005
Posts: 457

PostPosted: Mon Jul 17, 2006 4:17 pm    Post subject: Re: autotrace Reply with quote
"Gints Plivna" <gints.plivna@gmail.com> a écrit dans le message de news: 1153137348.573607.108590@m79g2000cwm.googlegroups.com...
| Prasath wrote:
| > I agree that there might be a slight overhead in collecting the
| > statisitcs of a query. But I am concerned with the amount of time
| > taken. There must be something wrong with the session. I am looking
| > for some clues.
|
| Oracle docs are your friend
| http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/autotrac.htm
|
| SET AUTOTRACE ON EXPLAIN
| The AUTOTRACE report shows only the optimizer execution path.
|
| Only plan, no actual execute of query, therefore such a difference.
|
| Gints Plivna
| http://www.gplivna.eu/
|

Wrong! There is no result output but the query is executed.

Regards
Michel Cadot
Back to top
Gints Plivna
*nix forums beginner


Joined: 21 Jun 2006
Posts: 27

PostPosted: Mon Jul 17, 2006 7:47 pm    Post subject: Re: autotrace Reply with quote
Michel Cadot wrote:
Quote:
"Gints Plivna" <gints.plivna@gmail.com> a écrit dans le message de news: 1153137348.573607.108590@m79g2000cwm.googlegroups.com...
| Prasath wrote:
| > I agree that there might be a slight overhead in collecting the
| > statisitcs of a query. But I am concerned with the amount of time
| > taken. There must be something wrong with the session. I am looking
| > for some clues.
|
| Oracle docs are your friend
| http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/autotrac.htm
|
| SET AUTOTRACE ON EXPLAIN
| The AUTOTRACE report shows only the optimizer execution path.
|
| Only plan, no actual execute of query, therefore such a difference.
|
| Gints Plivna
| http://www.gplivna.eu/
|

Wrong! There is no result output but the query is executed.

Regards
Michel Cadot

Hmmmmmm, are you absolutely sure?
10046 level trace shows following lines in trace file for my select
with autotrace traceonly explain:
PARSING IN CURSOR #23 len=24 dep=0 uid=58 oct=3 lid=58 tim=131957562505
hv=104009821 ad='6cee7618'
select count(*) from big
END OF STMT
PARSE
#23:c=0,e=9979,p=0,cr=23,cu=0,mis=1,r=0,dep=0,og=1,tim=131957562496
WAIT #23: nam='SQL*Net message to client' ela= 7 driver id=1111838976
#bytes=1 p3=0 obj#=348 tim=131957562643
WAIT #23: nam='SQL*Net message from client' ela= 247 driver
id=1111838976 #bytes=1 p3=0 obj#=348 tim=131957562983

And tkprof shows following output:
select count(*)
from
big


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1 0.00 0.00 0 0 0
0

So it isn't executed, no rows fetched just parsed.

Of course inserts, deletes and updates are both parsed and executed:
lines from trace file
PARSING IN CURSOR #29 len=38 dep=0 uid=58 oct=2 lid=58 tim=132486678502
hv=3665109320 ad='69bd9bcc'
insert into big values (1, 1, 1, 1, 1)
END OF STMT
PARSE
#29:c=78125,e=92231,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=132486678492
BINDS #29:
WAIT #29: nam='db file sequential read' ela= 10293 file#=4 block#=54025
blocks=1 obj#=50039 tim=132486701502
EXEC
#29:c=0,e=14805,p=1,cr=1,cu=3,mis=0,r=1,dep=0,og=1,tim=132486703768

Gints Plivna
http://www.gplivna.eu/
Back to top
Michel Cadot
*nix forums Guru


Joined: 24 Apr 2005
Posts: 457

PostPosted: Tue Jul 18, 2006 4:48 am    Post subject: Re: autotrace Reply with quote
"Gints Plivna" <gints.plivna@gmail.com> a écrit dans le message de news: 1153165664.992848.207430@m73g2000cwd.googlegroups.com...
Michel Cadot wrote:
Quote:
"Gints Plivna" <gints.plivna@gmail.com> a écrit dans le message de news: 1153137348.573607.108590@m79g2000cwm.googlegroups.com...
| Prasath wrote:
| > I agree that there might be a slight overhead in collecting the
| > statisitcs of a query. But I am concerned with the amount of time
| > taken. There must be something wrong with the session. I am looking
| > for some clues.
|
| Oracle docs are your friend
| http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96533/autotrac.htm
|
| SET AUTOTRACE ON EXPLAIN
| The AUTOTRACE report shows only the optimizer execution path.
|
| Only plan, no actual execute of query, therefore such a difference.
|
| Gints Plivna
| http://www.gplivna.eu/
|

Wrong! There is no result output but the query is executed.

Regards
Michel Cadot

Hmmmmmm, are you absolutely sure?
10046 level trace shows following lines in trace file for my select
with autotrace traceonly explain:
PARSING IN CURSOR #23 len=24 dep=0 uid=58 oct=3 lid=58 tim=131957562505
hv=104009821 ad='6cee7618'
select count(*) from big
END OF STMT
PARSE
#23:c=0,e=9979,p=0,cr=23,cu=0,mis=1,r=0,dep=0,og=1,tim=131957562496
WAIT #23: nam='SQL*Net message to client' ela= 7 driver id=1111838976
#bytes=1 p3=0 obj#=348 tim=131957562643
WAIT #23: nam='SQL*Net message from client' ela= 247 driver
id=1111838976 #bytes=1 p3=0 obj#=348 tim=131957562983

And tkprof shows following output:
select count(*)
from
big


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 0 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1 0.00 0.00 0 0 0
0

So it isn't executed, no rows fetched just parsed.

Of course inserts, deletes and updates are both parsed and executed:
lines from trace file
PARSING IN CURSOR #29 len=38 dep=0 uid=58 oct=2 lid=58 tim=132486678502
hv=3665109320 ad='69bd9bcc'
insert into big values (1, 1, 1, 1, 1)
END OF STMT
PARSE
#29:c=78125,e=92231,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=132486678492
BINDS #29:
WAIT #29: nam='db file sequential read' ela= 10293 file#=4 block#=54025
blocks=1 obj#=50039 tim=132486701502
EXEC
#29:c=0,e=14805,p=1,cr=1,cu=3,mis=0,r=1,dep=0,og=1,tim=132486703768

Gints Plivna
http://www.gplivna.eu/


I think you meant
SET AUTOTRACE TRACEONLY EXPLAIN
and not
SET AUTOTRACE ON EXPLAIN
as you posted.

Regards
Michel Cadot
Back to top
Gints Plivna
*nix forums beginner


Joined: 21 Jun 2006
Posts: 27

PostPosted: Tue Jul 18, 2006 6:54 am    Post subject: Re: autotrace Reply with quote
Quote:
I think you meant
SET AUTOTRACE TRACEONLY EXPLAIN
and not
SET AUTOTRACE ON EXPLAIN
as you posted.

Regards
Michel Cadot

Oh yea, of course, mea culpa :)

Gints Plivna
http://www.gplivna.eu/
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [8 Posts] View previous topic :: View next topic
The time now is Fri Nov 21, 2008 12:53 pm | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Does Autotrace give the actual execution plan? HKLN Server 6 Thu Jul 06, 2006 6:17 am
No new posts no able to "set autotrace on" pls chk error peter Server 6 Sun May 14, 2006 9:10 am
No new posts Strange behavior on enabling autotrace.. nirav Server 10 Tue Dec 13, 2005 1:02 pm

Loans | Mortgage Calculator | Car Finance | Debt Consolidation | Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1549s ][ Queries: 14 (0.0523s) ][ GZIP on - Debug on ]