Oracle performance analysis - Autotrace workshop

Recommend

Contact

Menu
∙ Home ∙ Index (All Topics) ∙ About Me ∙ About This Blog ∙ Favourite Links ∙ RSS Feed

Categories
∙ ASP & PHP (1) ∙ HTML, XML and CSS (2) ∙ C / C++ (5) ∙ Java, JSP and Servlet (0) ∙ SQL-Oracle-PL/SQL (28) ∙ Operating Systems (1) ∙ OFF-Topic (8)

Popular Topics (Top 10)
∙ Decode Function in Oracle SQL (63931) ∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (59646) ∙ Oracle performance analysis - Tracing and performance evaluation (45573) ∙ Differences between C and C++ (36701) ∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (34233) ∙ When a transaction begins? (33161) ∙ Implicit vs. Explicit cursors - Performance analysis (18181) ∙ Turkcell Staj Günlüğü - 4: Transaction Management (17076) ∙ Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments (15243) ∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (15051)

Most Recent (Last 10)
∙ Matematik Asla Yalan Söylemez! ∙ Finding and Removing Loop on a Singly-Linked List ∙ Obfuscated C ∙ Is C a Vitamin? Yes, of course... ∙ Differences between C and C++ ∙ Whence C? Why C? Whither C? ∙ Türkçe Karakterli Domain'lerin İç Yüzü ∙ Windows Source Codes ∙ Decode Function in Oracle SQL ∙ Hello World!

Recent Comments (Last 10)
∙ "tebrikler" By yasin on Turkcell Staj Günlüğü - 5: Startup, Shutdown ∙ "Gercekten Güzel Bir Çalışma" By Hüseyin Karabakla on Neden hazır blog'ları kullanmadım ki? ∙ "Konu paralelinde güzel bir özet ek okuma - " By TongucY on Oracle performance analysis - Tracing and performance evaluation ∙ "harika" By burak ozcan on Derin anlamlı sözler - Bunlar da Türkçe olanlar :) ∙ "Tebrikler" By Tarık Bayzın on Turkcell Staj Günlüğü - 1: Introduction to Oracle ∙ "Gayet Başarılı.." By Fahri ATES on Turkcell Staj Günlüğü - 1: Introduction to Oracle ∙ "Helal olsun" By ender onder on Turkcell Staj Günlüğü - 5: Startup, Shutdown ∙ "tebrikler.." By ender ondeer on Turkcell Staj Günlüğü - 4: Transaction Management ∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez! ∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı?

Archive (Last 12 Months)
∙ Feb, 2008 (4) ∙ Jan, 2008 (2) ∙ Dec, 2007 (1) ∙ Sep, 2007 (4) ∙ Aug, 2007 (9) ∙ Jul, 2007 (22) ∙ Jun, 2007 (3) ∙ Index (All Records)

Other Related Blogs
∙ Tom Kyte’s Blog ∙ Steven Feuerstein’s Blog ∙ Jonathan Lewis’s Blog ∙ H.Tonguç Yılmaz Oracle Blog ∙ Mennan Tekbir's Blog ∙ Hakkı Oktay’s Blog ∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors

: 45
: 683492
: 44

Oracle performance analysis - Autotrace workshop

Category: SQL-Oracle-PL/SQL
Date: 17.07.2007 15:52:45

This entry has examples about how to gather SQL statement statistics with AUTOTRACE. Please first read the entry about the SQL Performance analysis: Oracle performance analysis - Tracing and performance evaluation

Note: I have written the code on a fresh install Oracle 10gR2 XE, so that you can follow step by step and see the errors and solutions. You may get different errors for other versions.

Version: Oracle XE Release 10.2.0.1.0
Server: Suse Linux 9.2 on VMWare Workstation 5.5.3
Client: SQL*Plus: Release 10.2.0.1.0 Console

Some parameters about code:

SYSDBA user: sys
Test user & password: test/test

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> grant all on plan_table to public;

Grant succeeded.

SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
-- Automatically running script
SQL> 
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL> 
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> 
SQL> set echo off
-- End of script
SQL> grant plustrace to public;

Grant succeeded.

SQL> create user test identified by test;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> set linesize 10000
SQL> set pagesize 10000
SQL> conn test/test
Connected.

SQL> set timing on
SQL> set autotrace on
SQL> create table t as select owner, object_name, object_id from all_objects;

Table created.

Elapsed: 00:00:03.29
SQL> select * from t;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
SYS                            DUAL                                  258
PUBLIC                         DUAL                                  259
SYS                            SYSTEM_PRIVILEGE_MAP                  311
PUBLIC                         SYSTEM_PRIVILEGE_MAP                  313
SYS                            TABLE_PRIVILEGE_MAP                   314
...

5293 rows selected.

Elapsed: 00:00:12.89

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5293 |   242K|     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |  5293 |   242K|     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
        412  consistent gets
         24  physical reads
          0  redo size
     168080  bytes sent via SQL*Net to client
       4248  bytes received via SQL*Net from client
        354  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5293  rows processed

SQL> select object_name from t where owner='TEST';

OBJECT_NAME
------------------------------
T

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    34 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    34 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='TEST')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        199  recursive calls
          0  db block gets
         89  consistent gets
          0  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        376  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index inx_t on t(owner);

Index created.

Elapsed: 00:00:00.22

SQL> select object_name from t where owner='TEST';

OBJECT_NAME
------------------------------
T

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 2582779724

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    34 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    34 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='TEST')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         35  consistent gets
          1  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        376  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Download Code

Summary

First we have created the plan_table, that is required for autotrace to trace execution plan. The table may not be on the system by default, but the script: "@$ORACLE_HOME/rdbms/admin/utlxplan.sql" creates it. Then we grant all to the plan_table to public, for other users to use autotrace.

Then, we created the "plustrace" role for using autotrace and grant that role to public. (Don't grant to public if you don't want some users to use autotrace)

Then, we created our user named "test", and enable timing and autotrace. Then, we created a table called "t" (actually a partial copy of all_objects) to experiment on it. Notice that no statistics -except elapsed time- is generated for DDL statement create.

For the experimentation, first we run the SQL: "select * from t;" Notice what statistics are generated for this single statement, automatically, just after a successfull execution. We easily see the execution plan for this simple query, that just show a full table scan was done.

For another experiment, we just executed: "select object_name from t where owner='TEST';" and see the similar execution plan. Then, we created an index on "owner" column, that we are filtering on the query, and run the same query. We see that generates a different execution plan, that based on an index scan on "owner" column, then index access.

Compare and analyze the statistics, especially the most important ones: db block gets, consistent gets and physical reads. This entry will not go into the details of analyzing the statistics and tuning the queries (I will write some topics about that later). But now, you have learned how to get the statistics information, and you will experiment on it with different tables and queries. Especially, execute some join queries or fetch data on views (such as all_objects), that might be a good practice to be ready learn about how the execution plan is generated and what is the meaning of the operations on it.

The last thing to consider in this topic is, you may not see the execution plan for queries working on other users' tables. You will get the same error with the example below:

SQL> conn test/test
Connected.

SQL> set timing on
SQL> set autotrace on
SQL> select object_name from all_objects;

OBJECT_NAME
------------------------------
DUAL
DUAL
SYSTEM_PRIVILEGE_MAP
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
...


5293 rows selected.

Elapsed: 00:00:07.50

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view 


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       5979  recursive calls
          0  db block gets
      29396  consistent gets
          0  physical reads
          0  redo size
     132749  bytes sent via SQL*Net to client
       4248  bytes received via SQL*Net from client
        354  SQL*Net roundtrips to/from client
         49  sorts (memory)
          0  sorts (disk)
       5293  rows processed

What you need to understand from the error is, you are working with another user's objects, or the view that you used references some objects that you don't have grant to access. It usually occurs when you acces the objects on a view, because if you directly access from table that means you already have select grant, so this error may not occur.

For above example, we are working on all_objects view which the query consists of many system tables. To see the execution plan of this, we may have a SELECT_CATALOG_ROLE :)

Let's see a really big execution plan at the end :)

Note: I have truncated the output from right because of the limitations on page width.

SQL> conn sys/1234 as sysdba
Connected.

SQL> grant select_catalog_role to test;

Grant succeeded.

SQL> conn test/test
Connected.

SQL> set timing on
SQL> set autotrace on
SQL> select object_name from all_objects;

OBJECT_NAME
------------------------------
DUAL
DUAL
SYSTEM_PRIVILEGE_MAP
SYSTEM_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
TABLE_PRIVILEGE_MAP
...


5293 rows selected.

Elapsed: 00:00:07.50

Execution Plan
----------------------------------------------------------
Plan hash value: 1699710930

------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Rows  | Bytes | Cost (%C
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |  3929 |   295K|    52  (
|*  1 |  FILTER                                    |            |       |       |
|*  2 |   HASH JOIN                                |            | 13330 |  1002K|    52  (
|   3 |    TABLE ACCESS FULL                       | USER$      |    38 |   114 |     2
|*  4 |    TABLE ACCESS FULL                       | OBJ$       | 13330 |   963K|    49  (
|*  5 |   TABLE ACCESS BY INDEX ROWID              | IND$       |     1 |     8 |     2
|*  6 |    INDEX UNIQUE SCAN                       | I_IND1     |     1 |       |     1
|   7 |    NESTED LOOPS                            |            |     1 |    24 |     2
|*  8 |     INDEX RANGE SCAN                       | I_OBJAUTH1 |     1 |    11 |     2
|*  9 |     FIXED TABLE FULL                       | X$KZSRO    |     1 |    13 |     0
|* 10 |      FIXED TABLE FULL                      | X$KZSPR    |     1 |    26 |     0
|* 11 |       FIXED TABLE FULL                     | X$KZSPR    |     1 |    26 |     0
|* 12 |        FIXED TABLE FULL                    | X$KZSPR    |     1 |    26 |     0
|* 13 |         FIXED TABLE FULL                   | X$KZSPR    |     1 |    26 |     0
|* 14 |          FIXED TABLE FULL                  | X$KZSPR    |     1 |    26 |     0
|* 15 |           FIXED TABLE FULL                 | X$KZSPR    |     1 |    26 |     0
|* 16 |            FIXED TABLE FULL                | X$KZSPR    |     1 |    26 |     0
|* 17 |             FIXED TABLE FULL               | X$KZSPR    |     1 |    26 |     0
|* 18 |              FIXED TABLE FULL              | X$KZSPR    |     1 |    26 |     0
|* 19 |               FIXED TABLE FULL             | X$KZSPR    |     1 |    26 |     0
|* 20 |                FIXED TABLE FULL            | X$KZSPR    |     1 |    26 |     0
|* 21 |                 FIXED TABLE FULL           | X$KZSPR    |     1 |    26 |     0
|* 22 |                  FIXED TABLE FULL          | X$KZSPR    |     1 |    26 |     0
|* 23 |                   FIXED TABLE FULL         | X$KZSPR    |     1 |    26 |     0
|* 24 |                    FIXED TABLE FULL        | X$KZSPR    |     1 |    26 |     0
|  25 |                     VIEW                   |            |     1 |    13 |     2
|  26 |                      FAST DUAL             |            |     1 |       |     2
|* 27 |                       FIXED TABLE FULL     | X$KZSPR    |     1 |    26 |     0
|* 28 |                        FIXED TABLE FULL    | X$KZSPR    |     1 |    26 |     0
|* 29 |                         FIXED TABLE FULL   | X$KZSPR    |     1 |    26 |     0
|* 30 |                          FIXED TABLE FULL  | X$KZSPR    |     1 |    26 |     0
|* 31 |                           FIXED TABLE FULL | X$KZSPR    |     1 |    26 |     0
|* 32 |                            FIXED TABLE FULL| X$KZSPR    |     1 |    26 |     0
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT /*+ */ 1 F
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
              "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
              (("O"."OWNER#"=USERENV('SCHEMAID') OR "O"."OWNER#"=1) OR "O"."TYPE#"<>11 AND
               EXISTS (SELECT /*+ */ 0 FROM "SYS"."OBJAUTH$" "OBJAUTH$",SYS."X$KZSRO" "X$K
              "GRANTEE#"="KZSROROL" AND "OBJ#"=:B2 AND ("PRIVILEGE#"=3 OR "PRIVILEGE#"=6 O
              "PRIVILEGE#"=9 OR "PRIVILEGE#"=10 OR "PRIVILEGE#"=11 OR "PRIVILEGE#"=12 OR "
              "PRIVILEGE#"=17 OR "PRIVILEGE#"=18)) OR ("O"."TYPE#"=1 OR "O"."TYPE#"=2 OR "
              "O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=19 OR "O"."TYPE#"=20 OR "O"."T
              "O"."TYPE#"=35) AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WH
              "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47
              (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))) OR "O"."
              EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE ((-"KZSPRPRV")=(-
              (-"KZSPRPRV")=(-181)) AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=11 A
              /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=(-141) AND
              "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=12 AND  EXISTS (SELECT /*+ */
              "X$KZSPR" WHERE (-"KZSPRPRV")=(-152) AND "INST_ID"=USERENV('INSTANCE')) OR (
              "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=28 OR "O"."TYPE#"=29 OR "O"."T
              "O"."TYPE#"=56) AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WH
              ((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTAN
              "O"."TYPE#"=22 AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHE
              "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-1
              (-"KZSPRPRV")=(-191) OR (-"KZSPRPRV")=(-192))) OR "O"."TYPE#"=14 AND  EXISTS
              FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=(-181) AND "INST_ID"=USEREN
              "O"."TYPE#"=6 AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHER
              (-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=33 AN
              /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AN
              ((-"KZSPRPRV")=(-200) OR (-"KZSPRPRV")=(-201) OR (-"KZSPRPRV")=(-202) OR (-"
              (-"KZSPRPRV")=(-204))) OR ("O"."TYPE#"=69 OR "O"."TYPE#"=72 OR "O"."TYPE#"=7
              AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=US
              AND ((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-252) OR (-"KZSPRPRV")=(-253) OR
              (-"KZSPRPRV")=(-254))) OR "O"."TYPE#"=62 AND  EXISTS (SELECT /*+ */ 0 FROM S
              "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-246) OR (
              OR (-"KZSPRPRV")=(-248) OR (-"KZSPRPRV")=(-249))) OR "O"."TYPE#"=32 AND  EXI
              FROM SYS."X$KZSPR" "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZS
              (-"KZSPRPRV")=(-206) OR (-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208))) OR "O
              EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=12
              "INST_ID"=USERENV('INSTANCE')) OR "O"."TYPE#"=59 AND  EXISTS (SELECT /*+ */
              "X$KZSPR" WHERE "INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-258) OR (
              OR (-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261))) OR "O"."TYPE#"=66 AND  EXI
              FROM SYS."X$KZSPR" "X$KZSPR" WHERE (-"KZSPRPRV")=(-265) AND "INST_ID"=USEREN
              "O"."TYPE#"=55 AND  (SELECT /*+ NO_MERGE */ "XML_SCHEMA_NAME_PRESENT"."IS_SC
              "."ID2") FROM  (SELECT /*+ NO_MERGE */ USERENV('SCHEMAID') "ID2" FROM SYS."D
              OR "O"."TYPE#"=23 AND  EXISTS (SELECT /*+ */ 0 FROM SYS."X$KZSPR" "X$KZSPR"
              ((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTAN
   2 - access("O"."OWNER#"="U"."USER#")
   4 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
              "O"."LINKNAME" IS NULL)
   5 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYP
              OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
   6 - access("I"."OBJ#"=:B1)
   8 - access("OBJ#"=:B1)
       filter("PRIVILEGE#"=3 OR "PRIVILEGE#"=6 OR "PRIVILEGE#"=7 OR "PRIVILEGE#"=9 OR
              "PRIVILEGE#"=10 OR "PRIVILEGE#"=11 OR "PRIVILEGE#"=12 OR "PRIVILEGE#"=16 OR
              "PRIVILEGE#"=18)
   9 - filter("GRANTEE#"="KZSROROL")
  10 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47
              (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))
  11 - filter(((-"KZSPRPRV")=(-184) OR (-"KZSPRPRV")=(-181)) AND "INST_ID"=USERENV('INSTAN
  12 - filter((-"KZSPRPRV")=(-141) AND "INST_ID"=USERENV('INSTANCE'))
  13 - filter((-"KZSPRPRV")=(-152) AND "INST_ID"=USERENV('INSTANCE'))
  14 - filter(((-"KZSPRPRV")=(-144) OR (-"KZSPRPRV")=(-141)) AND "INST_ID"=USERENV('INSTAN
  15 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-189) OR (-"KZSPRPRV")=(-1
              (-"KZSPRPRV")=(-191) OR (-"KZSPRPRV")=(-192)))
  16 - filter((-"KZSPRPRV")=(-181) AND "INST_ID"=USERENV('INSTANCE'))
  17 - filter((-"KZSPRPRV")=(-109) AND "INST_ID"=USERENV('INSTANCE'))
  18 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-200) OR (-"KZSPRPRV")=(-2
              (-"KZSPRPRV")=(-202) OR (-"KZSPRPRV")=(-203) OR (-"KZSPRPRV")=(-204)))
  19 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-251) OR (-"KZSPRPRV")=(-2
              (-"KZSPRPRV")=(-253) OR (-"KZSPRPRV")=(-254)))
  20 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-246) OR (-"KZSPRPRV")=(-2
              (-"KZSPRPRV")=(-248) OR (-"KZSPRPRV")=(-249)))
  21 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-205) OR (-"KZSPRPRV")=(-2
              (-"KZSPRPRV")=(-207) OR (-"KZSPRPRV")=(-208)))
  22 - filter((-"KZSPRPRV")=12 AND "INST_ID"=USERENV('INSTANCE'))
  23 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-258) OR (-"KZSPRPRV")=(-2
              (-"KZSPRPRV")=(-260) OR (-"KZSPRPRV")=(-261)))
  24 - filter((-"KZSPRPRV")=(-265) AND "INST_ID"=USERENV('INSTANCE'))
  27 - filter(((-"KZSPRPRV")=(-177) OR (-"KZSPRPRV")=(-178)) AND "INST_ID"=USERENV('INSTAN
  28 - filter(((-"KZSPRPRV")=(-222) OR (-"KZSPRPRV")=(-223)) AND "INST_ID"=USERENV('INSTAN
  29 - filter(((-"KZSPRPRV")=(-268) OR (-"KZSPRPRV")=(-267)) AND "INST_ID"=USERENV('INSTAN
  30 - filter(((-"KZSPRPRV")=(-265) OR (-"KZSPRPRV")=(-266)) AND "INST_ID"=USERENV('INSTAN
  31 - filter("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47
              (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50)))
  32 - filter(((-"KZSPRPRV")=(-277) OR (-"KZSPRPRV")=(-278)) AND "INST_ID"=USERENV('INSTAN

Statistics
----------------------------------------------------------
       5979  recursive calls
          0  db block gets
      29396  consistent gets
          0  physical reads
          0  redo size
     132749  bytes sent via SQL*Net to client
       4248  bytes received via SQL*Net from client
        354  SQL*Net roundtrips to/from client
         49  sorts (memory)
          0  sorts (disk)
       5293  rows processed

Download Code