Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: autotrace
"Gints Plivna" <gints.plivna_at_gmail.com> a écrit dans le message de news: 1153165664.992848.207430_at_m73g2000cwd.googlegroups.com...
Michel Cadot wrote:
> "Gints Plivna" <gints.plivna_at_gmail.com> a écrit dans le message de news: 1153137348.573607.108590_at_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
Received on Mon Jul 17 2006 - 23:48:51 CDT