Re: high recursive cpu usage

From: <J.Velikanovs_at_alise.lv>
Date: Thu, 8 Jul 2004 21:35:29 +0300
Message-ID: <OF3F422E20.8060234F-ONC2256ECB.0065325E-C2256ECB.0066D1DF@alise.lv>

Take a look on 10046 trace file.
All statements with dep!=0 are recrucive. Consider example belov.
PL/SQL block is non-recrucive.
SQL (select count(*) into v_n from dual;) is recricive. So it can be usual situation then recrucive statements take most of the time.
Different question if those are DD SQL-s, then this time definetly overhead and needed as much as possible avoided. For example SQL (select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#)
is clasic DD SQL, usualy such SQL-s don't takes segnificat time.

So take a look on TRC file.

SYS:jozh> @bt

PL/SQL procedure successfully completed.

SYS:jozh> declare
SYS:jozh> v_n number;
SYS:jozh> begin
SYS:jozh> select count(*) into v_n from dual;
SYS:jozh> end;
SYS:jozh> /



=====================

PARSING IN CURSOR #1 len=66 dep=0 uid=0 oct=47 lid=0 tim=728752024 hv=421843074 ad='65d460d8'
declare
v_n number;
begin
select count(*) into v_n from dual;
end;
END OF STMT
PARSE
#1:c=30043,e=124554,p=1,cr=37,cu=0,mis=1,r=0,dep=0,og=4,tim=728752010 BINDS #1:

PARSING IN CURSOR #3 len=25 dep=1 uid=0 oct=3 lid=0 tim=728753142 hv=133767796 ad='65d44820'
SELECT count(*) from dual
END OF STMT
PARSE #3:c=0,e=809,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=728753130 BINDS #3:
EXEC #3:c=0,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=728753337 FETCH #3:c=0,e=104,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=728753481

EXEC #1:c=0,e=1392,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=728753577
WAIT #1: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 444 p1=1413697536 p2=1

p3=0

Jurijs
9268222

http://otn.oracle.com/ocm/jvelikanovs.html

"Harvinder Singh" <Harvinder.Singh_at_MetraTech.com> Sent by: oracle-l-bounce_at_freelists.org
08.07.2004 21:21
Please respond to oracle-l

        To:     <oracle-l_at_freelists.org>
        cc: 
        Subject:        high recursive cpu usage

Hi,

We are testing the Insert performance on Oracle as our application do lot of batch inserts daily and simulating the application behavior. I am testing the insert performance into a table tab2 having 4 indexes from 2 sessions both reading from the same table tab1. I am inserting in a batch of 1000 rows at a time and in 1 session we are inserting 1000 batches so total 1M rows inserted. Also we are doing commit after every 1000 rows.
For reading we are using bulk collect with fetch 1000 and for insert we are using forall loop...This is 10g on Win2k with 2 cpu hyper-threading machine (looks 4 cpu)
Now to insert 1M records total cpu used is 80 sec and out of that 76 sec is used by service "recursive cpu usage" and "db file sequential read" wait is taking about 15 sec. Also from the event 10046, Fetch of 1M rows is taking CPU time 15.6 sec and Insert is taking cpu time 66 sec.

What can be the reason for high value of "recursive cpu usage"?

Thanks
--Harvinder

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Thu Jul 08 2004 - 13:39:59 CDT