Quote: Suppose I had one billion waits for full scans -- looks like a lot of time.  I have one thousand for index reads -- looks small.

What if we start by looking not at the quantity of reads, but at the average wait time instead? From that perspective the view might be suggesting whether it would be a worthwhile to gain 1,000,000,000 sequential read waits while reducing scattered read waits by only 500,000,000 -- total db_file wait time would be reduced even though the total db_file number of waits has increased.


  1  select TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT
  2*        from v$system_event where event ='db file scattered read'
SQL> /

TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
----------- ----------- ------------
      19543      284113   14.5378396

SQL> select TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT
  2         from v$system_event where event ='db file sequential read'
  3  /

TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
----------- ----------- ------------
     192772      246160   1.27694893

Given the ratio of (avg sequential read wait time)/(avg scattered read wait time) of 1:11 that I see on my system, I might be willing to take on many more sequential reads for each scattered read that I eliminate. All completely theoretical of course, and it probably represents about 1/100th of the total system performance from the user point of view.

Note that:

i) this is a small data warehouse db, heavy on bitmap indexes and range partitions, if that makes a difference
ii) I don't have a performance problem, I'm just tuning for the sake of it ;)

Hey, it could benefit someone else though, right? I'm sure that index_optimizer_cost_adj is a much neglected parameter -- when I took over this database an (obvious) adjustment from "100" down to "15" reduced average report time from 75 seconds down to 15 seconds. And speaking of the users point of view, not a single one of them noticed this (literally) overnight improvement -- an example which you are quite free to mention when bashing people for their never-ending tuning exercises.

Talking of which, I'm now off to implement composite partitioning, bitmap join indexing, and data segment compression on a major fact table in order to get that average below ten seconds. Like anyone cares. 

Hi Tom, 

We are using siebel CRM. siebel recommands rule base optimizer. we have other ssystem in Oracle that use cost based Optimizer.

While doing query we have to join tables from both Siebel crm(Rule base opt.) and other systems(cost based optimizer).

--I have seen lots of performance problem 
What is your suggestions on this matter?

2) Star hint

We have some bad queries that is taking more then five(5) minitues, if I use /*+ star */ hint it run very fast and return result with in 10 or less SECONDS !!!!

Could you explain why this is happening  ? 
We have mix enviournment (Both rule based and cost bas)

For eg. 

---------------------
Without Hint
---------------------

  1  select   'OPTY', op.row_id, sysdate, 'FOR_ASSIGNMENT', 'ASSIGNMENT CHANGE - HOT PROS OPTY - ZIP
  2   from siebel.s_opty op, siebel.s_contact cn, siebel.s_addr_per ad,
  3    sblint.release_rep_assignment rra, siebel.s_stg stg, mkt.listing_state@oraprod1@mkt_link st
  4   where op.pr_con_id = cn.row_id and cn.pr_per_addr_id = ad.row_id
  5    and op.opty_cd in ('Hot Prospect', 'Prospect')
  6    and ( op.x_sub_type <> 'Last Year Cancel' or op.x_sub_type is null )
  7              and ( nvl(op.x_source,' ') <> 'Last Year Cancel')                    -- dz 2/2003
  8    and rra.sales_level_code = 3 and stg.stage_status_cd = 'Open'
  9    and op.curr_stg_id = stg.row_id
 10    and substr(ad.zipcode, 0, 5) = rra.zip_code
 11    and rra.zip_code is not null
 12        --    add code to match on state, county, and city  - zeller
 13         and ad.city = rra.city and ad.county = rra.county and ad.state = st.state
 14    and st.state_no = rra.listing_state_no
 15    and not exists (select * from sbl_assignment where object_id = op.row_id and object = 'OPTY'
 16*       and status <> 'Archived')
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   FILTER
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             NESTED LOOPS
   7    6               REMOTE*                                        ORAPROD1
                                                                       .WORLD@M
                                                                       KT_LINK

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR_PER'
   9    8                 INDEX (RANGE SCAN) OF 'S_ADDR_PER_M2' (NON-U
          NIQUE)

  10    5             TABLE ACCESS (BY INDEX ROWID) OF 'RELEASE_REP_AS
          SIGNMENT'

  11   10               INDEX (RANGE SCAN) OF 'RELEASE_REP_IDX2' (NON-
          UNIQUE)

  12    4           TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
  13   12             INDEX (RANGE SCAN) OF 'S_CONTACT_PR_PER_ADDR_ID_
          X' (NON-UNIQUE)

  14    3         TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY'
  15   14           INDEX (RANGE SCAN) OF 'S_OPTY_M9' (NON-UNIQUE)
  16    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_STG'
  17   16         INDEX (UNIQUE SCAN) OF 'S_STG_P1' (UNIQUE)
  18    1     TABLE ACCESS (BY INDEX ROWID) OF 'SBL_ASSIGNMENT'
  19   18       INDEX (RANGE SCAN) OF 'SBL_ASSIGNMENT_IDX1' (NON-UNIQU
          E)



   7 SERIAL_FROM_REMOTE            SELECT "STATE","STATE_NO" FROM "MKT"."LISTIN
                                   G_STATE" "ST"



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
   49228973  consistent gets
     300521  physical reads
         68  redo size
        280  bytes sent via SQL*Net to client
        249  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

---------------------
With  /*+ star */ Hint
---------------------


SQL> ed
Wrote file afiedt.buf

  1  select  /*+ star */  'OPTY', op.row_id, sysdate, 'FOR_ASSIGNMENT', 'ASSIGNMENT CHANGE - HOT PRO
  2   from siebel.s_opty op, siebel.s_contact cn, siebel.s_addr_per ad,
  3    sblint.release_rep_assignment rra, siebel.s_stg stg, mkt.listing_state@oraprod1@mkt_link st
  4   where op.pr_con_id = cn.row_id and cn.pr_per_addr_id = ad.row_id
  5    and op.opty_cd in ('Hot Prospect', 'Prospect')
  6    and ( op.x_sub_type <> 'Last Year Cancel' or op.x_sub_type is null )
  7              and ( nvl(op.x_source,' ') <> 'Last Year Cancel')                    -- dz 2/2003
  8    and rra.sales_level_code = 3 and stg.stage_status_cd = 'Open'
  9    and op.curr_stg_id = stg.row_id
 10    and substr(ad.zipcode, 0, 5) = rra.zip_code
 11    and rra.zip_code is not null
 12        --    add code to match on state, county, and city  - zeller
 13         and ad.city = rra.city and ad.county = rra.county and ad.state = st.state
 14    and st.state_no = rra.listing_state_no
 15    and not exists (select * from sbl_assignment where object_id = op.row_id and object = 'OPTY'
 16*       and status <> 'Archived')
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=886 Card=1 Bytes=318)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=886 Card=1 Bytes=318)
   3    2       NESTED LOOPS (Cost=60 Card=1 Bytes=300)
   4    3         NESTED LOOPS (Cost=58 Card=1 Bytes=213)
   5    4           MERGE JOIN (CARTESIAN) (Cost=57 Card=1 Bytes=196)
   6    5             NESTED LOOPS (Cost=41 Card=1 Bytes=124)
   7    6               TABLE ACCESS (FULL) OF 'S_STG' (Cost=1 Card=20
           Bytes=520)

   8    6               TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY' (Cos
          t=2 Card=1 Bytes=98)

   9    8                 INDEX (RANGE SCAN) OF 'S_OPTY_F5' (NON-UNIQU
          E) (Cost=1 Card=1)

  10    5             SORT (JOIN) (Cost=55 Card=1 Bytes=72)
  11   10               TABLE ACCESS (FULL) OF 'RELEASE_REP_ASSIGNMENT
          ' (Cost=16 Card=1 Bytes=72)

  12    4           REMOTE* (Cost=1 Card=74 Bytes=1258)                ORAPROD1
                                                                       .WORLD@M
                                                                       KT_LINK

  13    3         TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR_PER' (Cost=
          2 Card=2000 Bytes=174000)

  14   13           INDEX (RANGE SCAN) OF 'S_ADDR_PER_M1' (NON-UNIQUE)
           (Cost=1 Card=2000)

  15    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT' (Cost=826
           Card=2000 Bytes=36000)

  16   15         INDEX (FULL SCAN) OF 'S_CONTACT_F6' (NON-UNIQUE) (Co
          st=26 Card=2000)

  17    1     TABLE ACCESS (BY INDEX ROWID) OF 'SBL_ASSIGNMENT' (Cost=
          2 Card=1 Bytes=63)

  18   17       INDEX (RANGE SCAN) OF 'SBL_ASSIGNMENT_IDX1' (NON-UNIQU
          E) (Cost=1 Card=1)



  12 SERIAL_FROM_REMOTE            SELECT "STATE","STATE_NO" FROM "MKT"."LISTIN
                                   G_STATE" "ST" WHERE "STATE_NO"=:1



Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
      25797  consistent gets
       6508  physical reads
          0  redo size
        280  bytes sent via SQL*Net to client
        249  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         32  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> 

--LOTS of  difference between statistics!!!

Thanks,

 

Tom,
I just inherited an application that runs on 7.3.4. Yes, it’s really old :). But I’m at a loss here and could really use your help!

Here’s whats happening. 
1) The database is set to use RBO. 
2) Table AR.S_ASSET has never been analyzed. I infact deleted statistics from it to make sure.
3) Column PROD_ID is the leading column in unique concatenated index S_ASSET_U1_TEMP
4) select count(ASSET_NUM) from ar.s_asset; resorts to a FTS everytime! Even if I set the optimizer_goal=CHOOSE at the session level. Why??
5) select /*+ index(S_ASSET S_ASSET_U1_TEMP) */ count(PROD_ID) from ar.s_asset; uses the index (and thus resorts to CBO due to hint) and works quite nicely as expected.

Could you explain step 4 and what I could do to make this “index happy”? This is just the tip of the iceberg. The whole application I am told is performing poorly. The application requires an RBO apparently. 

Here are details of the above steps if it helps. I have described the table and index details at the bottom. It is wide. 
PS : I also did try analyzing the table to see if makes a difference to this exercise, and it didn’t. It yielded similar results.

1)
SVRMGR> show parameter opt
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
optimizer_mode string RULE
optimizer_parallel_pass boolean TRUE
optimizer_percent_parallel integer 0
optimizer_search_limit integer 5

2) 
SQL> analyze table ar.s_asset delete statistics;
Table analyzed.

3) 
SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where table_name='S_ASSET';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
S_ASSET_U1_TEMP                ASSET_NUM                                    1
S_ASSET_U1_TEMP                PROD_ID                                      2
S_ASSET_U1_TEMP                CONFLICT_ID                                  3

4) 
SQL> l
  1* select count(ASSET_NUM) from ar.s_asset
SQL> /
COUNT(ASSET_NUM)
----------------
          292216
Elapsed: 00:00:06.14
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'S_ASSET'

SQL> alter session set optimizer_goal=CHOOSE;
Session altered.

SQL> select count(ASSET_NUM) from ar.s_asset;
COUNT(ASSET_NUM)
----------------
          292216
Elapsed: 00:00:06.16
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'S_ASSET'
Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
      11209  consistent gets
      11209  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        280  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

5) 
SQL> l
  1* select /*+ index(S_ASSET S_ASSET_U1_TEMP) */ count(ASSET_NUM) from ar.s_asset
SQL> /
COUNT(ASSET_NUM)
----------------
          292216
Elapsed: 00:00:03.46
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=456431 Bytes
          =23734412)

   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'S_ASSET_U1_TEMP' (UNIQUE) (Cost=26
           Card=456431 Bytes=23734412)
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       2493  consistent gets
          0  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        318  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> desc s_asset
 Name                            Null?    Type
 ------------------------------- -------- ----
 ROW_ID                          NOT NULL VARCHAR2(15)
 CREATED                         NOT NULL DATE
 CREATED_BY                      NOT NULL VARCHAR2(15)
 LAST_UPD                        NOT NULL DATE
 LAST_UPD_BY                     NOT NULL VARCHAR2(15)
 MODIFICATION_NUM                NOT NULL NUMBER(10)
 CONFLICT_ID                     NOT NULL VARCHAR2(15)
 ASSET_NUM                       NOT NULL VARCHAR2(100)
 BUILD                                    VARCHAR2(30)
 COMMENTS                                 VARCHAR2(250)
 END_DT                                   DATE
 INSTALL_DT                               DATE
 INVLOC_ID                                VARCHAR2(15)
 METER_CNT                                NUMBER(22,7)
 NAME                                     VARCHAR2(100)
 OU_ADDR_ID                               VARCHAR2(15)
 OWNER_ACCNT_ID                           VARCHAR2(15)
 OWNER_ASSET_NUM                          VARCHAR2(50)
 PAR_ASSET_ID                             VARCHAR2(15)
 PER_ADDR_ID                              VARCHAR2(15)
 PROD_ID                                  VARCHAR2(15)
 PROD_INV_ID                              VARCHAR2(15)
 PR_CON_ID                                VARCHAR2(15)
 PR_EMP_ID                                VARCHAR2(15)
 QTY                                      NUMBER(22,7)
 REF_NUMBER_1                             VARCHAR2(30)
 REF_NUMBER_2                             VARCHAR2(30)
 REF_NUMBER_3                             VARCHAR2(30)
 REGISTERED_DT                            DATE
 SERIAL_NUM                               VARCHAR2(100)
 SHIP_DT                                  DATE
 START_DT                                 DATE
 STATUS_CD                                VARCHAR2(30)
 VERSION                                  VARCHAR2(30)
 WARRANTY_END_DT                          DATE
 WARRANTY_START_DT                        DATE
 WARRANTY_TYPE_CD                         VARCHAR2(30)
 X_CFG_LEVEL                              NUMBER(22,7)
 X_CMR_NUM                                VARCHAR2(15)
 X_CONTR_NUM                              VARCHAR2(15)
 X_INSTALL_DT                             DATE
 X_INSTALL_FLG                            CHAR(1)
 X_LEASE_EXP_DT                           DATE
 X_LEASE_NAME                             VARCHAR2(50)
 X_MACCSR_NAME                            VARCHAR2(15)
 X_PCSR_NAME                              VARCHAR2(15)
 X_PROD_ID                                VARCHAR2(50)
 X_SYSTEM_ID                              VARCHAR2(15)
 X_SOFT_REL_NUM                           VARCHAR2(15)
 

Thank you for the detailed explanation. I have a followup question/clarification.

I made a typo in my previous message.
"3) Column PROD_ID is the leading column in unique concatenated index S_ASSET_U1_TEMP"

That should read - 
3) Column ASSET_NUM is the leading column..(as is demonstrated later in the same message).

"The RBO isn't smart enough to count rows via the index, not without some sort of predicate (and predicate would have to be on the column that is the leading edge of the index -- but since that column is NULLABLE you probably cannot even do that)"

I'm not sure if this applies here. ASSET_NUM is "NOT NULL". It is the leading column of the unique concatenated index. Even with a predicate, the query results in a FTS with a RBO.

SQL> l
  1* select ASSET_NUM from ar.S_ASSET where ASSET_NUM='1234'
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (FULL) OF 'S_ASSET'

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
      11209  consistent gets
      11204  physical reads
          0  redo size
        171  bytes sent via SQL*Net to client
        294  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


While when I do something as simple as the below, it nicely resorts to index range scan as expected. Am I not comparing apples to apples here? Why the different behaviour then?

SQL> create table t ( x varchar2(100) NOT NULL,y int);
Table created.
SQL> create unique index t_ind on t(x,y);
Index created.
SQL> select x from ar.t where x='1234';
no rows selected
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   INDEX (RANGE SCAN) OF 'T_IND' (UNIQUE)



Why did it do a FTS in the former, while a index range scan in the latter? x in the latter, and ASSET_NUM are both leading NOT NULL columns of a unique concatenated index.


PS : Yes, it is SIEBEL indeed! :-)). Why do you ask? Is the application notoriously famous?
 

Okay, it works as expected now! I still don't know "why" though. I found another table called S_ASSET_BACKUP_BY_ANU in there. Those were the initials of the dba in the previous company. I dropped that table. And now when I retry, it uses an index range scan. Doesn't make any sense to me. I didn't bother to check synonyms/grants/indexes on the table before I dropped it. 

Obviously this had something to do with that table. But what? Seems a mystery. Maybe I should restore that table to find out..

SQL> l
  1  select ASSET_NUM from AR.S_ASSET where ASSET_NUM='1234'
  2*
SQL> /

no rows selected

Elapsed: 00:00:02.75

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   INDEX (RANGE SCAN) OF 'S_ASSET_U1_TEMP' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        171  bytes sent via SQL*Net to client
        297  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 

Hi Tom,

Why does this query do full table scan on table t2 when there is a subquery? How can I avoid full table scan in this case.  Of course, four tables have different data in PROD.  It still refused to use index on t2 even I use hint.  We have to use RBO.  

Database version:  9204


Thanks so much for your help.

----------------------------------------------------

create table t1 as select * from sys.dba_objects;
create index t1_object_id_idx on t1(object_id);

create table t2 as select * from sys.dba_objects;
create index t2_object_id_idx on t2(object_id);

create table t3 as select * from sys.dba_objects;
create index t3_object_id_idx on t3(object_id);

create table t4 as select * from sys.dba_objects;
create index t4_object_id_idx on t4(object_id);

create or replace view v1(object_id) as 
    select t1.object_id
    from t1, t2
    where t1.object_id=t2.object_id
    union
    select t3.object_id
    from t3, t2
    where t3.object_id=t2.object_id;



SQL>explain plan for select * from v1
 where object_id=1;

SQL> @d:/oracle/ora92/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |       |       |       |
|   1 |  VIEW                | V1                |       |       |       |
|   2 |   SORT UNIQUE        |                   |       |       |       |
|   3 |    UNION-ALL         |                   |       |       |       |
|   4 |     NESTED LOOPS     |                   |       |       |       |
|*  5 |      INDEX RANGE SCAN| T1_OBJECT_ID_IDX  |       |       |       |
|*  6 |      INDEX RANGE SCAN| T2_OBJEC_ID_IDX   |       |       |       |
|   7 |     NESTED LOOPS     |                   |       |       |       |
|*  8 |      INDEX RANGE SCAN| T3_OBJECT_ID_IDX  |       |       |       |
|*  9 |      INDEX RANGE SCAN| T2_OBJEC_ID_IDX   |       |       |       |
--------------------------------------------------------------------------

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

   5 - access("T1"."OBJECT_ID"=1)
   6 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   8 - access("T3"."OBJECT_ID"=1)
   9 - access("T3"."OBJECT_ID"="T2"."OBJECT_ID")

Note: rule based optimization

25 rows selected.

SQL> truncate table plan_table;

Table truncated.


SQL> explain plan for select * from v1
  2  where object_id in (select object_id from t4
  3                      where object_id=1);

Explained.

SQL> @d:/oracle/ora92/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id  | Operation               |  Name             | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |       |       |       |
|   1 |  MERGE JOIN             |                   |       |       |       |
|   2 |   SORT JOIN             |                   |       |       |       |
|   3 |    VIEW                 | V1                |       |       |       |
|   4 |     SORT UNIQUE         |                   |       |       |       |
|   5 |      UNION-ALL          |                   |       |       |       |
|   6 |       NESTED LOOPS      |                   |       |       |       |
|   7 |        TABLE ACCESS FULL| T2                |       |       |       |
|*  8 |        INDEX RANGE SCAN | T1_OBJECT_ID_IDX  |       |       |       |
|   9 |       NESTED LOOPS      |                   |       |       |       |
|  10 |        TABLE ACCESS FULL| T2                |       |       |       |
|* 11 |        INDEX RANGE SCAN | T3_OBJECT_ID_IDX  |       |       |       |
|* 12 |   SORT JOIN             |                   |       |       |       |
|  13 |    VIEW                 | VW_NSO_1          |       |       |       |
|  14 |     SORT UNIQUE         |                   |       |       |       |
|* 15 |      INDEX RANGE SCAN   | T4_OBJECT_ID_IDX  |       |       |       |
-----------------------------------------------------------------------------

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

   8 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  11 - access("T3"."OBJECT_ID"="T2"."OBJECT_ID")
  12 - access("V1"."OBJECT_ID"="VW_NSO_1"."$nso_col_1")
       filter("V1"."OBJECT_ID"="VW_NSO_1"."$nso_col_1")
  15 - access("T4"."OBJECT_ID"=1)

Note: rule based optimization

32 rows selected.

SQL>