RE: [Fwd: Re: 10046 trace - unaccounted for time] - long raw

  • From: <Jay.Miller@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 18 Sep 2008 14:59:16 -0400

Does anyone know if the below mentioned anomaly of LOB data operations
showing as unaccounted also applies to LONG RAWs?  I'm ending up with a
trace that has a lot of unaccounted for time and it's on tables with
LONG RAW datatypes.

I suspect that's what's happening but wanted to confirm it.

Thanks, 

Jay Miller

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tanel Poder
Sent: Friday, August 08, 2008 7:04 AM
To: kmoore@xxxxxxxxxxxx
Cc: 'oracle-l'
Subject: RE: [Fwd: Re: 10046 trace - unaccounted for time]

The reason why you didn't see any cursor using that CPU time could be
due how some Oracle calls are handled.

For example when you run "describe xyz" in sqlplus, that describe
command does not go through normal cursor parse/execute/fetch cycle. In
fact there is no library cache cursor associated with this call at all
(describe calls opidsc directly in kernel).

The same goes with LOB data operations. The INSERT INTO ... statement
itself is parsed and a shared cursor is generated for it, but the lob
data load operations will bypass this cursor execution mechanism and
call the data loading functions directly. The direct load interface
probably behaves the same way.

In summary - as these operations bypass normal cursor execution, they
also don't hit any cursor instrumentation codepath. However if these
operations hit some waits, then these are instrumented and reported as
waits for CURSOR #0 (which is kind of pseudocursor).

Some relevant info is here:

http://vsadilovskiy.wordpress.com/2007/10/29/trace-and-statspack-lob-1/

--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Keith Moore
> Sent: Friday, August 08, 2008 02:50
> To: kmoore@xxxxxxxxxxxx
> Cc: oracle-l
> Subject: Re:[Fwd: Re: 10046 trace - unaccounted for time]
> 
> Can anyone tell me whether moving the blob to a different tablespace 
> will cause the index to be rebuilt. I know the index moves so it seems

> like it would need to be rebuilt but just wanted to confirm.
> 
> Keith
> 
> > Thanks. We have confirmed it is some type of LOB index corruption.
> >
> > Insert to BLOB stored inline works fine.
> > Insert to BLOB stored out of line (one row, 5MB blob) takes
> 4 minutes
> > Insert to BLOB stored out of line (new copy of table) takes
> 0.125 seconds.
> >
> > We are now rebuilding table.
> >
> > Thanks to everyone for their help.
> >
> > Keith
> >
> 
> 
> --
> //www.freelists.org/webpage/oracle-l
> 
> 

--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts:

  • » RE: [Fwd: Re: 10046 trace - unaccounted for time] - long raw