... The change from 30% to 15% is partly due to the
initial runs were using exclusivly explicit cursors & I've been able to get
some of the key ones changed to implict (going native on explicit is _almost_
as good, but still time wasted on open/fetch/close)
....
probably - you would see no difference between native and non-native.
When you were doing explicit, you were doing row by row (slow by slow)
When you went implicit, you got an implicit array fetch of 100 rows at a time!
You are comparing apples to flying toaster ovens, you changed more than one thing, you cannot point therefore to anyone thing and say "that did it"
ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 cursor c is select * from stage slow_by_slow;
3 l_rec c%rowtype;
4 begin
5 for x in ( select * from stage implicit ) loop null; end loop;
6 open c;
7 loop fetch c into l_rec; exit when c%notfound; end loop;
8 close c;
9 end;
10 /
PL/SQL procedure successfully completed.
SELECT * FROM STAGE IMPLICIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 501 0.38 0.39 0 1185 0 50005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 503 0.40 0.42 0 1186 0 50005
Rows Row Source Operation
------- ---------------------------------------------------
50005 TABLE ACCESS FULL STAGE (cr=1185 pr=0 pw=0 time=250272 us)
********************************************************************************
SELECT * FROM STAGE SLOW_BY_SLOW
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 50006 0.93 0.74 0 50010 0 50005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50008 0.94 0.75 0 50011 0 50005
Rows Row Source Operation
------- ---------------------------------------------------
50005 TABLE ACCESS FULL STAGE (cr=50010 pr=0 pw=0 time=401436 us)
so, unless you test the same code both ways - I still doubt your numbers are due to ncomp'ing
a test that test's 100,000,000 null loops doesn't impress.
I can show you a procedure that gets a 5x decrease in runtime. It is horrific code, not anything you would actually ever really code. It would be a bad example of native compilation speed ups.
I'd be interested in a real compare of the same base code...