Re: Tuning LOB: Where is all my time going?

I've looked at v$session_wait and the only waits that I catch are:

direct path read (lob)
direct path write (lob)

I've verified the tkprof timings for these events against the raw data.

I've looked through the raw trace and see no unusual waits listed, nor time spent between trace lines.

I looked at v$system_event, I took two snapshots and reviewed the differences. Nothing jumped out at me.

The end of this message shows some of the cursors that were parsed whilst the CTAS was in progress.

I am still scratching my head.

Cheers,

Mat.

PARSING IN CURSOR #6 len=198 dep=1 uid=0 oct=3 lid=0 tim=18446744072113602016 hv=2703824309 ad='21f9086c' 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
...
PARSING IN CURSOR #7 len=331 dep=1 uid=0 oct=6 lid=0 tim=18446744072113604229 hv=4151580176 ad='21b411f8' update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null) END OF STMT
...
PARSING IN CURSOR #7 len=116 dep=1 uid=0 oct=3 lid=0 tim=18446744072113605946 hv=431456802 ad='21f8a8d0' select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
...
PARSING IN CURSOR #8 len=205 dep=1 uid=0 oct=2 lid=0 tim=18446744072113606787 hv=2296808019 ad='21ad9834' insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16, :17)
END OF STMT
...
PARSING IN CURSOR #8 len=36 dep=1 uid=0 oct=3 lid=0 tim=18446744072113609261 hv=1254950678 ad='21ad543c' select file# from file$ where ts#=:1
END OF STMT
...
PARSING IN CURSOR #8 len=254 dep=1 uid=0 oct=2 lid=0 tim=18446744072113611958 hv=2689189869 ad='21ad20a0' insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,scanhint, hwmincr, spare1) values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:17,0,NULL,:17)) END OF STMT "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ckhirl$8v9$1_at_sparta.btinternet.com>...
> There may be some issues with the way
> that Oracle is failing to record time against
> the direct path reads and writes.
>
> Take a look at the trace file and see if that
> gives you any clues - in particular with lines
> containing "tim=" timestamps, which will be
> microsecond timing.
>
> Other checks:
> Repeat the experiment doing rapid:
>
> select * from v$session_wait where sid =
> {sid of session doing the ctas}
>
> to see if there is something in v$session_wait
> that is not getting into the trace file.
>
>
> Take a couple of snapshots of v$system_event
> to see if the "lost time" is somehow being recorded
> against some other session (such as dbwr or I/O
> slaves).
>
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated Sept 19th
>

> Received on Thu Oct 14 2004 - 02:40:20 CDT