Sunday, February 8, 2009

Cost-Based Oracle - Fundamentals: Test cases applied against 10.2.0.4 and 11.1.0.6 (Part 2)

Back to part 1

This part of this series covers chapter 2 of the book, the "Tablescan".

Running the test cases of chapter 2 against 10.2.0.4 and 11.1.0.6 revealed that apart from a few oddities all of the results were consistent with those reported by Jonathan for the previous releases.

In the following I'll attempt to cover the most important findings.

Effects of Block Sizes

One particular issue that the book already covered in detail is the effect of using non-standard block sizes on the optimizer's estimates.

Let's start with re-iterating what has already been written by Jonathan. Using traditional I/O only costing (CPU costing disabled), 8KB default blocksize and a db_file_multiblock_read_count = 8 the following costs were reported for a full table scan of an 80MB table segment:


Block Size Cost Adjusted dbf_mbrc Number of blocks
---------- ---------- ----------------- ----------------
2KB 2,439 16.39 40,000
4KB 1,925 10.40 20,000
8KB 1,519 6.59 10,000
16KB 1,199 4.17 5,000


What can be deduced from these findings? The optimizer is consistent with the runtime engine in that it adjusts the db_file_multiblock_read_count (dbf_mbrc) according to the block size, e.g. the 2KB block size segment is scanned using an dbf_mbrc of 32, because the default block size of 8KB times the dbf_mbrc of 8 results in a multi-block read request of 64KB.

A dbf_mbrc of 32 results in an adjusted dbf_mbrc used for cost calculation of 16.39, as can be seen in the previous examples of the book, presumably because the optimizer assumes that multi-block reads requesting more blocks have a higher probability of getting reduced due to blocks already in the buffer cache. It's this assumption that tempts to deduce that there is a element of truth that using a larger block size might be able to reduce the number of I/O requests.

Consider that in the case of smaller blocks (e.g. 2KB) some of them are already in the buffer cache due to single row/block access. This could actually require to split a 32 block multi-block read request into multiple smaller ones, increasing the number of I/O requests required to read the data.

On the other hand, in the same scenario using a larger block size (e.g. 16KB) the single rows accessed might possibly be located in less blocks, leading to less fragmentation of the 4 block multi-block read requests.

But the usage of the larger block size in this scenario might also lead to much greater portion of the segment competing for the buffer cache with other concurrent requests due to the fact that a single row request requires the whole (larger) block to be read possibly lowering the overall effectiveness of the buffer cache, so it probably depends largely on the actual usage pattern and it's likely that both effects outweigh each other in most cases.

Let's move on to CPU costing and the effects of non-standard block sizes. Again the same 80MB table segment is read. The dbf_mbrc is set to 8, and in case of the WORKLOAD system statistics ("Normal" column below) the values have been set deliberately to mimic the NOWORKLOAD statistics, which means a MBRC of 8, sreadtim = 12 and mreadtim = 26. The results shown can be reproduced using the script "tablescan_04.sql".


Block size Noworkload Normal Number of Blocks
---------- ---------- ------ ----------------
2KB 7,729 10,854 40,000
4KB 4,387 5,429 20,000
8KB 2,717 2,717 10,000
16KB 1,881 1,361 5,000


Let's recall the formula to calculate the I/O cost with CPU costing enabled, reduced to the multi-block read requests part that matter in this particular case:

Cost = (number of multi-block read requests)*mreadtim/sreadtim

And here is the formula that is used to calculate mreadtim and sreadtim in the case of NOWORKLOAD statistics, which records only ioseektim (disk random seek time in milliseconds) and iotrfspeed (disk transfer speed in bytes per millisecond) for the I/O calculation:

MBRC = dbf_mbrc
sreadtim = ioseektim + db_block_size / iotfrspeed
mreadtim = ioseektim + dbf_mbrc * db_block_size / iotfrspeed

NOWORKLOAD system statistics

Slotting in the values we have, considering that we have non-standard block sizes and an adjusted dbf_mbrc at runtime:

2KB, NOWORKLOAD:

MBRC = 32 (adjusted for 2KB block size)
sreadtim = 10 + 2,048 / 4,096 = 10 + 0.5 = 10.5
mreadtim = 10 + 32 * 2,048 / 4,096 = 26

Cost = (40,000 / 32) * 26 / 10.5 = 3,095 (rounded), actually => 7,729 !

Clearly, something is going wrong here, the difference can't be explained by some kind of "rounding" issues.

After fiddling a bit around, it becomes obvious that the optimizer uses a different set of values for the formula:

2KB, NOWORKLOAD (actual):

MBRC = 32 (adjusted for 2KB block size)
sreadtim = 10 + 8192 / 4,096 = 10 + 2 = 12
mreadtim = 10 + 32 * 8,192 / 4,096 = 74 (!)

Cost = (40,000 / 32) * 74 / 12 = 7,708 (rounded), actually => 7,729

That's close enough to explain the remaining part with the CPU cost. So the optimizer uses an odd mixture of adjusted and unadjusted values which might be deliberate, but seem to be questionable at least, in particular a multi-block read request calculated to take 74ms.

The MBRC is adjusted, but obviously the default block size is used instead of the non-standard block size.

Let's check the results for the 16KB block size, first by looking at what we expect to get when slotting in the obvious values:

16KB, NOWORKLOAD:

MBRC = 4 (adjusted for 16KB block size)
sreadtim = 10 + 16,384 / 4,096 = 10 + 4 = 14
mreadtim = 10 + 4 * 16,384 / 4,096 = 26

Cost = (5,000 / 4) * 26 / 14 = 2,321 (rounded), actually => 1,881 !

Again the difference is significant, let's try the modified formula:

16KB, NOWORKLOAD (actual):

MBRC = 4 (adjusted for 16KB block size)
sreadtim = 10 + 8,192 / 4,096 = 10 + 2 = 12
mreadtim = 10 + 4 * 8,192 / 4,096 = 18

Cost = (5,000 / 4) * 18 / 12 = 1,875 (rounded), actually => 1,881

So it looks like my theory applies and the obvious question remains why the optimizer uses a quite unintuitive and odd set of values for the cost calculation of the NOWORKLOAD statistics.

Looking at the results when using the correct non-standard block size in the formula it's obvious that the variation in cost calculation for the non-standard block sizes would be much smaller than the actual variation observed. The actual smallest cost encountered was 1,881 for 16KB block size and 7,729 for 2KB. Using the correct block size this would be 2,321 (+6 CPU cost) for 16KB block size and 3,095 (+21 CPU cost) for the 2KB block size, which is much closer to the default block size cost of 2,717.

I've added another test case "tablescan_04a.sql" to the code depot which is not part of the official distribution. Its purpose is to check how the optimizer deals with the formula when dealing with a query based on multiple objects residing in multiple block sizes. The results show that for each individual object above formula is still used to estimate the cost, which makes the behaviour even more questionable why the optimizer is then not using the correct block sizes for each individual calculation.

WORKLOAD system statistics

What about the results of the WORKLOAD system statistics? How is it going to deal with the non-standard block sizes?

Let's check the formula:

MBRC = 8
mreadtim = 26
sreadtim = 12

as set in "tablescan_04.sql"

2KB, WORKLOAD:

Assuming an adjusted MRBC of 32 for the 2KB block size:

Cost = (40,000/32)*26/12 = 2,708 (rounded), actually 10,854 !

So again, something is clearly not following the assumptions. Let's try with the MBRC left unchanged (unadjusted):

2KB, WORKLOAD (actual):

Cost = (40,000/8)*26/12 = 10,833 (rounded), actually 10,854

Checking the same for the 16KB block size:

16KB, WORKLOAD:

Assuming an adjusted MRBC of 4 for the 16KB block size:

Cost = (5,000/4)*26/12 = 2,708 (rounded), actually 1,361 !

16KB, WORKLOAD (actual):

Cost = (5,000/8)*26/12 = 1,354 (rounded), actually 1,361

So the obvious problem of the WORKLOAD system statistics with non-standard block sizes is that the MBRC is not adjusted, but the number of blocks decrease/increase according to the block size used, resulting in a even larger variation in cost than the NOWORKLOAD statistics.

So I can only repeat what Jonathan has already written in his book: Be very cautious with using different block sizes for different objects. In particular with CPU costing enabled the cost based optimizer uses some questionable values to calculate the I/O cost leading to large variations in costs that very likely don't reflect the actual cost difference encountered at runtime. The result is that objects that reside in larger block sizes obviously are going to favor full table scans due to the lower cost, and the opposite applies to objects in smaller non-standard block sizes.

CPU costing and predicate re-ordering

One of the unique features of CPU costing is the ability to cost the predicate evaluation order and possibly perform a re-ordering to lower the cost (which can be prevented by using the ORDERED_PREDICATES hint). Running the test case provided by Jonathan against 11.1.0.6 and 11.1.0.7 shows a oddity regarding the costing of the TO_NUMBER function. Obviously the cost of 100 for a single call to TO_NUMBER doesn't apply in 11.1. This might be deliberate but seems to be questionable; other conversion functions like TO_CHAR or TO_DATE were still showing the same cost as in the other versions.

Here is the output from 10.2.0.4 of the "cpu_costing.sql" script:


Predicted cost (9.2.0.6): 1070604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 1,070,604

Predicted cost (9.2.0.6): 762787

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 762,786

Predicted cost (9.2.0.6): 1070232

Filter Predicate CPU cost
------------------------------------------------------------ ------------
TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 1,070,231

Predicted cost (9.2.0.6): 762882

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 762,881

Predicted cost (9.2.0.6): 770237

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 770,236

Predicted cost (9.2.0.6): 785604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 785,604

Left to its own choice of predicate order

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 762,786

And one last option where the coercion on v1 is not needed
Predicted cost (9.2.0.6): 770604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"V1"='1' AND "N2"=18 AND "N1"=998 770,604


Apart from some minor rounding issues the results correspond to those from 9.2 and 10.1.

Here's the output running the same against 11.1.0.7:


Predicted cost (9.2.0.6): 1070604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998 770,604

Predicted cost (9.2.0.6): 762787

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1 762,781

Predicted cost (9.2.0.6): 1070232

Filter Predicate CPU cost
------------------------------------------------------------ ------------
TO_NUMBER("V1")=1 AND "N1"=998 AND "N2"=18 770,231

Predicted cost (9.2.0.6): 762882

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 762,781

Predicted cost (9.2.0.6): 770237

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N2"=18 AND "N1"=998 AND TO_NUMBER("V1")=1 770,231

Predicted cost (9.2.0.6): 785604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N2"=18 AND TO_NUMBER("V1")=1 AND "N1"=998 770,604

Left to its own choice of predicate order

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"N1"=998 AND TO_NUMBER("V1")=1 AND "N2"=18 762,781

And one last option where the coercion on v1 is not needed
Predicted cost (9.2.0.6): 770604

Filter Predicate CPU cost
------------------------------------------------------------ ------------
"V1"='1' AND "N2"=18 AND "N1"=998 770,604


It's rather obvious that the TO_NUMBER function is not costed in the same way as previously, up to the point where 11g comes to a different conclusion when left on its own choice, and I doubt that the change is for the better, because the TO_NUMBER function is more often evaluated than necessary.

I've added some more test cases ("cpu_costing_2.sql" to "cpu_costing_5.sql") that show that other functions don't show this different treatment, so it seems to be particular issue of the TO_NUMBER function costing.

Single table selectivity, unknown bind variables and range comparisons

This difference showed up as a side effect of the "partition.sql" script. The recent releases (9.2.0.8, 10.2.0.4 and 11.1.0.6/7) seem to have been extended by an additional sanity check when applying the default 5% based selectivity of bind variables when performing range comparisons.

As pointed out by Jonathan it's quite unrealistic that a range comparison to an unknown bind variable results in a estimated cardinality lower than the cardinality estimated for an individual value. Consider e.g. a table consisting of 1,200 rows, 12 distinct values and a uniform distribution. A single value corresponds to 100 rows, but a range comparison to an unknown bind variables resulted previously in a reported cardinality of 60 (1/20 resp. 5% hard coded).

This is no longer the case with 9.2.0.8, 10.2.0.4 and 11.1.0.6/7: Obviously a lower limit of 1/NUM_DISTINCT applies, in this particular case this would be 100 instead of 60.

The rule seems to be more complex, and is again different in 11.1.0.6/7 than in previous versions, in particular when dealing with multiple predicates, but that's something to be left for Chapter 3 which deals with the "Single Table selectivity".

Updated code depot

You can download the updated code depot containing all scripts and the spool results from here: My homepage (SQLTools++, an open source lightweight SQL Oracle GUI for Windows)

The original code depot (still maintained by Jonathan) can be found here.

Friday, February 6, 2009

How to force a hard parse

It's not uncommon to be confronted with a bad behaving application that doesn't share its SQLs by using bind variables but uses literals instead. This leads to well known issues like increased CPU usage, library cache contention etc. due to the hard parse activities required.

On a recent OTN thread the question was raised, what to do if you are confronted with the opposite, a bad behaving application that shares SQL using bind variables that should not be shared? Although this might sound a bit odd at first, in pre-11g databases this "over-use" of bind variables can lead to severe issues, in particular if you have long running queries, possibly together with skewed data. Sharing SQL in such scenarios can lead to poor performance if the optimizer re-uses unsuitable execution plans that otherwise would not be used if a hard parse was forced using literals.

Obviously fixing the application and using literals where appropriate is the correct approach, but what can be done if you can't change the code, e.g. in case it's a third party application?

One possible solution is to "mis-use" the row level security feature of Oracle (RLS, often also called Virtual Private Database, VPD, also known as FGAC, Fine Grained Access Control) to modify the queries on the fly and therefore forcing a hard parse every time a query is using one of the objects the RLS policy is applied to.

The following code snippet demonstrates three slightly different approaches that were mentioned in the thread, all using the same basic principle: Add a non-filtering predicate to the query that makes the query unsharable resp. unique, e.g. like "1 = 1". The third one was contributed by Jonathan Lewis, by the way.


-- Global context approach
create table hard_parse_test
as
select * from all_objects
where rownum <= 1000;

exec dbms_stats.gather_table_stats(null, 'hard_parse_test')

create or replace context ctx_force_hard_parse
using pkg_rls_force_hard_parse
accessed globally;

create or replace package pkg_rls_force_hard_parse is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
procedure clear_context;
end pkg_rls_force_hard_parse;
/

create or replace package body pkg_rls_force_hard_parse is
g_counter pls_integer;

procedure clear_context
is
begin
dbms_session.clear_context('CTX_FORCE_HARD_PARSE', NULL, 'G_COUNTER');
end clear_context;

procedure set_context
is
begin
dbms_session.set_context('ctx_force_hard_parse', 'G_COUNTER', to_char(g_counter, 'TM'));
end set_context;

procedure get_context
is
begin
g_counter := to_number(SYS_CONTEXT('ctx_force_hard_parse', 'G_COUNTER'));
end get_context;

function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
is
s_predicate varchar2(100);
begin
get_context;
if g_counter is null then
g_counter := 0;
end if;
g_counter := g_counter + 1;
if g_counter > 1000000 then
g_counter := 1;
end if;
set_context;
-- s_predicate := '1 = 1';
s_predicate := to_char(g_counter, 'TM') || ' = ' || to_char(g_counter, 'TM');
-- s_predicate := 'object_type = ''TABLE''';
return s_predicate;
end force_hard_parse;
begin
get_context;
if g_counter is null then
g_counter := 0;
set_context;
end if;
end pkg_rls_force_hard_parse;
/

exec DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse.force_hard_parse', 'select');

-- random approach
create table hard_parse_test_rand
as
select * from all_objects
where rownum <= 1000;

exec dbms_stats.gather_table_stats(null, 'hard_parse_test_rand')

create or replace package pkg_rls_force_hard_parse_rand is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
end pkg_rls_force_hard_parse_rand;
/

create or replace package body pkg_rls_force_hard_parse_rand is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
is
s_predicate varchar2(100);
n_random pls_integer;
begin
n_random := round(dbms_random.value(1, 1000000));
-- s_predicate := '1 = 1';
s_predicate := to_char(n_random, 'TM') || ' = ' || to_char(n_random, 'TM');
-- s_predicate := 'object_type = ''TABLE''';
return s_predicate;
end force_hard_parse;
end pkg_rls_force_hard_parse_rand;
/

exec DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test_rand', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse_rand.force_hard_parse', 'select');

-- sequence approach
create sequence hard_parse_test_seq maxvalue 1000000 cycle;

create table hard_parse_test_sequence
as
select * from all_objects
where rownum <= 1000;

exec dbms_stats.gather_table_stats(null, 'hard_parse_test_sequence')

create or replace package pkg_rls_force_hard_parse_seq is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2;
end pkg_rls_force_hard_parse_seq;
/

create or replace package body pkg_rls_force_hard_parse_seq is
function force_hard_parse (in_schema varchar2, in_object varchar2) return varchar2
is
s_predicate varchar2(100);
n_seq pls_integer;
begin
select hard_parse_test_seq.nextval
into n_seq
from dual;
-- s_predicate := '1 = 1';
s_predicate := to_char(n_seq, 'TM') || ' = ' || to_char(n_seq, 'TM');
-- s_predicate := 'object_type = ''TABLE''';
return s_predicate;
end force_hard_parse;
end pkg_rls_force_hard_parse_seq;
/

exec DBMS_RLS.ADD_POLICY (USER, 'hard_parse_test_sequence', 'hard_parse_policy', USER, 'pkg_rls_force_hard_parse_seq.force_hard_parse', 'select');

-- trace
alter session set tracefile_identifier = 'hard_parse_test_session_loop1';

exec dbms_monitor.session_trace_enable

declare
begin
for i in 1..1000 loop
for rec in (select * from hard_parse_test_sequence) loop
exit;
end loop;
end loop;
end;
/

exec dbms_monitor.session_trace_disable


If you check the trace files you'll see that the query in the PL/SQL loop got hard parsed for each execution, so this looks promising. Stress testing the implementation by running the loop from multiple sessions simultaneously showed that it seems to work well, and effectively prevents the sharing of the SQLs.

Using Tom Kyte's RUNSTATS package shows that there are not too many differences in resource consumption of the different approaches:


-- statistics
set timing on echo on serveroutput on trimspool on

spool hard_parse_force_rls_testcase.log

alter session set nls_language = 'AMERICAN';

alter system flush shared_pool;

exec runstats_pkg.rs_start

declare
begin
for i in 1..1000 loop
for rec in (select * from hard_parse_test) loop
exit;
end loop;
end loop;
end;
/

exec runstats_pkg.rs_middle

declare
begin
for i in 1..1000 loop
for rec in (select * from hard_parse_test_rand) loop
exit;
end loop;
end loop;
end;
/

exec runstats_pkg.rs_stop

spool off

-- statistics
set timing on echo on serveroutput on trimspool on

spool hard_parse_force_rls_testcase2.log

alter session set nls_language = 'AMERICAN';

alter system flush shared_pool;

exec runstats_pkg.rs_start

declare
begin
for i in 1..1000 loop
for rec in (select * from hard_parse_test_sequence) loop
exit;
end loop;
end loop;
end;
/

exec runstats_pkg.rs_middle

declare
begin
for i in 1..1000 loop
for rec in (select * from hard_parse_test_rand) loop
exit;
end loop;
end loop;
end;
/

exec runstats_pkg.rs_stop

spool off


Results for Global Context vs Random:


Run1 ran in 1941 hsecs
Run2 ran in 1869 hsecs
run 1 ran in 103,85% of the time

Name Run1 Run2 Diff
LATCH.list of block allocation 0 1 1
LATCH.transaction branch alloc 7 6 -1
STAT...session cursor cache co 1 0 -1
STAT...table scan blocks gotte 2,002 2,001 -1
STAT...redo entries 9 10 1
STAT...calls to kcmgcs 4 5 1
LATCH.user lock 0 1 1
LATCH.transaction allocation 0 1 1
LATCH.longop free list parent 7 6 -1
STAT...opened cursors current 1 2 1
LATCH.session timer 7 6 -1
LATCH.mostly latch-free SCN 7 6 -1
LATCH.threshold alerts latch 1 0 -1
LATCH.lgwr LWN SCN 7 6 -1
LATCH.ASM db client latch 14 12 -2
STAT...cursor authentications 3 5 2
LATCH.job_queue_processes para 0 2 2
LATCH.session idle bit 16 14 -2
LATCH.cache buffers lru chain 7 5 -2
LATCH.shared pool sim alloc 2 0 -2
LATCH.dml lock allocation 0 2 2
LATCH.undo global data 3 6 3
LATCH.parameter list 5 2 -3
STAT...table fetch continued r 3 0 -3
STAT...bytes received via SQL* 1,224 1,227 3
LATCH.redo allocation 16 19 3
LATCH.object queue header oper 50 46 -4
LATCH.In memory undo latch 8 12 4
LATCH.library cache lock alloc 12 16 4
LATCH.parallel query alloc buf 4 0 -4
STAT...consistent changes 37 32 -5
STAT...db block changes 47 42 -5
LATCH.session state list latch 6 0 -6
LATCH.active service list 40 33 -7
STAT...undo change vector size 2,132 2,140 8
STAT...parse time cpu 13 5 -8
LATCH.messages 124 116 -8
STAT...parse count (hard) 1,013 1,004 -9
STAT...parse time elapsed 15 5 -10
STAT...db block gets 39 28 -11
STAT...db block gets from cach 39 28 -11
LATCH.channel operations paren 98 84 -14
STAT...enqueue releases 1,020 1,005 -15
STAT...enqueue requests 1,021 1,005 -16
LATCH.checkpoint queue latch 184 168 -16
LATCH.library cache load lock 60 41 -19
STAT...redo size 2,884 2,904 20
STAT...workarea executions - o 38 14 -24
STAT...table scan rows gotten 178,060 178,030 -30
STAT...cluster key scans 47 8 -39
LATCH.kks stats 1,056 1,015 -41
STAT...rows fetched via callba 58 15 -43
STAT...cluster key scan block 51 8 -43
STAT...hot buffers moved to he 45 0 -45
STAT...workarea memory allocat 34 -12 -46
STAT...sorts (memory) 63 15 -48
STAT...free buffer inspected 49 0 -49
STAT...CPU used by this sessio 1,915 1,856 -59
STAT...parse count (total) 2,137 2,074 -63
STAT...CPU used when call star 1,921 1,857 -64
STAT...recursive cpu usage 1,918 1,853 -65
STAT...index scans kdiixs1 137 67 -70
STAT...Elapsed Time 1,944 1,873 -71
LATCH.SQL memory manager worka 489 416 -73
STAT...DB time 1,945 1,867 -78
STAT...index fetch by key 116 25 -91
STAT...session cursor cache hi 2,157 2,059 -98
STAT...opened cursors cumulati 2,172 2,071 -101
LATCH.enqueues 2,365 2,252 -113
STAT...execute count 3,208 3,094 -114
LATCH.enqueue hash chains 2,368 2,253 -115
STAT...calls to get snapshot s 3,210 3,091 -119
STAT...consistent gets - exami 377 131 -246
STAT...no work - consistent re 2,456 2,183 -273
STAT...buffer is not pinned co 617 230 -387
STAT...table fetch by rowid 555 103 -452
STAT...consistent gets 4,841 4,323 -518
STAT...consistent gets from ca 4,841 4,323 -518
STAT...session logical reads 4,880 4,351 -529
STAT...sorts (rows) 3,173 2,561 -612
STAT...buffer is pinned count 653 9 -644
LATCH.cache buffers chains 9,799 8,728 -1,071
LATCH.simulator hash latch 50 2,023 1,973
STAT...recursive calls 8,243 6,256 -1,987
LATCH.session allocation 7,735 2,065 -5,670
LATCH.global ctx hash table la 6,004 0 -6,004
LATCH.library cache lock 1,011,473 1,003,687 -7,786
LATCH.shared pool simulator 509,871 517,895 8,024
LATCH.library cache pin 34,233 22,204 -12,029
LATCH.library cache 1,061,947 1,040,025 -21,922
STAT...session pga memory max 310,652 196,608 -114,044
STAT...session uga memory 130,928 0 -130,928
LATCH.row cache objects 37,688,620 37,550,193 -138,427
STAT...session uga memory max 299,884 123,440 -176,444
STAT...session pga memory 310,652 0 -310,652
LATCH.shared pool 896,770 283,900 -612,870

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
41,235,708 40,439,510 -796,198 101.97%


Results for Sequence vs. Random:


Run1 ran in 1912 hsecs
Run2 ran in 1863 hsecs
run 1 ran in 102,63% of the time

Name Run1 Run2 Diff
LATCH.compile environment latc 1 0 -1
LATCH.job_queue_processes para 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.parameter list 3 2 -1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.KMG MMAN ready and start 7 6 -1
LATCH.FOB s.o list latch 1 0 -1
LATCH.parameter table allocati 1 0 -1
STAT...cursor authentications 4 3 -1
STAT...session cursor cache co 1 0 -1
STAT...index crx upgrade (posi 2 1 -1
STAT...calls to kcmgcs 4 5 1
STAT...shared hash latch upgra 2 1 -1
LATCH.resmgr:active threads 2 0 -2
LATCH.resmgr:free threads list 2 0 -2
STAT...table fetch continued r 2 0 -2
LATCH.post/wait queue 2 0 -2
LATCH.user lock 2 0 -2
LATCH.dummy allocation 2 0 -2
LATCH.mostly latch-free SCN 9 6 -3
LATCH.lgwr LWN SCN 9 6 -3
LATCH.list of block allocation 3 0 -3
LATCH.transaction allocation 3 0 -3
STAT...change write time 3 0 -3
STAT...parse time elapsed 10 13 3
LATCH.active service list 44 41 -3
STAT...workarea memory allocat -18 -21 -3
LATCH.parallel query alloc buf 4 0 -4
LATCH.PL/SQL warning settings 2,010 2,006 -4
STAT...free buffer requested 8 12 4
STAT...parse time cpu 10 6 -4
STAT...consistent changes 37 32 -5
STAT...bytes received via SQL* 1,233 1,227 -6
LATCH.resmgr group change latc 6 0 -6
LATCH.channel operations paren 98 92 -6
STAT...physical read total IO 0 6 6
STAT...physical read IO reques 0 6 6
STAT...physical reads cache 0 6 6
STAT...physical reads 0 6 6
STAT...user I/O wait time 0 7 7
LATCH.library cache pin alloca 15 7 -8
LATCH.session state list latch 8 0 -8
LATCH.Consistent RBA 9 1 -8
LATCH.active checkpoint queue 15 6 -9
LATCH.simulator lru latch 10 0 -10
STAT...parse count (hard) 1,016 1,006 -10
LATCH.In memory undo latch 25 8 -17
LATCH.cache buffers lru chain 30 12 -18
LATCH.redo allocation 34 15 -19
LATCH.client/application info 24 0 -24
LATCH.shared pool sim alloc 0 24 24
STAT...workarea executions - o 38 13 -25
LATCH.messages 158 126 -32
LATCH.redo writing 54 22 -32
LATCH.library cache lock alloc 43 8 -35
STAT...rows fetched via callba 53 15 -38
STAT...hot buffers moved to he 41 2 -39
STAT...CPU used by this sessio 1,880 1,839 -41
LATCH.SQL memory manager worka 456 412 -44
STAT...recursive cpu usage 1,880 1,835 -45
STAT...cluster key scans 51 5 -46
STAT...CPU used when call star 1,885 1,839 -46
STAT...Elapsed Time 1,914 1,866 -48
LATCH.kks stats 1,084 1,036 -48
STAT...cluster key scan block 55 5 -50
LATCH.object queue header heap 90 36 -54
STAT...DB time 1,915 1,860 -55
STAT...sorts (memory) 68 12 -56
STAT...free buffer inspected 61 0 -61
STAT...index scans kdiixs1 133 66 -67
LATCH.object queue header oper 136 60 -76
STAT...commit cleanouts 101 0 -101
STAT...commit cleanouts succes 101 0 -101
STAT...calls to kcmgas 101 0 -101
STAT...deferred (CURRENT) bloc 101 0 -101
LATCH.session idle bit 123 14 -109
STAT...parse count (total) 2,214 2,071 -143
LATCH.library cache load lock 206 28 -178
LATCH.checkpoint queue latch 420 240 -180
STAT...index fetch by key 217 21 -196
STAT...session cursor cache hi 2,255 2,058 -197
STAT...redo entries 211 10 -201
LATCH.enqueues 2,543 2,334 -209
LATCH.dml lock allocation 214 0 -214
STAT...opened cursors cumulati 2,289 2,067 -222
STAT...no work - consistent re 2,450 2,182 -268
STAT...db block gets from cach 345 30 -315
STAT...db block gets 345 30 -315
STAT...enqueue releases 1,327 1,007 -320
STAT...enqueue requests 1,328 1,007 -321
LATCH.undo global data 322 1 -321
STAT...consistent gets - exami 465 123 -342
STAT...buffer is not pinned co 600 228 -372
STAT...db block changes 451 43 -408
STAT...table fetch by rowid 545 102 -443
STAT...consistent gets 4,924 4,314 -610
STAT...consistent gets from ca 4,924 4,314 -610
STAT...sorts (rows) 3,178 2,561 -617
STAT...buffer is pinned count 654 9 -645
LATCH.enqueue hash chains 3,074 2,336 -738
LATCH.library cache lock 1,008,133 1,007,327 -806
STAT...session logical reads 5,269 4,344 -925
LATCH.simulator hash latch 242 2,027 1,785
STAT...execute count 5,310 3,090 -2,220
STAT...calls to get snapshot s 5,413 3,087 -2,326
LATCH.library cache 1,040,087 1,043,596 3,509
LATCH.library cache pin 18,535 22,182 3,647
LATCH.row cache objects 37,693,219 37,688,109 -5,110
STAT...recursive calls 11,801 6,131 -5,670
LATCH.sequence cache 6,210 0 -6,210
LATCH.cache buffers chains 18,122 8,807 -9,315
LATCH.shared pool simulator 538,704 558,857 20,153
STAT...undo change vector size 22,736 2,148 -20,588
LATCH.session allocation 25,092 2,957 -22,135
STAT...physical read total byt 0 49,152 49,152
STAT...physical read bytes 0 49,152 49,152
STAT...redo size 65,780 2,928 -62,852
STAT...session uga memory 65,464 0 -65,464
STAT...session pga memory 131,072 65,536 -65,536
STAT...session pga memory max 262,144 131,072 -131,072
STAT...session uga memory max 315,052 65,464 -249,588
LATCH.shared pool 702,224 301,771 -400,453

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
41,062,061 40,644,708 -417,353 101.03%


In passing, it looks like that the tests on 10.2.0.4 Win32bit revealed that the 10g RLS code has some built-in check which ignores non-filtering predicates like "1 = 1", at least the predicate doesn't show up in the DBMS_XPLAN.DISPLAY output whereas an actual filter predicate like "OBJECT_TYPE = 'TABLE'" does show up. Still the hard parse is forced, so it's even better if the query is effectively not modified.

It's worth to mention that the RLS policy adds a significant overhead. If you check the trace files you'll notice that the policy function gets called once per parse and obviously twice per execution, so a single parse/execute of a query causes two hard parses and three calls to the policy function.

By the way, Tanel Poder mentioned in the OTN thread that he demonstrated a quite similar solution some time back at HotSOS which I wasn't aware of. His demo shows some enhanced versions of the solution including the ability to check for particular SQLs so that not every SQL is affected. Thanks, Tanel, for the information and link.