Date: Fri, 08 Aug 2008 19:03:59 +0800
From: Tanel Poder <tanel.poder.003@mail.ee>
Subject: RE: [Fwd: Re: 10046 trace - unaccounted for time]
In-reply-to: <4749.206.227.160.10.1218134974.squirrel@lady.zephyrus.com>
To: kmoore@zephyrus.com
Cc: "'oracle-l'" <oracle-l@freelists.org>
Message-id: <7E714864A7624020BDD4350C45CF4A1B@windows01>
MIME-version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <14069.206.227.160.10.1218131668.squirrel@lady.zephyrus.com> <4749.206.227.160.10.1218134974.squirrel@lady.zephyrus.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: tanel.poder.003@mail.ee

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@freelists.org 
> [mailto:oracle-l-bounce@freelists.org] On Behalf Of Keith Moore
> Sent: Friday, August 08, 2008 02:50
> To: kmoore@zephyrus.com
> 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
> >
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 

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