Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 01, 2003 - 11:12 am UTC

Last updated: December 28, 2003 - 11:13 am UTC

Version: 817

Viewed 1000+ times

You Asked

Hi Tom
I havent come across a guy like you in my 4 yrs of little Oracle DBA experience who just knows everything about Oracle . So I guess if someone faces you as an interviewer that would be the REAL ultimate challenge.
Do you have a few of your favorite or really important questions that you would ask an Oracle DBA for 817/9i?

Im not sure if anyone has asked you this question in the past but that would make my day(lots of my days).

Thank You
TS

and Tom said...

search for


"interview questions"

on this site. And you know what -- every day I learn that I don't know everything about Oracle. Everyday -- literally -- I learn something new. Either from researching a question -- or being shown I was wrong about some conclusion I had made...

Question everything you hear or see from everyone. Don't believe ANYTHING unless it comes with a proof -- and even then try to break the proof.

Jonathan Lewis just did that to me. At the hotsos conference, I showed that using session_cached_cursors can be used to alleviate some of the overhead of recursive SQL (say the sql oracle does to update seq$ when you get a sequence).

Well, it was the truth, but not the WHOLE truth. My "proof" worked 100% of the time, he just moved things about a tiny bit and the "proof" fell apart. It turns out -- in the some circumstances session_cached_cursors can help but not in the general case!

Question Authority. Don't believe anything unless you can see it, prove it, experience it. If a test provided doesn't reproduce similar findings for you -- or by modifying the test just a little breaks it -- the hypothesis is incorrect.



Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What was Jonathon's Response?

A reader, March 01, 2003 - 7:03 pm UTC

Tom,

Would you mind sharing with us how Jonathon "broke" the proof of what you said. This should be interesting and (needless to say) highly educating!

Tom Kyte
March 01, 2003 - 7:13 pm UTC 

Sure, here was the script I ran, using my runstats framework.  The goal was to use this to show that session_cached_cursors reduces the work done, reduces the LATCHES used by heavy recursive sql processing.  I use a NOCACHE sequence to force artifically high recursive SQL:

ops$tkyte@ORA920> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.


ops$tkyte@ORA920> create or replace view stats
  2  as select 'STAT...' || a.name name, b.value
  3        from v$statname a, v$mystat b
  4       where a.statistic# = b.statistic#
  5      union all
  6      select 'LATCH.' || name,  gets
  7        from v$latch;

View created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> drop sequence seq;

Sequence dropped.

ops$tkyte@ORA920> create sequence seq nocache;

Sequence created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920> /*
DOC>now, we'll see about tons of recursive sql. one easy way to see that
DOC>will be with a sequence that is not cached (updates to seq$ --
DOC>tons of them)...
DOC>*/
ops$tkyte@ORA920> pause

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6         l_seq   number;
  7  begin
  8      insert into run_stats select 'before', stats.* from stats;
  9
 10      l_start := dbms_utility.get_time;
 11      execute immediate 'alter session set session_cached_cursors=0';
 12      for i in 1 .. 10000
 13      loop
 14          select seq.nextval into l_seq from dual;
 15      end loop;
 16      l_run1 := (dbms_utility.get_time-l_start);
 17      dbms_output.put_line( l_run1 || ' hsecs' );
 18
 19      insert into run_stats select 'after 1', stats.* from stats;
 20      l_start := dbms_utility.get_time;
 21      execute immediate 'alter session set session_cached_cursors=100';
 22      for i in 1 .. 10000
 23      loop
 24          select seq.nextval into l_seq from dual;
 25      end loop;
 26      l_run2 := (dbms_utility.get_time-l_start);
 27      dbms_output.put_line( l_run2 || ' hsecs' );
 28      dbms_output.put_line
 29      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 30
 31      insert into run_stats select 'after 2', stats.* from stats;
 32      execute immediate 'alter session set session_cached_cursors=0';
 33  end;
 34  /
1620 hsecs
1466 hsecs
run 1 ran in 110.5% of the time

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> pause

ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11     and abs( (c.value-b.value)-(b.value-a.value)) > 250
 12   order by abs( (c.value-b.value)-(b.value-a.value))
 13  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
STAT...buffer is not pinned co      10354      10084       -270
unt

STAT...session logical reads        70823      70533       -290
LATCH.enqueue hash chains           40620      40928        308
STAT...consistent gets              40459      40132       -327
LATCH.session allocation              535       1216        681
STAT...recursive calls             151473     150566       -907
LATCH.cache buffers chains         194203     195242       1039
STAT...redo size                  7267048    7268796       1748<b>
STAT...session cursor cache hi          0      10004      10004</b>
ts

LATCH.library cache pin            121182     100900     -20282
LATCH.shared pool                   71584      51083     -20501
LATCH.library cache pin alloca      40792        692     -40100
tion

LATCH.library cache                162211     111753     -50458
STAT...session pga memory max       65536          0     -65536

14 rows selected.


ops$tkyte@ORA920> select run1, run2, diff, round( run1/run2*100, 2 ) run1_pct_of_run2
  2    from (
  3  select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
  4         sum( (c.value-b.value)-(b.value-a.value)) diff
  5    from run_stats a, run_stats b, run_stats c
  6   where a.name = b.name
  7     and b.name = c.name
  8     and a.runid = 'before'
  9     and b.runid = 'after 1'
 10     and c.runid = 'after 2'
 11     and (c.value-a.value) > 0
 12     and (c.value-b.value) <> (b.value-a.value)
 13     and a.name like 'LATCH%'
 14         )
 15  /

      RUN1       RUN2       DIFF RUN1_PCT_OF_RUN2
---------- ---------- ---------- ----------------
    784762     656052    -128710           119.62

<b>so, that seems to nail it right?  got 10,000 cached cursor hits -- those are my updates to seq$ that are happening.  Got lots less latches too -- all well and fine.

Well, Jonathan ran the test like this</b>


ops$tkyte@ORA920> alter system flush shared_pool;

System altered.

ops$tkyte@ORA920> /*
DOC>now, we'll see about tons of recursive sql. one easy way to see that
DOC>will be with a sequence that is not cached (updates to seq$ --
DOC>tons of them)...
DOC>*/
ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5      l_seq   number;
  6  begin
  7      insert into run_stats select 'before', stats.* from stats;
  8
  9      l_start := dbms_utility.get_time;
 10      execute immediate 'alter session set session_cached_cursors=0';
 11      for i in 1 .. 10000
 12      loop
 13          select seq.nextval into l_seq from dual;
 14      end loop;
 15      l_run1 := (dbms_utility.get_time-l_start);
 16      dbms_output.put_line( l_run1 || ' hsecs' );
 17  end;
 18  /
1803 hsecs

PL/SQL procedure successfully completed.

<b>see, broke it into 2 bits here</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5      l_seq   number;
  6  begin
  7      insert into run_stats select 'after 1', stats.* from stats;
  8      l_start := dbms_utility.get_time;
  9      execute immediate 'alter session set session_cached_cursors=100';
 10      for i in 1 .. 10000
 11      loop
 12          select seq.nextval into l_seq from dual;
 13      end loop;
 14      l_run2 := (dbms_utility.get_time-l_start);
 15      dbms_output.put_line( l_run2 || ' hsecs' );
 16      dbms_output.put_line
 17      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 18
 19      insert into run_stats select 'after 2', stats.* from stats;
 20      execute immediate 'alter session set session_cached_cursors=0';
 21  end;
 22  /
1654 hsecs
run 1 ran in % of the time

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11     and abs( (c.value-b.value)-(b.value-a.value)) > 250
 12   order by abs( (c.value-b.value)-(b.value-a.value))
 13  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
STAT...buffer is not pinned co      10346      10075       -271
unt

STAT...free buffer requested            5        298        293
LATCH.library cache load lock         308         12       -296
STAT...session logical reads        70793      70497       -296
STAT...bytes sent via SQL*Net         300          0       -300
to client

STAT...consistent gets              40446      40120       -326
LATCH.enqueue hash chains           40992      40626       -366
LATCH.cache buffers lru chain         263        784        521
LATCH.checkpoint queue latch          993       1539        546
LATCH.child cursor hash table         744         90       -654
STAT...recursive calls             151407     150535       -872
STAT...bytes received via SQL*       1106          0      -1106
Net from client

LATCH.row cache enqueue latch       21520      20210      -1310
STAT...CPU used by this sessio       1532          0      -1532
n

STAT...CPU used when call star       1532          0      -1532
ted

LATCH.row cache objects             61794      60226      -1568
LATCH.library cache pin alloca      43698      40528      -3170
tion

LATCH.library cache pin            126892     120692      -6200
LATCH.cache buffers chains         201966     195086      -6880
LATCH.shared pool                   78339      70891      -7448
LATCH.enqueues                        128      10046       9918
LATCH.session idle bit                 11      10004       9993
LATCH.session allocation            35296      21060     -14236
STAT...session pga memory max       65536          0     -65536
STAT...redo size                  7267188    8668040    1400852

25 rows selected.

<b>See we are missing the cached cursor hits, we didn't get them and worse:</b>


ops$tkyte@ORA920> select run1, run2, diff, round( run1/run2*100, 2 ) run1_pct_of_run2
  2    from (
  3  select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
  4         sum( (c.value-b.value)-(b.value-a.value)) diff
  5    from run_stats a, run_stats b, run_stats c
  6   where a.name = b.name
  7     and b.name = c.name
  8     and a.runid = 'before'
  9     and b.runid = 'after 1'
 10     and c.runid = 'after 2'
 11     and (c.value-a.value) > 0
 12     and (c.value-b.value) <> (b.value-a.value)
 13     and a.name like 'LATCH%'
 14         )
 15  /

      RUN1       RUN2       DIFF RUN1_PCT_OF_RUN2
---------- ---------- ---------- ----------------
    857464     835761     -21703            102.6

<b>we didn't fix the latching.... So, basically it "works sometimes" but not consistently</b>


Conclusion -- it can work to help sometimes -- so the "whole truth" is:

session_cached_cursors will help you increase the scalability of YOUR application when YOU don't cache the cursors.  It can SOMETIMES, under SOME conditions help with recursive SQL.


Fortunately, I was most interested in the "YOUR APPLICATION" with "YOUR UNCHACHED cursors" -- the recursive sql was just a nice side effect, that apparently isn't 100% effective.

(please don't ask me why the above is true, it just is ;)

Gotta finish the job :-)

Connor McDonald, March 02, 2003 - 2:54 am UTC

"please don't ask me why the above is true, it just is"

come on - you can't just leave it that!


Tom Kyte
March 02, 2003 - 9:23 am UTC

oh yes i can ;)


I just did for that one. (it looks flaky, but I have a hard time decribing it as a "product issue")

Nice

Jacob, December 28, 2003 - 2:46 am UTC

Hello sir,
How can we use dbms_stats package to generate statistics
for all tables in a schema?Does that need a loop structure?
like
sql> begin
for x in (select table_name from tabs) loop
exec dbms_stats.get_table_stats('scott',x.table_name)
....
Moreover I feel comfortable with
sql>exec dbms_utility.analyze_schema('SCOTT','COMPUTE');
Do you find any performance tradeoff in using so?
Waiting for your reply.
Bye!

Tom Kyte
December 28, 2003 - 11:13 am UTC

hows about checking out all of the features of dbms_stats -- one of which is dbms_stats.gather_schema_stats

...... see the docs!

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.