Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Archive for the ‘Misc.’ Category

Another way to use trace file

with 6 comments

I believe that many of you sometimes have strong need for customized trace analysis.

For instance, see following wait records of 10046 trace file.

...
WAIT #11: nam='buffer busy waits' ela= 4 file#=17 block#=2313 class#=8 obj#=81140 tim=520863116874
WAIT #11: nam='buffer busy waits' ela= 5456 file#=17 block#=2624 class#=1 obj#=81140 tim=520864249735
WAIT #11: nam='buffer busy waits' ela= 6 file#=17 block#=2569 class#=8 obj#=81140 tim=520864249933
...



What I want is to sort the buffer busy waits events by the class# like this:

CLASS#            CNT ELAPSED
---------- ---------- -------
8                1456    3.01
1                 420    1.54
4                   5     .00
35                  2     .00
77                  1     .00



But traditional tkprof has no such things. So what can I do? Hire developer? Do some excelling?

Nope. My approach is totally 100% SQL based, which is quite natural and easy for me. :) Most of all, it’s totally free. :)

Let’s see how I do it.

1. Create directory and array

-- create directory and varchar2 array
col value new_value user_dump_directory;

select value from v$parameter where name = 'user_dump_dest';

create or replace directory user_dump_dir as '&user_dump_directory';

create or replace type varchar2_array as table of varchar2(4000);
/



2. Create pipelined table function with utl_file package. If you’re not familiar with pipelined table function, visit here.

-- create get_trace_file1 function
create or replace function get_trace_file1(s_id number default userenv('sid'))
return varchar2_array
pipelined
as
  v_handle  utl_file.file_type;
  v_filename  varchar2(2000);
  v_line      varchar2(4000);
begin
  -- get exact file_name
  select i.value||'_ora_'||p.spid||'.trc' into v_filename
  from v$process p, v$session s, 
    (select value from v$parameter where name = 'instance_name') i
  where p.addr = s.paddr 
        and s.sid = s_id
  ;
  
  v_handle := utl_file.fopen('USER_DUMP_DIR', v_filename, 'R', 32767);
  
  loop
    begin
      utl_file.get_line(v_handle, v_line);
    exception when no_data_found then
      exit;
    end;
    
    pipe row(v_line);
  end loop;
  
  return;
end;
/

-- create get_trace_file2 function
create or replace function get_trace_file2(file_name in varchar2)
return varchar2_array
pipelined
as
  v_handle  utl_file.file_type;
  v_line      varchar2(20000);
begin
  
  v_handle := utl_file.fopen('USER_DUMP_DIR', file_name, 'R', 32767);
  
  loop
    begin
      utl_file.get_line(v_handle, v_line);
    exception when no_data_found then
      exit;
    end;
    
    pipe row(v_line);
  end loop;
  
  return;
end;
/



3. Good. Let’s play with it

alter session set events '10046 trace name context forever, level 8';

select count(*) from t1;

alter session set events '10046 trace name context off';

select * from table(get_trace_file1);

Dump file c:\oracle\admin\ukja10\udump\ukja10_ora_2820.trc
Thu Mar 19 15:10:10 2009
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1481M/3070M, Ph+PgF:3140M/4960M, VA:1359M/2047M
Instance name: ukja10

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 2820, image: ORACLE.EXE (SHAD)


*** ACTION NAME:() 2009-03-19 15:10:10.109
*** MODULE NAME:(SQL*Plus) 2009-03-19 15:10:10.109
*** SERVICE NAME:(UKJA10) 2009-03-19 15:10:10.109
*** SESSION ID:(157.1205) 2009-03-19 15:10:10.109
=====================
PARSING IN CURSOR #1 len=68 dep=0 uid=61 oct=42 lid=61 tim=172367882897 hv=740818757 ad='27e12b14'
alter session set events '10046 trace name context forever, level 8'
END OF STMT
...



4. Very cool. Now I sort the buffer busy waits event by class# like this.(Actually, not that elegant query)

col class# format a10
col elapsed format 99.99

with t as (
  select /*+ materialize */
    column_value as line, 
    instr(column_value, 'ela=') ei, 
    instr(column_value, 'file#=') fi,
    instr(column_value, 'block#=') bi,
    instr(column_value, 'class#=') ci,
    instr(column_value, 'obj#=') oi,
    instr(column_value, 'tim=') ti
  from table(get_trace_file2('trc_1.trc'))
  --from table(get_trace_file2('trc_2.trc'))
  where 
    instr(column_value, 'WAIT #2: nam=''buffer busy waits''') > 0
    --instr(column_value, 'WAIT #11: nam=''buffer busy waits''') > 0
)
select
  class# 
  , count(1) as cnt
  , sum(elapsed)/1000000 as elapsed
from
  (
    select
      substr(line, ei+4, fi-ei-4) as elapsed,
      substr(line, fi+6, bi-fi-6) as file#,
      substr(line, bi+7, ci-bi-7) as block#,
      substr(line, ci+7, oi-ci-7) as class#,
      substr(line, oi+5, ti-oi-5) as obj#,
      substr(line, ti+4) as tim
    from
      t 
  ) x
group by 
  class#
order by 
  2 desc, 3 desc
;



What I’m actually doing is:

  1. trc_1.trc file contains the 10046 trace result with 40K extent size in ASSM.
  2. trc_2.trc file contains the 10046 trace result with 10M extent size in ASSM.
  3. 10 concurrent sessions insert into the table with 10046 trace enabled.
  4. I’d like to analyze how extent size affects the pattern of buffer contention.

Here is the result.

-- when extent size is 40K
CLASS#            CNT ELAPSED
---------- ---------- -------
8                 285    1.40
1                 215    4.64
4                  42     .01
9                   2     .00
19                  1     .00
33                  1     .00

-- when extent size is 10M
CLASS#            CNT ELAPSED
---------- ---------- -------
8                1456    3.01
1                 420    1.54
4                   5     .00
35                  2     .00
77                  1     .00



See the diffference. With big extent size, we have major buffer contention on 1st level bitmap block(8). With (too) smaller extent size, the major contention is on data block(1).

Hm… What does this mean? Well, it’s not the topic. :)

My point here is that Oracle has more funtionality than you imagine. Simple tricks with pipelined table function and utl_file package made it possible for me to analyze the whole new pattern of wait events.

PS) Instead of pipelined table function, the external table is also feasible. But I believe that the pipeline table function version is far more elegant.

Written by Dion Cho

March 19, 2009 at 6:49 am

Tracing user specific SQL statements

with 4 comments

Assuming following request:

I would like to trace SQL statements of specific Oracle user, not any other users.

My first choice would be the powerful end to end tracing facility of Oracle 10g.
Next one is dbms_xplan package which is not only powerful but also the most important package in Oracle.

1. End to end tracing to trace specific user.

UKJA@ukja102>  -- 1. Create logon trigger in which I give identifier to user's session                                                   
UKJA@ukja102> connect /as sysdba                                                     
Connected.                                                                           
                                                                                     
SYS@ukja10> create or replace trigger logon_trigger                                  
  2  after logon on database                                                         
  3  begin                                                                           
  4    if ora_login_user = 'UKJA' then                                               
  5      dbms_session.set_identifier('ukja');                                       
  6    end if;                                                                       
  7  end;                                                                            
  8  /                                                                               
                                                                                     
Trigger created.                                                                     
                                                                                     
SYS@ukja10> -- 2. Enable sql*trace for given client identifier                                                                       
SYS@ukja10> exec dbms_monitor.client_id_trace_enable(client_id=>'ukja', -            
>           waits=>true, binds=>true);                                               
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 3. Logon as UKJA user and execute some queries                                                                         
SYS@ukja10> connect ukja/ukja@ukja102                                                
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           
                                                                                     
UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> connect ukja/ukja@ukja102                                              
Connected.                                                                           

UKJA@ukja102> select count(*) from t1;                                               
                                                                                     
  COUNT(*)                                                                           
----------                                                                           
         0                                                                           
                                                                                     
UKJA@ukja102> -- 4. Disable sql*trace for given client identifier                                                                      
UKJA@ukja102> connect sys /as sysdba                                                 
Connected.                                                                           
                                                                                     
SYS@ukja10> exec dbms_monitor.client_id_trace_disable(client_id=>'ukja');            
                                                                                     
PL/SQL procedure successfully completed.                                             
                                                                                     
SYS@ukja10>  -- 5. Now the most difficult part. 
Our 3 trace files are scattered in the user dump directory. We should merge it.
The fantastic trcsess tool does this for me.   
                                                                      
SYS@ukja10> col value new_value dump_dir                                             
SYS@ukja10>                                                                          
SYS@ukja10> select value from v$parameter where name = 'user_dump_dest';             
                                                                                     
VALUE                                                                                
--------------------                                                                 
C:\ORACLE\ADMIN\UKJA                                                                 
10\UDUMP                                                                             
                                                                                     
                                                                                     
SYS@ukja10> -- Merge!                                                                         
SYS@ukja10> ho trcsess output=trc1.out clientid=ukja &dump_dir\*.trc                 
                                                                                     
SYS@ukja10> -- And tkprof                                                                          
SYS@ukja10> ho tkprof trc1.out tk1.out                                               

 -- Tkprof report. The result(3 executions from different sessions) was merged successfully! 
select count(*) 
from
 t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.00          0          9          0           3                                                                                     



This end to end tracing is quite flexible and powerful. Love it!

2. DBMS_XPLAN.DISPLAY_CURSOR to trace specific user.

UKJA@ukja102>  -- Following simple query would capture the queries
which start with 'select /*+ gather_plan_statistics */ count(*)%' and
executed by UKJA user
UKJA@ukja102> select                                                                 
  2      cursor(select * from table(                                                 
  3          dbms_xplan.display_cursor(s.sql_id, s.child_number, 'allstats last')))  
  4  from v$sql s                                                                    
  5  where s.parsing_schema_name = 'UKJA'                                            
  6        and s.sql_text like 'select /*+ gather_plan_statistics */ count(*) %'     
  7  ;                                                                               
                                                                                     
CURSOR(SELECT*FROMTA                                                                 
--------------------                                                                 
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  fuynj2z4vdjks, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t3                                
                                                                                     
Plan hash value: 463314188                                                           
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL| T3   |      1 |   1000 |   1000 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  5cunshnzqa8vb, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t2                                
                                                                                     
Plan hash value: 3321871023                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
CURSOR STATEMENT : 1                                                                 
                                                                                     
PLAN_TABLE_OUTPUT                                                                    
-------------------------------------------------------------------------------------
SQL_ID  cz662tawx6sbt, child number 0                                                
-------------------------------------                                                
select /*+ gather_plan_statistics */ count(*) from t1                                
                                                                                     
Plan hash value: 3724264953                                                          
                                                                                     
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------
                                                                                     



Again, very easy and powerful.

Written by Dion Cho

March 10, 2009 at 6:57 am

Posted in Misc.

Tagged with , ,

Executing DDL in the DDL trigger

with 2 comments

One of the common requests on the DDL trigger is to execute DDL in it.

For instance, you would like to grant select on the table to another user in the create table DDL trigger. But the fact is that you just end up with following error.

30511, "invalid DDL operation in system triggers"
// *Cause:  An attempt was made to perform an invalid DDL operation
//          in a system trigger. Most DDL operations currently are not
//          supported in system triggers. The only currently supported DDL
//          operations are table operations and ALTER?COMPILE operations.
// *Action: Remove invalid DDL operations in system triggers.


So I devised the trick to use dbms_job to execute the DDL in background.

Let me show a simple demonstration on how to execute DDL in the DDL trigger.

SYS@ukja10> -- as sys user
SYS@ukja10> connect sys /as sysdba
Connected.

SYS@ukja10> -- create after create ddl trigger to execute grant after creating table
SYS@ukja10> create or replace trigger ddl_trigger_test
  2  after create on database
  3  begin
  4  
  5    -- this does not work, ORA-30511
  6    if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then
  7      execute immediate 'grant select on ' || ora_dict_obj_owner || '.' ||
  8              ora_dict_obj_name || ' to ukja2';
  9    end if;
 10  
 11  end;
 12  /

Trigger created.

SYS@ukja10> connect ukja/ukja@ukja102
Connected.

UKJA@ukja102> -- Oops! ORA-30511 error
UKJA@ukja102> create table tx(c1 int);
create table tx(c1 int)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 5 


UKJA@ukja102> connect sys /as sysdba
Connected.

SYS@ukja10> -- stored procedure to execute the DDL statements(in background)
SYS@ukja10> create or replace procedure execute_grant(v_ddl in varchar2)
  2  is
  3  begin
  4  
  5    execute immediate v_ddl;
  6  
  7  end;
  8  /

Procedure created.

SYS@ukja10> -- create after create ddl trigger. see how I forward the request to background process
SYS@ukja10> create or replace trigger ddl_trigger_test
  2  after create on database
  3  declare
  4    v_ddl   varchar2(4000);
  5    v_job   number;
  6  begin
  7    if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then
  8      v_ddl := 'grant select on ' || ora_dict_obj_owner || '.' ||
  9              ora_dict_obj_name || ' to ukja2';
 10    end if;
 11  
 12    -- execute the ddl after 1 sec
 13    dbms_job.submit(v_job, 'execute_grant(''' || v_ddl || ''');', sysdate+(1/24/60/60));
 14  
 15  
 16  end;
 17  /

Trigger created.


SYS@ukja10> connect ukja/ukja@ukja102
Connected.

UKJA@ukja102> create table tx(c1 int);

Table created.

UKJA@ukja102> -- wait for a while to ensure that the job was executed 
UKJA@ukja102> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

UKJA@ukja102> -- check if another user(ukja2) has been granted to select ukja's table
UKJA@ukja102> connect ukja2/ukja2@ukja102
Connected.

UKJA2@ukja102> -- oh, yes! ukja.tx is visible! 
UKJA2@ukja102> select * from ukja.tx;  

no rows selected


Simple but powerful solution, isn’t it?

I really enjoy the power of dbms_job to implement simple asynchronous job in Oracle.

Written by Dion Cho

March 9, 2009 at 7:16 am

Posted in Misc.

Tagged with ,

OERR for Windows

with one comment

Yes, Oracle does not ship oerr with Windows version. But some smart guys struggled to solve the problem. Here you can download the windows oerr implemented with Perl.

But, I’ve lived a long life without this tool. I just built up my own oerr written in 100% SQL.

One very simple but not useful way is to use following code.

dbms_output.put_line(sqlerrm(-1555)) ;



But this generate only the simple message, without cause and action information.

ORA-01555: snapshot too old: rollback segment number  with name "" too small



Following is how I built up my own oerr which is capable of showing the entire message.

1. Create directory where oraus.msg file lives(In this demo, I’m in Windows oracle database)

create or replace directory dir_oraus
as 'C:\Documents and Settings\exem'
;



2. Create external table to which oraus.msg file is loaded.

create table ext_oraus(
  line    varchar2(4000)
) organization external
(
  default directory dir_oraus
  access parameters
  (
    records delimited by newline
    fields terminated by '~'
  )
  location ('oraus.msg')
)
reject limit unlimited
;



3. Create permanent table to store the contents of oraus.msg file and load the contents into that table.

create table t_oraus (
  err_code  varchar2(5),
  message   varchar2(1000),
  texts     clob
);
declare
  v_err_code  varchar2(4000);
  v_message  varchar2(4000);
  v_texts    clob;
begin
  for r in (select line from ext_oraus) loop
    if substr(r.line, 6, 1) = ',' then
      -- record new data
      if v_err_code is not null then
        insert into t_oraus(err_code, message, texts)
          values(v_err_code, v_message, v_texts);
      end if;
      v_err_code := substr(r.line, 1, 5);
      v_message := substr(r.line, 14);
      v_texts := '';
    elsif substr(r.line, 1, 2) = '//' then
      v_texts := v_texts || r.line || chr(13)|| chr(10);
    end if;
  end loop;
  -- last record
  insert into t_oraus(err_code, message, texts)
          values(v_err_code, v_message, v_texts);
  commit;
end;
/
create index t_oraus_n1 on t_oraus(err_code);


4. Now we can do oerr just by selecting corresponding data from the table t_oraus

select err_code, texts
from t_oraus
where err_code = '01555'
;

ERR_C TEXTS
----- -------------------------------------------------------------------------
01555 // *Cause: rollback records needed by a reader for consistent read are
      //           overwritten by other writers
      // *Action: If in Automatic Undo Management mode, increase undo_retention
      //          setting. Otherwise, use larger rollback segments



5. The fact that the whole data is stored in RDBMS gives me the full flexibility, like

ed oerr.sql
select err_code, texts
from t_oraus
where err_code = lpad(to_char(&1), 5, '0')
;


@oerr 1555
ERR_C TEXTS
----- --------------------------------------------------------------------------
01555 // *Cause: rollback records needed by a reader for consistent read are
      //           overwritten by other writers
      // *Action: If in Automatic Undo Management mode, increase undo_retention
      //          setting. Otherwise, use larger rollback segments



Or

select err_code, texts
from t_oraus
where err_code like '0403%'
;
ERR_C TEXTS
----- ----------------------------------------------------------------------------
04030 // *Cause:  Operating system process private memory has been exhausted
      // *Action:

04031 // *Cause:  More shared memory is needed than was allocated in the shared
      //          pool.
      // *Action: If the shared pool is out of memory, either use the
      //          dbms_shared_pool package to pin large packages,
      //          reduce your use of shared memory, or increase the amount of
      //          available shared memory by increasing the value of the
      //          INIT.ORA parameters "shared_pool_reserved_size" and
      //          "shared_pool_size".
      //          If the large pool is out of memory, increase the INIT.ORA
      //          parameter "large_pool_size".

04032 // *Cause:  attempt to set workarea_size_policy to AUTO while
      //            pga_aggregate_target is not specified
      // *Action: before setting workarea_size_policy, set pga_aggregate_target
      //          to a value representing the total private memory available to
      //          the instance. This total is generally the total physical memory
      //          available in the system minus what is needed for the SGA minus
      //          what is needed for the operating system (e.g. 500MB)

04033 // *Cause:  The system had insufficient memory to grow the pool to the
      //          specified size.
      // *Action: Specify a smaller value to grow the pool.

04034 // *Cause:  The pool could not shrink to the specified size as it could not
      //          free memory.
      // *Action: Specify a larger value to which to shrink the pool to.



Very flexible, isn’t it?

I’m just still curios why Oracle doesn’t ship oerr for Windows. :)

Written by Dion Cho

March 4, 2009 at 8:52 am

Posted in Misc.

Tagged with ,

Making SQL always hard parsed – Using row level security

leave a comment »

I hit a very interesting OTN Forum discussion. To make the long story short, the request was as following

How can I make the SQL always hard parsed even when bind variables are being used? (Changing the code is not an option)

Not common in OLTP, right? But on DSS environment, this could be very common and even useful!

As far as I remember, MSSQL Server has the functionality not to load the SQL onto shared memory. Very cool feature.

But Oracle? No, it’s impossbile.

The discussion got many suggestions. Let’s have a look at them.

1. Adaptive cursor sharing will save us.

Cool. It’s 11g new feature. Love it. But this is 10g database.

2. SQL Tuning advisor(SQL profile) or stored outline

Not relevant.

3. Get rid of statistics and sample dynamically

But dynamic sampling works only on optimization time. Dynamic sampling itself does not make SQL always optimized.

4. Change code to use literal value

Not an option.

5. Invalidate the cursor by updating statistics.
Not bad. By updating statistics like this,

exec dbms_stats.set_table_stats('schema_name', 'table_name', -
num_rows=>null, no_invalidate=>false);

We can invalidate all the cursors dependent on the table without the danger of changing statistics.  But this is also not an option because we can’t change the software code.

(This was my suggestion, actually)


6. Apply row-level security model to generate random predicate.

Very clever idea by this guy. Clever, Randolf! Maybe the only way to meet the request.

Do not change code, but make my SQL always hard parsed!

It works like this:

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

exec dbms_stats.gather_table_stats(user, 't1');

drop sequence s1;

create sequence s1 cache 1000;

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

create or replace package body pkg_rls_hard_parse is
  function force_hard_parse (in_schema varchar2, in_object varchar2)
      return varchar2
  is
    s_predicate varchar2(100);
    n_random pls_integer;
  begin
    select s1.nextval || ' = ' || s1.currval into s_predicate from dual;
    return s_predicate;
  end force_hard_parse;
end pkg_rls_hard_parse;
/ 

exec dbms_rls.add_policy (user, 't1', 'hard_parse_policy', user, -
        'pkg_rls_hard_parse.force_hard_parse', 'select');  <-- Clever!

alter system flush shared_pool;

exec dbms_monitor.session_trace_enable;

-- Execute the same query 10 times
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;
select /* placeholder */ count(*) from t1;

exec dbms_monitor.session_trace_disable;

The result of tkprof is like following:

select /* placeholder */ count(*)
from
 t1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.02          0          0          0           0
Execute     10      0.01       0.01          0          0          0           0
Fetch       20      0.07       0.06          0        150          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       40      0.09       0.10          0        150          0          10

Misses in library cache during parse: 10   <-- Look here!
Misses in library cache during execute: 10

Cool!

One of the problems pointed out was that this row level security feature has some overhead. For instance, have a look at this:

SELECT S1.NEXTVAL || ' = ' || S1.CURRVAL
FROM
 DUAL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     30      0.00       0.00          0          0          0           0
Fetch       30      0.00       0.00          0          0          1          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       61      0.00       0.01          0          0          1          30
begin :con := PKG_RLS_HARD_PARSE.FORCE_HARD_PARSE(:sn, :on); end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       30      0.00       0.01          0          0          0           0
Execute     30      0.01       0.01          0          0          0          30
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       60      0.01       0.03          0          0          0          30

It looks like Oracle calls the policy function multiple times to apply row the level security policy. Not sure why, but there would be some overhead especially when the original queries are very frequently called.

Another problem to note is that it would flood the shared pool. We are generating tons of unshareable SQLs! But this is very heavy DDS system that parse overhead wouldn’t be a big deal.

This kind of clever tricks really makes me happy. :)

Written by Dion Cho

February 6, 2009 at 5:47 am

Posted in Misc.

Tagged with ,

I’m new blogger on Oracle database performance

with one comment

I’m just starting blogging in English today.

My name is Dion Cho(Korean name is “Cho Dong Wook”).

I’m education specialist on Oracle database performance.

I call myself Oracle Performance Storyteller – not very cute :(


I wrote 3 books. All in Korean.

[Optimizing Oracle Optimizer]

[Advanced Oracle Wait Interface in Oracle 10g]

[Advanced Oracle Wait Interface in Oracle 10g RAC]

I just like to share my knowledge and experience with all of you.

Written by Dion Cho

January 21, 2009 at 7:01 am

Posted in Misc.

Follow

Get every new post delivered to your Inbox.

Join 60 other followers