If you want to execute just the script search for "=======". Statements below this lines are the actual scripts which I used to execute this.
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 22 16:29:04 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> set lines 200
SQL> col comp_name format A30
SQL> set pages 9999
SQL> conn / as sysdba
Connected.
SQL> drop user test_user1 cascade;
User dropped.
SQL> drop user test_user2 cascade;
User dropped.
SQL> drop user test_user3 cascade;
User dropped.
SQL> create user test_user1 identified by password;
User created.
SQL> create user test_user2 identified by password;
User created.
SQL> create user test_user3 identified by password;
User created.
SQL> alter user test_user1 quota unlimited on users;
User altered.
SQL> alter user test_user2 quota unlimited on users;
User altered.
SQL> alter user test_user3 quota unlimited on users;
User altered.
SQL> grant create session, create table, create procedure to test_user1;
Grant succeeded.
SQL> grant create session, create table, create procedure to test_user2;
Grant succeeded.
SQL> grant create session, create synonym, alter session to test_user3;
Grant succeeded.
SQL> grant execute on ctx_ddl to test_user1;
Grant succeeded.
SQL> grant execute on dbms_random to test_user1;
Grant succeeded.
SQL> grant execute on ctx_ddl to test_user2;
Grant succeeded.
SQL> grant execute on dbms_random to test_user2;
Grant succeeded.
SQL> grant execute on dbms_monitor to test_user3;
Grant succeeded.
SQL> select comp_id, comp_name, version from dba_Registry where comp_id = 'OLS';
COMP_ID COMP_NAME VERSION
------------------------------ ------------------------------ ------------------------------
OLS Oracle Label Security 10.2.0.4.0
SQL> conn test_user1/password
Connected.
SQL> create table test_user1_tab
(
sno number not null,
random_text Varchar2(10),
xml_text xmltype,
olslabel Number Not null,
constraint pk_test_user1_tab primary key (sno)
);
Table created.
SQL> create index test_user1_tab_ctx on test_user1_tab
(
xml_text
)
indextype is ctxsys.context;
Index created.
SQL> create index test_user1_tab_norm_idx on test_user1_tab (random_text);
Index created.
SQL> insert into test_user1_tab
(
sno, random_text, xml_text, olslabel
)
select level, 'ABCD' || level, xmltype(''||level||''), 1
from dual
connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_Stats.gather_table_Stats(NULL,'TEST_USER1_TAB', cascade => true);
PL/SQL procedure successfully completed.
SQL> exec ctx_ddl.sync_index('TEST_USER1_TAB_CTX');
PL/SQL procedure successfully completed.
SQL> create or replace procedure test_proc_user1
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user1.test_user1_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user1;
/
Procedure created.
SQL> grant select on test_user1_tab to test_user2;
Grant succeeded.
SQL> grant execute on test_proc_user1 to test_user3;
Grant succeeded.
SQL> conn test_user2/password
Connected.
SQL> create table test_user2_tab
(
sno number not null,
random_text Varchar2(10),
xml_text xmltype,
olslabel Number not null,
constraint pk_test_user2_tab primary key (sno)
);
Table created.
SQL> create index test_user2_tab_ctx on test_user2_tab
(
xml_text
)
indextype is ctxsys.context;
Index created.
SQL> create index test_user2_tab_norm_idx on test_user2_tab (random_text);
Index created.
SQL> insert into test_user2_tab
(
sno, random_text, xml_text, olslabel
)
select level, 'ABCD' || level, xmltype(''||level||''), 1
from dual
connect by level <= 1000;
1000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_Stats.gather_table_Stats(NULL,'TEST_USER2_TAB', cascade => true);
PL/SQL procedure successfully completed.
SQL> exec ctx_ddl.sync_index('TEST_USER2_TAB_CTX');
PL/SQL procedure successfully completed.
SQL> create or replace procedure test_proc_user1
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user1.test_user1_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user1;
/
Procedure created.
SQL> create or replace procedure test_proc_user2
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user2.test_user2_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user2.test_user2_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user2.test_user2_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user2;
/
Procedure created.
SQL> grant execute on test_proc_user2 to test_user3;
Grant succeeded.
SQL> grant execute on test_proc_user1 to test_user3;
Grant succeeded.
SQL> conn lbacsys/lbacsys
Connected.
SQL> exec sa_sysdba.drop_policy(policy_name => 'TEST_POLICY');
PL/SQL procedure successfully completed.
SQL> exec sa_sysdba.create_policy(policy_name => 'TEST_POLICY', column_name => 'olslabel' );
PL/SQL procedure successfully completed.
SQL> exec sa_components.create_level(policy_name => 'TEST_POLICY', level_num => 10, short_name => 'FLAT', long_name => 'FLAT');
PL/SQL procedure successfully completed.
SQL> exec sa_label_admin.create_label(policy_name => 'TEST_POLICY', label_tag => 1, label_value => 'FLAT');
PL/SQL procedure successfully completed.
SQL> begin
sa_policy_admin.apply_table_policy(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER1' ,
table_name => 'TEST_USER1_TAB' ,
table_options => 'READ_CONTROL,LABEL_UPDATE,WRITE_CONTROL'
);
end;
/
PL/SQL procedure successfully completed.
SQL> begin
sa_policy_admin.apply_table_policy
(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER2' ,
table_name => 'TEST_USER2_TAB' ,
table_options => 'READ_CONTROL,LABEL_UPDATE,WRITE_CONTROL'
);
end;
/
PL/SQL procedure successfully completed.
SQL> begin
sa_user_admin.set_user_labels
(
policy_name => 'TEST_POLICY',
user_name => 'TEST_USER3' ,
max_read_label => 'FLAT'
);
end;
/
PL/SQL procedure successfully completed.
SQL> conn test_user1/password
Connected.
SQL> select count(*) from test_user1_tab;
COUNT(*)
----------
0
SQL> conn test_user2/password
Connected.
SQL> select count(*) from test_user2_tab;
COUNT(*)
----------
0
SQL> conn / as sysdba
Connected.
SQL> select count(*) from test_user1.test_user1_tab;
COUNT(*)
----------
1000
SQL> select count(*) from test_user2.test_user2_tab;
COUNT(*)
----------
1000
SQL> alter system flush shared_pool;
System altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
$ sqlplus test_user3/password
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 22 16:34:18 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter session set tracefile_identifier = 'TRC_PROC_USER1';
Session altered.
SQL> exec dbms_monitor.session_trace_enable(waits => true, binds => true);
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec test_user1.test_proc_user1;
Found 438
Found 945
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 22 16:34:50 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter system flush shared_pool;
System altered.
SQL> conn test_user3/password
Connected.
SQL> alter session set tracefile_identifier = 'TRC_PROC_USER2';
Session altered.
SQL> exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec test_user2.test_proc_user2;
Found 307
Found 347
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 22 16:35:32 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter system flush shared_pool;
System altered.
SQL> conn test_user3/password
Connected.
SQL> alter session set tracefile_identifier = 'TRC_PROC_USER1_IN_USER2';
Session altered.
SQL> exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec test_user2.test_proc_user1;
Found 505
Found 754
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
$ sqlplus lbacsys/lbacsys
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 22 16:37:32 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> begin
sa_policy_admin.disable_table_policy
(
policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER1' ,
table_name => 'TEST_USER1_TAB'
);
end;
/
PL/SQL procedure successfully completed.
SQL> conn test_user1/password
Connected.
SQL> select count(*) from test_user1_tab;
COUNT(*)
----------
1000
SQL> conn / as sysdba
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> conn test_user3/password
Connected.
SQL> alter session set tracefile_identifier = 'TRC_PROC_USER1_IN_USER2_NOPOLICY';
Session altered.
SQL> exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec test_user2.test_proc_user1;
Found 948
Found 625
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
$
Trace file Information :
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 22 16:47:07 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: xxxxxx_ora_23491_TRC_PROC_USER1.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
...
PARSING IN CURSOR #51 len=56 dep=1 uid=148 oct=3 lid=148 tim=25679881746375 hv=3742817072 ad='9a544c90'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
END OF STMT
SQL> select * from table(dbms_xplan.display_cursor('260kauggjdqth',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 260kauggjdqth, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
Plan hash value: 1966213943
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("SNO"=:B1)
19 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 148 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=203 us)
1 INDEX UNIQUE SCAN PK_TEST_USER1_TAB (cr=2 pr=0 pw=0 time=62 us)(object id 86271)
PARSING IN CURSOR #33 len=78 dep=1 uid=148 oct=3 lid=148 tim=25679878095557 hv=1386526246 ad='9a565c20'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
END OF STMT
SQL> select sql_id from v$sql where hash_Value = 1386526246;
SQL_ID
-------------
0dhx3959a9dj6
SQL> select * from table(dbms_xplan.display_cursor('0dhx3959a9dj6',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0dhx3959a9dj6, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
Plan hash value: 347541470
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.60 | 651 |
|* 2 | DOMAIN INDEX | TEST_USER1_TAB_CTX | 1 | | 1 |00:00:00.60 | 650 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("CTXSYS"."CONTAINS"("TEST_USER1_TAB"."SYS_NC00006$",:B1,1)>0)
19 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.10 0 0 0 0
Execute 1 0.35 0.28 0 273 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.46 0.38 0 273 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 148 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=576 pr=2 pw=0 time=372495 us)
0 DOMAIN INDEX TEST_USER1_TAB_CTX (cr=576 pr=2 pw=0 time=372476 us)
PARSING IN CURSOR #52 len=67 dep=1 uid=148 oct=3 lid=148 tim=25679881844100 hv=3041396959 ad='8a074e18'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
END OF STMT
SQL> select sql_id from v$sql where hash_value = 3041396959;
SQL_ID
-------------
3tb0jkqunh26z
SQL> set lines 200
SQL> set pages 999
SQL> select * from table(dbms_xplan.display_cursor('3tb0jkqunh26z',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3tb0jkqunh26z, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
Plan hash value: 3438376433
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | TEST_USER1_TAB_NORM_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("RANDOM_TEXT" LIKE :B1)
filter("RANDOM_TEXT" LIKE :B1)
20 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 148 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=211 us)
1 INDEX RANGE SCAN TEST_USER1_TAB_NORM_IDX (cr=2 pr=0 pw=0 time=121 us)(object id 86284)
...
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.13 0.11 0 83 0 0
Execute 4 0.12 0.10 0 113 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.25 0.22 0 196 0 4
Misses in library cache during parse: 3
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 3.70 9.46
log file sync 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 428 0.69 0.55 0 0 0 0
Execute 1070 3.20 3.20 0 991 3 146
Fetch 2036 0.28 1.00 37 4393 0 2514
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3534 4.17 4.77 37 5384 3 2660
Misses in library cache during parse: 91
Misses in library cache during execute: 85
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 37 0.64 0.69
45 user SQL statements in session.
874 internal SQL statements in session.
919 SQL statements in session.
********************************************************************************
Trace file: xxxxxx_ora_23491_TRC_PROC_USER1.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
45 user SQL statements in trace file.
874 internal SQL statements in trace file.
919 SQL statements in trace file.
97 unique SQL statements in trace file.
25098 lines in trace file.
14 elapsed seconds in trace file.
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 22 16:50:02 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: xxxxxx_ora_23496_TRC_PROC_USER2.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
PARSING IN CURSOR #34 len=78 dep=1 uid=149 oct=3 lid=149 tim=25680220380883 hv=1154448836 ad='97a942a0'
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
END OF STMT
SQL> SELECT SQL_ID, child_number from v$sql where hash_Value = 1154448836;
SQL_ID CHILD_NUMBER
------------- ------------
4rbrck12cyzf4 0
SQL> select * from table(dbms_xplan.display_cursor('4rbrck12cyzf4',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4rbrck12cyzf4, child number 0
-------------------------------------
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
Plan hash value: 3135681325
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER2_TAB | 1 | 1 | 1 |00:00:00.61 | 653 |
|* 2 | DOMAIN INDEX | TEST_USER2_TAB_CTX | 1 | | 1 |00:00:00.61 | 652 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("CTXSYS"."CONTAINS"("TEST_USER2_TAB"."SYS_NC00006$",:B1,1)>0)
19 rows selected.
SELECT *
FROM
TEST_USER2.TEST_USER2_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.11 0 0 0 0
Execute 1 0.25 0.28 0 273 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.36 0.39 0 273 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID TEST_USER2_TAB (cr=576 pr=0 pw=0 time=375692 us)
0 DOMAIN INDEX TEST_USER2_TAB_CTX (cr=576 pr=0 pw=0 time=375673 us)
PARSING IN CURSOR #52 len=56 dep=1 uid=149 oct=3 lid=149 tim=25680223861131 hv=1793670910 ad='978458f0'
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE SNO = :B1
END OF STMT
SQL> SELECT SQL_ID, child_number from v$sql where hash_Value = 1793670910;
SQL_ID CHILD_NUMBER
------------- ------------
dtwp1dxpfkgry 0
SQL> select * from table(dbms_xplan.display_cursor('dtwp1dxpfkgry',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dtwp1dxpfkgry, child number 0
-------------------------------------
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE SNO = :B1
Plan hash value: 823824981
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER2_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_TEST_USER2_TAB | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("SNO"=:B1)
19 rows selected.
SELECT *
FROM
TEST_USER2.TEST_USER2_TAB WHERE SNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER2_TAB (cr=3 pr=0 pw=0 time=171 us)
1 INDEX UNIQUE SCAN PK_TEST_USER2_TAB (cr=2 pr=0 pw=0 time=44 us)(object id 86293)
PARSING IN CURSOR #53 len=67 dep=1 uid=149 oct=3 lid=149 tim=25680223955789 hv=611282564 ad='97a72d58'
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE RANDOM_TEXT LIKE :B1
END OF STMT
SQL> SELECT SQL_ID, child_number from v$sql where hash_Value = 611282564;
SQL_ID CHILD_NUMBER
------------- ------------
2has8tck6yvn4 0
SQL> select * from table(dbms_xplan.display_cursor('2has8tck6yvn4',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2has8tck6yvn4, child number 0
-------------------------------------
SELECT * FROM TEST_USER2.TEST_USER2_TAB WHERE RANDOM_TEXT LIKE :B1
Plan hash value: 1177287277
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER2_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | TEST_USER2_TAB_NORM_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("RANDOM_TEXT" LIKE :B1)
filter("RANDOM_TEXT" LIKE :B1)
20 rows selected.
SELECT *
FROM
TEST_USER2.TEST_USER2_TAB WHERE RANDOM_TEXT LIKE :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER2_TAB (cr=3 pr=0 pw=0 time=211 us)
1 INDEX RANGE SCAN TEST_USER2_TAB_NORM_IDX (cr=2 pr=0 pw=0 time=121 us)(object id 86306)
...
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.10 0.10 0 82 0 0
Execute 4 0.09 0.10 0 113 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.19 0.20 0 195 0 4
Misses in library cache during parse: 3
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 2.76 7.36
log file sync 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 440 0.52 0.55 0 0 0 0
Execute 1070 3.40 3.69 0 991 3 146
Fetch 2037 0.31 0.30 0 4389 0 2401
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3547 4.23 4.55 0 5380 3 2547
Misses in library cache during parse: 91
Misses in library cache during execute: 85
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: shared pool 16 0.05 0.44
45 user SQL statements in session.
874 internal SQL statements in session.
919 SQL statements in session.
********************************************************************************
Trace file: xxxxxx_ora_23496_TRC_PROC_USER2.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
45 user SQL statements in trace file.
874 internal SQL statements in trace file.
919 SQL statements in trace file.
97 unique SQL statements in trace file.
25081 lines in trace file.
12 elapsed seconds in trace file.
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 22 16:51:17 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: xxxxxx_ora_23523_TRC_PROC_USER1_IN_USER2.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
PARSING IN CURSOR #34 len=78 dep=1 uid=149 oct=3 lid=149 tim=25680469050743 hv=1386526246 ad='91458d10'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
END OF STMT
SQL> select * from table(dbms_Xplan.display_cursor('0dhx3959a9dj6',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0dhx3959a9dj6, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
Plan hash value: 301559538
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
|* 1 | VIEW | TEST_USER1_TAB | 1 | 100 | 1 |00:00:02.20 | 3707 |
|* 2 | TABLE ACCESS FULL| TEST_USER1_TAB | 1 | 100 | 1000 |00:00:00.02 | 23 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CTXSYS"."CONTAINS"("XML_TEXT",:B1,1)>0)
2 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
19 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.11 0 0 0 0
Execute 1 0.14 0.14 0 132 0 0
Fetch 1 0.28 0.28 0 2024 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.52 0.53 0 2156 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 VIEW TEST_USER1_TAB (cr=3706 pr=0 pw=0 time=1669270 us)
1000 TABLE ACCESS FULL TEST_USER1_TAB (cr=23 pr=0 pw=0 time=18212 us)
PARSING IN CURSOR #46 len=56 dep=1 uid=149 oct=3 lid=149 tim=25680471791511 hv=3742817072 ad='978d0278'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
END OF STMT
SQL> select sql_id, child_number from v$sql where hash_Value = 3742817072;
SQL_ID CHILD_NUMBER
------------- ------------
260kauggjdqth 0
SQL> select * from table(dbms_Xplan.display_cursor('260kauggjdqth',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 260kauggjdqth, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
Plan hash value: 1966213943
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("SNO"=:B1)
19 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=181 us)
1 INDEX UNIQUE SCAN PK_TEST_USER1_TAB (cr=2 pr=0 pw=0 time=54 us)(object id 86271)
PARSING IN CURSOR #41 len=67 dep=1 uid=149 oct=3 lid=149 tim=25680471887936 hv=3041396959 ad='8dffebb0'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
END OF STMT
SQL> select sql_id, child_number from v$sql where hash_Value = 3041396959;
SQL_ID CHILD_NUMBER
------------- ------------
3tb0jkqunh26z 0
SQL> select * from table(dbms_xplan.display_cursor('3tb0jkqunh26z',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3tb0jkqunh26z, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
Plan hash value: 3438376433
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | TEST_USER1_TAB_NORM_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OLSLABEL"=TO_NUMBER(SYS_CONTEXT('LBAC$4_LAB','LBAC$MINLABEL')))
2 - access("RANDOM_TEXT" LIKE :B1)
filter("RANDOM_TEXT" LIKE :B1)
20 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=245 us)
1 INDEX RANGE SCAN TEST_USER1_TAB_NORM_IDX (cr=2 pr=0 pw=0 time=136 us)(object id 86284)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.11 0 85 0 0
Execute 4 0.10 0.10 0 113 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.13 0.21 0 198 0 4
Misses in library cache during parse: 3
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 4.68 9.86
log file sync 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 321 0.42 0.43 0 0 0 0
Execute 848 2.36 2.31 0 172 3 140
Fetch 1594 0.57 0.52 0 5294 0 2086
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2763 3.35 3.27 0 5466 3 2226
Misses in library cache during parse: 77
Misses in library cache during execute: 72
30 user SQL statements in session.
679 internal SQL statements in session.
709 SQL statements in session.
********************************************************************************
Trace file: xxxxxx_ora_23523_TRC_PROC_USER1_IN_USER2.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
30 user SQL statements in trace file.
679 internal SQL statements in trace file.
709 SQL statements in trace file.
83 unique SQL statements in trace file.
19980 lines in trace file.
13 elapsed seconds in trace file.
TKPROF: Release 10.2.0.4.0 - Production on Tue Dec 22 16:52:29 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: xxxxxx_ora_23609_TRC_PROC_USER1_IN_USER2_NOPOLICY.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
PARSING IN CURSOR #35 len=78 dep=1 uid=149 oct=3 lid=149 tim=25680783052614 hv=1386526246 ad='91458d10'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
END OF STMT
SQL> select sql_id, child_number from v$sql where hash_Value = 1386526246;
SQL_ID CHILD_NUMBER
------------- ------------
0dhx3959a9dj6 0
SQL> select * from table(dbms_xplan.display_cursor('0dhx3959a9dj6',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0dhx3959a9dj6, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
Plan hash value: 347541470
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.60 | 639 |
|* 2 | DOMAIN INDEX | TEST_USER1_TAB_CTX | 1 | | 1 |00:00:00.60 | 638 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CTXSYS"."CONTAINS"("TEST_USER1_TAB"."SYS_NC00006$",:B1,1)>0)
18 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE CONTAINS(XML_TEXT, :B1 , 1) > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.26 0.24 0 273 0 0
Fetch 1 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.28 0.27 0 273 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=564 pr=0 pw=0 time=354159 us)
0 DOMAIN INDEX TEST_USER1_TAB_CTX (cr=564 pr=0 pw=0 time=354142 us)
PARSING IN CURSOR #48 len=56 dep=1 uid=149 oct=3 lid=149 tim=25680786686690 hv=3742817072 ad='978d0278'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
END OF STMT
SQL> select sql_id, child_number from v$sql where hash_Value = 3742817072;
SQL_ID CHILD_NUMBER
------------- ------------
260kauggjdqth 0
SQL> select * from table(dbms_xplan.display_cursor('260kauggjdqth',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 260kauggjdqth, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
Plan hash value: 1966213943
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | PK_TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SNO"=:B1)
18 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE SNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=98 us)
1 INDEX UNIQUE SCAN PK_TEST_USER1_TAB (cr=2 pr=0 pw=0 time=41 us)(object id 86271)
PARSING IN CURSOR #49 len=67 dep=1 uid=149 oct=3 lid=149 tim=25680786780942 hv=3041396959 ad='8dffebb0'
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
END OF STMT
SQL> select sql_id, child_number from v$sql where hash_Value = 3041396959;
SQL_ID CHILD_NUMBER
------------- ------------
3tb0jkqunh26z 0
SQL> select * from table(dbms_xplan.display_cursor('3tb0jkqunh26z',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3tb0jkqunh26z, child number 0
-------------------------------------
SELECT * FROM TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
Plan hash value: 3438376433
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_USER1_TAB | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | TEST_USER1_TAB_NORM_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RANDOM_TEXT" LIKE :B1)
filter("RANDOM_TEXT" LIKE :B1)
19 rows selected.
SELECT *
FROM
TEST_USER1.TEST_USER1_TAB WHERE RANDOM_TEXT LIKE :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 149 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_USER1_TAB (cr=3 pr=0 pw=0 time=193 us)
1 INDEX RANGE SCAN TEST_USER1_TAB_NORM_IDX (cr=2 pr=0 pw=0 time=124 us)(object id 86284)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.08 0.08 0 1932 1 0
Execute 4 0.10 0.08 0 107 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.18 0.16 0 2039 1 4
Misses in library cache during parse: 3
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 2.60 6.32
log file sync 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 576 0.76 0.68 0 0 6 0
Execute 1411 4.26 4.24 0 1156 250 210
Fetch 2540 0.38 0.35 1 5424 0 2760
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4527 5.40 5.28 1 6580 256 2970
Misses in library cache during parse: 138
Misses in library cache during execute: 129
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
47 user SQL statements in session.
1191 internal SQL statements in session.
1238 SQL statements in session.
********************************************************************************
Trace file: xxxxxx_ora_23609_TRC_PROC_USER1_IN_USER2_NOPOLICY.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
47 user SQL statements in trace file.
1191 internal SQL statements in trace file.
1238 SQL statements in trace file.
145 unique SQL statements in trace file.
32541 lines in trace file.
11 elapsed seconds in trace file.
SQL> show parameter opti
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string asynch
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string FIRST_ROWS_100
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
============================================================================================
set lines 200
col comp_name format A30
set pages 9999
conn / as sysdba
drop user test_user1 cascade;
drop user test_user2 cascade;
drop user test_user3 cascade;
create user test_user1 identified by password;
create user test_user2 identified by password;
create user test_user3 identified by password;
alter user test_user1 quota unlimited on users;
alter user test_user2 quota unlimited on users;
alter user test_user3 quota unlimited on users;
grant create session, create table, create procedure to test_user1;
grant create session, create table, create procedure to test_user2;
grant create session, create synonym, alter session to test_user3;
grant execute on ctx_ddl to test_user1;
grant execute on dbms_random to test_user1;
grant execute on ctx_ddl to test_user2;
grant execute on dbms_random to test_user2;
grant execute on dbms_monitor to test_user3;
select comp_id, comp_name, version from dba_Registry where comp_id = 'OLS';
conn test_user1/password
create table test_user1_tab
(
sno number not null,
random_text Varchar2(10),
xml_text xmltype,
olslabel Number Not null,
constraint pk_test_user1_tab primary key (sno)
);
create index test_user1_tab_ctx on test_user1_tab
(
xml_text
)
indextype is ctxsys.context;
create index test_user1_tab_norm_idx on test_user1_tab (random_text);
insert into test_user1_tab
(
sno, random_text, xml_text, olslabel
)
select level, 'ABCD' || level, xmltype(''||level||''), 1
from dual
connect by level <= 1000;
commit;
exec dbms_Stats.gather_table_Stats(NULL,'TEST_USER1_TAB', cascade => true);
exec ctx_ddl.sync_index('TEST_USER1_TAB_CTX');
create or replace procedure test_proc_user1
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user1.test_user1_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user1;
/
grant select on test_user1_tab to test_user2;
grant execute on test_proc_user1 to test_user3;
conn test_user2/password
create table test_user2_tab
(
sno number not null,
random_text Varchar2(10),
xml_text xmltype,
olslabel Number not null,
constraint pk_test_user2_tab primary key (sno)
);
create index test_user2_tab_ctx on test_user2_tab
(
xml_text
)
indextype is ctxsys.context;
create index test_user2_tab_norm_idx on test_user2_tab (random_text);
insert into test_user2_tab
(
sno, random_text, xml_text, olslabel
)
select level, 'ABCD' || level, xmltype(''||level||''), 1
from dual
connect by level <= 1000;
commit;
exec dbms_Stats.gather_table_Stats(NULL,'TEST_USER2_TAB', cascade => true);
exec ctx_ddl.sync_index('TEST_USER2_TAB_CTX');
create or replace procedure test_proc_user1
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user1.test_user1_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user1.test_user1_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user1;
/
create or replace procedure test_proc_user2
is
l_Search_Xml_String Varchar2(10);
l_sno Number;
l_Search_String Varchar2(10);
Begin
l_Search_Xml_String := to_char(round(dbms_random.value(1,1000)));
l_Search_String := 'ABCD' || to_char(round(dbms_random.value(1,1000))) || '%';
l_sno := round(dbms_random.value(1,1000));
For i in (select * from test_user2.test_user2_tab
where contains(xml_text, l_search_xml_string, 1) > 0
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user2.test_user2_tab
where sno = l_sno
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
For i in (select * from test_user2.test_user2_tab
where random_Text like l_Search_String
)
Loop
dbms_output.put_line('Found ' || i.sno );
End Loop;
End test_proc_user2;
/
grant execute on test_proc_user2 to test_user3;
grant execute on test_proc_user1 to test_user3;
conn lbacsys/lbacsys
exec sa_sysdba.drop_policy(policy_name => 'TEST_POLICY');
exec sa_sysdba.create_policy(policy_name => 'TEST_POLICY', column_name => 'olslabel' );
exec sa_components.create_level(policy_name => 'TEST_POLICY', level_num => 10, short_name => 'FLAT', long_name => 'FLAT');
exec sa_label_admin.create_label(policy_name => 'TEST_POLICY', label_tag => 1, label_value => 'FLAT');
begin
sa_policy_admin.apply_table_policy(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER1' ,
table_name => 'TEST_USER1_TAB' ,
table_options => 'READ_CONTROL,LABEL_UPDATE,WRITE_CONTROL'
);
end;
/
begin
sa_policy_admin.apply_table_policy
(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER2' ,
table_name => 'TEST_USER2_TAB' ,
table_options => 'READ_CONTROL,LABEL_UPDATE,WRITE_CONTROL'
);
end;
/
begin
sa_user_admin.set_user_labels
(
policy_name => 'TEST_POLICY',
user_name => 'TEST_USER3' ,
max_read_label => 'FLAT'
);
end;
/
conn test_user1/password
select count(*) from test_user1_tab;
conn test_user2/password
select count(*) from test_user2_tab;
conn / as sysdba
select count(*) from test_user1.test_user1_tab;
select count(*) from test_user2.test_user2_tab;
Rem to force the query to hard parse
alter system flush shared_pool;
exit
sqlplus test_user3/password
alter session set tracefile_identifier = 'TRC_PROC_USER1';
alter session set events '10053 trace name context forever, level 1';
exec dbms_monitor.session_trace_enable(waits => true, binds => true);
set serveroutput on
exec test_user1.test_proc_user1;
exit
Rem to force it to do hard parse again
sqlplus / as sysdba
alter system flush shared_pool;
conn test_user3/password
alter session set tracefile_identifier = 'TRC_PROC_USER2';
alter session set events '10053 trace name context forever, level 1';
exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
set serveroutput on
exec test_user2.test_proc_user2;
exit
Rem to force it to do hard parse again
sqlplus / as sysdba
alter system flush shared_pool;
conn test_user3/password
alter session set tracefile_identifier = 'TRC_PROC_USER1_IN_USER2';
alter session set events '10053 trace name context forever, level 1';
exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
set serveroutput on
exec test_user2.test_proc_user1;
exit
sqlplus lbacsys/lbacsys
begin
sa_policy_admin.disable_table_policy
(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER1' ,
table_name => 'TEST_USER1_TAB'
);
end;
/
conn test_user1/password
select count(*) from test_user1_tab;
conn / as sysdba
alter system flush shared_pool;
conn test_user3/password
alter session set tracefile_identifier = 'TRC_PROC_USER1_IN_USER2_NOPOLICY';
alter session set events '10053 trace name context forever, level 1';
exec dbms_monitor.session_Trace_enable(waits => true, binds => true);
set serveroutput on
exec test_user2.test_proc_user1;
exit
conn lbacsys/lbacsys
begin
sa_policy_admin.enable_table_policy
(policy_name => 'TEST_POLICY',
schema_name => 'TEST_USER1' ,
table_name => 'TEST_USER1_TAB'
);
end;
/