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

If you have buffer cache space, try caching the LOB segment.

alter table BIZDOCCONTENT lob (CONTENTSYS_LOB0000026859C00005$$ ) (cache);

"Matt Butler" <mathewbutler_at_yahoo.com> wrote in message news:19f48a45.0410132340.450fcc1a_at_posting.google.com...
> 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 Mon Oct 18 2004 - 12:28:20 CDT