Using analytical function rank() - an example of improving query performance

Today, I have encountered a sql in a production database, which looks like

select distinct rs, bid, 
     [some_other_cols ...  ]
  from
     prod_table n
   where ee_date in (
       select max(ee_date) from
                prod_table n1
           where n1.bid = n.bid
             and n1.cc = n.cc
             and n1.rs=n.rs)



---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |   571 | 51390 |   197   (4)| 00:00:03 |
|   1 |  HASH UNIQUE          |               |   571 | 51390 |   197   (4)| 00:00:03 |
|*  2 |   FILTER              |               |       |       |            |          |
|   3 |    HASH GROUP BY      |               |   571 | 51390 |   197   (4)| 00:00:03 |
|*  4 |     HASH JOIN         |               | 22250 |  1955K|   194   (3)| 00:00:03 |
|   5 |      TABLE ACCESS FULL| PROD_TABLE    | 22250 |   738K|    96   (2)| 00:00:02 |
|   6 |      TABLE ACCESS FULL| PROD_TABLE    | 22250 |  1216K|    97   (3)| 00:00:02 |
---------------------------------------------------------------------------------------

It contains a correlated sub-query. I rewrote it by using the analytical function rank(). I found that the consistent gets reduced by half

select distinct rs,bid, 
     [some_other_cols ...  ]
    from
    (
    select  n1.*,
           rank() over ( partition by bid, cc, rs order by ee_date desc ) rank
    from  prod_table n1
    )
    where rank =1;


-------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               | 22250 |   956K|       |   602   (2)| 00:00:08
|   1 |  HASH UNIQUE              |               | 22250 |   956K|  2984K|   602   (2)| 00:00:08
|*  2 |   VIEW                    |               | 22250 |   956K|       |   350   (2)| 00:00:05
|*  3 |    WINDOW SORT PUSHED RANK|               | 22250 |   956K|  2984K|   350   (2)| 00:00:05
|   4 |     TABLE ACCESS FULL     | PROD_TABLE    | 22250 |   956K|       |    98   (4)| 00:00:02
-------------------------------------------------------------------------------------------------

Composite index selectivity

I did tests with 10053 trace today and found that Oracle CBO behavior changes regarding compute selectivity of 3-column composite index from 9.2.0.8 to 10.2.0.4. (note: no histogram on any columns)

In 9i: selectivty of index(col1, col2, col3)= density (co11) x denisty (col2) x denisity(col3);

In 10g and 11g selectivity of index(col1, col2, col3) = 1/DISTINCT_KEYS where DISTINCT_KEYS is from index stats obtainable through dba_indexes.


Here is the test case which mimics the production able and the problem sql.

----- begin of the test case ----

drop table test;
create table test
as
select level id,
       trunc(mod(level,80)) id1,
       20*trunc(mod(level, 40)) id2,
       1    id3,
       trunc(mod(level,50)) id4
from dual
connect by level <=1000000;


create index test_ix1 on test(id1, id2, id3);

exec dbms_stats.gather_table_stats(user,'test', method_opt=>'FOR ALL COLUMNS size 1', cascade=>true);


alter session set tracefile_identifier = test;
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

explain plan for
select id
from test
where id1 = 20
and id3=1
and id2 = 400;

ALTER SESSION SET EVENTS '10053 trace name context off';
Exit
------ end of test case  -----------------------

#### 9.2.0.8  10053 trace excerpt


***************************************
SINGLE TABLE ACCESS PATH
Column:        ID1  Col#: 2      Table: TEST   Alias: TEST
    NDV: 80        NULLS: 0         DENS: 1.2500e-02 LO:  0  HI: 79
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        ID3  Col#: 4      Table: TEST   Alias: TEST
    NDV: 1         NULLS: 0         DENS: 1.0000e+00 LO:  1  HI: 1
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:        ID2  Col#: 3      Table: TEST   Alias: TEST
    NDV: 40        NULLS: 0         DENS: 2.5000e-02 LO:  0  HI: 780
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: TEST     ORIG CDN: 1000000  ROUNDED CDN: 313  CMPTD CDN: 313
  Access path: tsc  Resc:  857  Resp:  847
  Access path: index (equal)
      Index: TEST_IX1
  TABLE: TEST
      RSC_CPU: 711380   RSC_IO: 82
  IX_SEL:  0.0000e+00  TB_SEL:  3.1250e-04
  BEST_CST: 83.00  PATH: 4  Degree:  1


#### 10.2.0.4  10053 trace excerpt

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): ID1(NUMBER)
    AvgLen: 3.00 NDV: 81 Nulls: 0 Density: 0.012346 Min: 0 Max: 79
  Column (#4): ID3(NUMBER)
    AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 1 Min: 1 Max: 1
  Column (#3): ID2(NUMBER)
    AvgLen: 4.00 NDV: 40 Nulls: 0 Density: 0.025 Min: 0 Max: 780
  Table: TEST  Alias: TEST
    Card: Original: 1003092  Rounded: 310  Computed: 309.60  Non Adjusted: 309.60
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  730.88  Resp: 730.88  Degree: 0
      Cost_io: 700.00  Cost_cpu: 284170229
      Resp_io: 700.00  Resp_cpu: 284170229
  Access Path: index (AllEqRange)
    Index: TEST_IX1
    resc_io: 3173.00  resc_cpu: 27721329
    ix_sel: 0.0125  ix_sel_with_filters: 0.0125
    Cost: 3176.01  Resp: 3176.01  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 730.88  Degree: 1  Resp: 730.88  Card: 309.60  Bytes: 0

Note:
select index_name, distinct_keys from user_indexes where index_name='TEST_IX1';

INDEX_NAME                     DISTINCT_KEYS
------------------------------ -------------
TEST_IX1                                  80


#### 11.2.0.1  10053 trace excerpt


***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TEST[TEST]
  ColGroup (#1, Index) TEST_IX1
    Col#: 2 3 4    CorStregth: 40.00
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  Table: TEST  Alias: TEST
    Card: Original: 1000000.000000  Rounded: 12500  Computed: 12500.00  Non Adjusted: 12500.00
  Access Path: TableScan
    Cost:  629.80  Resp: 629.80  Degree: 0
      Cost_io: 617.00  Cost_cpu: 283372261
      Resp_io: 617.00  Resp_cpu: 283372261
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  ColGroup Usage:: PredCnt: 3  Matches Full: #1  Partial:  Sel: 0.0125
  Access Path: index (AllEqRange)
    Index: TEST_IX1
    resc_io: 3173.00  resc_cpu: 27721329
    ix_sel: 0.012500  ix_sel_with_filters: 0.012500
    Cost: 3174.25  Resp: 3174.25  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 629.80  Degree: 1  Resp: 629.80  Card: 12500.00  Bytes: 0

Notice the ix_sel line in the above 10053 trace excerpt; for 10g and 11g it equal to 1/80. for 9i seems computed through multiplying selectivity of eeach column.

Update:
Came accross Jonathan Lewis's comment, and came to know this CBO behavior changes acctually from 10.1.0.4 ie. 10.1.0.4 is still similar to 9i behavior.


Below is his comment: (http://kr.forums.oracle.com/forums/thread.jspa?messageID=3153224)

--------
For a single-table query with the predicate
(full list of index columns) = (set of values)

The selectivities vary across version as follows:

11.1.0.6: Table selectivity and index selectivity given by distinct_keys in index
10.2.0.1: Table selectivity given by product of column selectivities, index selectivity given by distinct_keys in index
10.1.0.4: Table selectivity and index selectivity given by product of column selectivities

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

JL's original post about this behavior: http://jonathanlewis.wordpress.com/2008/03/11/everything-changes/

Privileges to enable a non-DBA user to generate AWR report

The following privileges seem working for the stated purpose:

GRANT SELECT ON SYS.V_$DATABASE TO xxx
/
GRANT SELECT ON SYS.V_$INSTANCE TO xxx
/
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO xxx
/
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO xxx
/
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO xxx
/
GRANT ADVISOR TO xxx
/

CURSOR_SHARING and PL/SQL procedure

With cursor_sharing=FORCE, in most cases, Oracle will replace literal values with bind variables, however,this is not the case for PL/SQL blocks. See the following test case. When using CALL proc_name(), it can. This behavior is mentioned in Jonathen Lewis's book "Cost-based Oracle Fundamentals" p159.

SQL>alter session set cursor_sharing=force;

Session altered.

SQL>drop table t;

Table dropped.

SQL>create table t(a number);

Table created.

SQL>
SQL>create or replace procedure denis_proc(a in number)
  2   is
  3  begin
  4    insert into t values(a);
  5   commit;
  6  end;
  7  /

Procedure created.

SQL>
SQL>exec denis_proc(1);

PL/SQL procedure successfully completed.

SQL>exec denis_proc(2);

PL/SQL procedure successfully completed.

SQL>exec denis_proc(3);

PL/SQL procedure successfully completed.

SQL>call denis_proc(4);

Call completed.

SQL>call denis_proc(5);

Call completed.

SQL>call denis_proc(6);

Call completed.

SQL>
SQL>col sql_text format a30
SQL>
SQL>select sql_id, executions, sql_text from v$sqlarea where sql_text like '%denis_proc%';

SQL_ID1G        EXECUTIONS SQL_TEXT
------------- ---------- ------------------------------
1ms5rkrgwt28v          1 BEGIN denis_proc(1); END;
0vcasv51bxgpv          1 BEGIN denis_proc(3); END;
dcna79fxryrkx          1 BEGIN denis_proc(2); END;
6skbuvu6fb9k0          3 call denis_proc(:"SYS_B_0")

SQL>
SQL>select * from t;

         A
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL>

Effect of a SQL* Plus environment variable: set pagesize 0

Last night, I troubleshooted a ksh shell script, in which a variable is assigned a value that is returned from SQL* Plus commands. I found that without 'set pagesize 0', the value returned had an undesirable leading space. Below is a test case that demonstrate this effect.

SQL> select col1, col3 from t;
 
      COL1 C
---------- -
         1 Y


$ cat test3.sh 
#!/bin/ksh 
 
RET1=` sqlplus -s  / << EOF
set  echo off head off feed off verify off term off  
select col3 from t
where  col1=1;
EOF
`
 
echo "RET1  >>>>"$RET1"<<<<"
if [[ $RET1 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi
 
RET2=` sqlplus -s  / << EOF
set echo off head off feed off verify off term off 
set pagesize 0
select col3 from t
where  col1=1;
EOF
`
 
echo "RET2  >>>>"$RET2"<<<<"
if [[ $RET2 == 'Y' ]]; then
  echo "Yes"
else
  echo "No"
fi


$ ./test3.sh 
RET1  >>>> Y<<<<
No
RET2  >>>>Y<<<<
Yes