Sequence Sequence Slow

[alexandervinson]

Personal my problem is as follows.
Part of my program runs a sequence many times, to be more specific about 300,000 times.
My problem is that when I analyze the trace of running one of the slower instructions is exactly the select sequence.nextval from dual
below follows the part of the trace of this query

Select all

 
SELECT sequence.NEXTVAL  
FROM 
 DUAL 
call     count       cpu    elapsed       disk      query    current        rows 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
Parse        1      0.00       0.00          0          0          0           0 
Execute 295452      3.00  172596.12          0          0          0           0 
Fetch   295452      2.53  193335.00          0          0         30      295452 
------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
total   590905      5.53  365931.12          0          0         30      295452

I have already tried several cache values ??(100,500,10,000 - ten thousand same) and the cost is always high

I do not know if it is some configuration of my bank ... something with the dual perhaps ... The queries the tables are very fast ... it is the one that is slow.
Thanks
Alex

[dr_gori]

Topic moved to DBA (left ghost topic here)
: -O

[fbifabio]

To say what may be happening I would have to know your BD environment (SGA settings etc), but it may be that the caching sequence values ??are being discarded from memory before they are used, so you end up. Having cache, what would expedite the recovery time of sequence values. On my blog I wrote an article that explains how to fix (PINAR) the values ??of the sequence cache in memory: http://www.fabioprado.net/2010/09/cache ... ences.html.
att,
Fabio Prado www.fabioprado.net

[alexandervinson]

Hi fabio ... your link está broken .. sends again please

[alexandervinson]

With Lincence for Slanga Paraense .........
"Mare" This is to make matters worse ....
I made the following test

Select all

 
declare  
 v number; 
begin 
  dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')); 
  for i in 1..30000 loop 
    SELECT  sequence.NEXTVAL  
     into v from dual; 
  end loop; 
  dbms_output.put_line(to_char(sysdate,'hh24:mi:ss')); 
end;  

And returned in a second .....................
now gave the neurons came into screw ... because the tkprof shows such a bad performance QD executed in a more complex procedure?

[alexandervinson]

Talk personal,
good the new thing is that I made a simple select tkprof of the sequence ... and look at the absurd that appeared ....
but the procedure runs fast .. so I I think I'm reading the results in an incorrect way .... Alquem can help me interpret this?




My interpretation from the beginning was that it was taking 20337 seconds, since in the header the file tells me Elapsed = Elapsed Time in Seconds Execution. However this is incorrect since the block executed in less than 1 followed ....

[ecalvetti]

Alexandervinson

gives a researched there to ensure ... but I believe that this metric in Oracle is measured in microseconds. In that case, you would have to split the result per 1000000 to give the result in seconds.
Let's see if someone with more experience agrees or disagrees ...

ABS.,
Calvetti
] OCA - Oracle Certified Associates
ITIL Foundation

[alexandervinson]

Personal,
I did a test ...
I did the TKProf of a block that only had the sequence call. And for my spoiling the steep number on the Elapsed column continued giant. But the block executed very quickly.

So I conclude that, although in the title of the TKProf file is written

Select all

elapsed  = elapsed time in seconds executing

This is not true if it is too fast, then it must be in milliseconds as informed by Ecalvetti

Thanks to all for aid

Alex