The Momen Blog

Who says Full Table Scans are Evil?

noreply@blogger.com (Asif Momen) — Tue, 14 Jul 2009 11:35:00 +0000

Recently, I had a developer complaining that a query which normally runs very fast is now taking huge amount of time to complete.

Following is the culprit query and its execution plan:

SELECT  DISTINCT SUBSTR(A.MF_NO,4) MF_NO,
 B.SRT_SEQ,
 SUBSTR(B.MAIN_CODE,1,6) MAIN_CODE
  FROM  DETAIL_TBL A,
        MASTER_TBL B    
  WHERE A.MRF_ID   = B.MRF_ID    
    AND A.MRF_CODE = B.MRF_CODE    
    AND TRIM(SUBSTR(A.MF_NO,4)) IN (SELECT LPAD(MF_NO,4,0) 
                                     FROM REMOTE_TBL@RMLINK 
                                    WHERE USER_LEVEL = 'RO'  
                                      AND STATUS_CODE <> 'D');

Execution Plan
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |       |       |  6646 (100)|          |        |      |
|   1 |  HASH UNIQUE                 |                       |     1 |    62 |  6646   (2)| 00:01:34 |        |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| MASTER_TBL            |     1 |    30 |     5   (0)| 00:00:01 |        |      |
|   3 |    NESTED LOOPS              |                       |     1 |    62 |  6645   (2)| 00:01:34 |        |      |
|*  4 |     HASH JOIN                |                       |     1 |    32 |  6640   (2)| 00:01:33 |        |      |
|   5 |      REMOTE                  | REMOTE_TBL            |     1 |    12 |    24   (0)| 00:00:01 |  REMDB | R->S |
|   6 |      TABLE ACCESS FULL       | DETAIL_TBL            |  4159K|    79M|  6589   (2)| 00:01:33 |        |      |
|*  7 |     INDEX RANGE SCAN         | SYS_C0011692          |     3 |       |     2   (0)| 00:00:01 |        |      |
----------------------------------------------------------------------------------------------------------------------

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

   4 - access(TRIM(SUBSTR("A"."MF_NO",:SYS_B_3))=LPAD("MF_NO",:SYS_B_4,TO_CHAR(:SYS_B_5)))
   7 - access("A"."MRF_ID"="B"."MRF_ID" AND "A"."MRF_CODE"="B"."MRF_CODE")

Using AWR SQL report (awrsqrpt.sql) for the time when the query was performing better shows that a Full Table Scan was performed on the MASTER_TBL.

Execution Plan
----------------------------------------------------------
Plan hash value: 1931109479

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |  2919K|   172M|       | 42564   (2)| 00:09:56 |        |      |
|   1 |  HASH UNIQUE         |                       |  2919K|   172M|   424M| 42564   (2)| 00:09:56 |        |      |
|*  2 |   HASH JOIN          |                       |  2919K|   172M|    11M| 15147   (2)| 00:03:33 |        |      |
|   3 |    TABLE ACCESS FULL | MASTER_TBL            |   289K|  8494K|       |  5590   (2)| 00:01:19 |        |      |
|*  4 |    HASH JOIN         |                       |  1225K|    37M|       |  6833   (2)| 00:01:36 |        |      |
|   5 |     REMOTE           | REMOTE_TBL            |    29 |   348 |       |    24   (0)| 00:00:01 |  REMDB | R->S |
|   6 |     TABLE ACCESS FULL| DETAIL_TBL            |  4191K|    79M|       |  6781   (2)| 00:01:35 |        |      |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."MRF_ID"="B"."MRF_ID" AND "A"."MRF_CODE"="B"."MRF_CODE")
   4 - access(TRIM(SUBSTR("A"."MF_NO",4))=LPAD("MF_NO",4,'0'))

When we add a Full Table Scan (FTS) hint on the MASTER_TBL, the same query ran in less than 20 seconds. We can modify the query to include FTS hint as it is a home grown application.

SELECT  /*+ FULL(A) */ DISTINCT SUBSTR(A.MF_NO,4) MF_NO,
 B.SRT_SEQ,
 SUBSTR(B.MAIN_CODE,1,6) MAIN_CODE
  FROM  DETAIL_TBL A,
        MASTER_TBL B    
  WHERE A.MRF_ID   = B.MRF_ID    
    AND A.MRF_CODE = B.MRF_CODE    
    AND TRIM(SUBSTR(A.MF_NO,4)) IN (SELECT LPAD(MF_NO,4,0) 
                                     FROM REMOTE_TBL@RMLINK 
                                    WHERE USER_LEVEL = 'RO'  
                                      AND STATUS_CODE <> 'D');


Execution Plan
----------------------------------------------------------
Plan hash value: 1931109479

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |  2919K|   172M|       | 42564   (2)| 00:09:56 |        |      |
|   1 |  HASH UNIQUE         |                       |  2919K|   172M|   424M| 42564   (2)| 00:09:56 |        |      |
|*  2 |   HASH JOIN          |                       |  2919K|   172M|    11M| 15147   (2)| 00:03:33 |        |      |
|   3 |    TABLE ACCESS FULL | MASTER_TBL            |   289K|  8494K|       |  5590   (2)| 00:01:19 |        |      |
|*  4 |    HASH JOIN         |                       |  1225K|    37M|       |  6833   (2)| 00:01:36 |        |      |
|   5 |     REMOTE           | REMOTE_TBL            |    29 |   348 |       |    24   (0)| 00:00:01 |  REMDB | R->S |
|   6 |     TABLE ACCESS FULL| DETAIL_TBL            |  4191K|    79M|       |  6781   (2)| 00:01:35 |        |      |
----------------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."MRF_ID"="B"."MRF_ID" AND "A"."MRF_CODE"="B"."MRF_CODE")
   4 - access(TRIM(SUBSTR("A"."MF_NO",4))=LPAD("MF_NO",4,'0'))

This worked as an emergency painkiller before the routine lab tests are performed and an appropriate action is decided.

In the bad execution plan, to access the master table (MASTER_TBL), optimizer is doing an index range scan (“SYS_C0011692”) and the cardinality reported is “3”. This is where the real problem is hiding. Drilling into the tables, columns and indexes for both the tables revealed this information:

Table Name: MASTER_TBL
Primary Key: MRF_ID, MRF_CODE, ORDNO
Num Rows: 287,456

Table Name: CHILD_TBL
Primary Key: MRF_ID, MRF_CODE, ORDNO
Num Rows: 4,159,964

Oops… there are nearly 300,000 records in the MASTER_TBL, but the execution plan indicates a cardinality of merely 3. So, there is something’s serious wrong somewhere. First suspect is table and index statistics. As suspected, statistics on MASTER_TBL were botched up. These were the steps performed on the MASTER_TBL:

1) Data was cleaned 2) Initial data load was run 3) Statistics were collected 4) Remaining major chunk of the data loaded

After collecting fresh statistics on the MASTER_TBL things got back on track. Also, data belonging to columns “MRF_ID” & “MRF_CODE” is evenly spread across all the used blocks in MASTER_TBL & CHILD_TBL tables. Moreover, there is no limiting condition on these columns which could encourage optimizer to go with an index access path. So, it would make no sense for the optimizer to go with an index access path.

Cardinality Analysis - A Review

noreply@blogger.com (Asif Momen) — Sun, 17 May 2009 10:44:00 +0000

After reading Michelle Deng’s “Tuning by Cardinality Feedback” paper on Jonathan's blog, I thought of re-creating the whole scenario to actually feel what is going on.

I did this test on Oracle 10g Release 2 (10.2.0.3) database with a block size of 16K. We first create the table and populate it in such a way that we match exactly with Michelle’s data.

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio NLSRTL Version 10.2.0.3.0 - Production Elapsed: 00:00:00.03 SQL> SQL> show parameter db_block_size NAME TYPE VALUE ------------------------------------ ----------- ---------- db_block_size integer 16384 SQL>

I performed couple of tests with different data distributions and here’s the first test case:

Test Case 1

Create and Populate Data

drop table prod_fctr_mv purge;

create table prod_fctr_mv(
 extract_mkt_ssk varchar2(5), 
 mkt_ssk     varchar2(20), 
 mkt_id      number(16), 
 hier_type    varchar2(20), 
 txt       varchar2(500));

insert into prod_fctr_mv 
 select '00006', '00006', 3, 'CORP', 
        rpad('*', 285, '*') 
   from dual 
  connect by level <= 42;


insert into prod_fctr_mv
  select
         lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
         lpad(mod(level, 73), 5, '0') mkt_ssk ,
         mod(level, 91) mkt_id ,
         decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                               3, 'WALL', 'CORP') hier_type ,
         rpad('*', 285, '*')
    from dual
   connect by level <= 4965 - 42 ;


update prod_fctr_mv set mkt_ssk = null 
 where mkt_ssk <> '00006' 
   and rownum <= 296;

update (select mkt_id 
          from prod_fctr_mv 
         where mkt_id <> 3) 
   set mkt_id = 3
 where rownum <= 72;

update (select extract_mkt_ssk  
          from prod_fctr_mv 
         where extract_mkt_ssk <> '00006'
           and mkt_id <> 3) 
   set extract_mkt_ssk = '00006' 
 where rownum <= 77;

select count(*) 
  from prod_fctr_mv
 where mkt_ssk  = '00006' ;

update (select mkt_ssk 
          from prod_fctr_mv 
         where mkt_ssk <> '00006' 
           and mkt_ssk is not null ) 
   set mkt_ssk = '00006' 
 where rownum <= 59;

update (select hier_type 
          from prod_fctr_mv 
         where hier_type = 'CORP' 
           and mkt_id <> 3
           and extract_mkt_ssk <> '00006')
   set hier_type = decode(mod(rownum, 2), 0, 'TALL', 'BALL') 
 where rownum <= 554;

commit;

Verify Data

Now that the data is populated, let’s see did we get on the right track.

SQL> exec dbms_stats.gather_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.42
SQL> 
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, 
  2         chain_cnt, avg_row_len
  3   from user_tables
  4  where table_name = 'PROD_FCTR_MV';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
PROD_FCTR_MV                         4965        108            0          0          0         305

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> select column_name, num_distinct, density, num_buckets, 
  2         num_nulls, histogram
  3    from user_tab_columns
  4   where table_name = 'PROD_FCTR_MV';

COLUMN_NAME                    NUM_DISTINCT    DENSITY NUM_BUCKETS  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------- ---------------
TXT                                       1          1           1          0 NONE
HIER_TYPE                                 5 .000100705           5          0 FREQUENCY
MKT_ID                                   91 .000100705          91          0 FREQUENCY
MKT_SSK                                  73 .000107089          73        296 FREQUENCY
EXTRACT_MKT_SSK                         100 .000100705         100          0 FREQUENCY

Elapsed: 00:00:00.07
SQL> 
SQL> 
SQL> select count(decode(mkt_id, 3, 1)) mkt_id,
  2         count(decode(extract_mkt_ssk, '00006', 1)) extract_mkt_ssk,
  3         count(decode(mkt_ssk, '00006', 1)) mkt_ssk,
  4         count(decode(hier_type, 'CORP', 1)) hier_type
  5    from prod_fctr_mv ;

    MKT_ID EXTRACT_MKT_SSK    MKT_SSK  HIER_TYPE
---------- --------------- ---------- ----------
       169             169        169       2441

Elapsed: 00:00:00.01
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';

  COUNT(*)
----------
        42

Elapsed: 00:00:00.03
SQL> 
SQL>

Search an optimal value for OPTIMIZER_DYNAMIC_SAMPLING parameter

Yea, the total number of rows, the count for the key columns, count for the query in question, and the statistics, they all match to what was presented by Michelle.

The real fun starts here:

SQL> set autotrace traceonly exp
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    20 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |     1 |    20 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

Delete the statistics and run the same query:

SQL> exec dbms_stats.delete_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL>

Optimizer has considered dynamic sampling but still the number of rows reported is now “72”, which is nearly twice than the actual number of rows. Let’s gradually increase the value of OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and check the plan.

SQL> alter session set optimizer_dynamic_sampling=3;

Session altered.

Elapsed: 00:00:00.00
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL> alter session set optimizer_dynamic_sampling=4;

Session altered.

Elapsed: 00:00:00.00
SQL>  @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL> alter session set optimizer_dynamic_sampling=5;

Session altered.

Elapsed: 00:00:00.00
SQL>   @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    72 |  2952 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL> alter session set optimizer_dynamic_sampling=6;

Session altered.

Elapsed: 00:00:00.01
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    42 |  1722 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL>
SQL> set autotrace off
SQL>

Finally, when OPTIMIZER_DYNAMIC_SAMPLING parameter reached to a value of “6”, the plan is now reporting correct cardinality.

Test Case 2

Let’s run through the entire test again with a slight modification. Instead of inserting all the 42 rows together, we will insert them scattered in between.

Create and Populate Data

In this test case, we will insert one row of our interest after every 100 rows. So, here goes the script:

drop table prod_fctr_mv purge;

create table prod_fctr_mv(
 extract_mkt_ssk varchar2(5), 
 mkt_ssk     varchar2(20), 
 mkt_id      number(16), 
 hier_type    varchar2(20), 
 txt       varchar2(500));

begin
  for i in 1..42 loop
    insert into prod_fctr_mv 
     select '00006', '00006', 3, 'CORP', 
            rpad('*', 285, '*') 
       from dual 
      connect by level <= 1;

    insert into prod_fctr_mv
      select
            lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
            lpad(mod(level, 73), 5, '0') mkt_ssk ,
            mod(level, 91) mkt_id ,
            decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                                  3, 'WALL', 'CORP') hier_type ,
            rpad('*', 285, '*')
       from dual
      connect by level <= 100 ;
  end loop;

  commit;
end;
/

select count(*) 
  from prod_fctr_mv;

  insert into prod_fctr_mv
    select
          lpad(mod(level, 100), 5, '0') extract_mkt_ssk ,
          lpad(mod(level, 73), 5, '0') mkt_ssk ,
          mod(level, 91) mkt_id ,
          decode(mod(level, 5), 0, 'CORP', 1, 'CALL', 2, 'CORP', 
                                3, 'WALL', 'CORP') hier_type ,
          rpad('*', 285, '*')
     from dual
    connect by level <= 723 ;

update prod_fctr_mv set mkt_ssk = null 
 where mkt_ssk <> '00006' 
   and rownum <= 296;

update (select mkt_id 
          from prod_fctr_mv 
         where mkt_id <> 3
           and extract_mkt_ssk <> '00006') 
   set mkt_id = 3
 where rownum <= 35;

update (select extract_mkt_ssk  
          from prod_fctr_mv 
         where extract_mkt_ssk <> '00006'
           and mkt_id <> 3) 
   set extract_mkt_ssk = '00006' 
 where rownum <= 77;

update (select mkt_ssk 
          from prod_fctr_mv 
         where mkt_ssk <> '00006' 
           and mkt_ssk is not null ) 
   set mkt_ssk = '00006' 
 where rownum <= 33;

update (select hier_type 
          from prod_fctr_mv 
         where hier_type = 'CORP' 
           and mkt_id <> 3
           and extract_mkt_ssk <> '00006')
   set hier_type = decode(mod(rownum, 2), 0, 'TALL', 'BALL') 
 where rownum <= 554;

commit;

Verify Data

Let us run through the same queries to verify data population.

SQL> exec dbms_stats.gather_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.09
SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, 
  2         chain_cnt, avg_row_len
  3   from user_tables
  4  where table_name = 'PROD_FCTR_MV';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
------------------------------ ---------- ---------- ------------ ---------- ---------- -----------
PROD_FCTR_MV                         4965        108            0          0          0         305

Elapsed: 00:00:00.01
SQL> 
SQL> 
SQL> select column_name, num_distinct, density, num_buckets, 
  2         num_nulls, histogram
  3    from user_tab_columns
  4   where table_name = 'PROD_FCTR_MV';

COLUMN_NAME                    NUM_DISTINCT    DENSITY NUM_BUCKETS  NUM_NULLS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------- ---------------
TXT                                       1          1           1          0 NONE
HIER_TYPE                                 5 .000100705           5          0 FREQUENCY
MKT_ID                                   91 .000100705          91          0 FREQUENCY
MKT_SSK                                  73 .000107089          73        296 FREQUENCY
EXTRACT_MKT_SSK                         100 .000100705         100          0 FREQUENCY

Elapsed: 00:00:00.03
SQL> 
SQL> select count(decode(mkt_id, 3, 1)) mkt_id,
  2         count(decode(extract_mkt_ssk, '00006', 1)) extract_mkt_ssk,
  3         count(decode(mkt_ssk, '00006', 1)) mkt_ssk,
  4         count(decode(hier_type, 'CORP', 1)) hier_type
  5    from prod_fctr_mv ;

    MKT_ID EXTRACT_MKT_SSK    MKT_SSK  HIER_TYPE
---------- --------------- ---------- ----------
       169             169        169       2441

Elapsed: 00:00:00.01
SQL> 
SQL> select count(*) 
  2    from prod_fctr_mv
  3   where mkt_id  = 3
  4     and extract_mkt_ssk = '00006'
  5     and mkt_ssk = '00006'
  6     and hier_type = 'CORP';

  COUNT(*)
----------
        42

Elapsed: 00:00:00.01
SQL>

Search an optimal value for OPTIMIZER_DYNAMIC_SAMPLING parameter

We managed to get the number of records and other statistics same as that of the original document. Now, we will hunt for the optimal value of OPTIMIZER_DYNAMIC_SAMPLING parameter.

SQL> set autotrace traceonly exp
SQL> 
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    20 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |     1 |    20 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

SQL> exec dbms_stats.delete_table_stats(user, 'prod_fctr_mv');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL> 
SQL> @t
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2899207340

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    41 |    42   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |    41 |            |          |
|*  2 |   TABLE ACCESS FULL| PROD_FCTR_MV |    36 |  1476 |    42   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("MKT_ID"=3 AND "EXTRACT_MKT_SSK"='00006' AND
              "MKT_SSK"='00006' AND "HIER_TYPE"='CORP')

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

SQL>

We get a better cardinality reported by the Optimizer at the default level of dynamic sampling (OPTIMIZER_DYNAMIC_SAMPLING=2).

Conclusion

In the above test cases the Oracle optimizer started displaying correct number of rows when OPTIMIZER_DYNAMIC_SAMPLING was set to 6 with interested data being inserted together while the default setting for OPTIMIZER_DYNAMIC_SAMPLING worked when data was almost equally distributed.

Michelle’s data distribution might be such that the Optimizer was able report correct number of rows at OPTIMIZER_DYNAMIC_SAMPLING=4.

Lessons learned include:

1) Column correlation could really mislead Optimizer, and

2) Data distribution plays equally critical role.

ORA-07445 When Querying V$SQL_PLAN View

noreply@blogger.com (Asif Momen) — Wed, 15 Apr 2009 11:07:00 +0000

On one of our production databases, I was looking for a particular index usage as how often was it used and at what times it was used. To achieve this, I did a little mining on AWR repository tables and found out that it was used 11 times in the last one month (AWR retention period).

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.01
SQL>


SQL> select sp.sql_id,
  2         sp.options,
  3         count(1) cnt
  4    from dba_hist_sql_plan sp,
  5         dba_hist_sqlstat ss
  6    where sp.sql_id = ss.sql_id
  7      and sp.object_owner  =  'PAYMAST' 
  8      and sp.operation like '%INDEX%'
  9      and sp.object_name = 'IDX_COMMIT_DET'
 10  group by sp.sql_id, 
 11           sp.options
 12  order by sp.sql_id, 
 13           sp.options;

SQL_ID        OPTIONS                               CNT
------------- ------------------------------ ----------
1b4xf87ntvfgn SAMPLE FAST FULL SCAN                   1
8qaj7c6wqcyvg SAMPLE FAST FULL SCAN                   7
9jcrv4kunuhg4 SAMPLE FAST FULL SCAN                   1
afzq942kp848t RANGE SCAN                              1
ar6c0r4s4kntp RANGE SCAN                              1

Elapsed: 00:00:00.54
SQL>

However, querying V$SQL_PLAN view for the same index threw ORA-03113 error. Hmm, I connected to the database and ran the same query again and it resulted in the same error.

SQL> select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET';

ADDRESS          HASH_VALUE SQL_ID        PLAN_HASH_VALUE CHILD_ADDRESS    CHILD_NUMBER TIMESTAMP       
---------------- ---------- ------------- --------------- ---------------- ------------ ------------
FILTER_PREDICATES                                                                                       
----------------------------------------------------------------------------------------------------
REMARKS                                                                                                 
----------------------------------------------------------------------------------------------------
07000000D98392B0 2773750041 afzq942kp848t       380836487 07000000D986D420            0 15-APR2009 12:2
"VOU_TYPE_NAME"=:B1                                                                                     


ERROR:
ORA-03113: end-of-file on communication channel



ERROR:
ORA-03114: not connected to ORACLE


Elapsed: 00:00:03.82
SQL>

Oracle has bumped some information into the alert log and here it is:

Errors in file /dbdata1/oradba/admin/PAYPROD/udump/payprod_ora_6488244.trc:
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []

and it created a 18MB trace file in the USER_DUMP_DEST which has all the gory details for the Oracle Support to work on. Below is an excerpt from the trace file:

*** 2009-04-15 12:27:46.646
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [msqsub+0008] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []
Current SQL statement for this session:
select * from V$SQL_PLAN where  operation  ='INDEX' and object_name ='IDX_COMMIT_DET'

The ORA-00600/ORA-07445 Lookup Tool in Oracle Metalink reports “A description for this ORA-07445 error is not yet available”.

I have to end up by lodging an SR and wait for Oracle Support’s response.

Strange Data Guard Issue

noreply@blogger.com (Asif Momen) — Tue, 17 Mar 2009 11:56:00 +0000

We have recently failed over one of our database to the DRC. At that time the production archivelog sequence was 80,000 plus. After staying there for couple of hours we recreated a physical standby database on the primary and switched back to original primary location. Everything went fine without any hoo-ha.

The failover and switchover took place on February 20, 2009. We have an automated job on all our standby databases to check whether the Standby is lagging behind the Primary, if so, then it automatically restores the missing archivelogs and applies them.

This job was working fine until day before yesterday (March 15, 2009) but started reporting errors thereafter. It failed with “RMAN-20242” error.

Starting restore at 15-MAR-09
released channel: ch12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/15/2009 07:26:59
RMAN-06004: ORACLE error from recovery catalog database: 
RMAN-20242: specification does not match any archive log in the recovery catalog

When notified, I queried V$ARCHIVE_GAP and the output of this query took me to a surprise:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL>
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1           776          82468

SQL>

Well, all of a sudden Oracle thinks that it is 81 thousand archive logs behind the primary. I tried to switching couple of archive logs on the production and the gap was still intact. I don't really know from where did Oracle took this information.

The automated job was trying to restore archives between 776 and 82468 and was kicked out by the recovery catalog database saying, there are no such archive logs (in fact there exist no such archivelogs).

I created a standby controlfile on the production database and replaced the controlfile on the standby database with the new one. The standby database seems to be happy with this version of controlfile and have stopped reporting any gap and the automated job is also leading a happy life.

SQL> select * from v$archive_gap;

no rows selected

SQL>

Working with DBMS_CRYPTO Package

noreply@blogger.com (Asif Momen) — Sat, 07 Feb 2009 14:18:00 +0000

Oracle Database 10g provides means to encrypt and decrypt your sensitive data using the built-in API called DBMS_CRYPTO. Using DBMS_CRYPTO API one can encrypt sensitive information like SSN, Credit Card Numbers, Debit Card Numbers, etc stored in the database as these are not supposed to be stored in plain text for security reasons. The DBMS_CRYPTO toolkit is easy to use and is intended to replace DBMS_OBFUSCATION_TOOLKIT which was introduced in 8.1.6.

The concept behind any encryption toolkit is to have two things: 1) An algorithm, the actual logic, and 2) The encryption key

Using these combination's we can safely encrypt our data.

In this blog entry, let us see how DBMS_CRYPTO can be used to encrypt data and also consider its performance implications.

To start with, lets create a test table and insert couple of records in plain text:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> create table emp(empid number, ename varchar2(100), ssn varchar2(80));

Table created.

SQL> insert into emp values (1, 'SMITH',        123456701);

1 row created.

SQL> insert into emp values (2, 'ALLEN',        123456702);

1 row created.

SQL> insert into emp values (3, 'WARD',         123456703);

1 row created.

SQL> commit;
SQL> set line 10000
SQL> column ename format a30
SQL> column ssn format a40
SQL> select * from emp;

     EMPID ENAME                          SSN
---------- ------------------------------ -------------
         1 SMITH                          123456701
         2 ALLEN                          123456702
         3 WARD                           123456703

SQL>

The SSN has been inserted into the table in plain text and can be read by anybody having SELECT privilege on EMP table. Now, lets create a package using DBMS_CRYPTO API to handle encryption and decryption.

SQL> Create or replace package cryptit is
  2    Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC;
  3    Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC;
  4  End cryptit;
  5  /

Package created.

SQL>
SQL> Create or replace package body cryptit is
  2    V_Key       RAW(128) := UTL_RAW.cast_to_raw('testkey1');          -- Key
  3
  4    Function encrypt_data( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
  5    IS
  6      l_data RAW(2048) := utl_raw.cast_to_raw(p_data);
  7      l_encrypted RAW(2048);
  8    BEGIN
  9      NULL;
 10      l_encrypted := dbms_crypto.encrypt                        -- Algorithm
 11                     ( src => l_data,
 12                       typ => DBMS_CRYPTO.DES_CBC_PKCS5,
 13                       key => V_KEY );
 14
 15      Return l_encrypted;
 16    END encrypt_data;
 17
 18    Function decrypt_data( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
 19    IS
 20      l_decrypted RAW(2048);
 21    BEGIN
 22      l_decrypted := dbms_crypto.decrypt                              -- Algorithm
 23                      ( src => p_data,
 24                        typ => DBMS_CRYPTO.DES_CBC_PKCS5,
 25                        key => V_KEY );
 26
 27      Return utl_raw.cast_to_varchar2(l_decrypted);
 28    END decrypt_data;
 29  End cryptit;
 30  /

Package body created.

SQL>

The package body consists of functions to encrypt and decrypt data along with a key. As these functions will always output the same value for a specific input, we can make them Deterministic functions. (A Deterministic Function always returns the same result any time they are called with a specific set of input values.)

Let's now make use of these functions to encrypt our sensitive data.

SQL> update emp set ssn = cryptit.encrypt_data(ssn);

3 rows updated.

SQL> select * from emp;

     EMPID ENAME                          SSN
---------- ------------------------------ ----------------------------------
         1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6
         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD                           5F3168C22E54060D8166D3757932A112

SQL>
SQL>

When inserting new records we simply make use of "cryptit.encrypt_data" function in the INSERT statement.

SQL> insert into emp values( 4, 'MOMEN', cryptit.encrypt_data(123456704));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

     EMPID ENAME                          SSN
---------- ------------------------------ ----------------------------------------
         1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6
         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD                           5F3168C22E54060D8166D3757932A112
         4 MOMEN                          5F3168C22E54060D2CFF7E7A35B14187

SQL>

SQL> column decrypted_ssn format a15
SQL> select empid, ename, cryptit.decrypt_data(ssn) decrypted_ssn, ssn from emp;

     EMPID ENAME                          DECRYPTED_SSN   SSN
---------- ------------------------------ --------------- ----------------------------------------
         1 SMITH                          123456701       5F3168C22E54060DE7D97B31F7E38BB6
         2 ALLEN                          123456702       5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD                           123456703       5F3168C22E54060D8166D3757932A112
         4 MOMEN                          123456704       5F3168C22E54060D2CFF7E7A35B14187

SQL>

All is fine as far as encryption is concerned, but lets see what happens when SSN is used in the WHERE clause of queries. We will create an index on SSN and run a query against it.

SQL> create index t_ssn on emp(ssn);

Index created.

SQL> set autotrace on exp
SQL> select * from emp where ssn = cryptit.encrypt_data('123456701');

     EMPID ENAME                          SSN
---------- ------------------------------ ----------------------------------------
         1 SMITH                          5F3168C22E54060DE7D97B31F7E38BB6


Execution Plan
----------------------------------------------------------
Plan hash value: 2894032564

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

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

   2 - access("SSN"=RAWTOHEX("CRYPTIT"."ENCRYPT_DATA"('123456701')))

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

SQL>

Well, the index "T_SSN" has been selected by the optimizer to be cost efficient. When a range of values is to be scanned, optimizer falls flat on its face as it has no idea and picks up FTS.

SQL> select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';

     EMPID ENAME                          SSN
---------- ------------------------------ ----------------------------------------
         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD                           5F3168C22E54060D8166D3757932A112
         4 MOMEN                          5F3168C22E54060D2CFF7E7A35B14187


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

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

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

   1 - filter("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702'
              AND "CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')

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

We can also create Function Based Indexes on encrypted columns such that data is accessed faster with a better execution plan. This is the reason I have marked both ENCRYPT_DATA and DECRYPT_DATA as DETERMINISTIC functions.

SQL> create index f_ssn_idx on emp(cryptit.decrypt_data(ssn));

Index created.

SQL> select * from emp where cryptit.decrypt_data(ssn) between '123456702' and '123456704';

     EMPID ENAME                          SSN
---------- ------------------------------ ----------------------------------------
         2 ALLEN                          5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD                           5F3168C22E54060D8166D3757932A112
         4 MOMEN                          5F3168C22E54060D2CFF7E7A35B14187


Execution Plan
----------------------------------------------------------
Plan hash value: 9274740

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

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

   2 - access("CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))>='123456702' AND
              "CRYPTIT"."DECRYPT_DATA"(HEXTORAW("SSN"))<='123456704')

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

SQL>

The optimizer has rightly picked up the Function based index to access requested data quickly.

DBMS_CRYPTO is a great way to encrypt sensitive data and we can also create indexes on the encrypted columns to speedup our queries. Lastly, remember to wrap the CRYPTIT package body so that the key is not exposed.

DBMS_CRYPTO can also be used to encrypted data recursively. Let us try to encrypt the already encrypted SSN from the above example.

SQL> select * from emp;

     EMPID ENAME    SSN
---------- -------- --------------------------------------------------------------------------------
         1 SMITH    5F3168C22E54060DE7D97B31F7E38BB6
         2 ALLEN    5F3168C22E54060D0C2527FBBD3DCD6C
         3 WARD     5F3168C22E54060D8166D3757932A112
         4 MOMEN    5F3168C22E54060D2CFF7E7A35B14187

SQL> update emp set ssn = cryptit.encrypt_data(ssn);

4 rows updated.

SQL> select * from emp;

     EMPID ENAME    SSN
---------- -------- --------------------------------------------------------------------------------
         1 SMITH    455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C
         2 ALLEN    455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F
         3 WARD     455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830
         4 MOMEN    455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF

SQL> commit;

Commit complete.

SQL> select empid, ename, cryptit.decrypt_data(cryptit.decrypt_data(ssn)) decrypted_ssn, ssn from emp;

     EMPID ENAME    DECRYPTED_SSN   SSN
---------- -------- --------------- --------------------------------------------------------------------------------
         1 SMITH    123456701       455E357D80B1C81F91D52088DA0FE03A0C133D129CFC3879EE7A8DE993FD459DB5C9E1EBEF37062C
         2 ALLEN    123456702       455E357D80B1C81F91D52088DA0FE03A98FC410A71CE64D57935691692D433B770C5F8AF07C9113F
         3 WARD     123456703       455E357D80B1C81F91D52088DA0FE03A9D32D14FE64F41DD66BE7FEF4C31384F032C16826EAA4830
         4 MOMEN    123456704       455E357D80B1C81F91D52088DA0FE03A04D5C0AD79E689EABD46F8EC335308E32D6F7E94167F6EDF

SQL>

For information on DBMS_CRYPTO check Oracle documentation.

Performance Impact of NOT NULL Constraint in PL/SQL

noreply@blogger.com (Asif Momen) — Sun, 01 Feb 2009 11:00:00 +0000

A NOT NULL constraint in PL/SQL requires that a variable contains value, if no value is specified for that variable then an error will occur. But, using this constraint in PL/SQL, we have to pay a small performance price.

Let us create two procedures and perform a small test, the first procedure (TEST_NOTNULL) will be contain "NOT NULL" constraint while the second procedure (TEST_NULL) will be without it.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> create or replace procedure test_notnull(p_iterations in number) is
  2    i  number;
  3    x  number NOT NULL := 0;
  4    t1 number;
  5  begin
  6    t1 := dbms_utility.get_time;
  7    for i in 1..p_iterations loop
  8      x := x + i;
  9    end loop;
 10    dbms_output.put_line( 'Time taken : ' || to_char(dbms_utility.get_time - t1));
 11  end;
 12  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure test_null(p_iterations in number) is
  2    i  number;
  3    x  number := 0;
  4    t1 number;
  5  begin
  6    t1 := dbms_utility.get_time;
  7    for i in 1..p_iterations loop
  8      x := x + i;
  9      if x is null then
 10        dbms_output.put_line('i know this will never happen, but still ... ');
 11      end if;
 12    end loop;
 13    dbms_output.put_line( 'Time taken : ' || to_char(dbms_utility.get_time - t1));
 14  end;
 15  /

Procedure created.

SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL> exec perf_notnull(1000000);
Time taken : 20

PL/SQL procedure successfully completed.

SQL> exec perf_null(1000000);
Time taken : 17

PL/SQL procedure successfully completed.

SQL>

Executing these procedures, we notice that TEST_NOTNULL procedure consumes a little more than its counterpart TEST_NULL. Yes, we are looping heavily as the point is to compare performance of NOT NULL contraint in PL/SQL.

The reason the NOT NULL Constraint incurs performance cost is: As x is constriant by NOT NULL, the value of the expression "x + i" is first assigned to a temporary variable, this temporary variable is validated against nullity. If the variable is not null, its value is assigned to "x", otherwise an exception is raised. So, this is where the extra time is consumed when one applies NOT NULL constraint in PL/SQL.

An alternative approach is to manually test for nullity and raise an exception as we did in TEST_NULL procedure.

"ORA-01403: no data found" on Oracle University's Website

noreply@blogger.com (Asif Momen) — Wed, 21 Jan 2009 11:30:00 +0000

Hi,

Today while looking for information on Oracle Certified Master (OCM), I stumbled upon "ORA-01403" error on OU's website. Here is how I ended up with this error.

This page lists availability of seats in US Cities, when I tried to change my country to "Saudi Arabia" (from the drop-down list on the upper-right corner of the page), it gave me "ORA-01403" error.

It's time now to add the EXCEPTION section.

Regards

"Heh, heh, that"s a joke, son"

noreply@blogger.com (Asif Momen) — Mon, 08 Dec 2008 04:44:00 +0000

Well, the title of this post is the error message from "Oracle® Database Error Messages: 10g Release 2 (10.2)" document.

PLS-00101: reserved for future use

Cause: This error message is not used yet.(Heh, heh, that"s a joke, son.) Action: none

Although, it has been reserved for future use but somehow it got leaked into Oracle Database 10g documentation. However, this same error is missing from Oracle 11g Database Error Messages. I think its reserved to be used with Oracle 12g, who knows? Has anybody encountered this error ??

Dynamic Sampling Myths Dispelled

noreply@blogger.com (Asif Momen) — Sun, 23 Nov 2008 08:06:00 +0000

There are couple of myths associated with Dynamic Sampling, I thought of eliminating them with the help of this post. Two myths associated with DS are:

i) Default number of blocks sampled are 32 in Oracle 10g and ii) Tables having less than 32 blocks are not considered by the optimizer for dynamic sampling.

Myth 1: Default number of blocks sampled are 32 in Oracle 10g

The default number of dynamic sampling blocks are 32. This is set using a hidden parameter “_optimizer_dyn_smp_blks” (ofcourse one should not change it). Here’s the query to see the default settings:


SQL> column "Parameter" format a30
SQL> column "Session Value" format a20
SQL> column "Instance Value" format a20
SQL>
SQL> select a.ksppinm  "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
  2    from x$ksppi a, x$ksppcv b, x$ksppsv c
  3   where a.indx = b.indx and a.indx = c.indx
  4     and ksppinm= '_optimizer_dyn_smp_blks';

Parameter                      Session Value        Instance Value
------------------------------ -------------------- --------------------
_optimizer_dyn_smp_blks        32                   32

Starting with Oracle 10g, by default “optimizer_dynamic_sampling” initialization parameter is set to “2”, meaning optimizer will dynamically sample all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks. So at this level, Optimizer will sample 64 blocks.

To verify how many blocks are sampled, we need to dig into the 10053 trace output. For the purpose of this test, I will create a test table and dump some data into it. Later enable 10053 trace, execute the query and disable the trace.


SQL> drop table test purge;

Table dropped.

SQL> create table test as select * from all_objects where 1=2;

Table created.

SQL> create index t_object_id on test(object_id);

Index created.

SQL> insert into test select * from all_objects;

35023 rows created.

SQL> commit;

Commit complete.

SQL> alter session set tracefile_identifier = 'blksize';

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> select * from test where object_id = 123;

no rows selected

SQL> alter session set events '10053 trace name context off';

Session altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Digging into the trace, we see the following:

** Executed dynamic sampling query:
    level : 2
    sample pct. : 12.701613
    actual sample size : 4097
    filtered sample card. : 0
    orig. card. : 82
    block cnt. table stat. : 496
    block cnt. for sampling: 496
    max. sample block cnt. : 64
    sample block cnt. : 63
    min. sel. est. : 0.01000000

Well, it’s clear for the trace that at level 2, the maximum number of blocks sampled (max. sample block cnt) are 64 and the actual number of blocks sampled (sample block cnt) are 63. (For some reason, Oracle always samples one block less)

So, this myth has been uncovered and we know how many blocks are sampled.

Myth 2: Tables having less than 32 blocks are not considered by the optimizer for dynamic sampling

It is widely understood that, tables having less than 32 blocks are ignored by the optimizer for dynamic sampling.

Let’s continue using the same table as in case 1. However, it contains data, so we will truncate it. After truncating, verify number of blocks allocated by querying USER_SEGMENTS view.


SQL> truncate table test;

Table truncated.

SQL> select blocks from user_segments where segment_name = 'TEST';

    BLOCKS
----------
         8

This table has only 8 blocks allocated. Let’s see whether optimizer considers dynamic sampling on this table by executing a simple query.

SQL> set autotrace traceonly exp
SQL> select * from test where object_id = 123;

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

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

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

   1 - filter("OBJECT_ID"=123)

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

SQL> set autotrace off
SQL>

Yes, Oracle optimizer did consider dynamic sampling although the number of blocks were less than 32.

So, in this post we were able to uncover myths associated with dynamic sampling through very simple and reproducible examples.

Analytic Functions: A Savior

noreply@blogger.com (Asif Momen) — Wed, 05 Nov 2008 11:11:00 +0000

Hi,

Yesterday, I have come across yet another performance issue. I received a complaint from one of our developers that they have a business report which is running extremely slow.

I received his mail and attached was the query:

SELECT   
  XLT.VOU_DET.VOU_DATE,
  XLT.VOU_DET.VALUE_DATE,
  XLT.VOU_DET.DESCRIPTION,
  XLT.VOU_DET.PIX_CODE,
  VOU_TYPE.DESCR,
  XLT.VOU_DET.PIX_BRN,
  XLT.VOU_DET.DR_BAL_ORIG,
  XLT.VOU_DET.CR_BAL_ORIG,
  XLT.VOU_DET.CLOSING_BAL_ORIG,
  XLT.VOU_LOOKUP.NAME,
  XLT.VOU_DET.VOU_SEQ,
  (SELECT TO_CHAR(X.OPEN_BAL_ORIG) 
     FROM XLT.VOU_DET X 
    WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                          AND to_date('30-09-2008', 'dd-mm-yyyy')
      AND X.VOU_CODE  = '9900016WXYRT01'
      AND X.VOU_SEQ = 1
      AND ROWNUM = 1) OPEN_BAL_ORIG,
  (SELECT count(  XLT.VOU_DET.DR_BAL_ORIG) 
     FROM (select DR_BAL_ORIG 
              From XLT.VOU_DET X 
             WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                                   AND to_date('30-09-2008', 'dd-mm-yyyy')
               AND X.VOU_CODE  = '9900016WXYRT01'
               AND X.DR_BAL_ORIG <>0)) DR_BAL_ORIG_CNT,
  (SELECT count(  XLT.VOU_DET.CR_BAL_ORIG) 
     FROM (select CR_BAL_ORIG 
             From XLT.VOU_DET X 
            WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                                  AND to_date('30-09-2008', 'dd-mm-yyyy')
              AND X.VOU_CODE  = '9900016WXYRT01'
              AND X.CR_BAL_ORIG <>0)) CR_BAL_ORIG_CNT 
FROM
  XLT.VOU_DET,
  XLT.X_VOU_TYPE  VOU_TYPE,
  XLT.VOU_LOOKUP
WHERE XLT.VOU_DET.VOU_TYPE_ID=VOU_TYPE.VOU_TYPE_ID
  AND XLT.VOU_DET.VOU_REF(+)=XLT.VOU_LOOKUP.CUST_CODE_WNG
  AND XLT.VOU_DET.ASOF_DATE  BETWEEN  to_date('01-05-2007', 'dd-mm-yyyy') AND to_date('30-09-2008', 'dd-mm-yyyy')
  AND XLT.VOU_DET.VOU_CODE  =  '9900016WXYRT01'
ORDER BY
  XLT.VOU_DET.VOU_SEQ,
  XLT.VOU_DET.ASOF_DATE;

This looks like a case of missing Analytic functions.

The three SELECT statements within the query can be easily replaced with simple Analytic functions. The first one is to fetch the opening balance:

  (SELECT TO_CHAR(X.OPEN_BAL_ORIG) 
     FROM XLT.VOU_DET X 
    WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                          AND to_date('30-09-2008', 'dd-mm-yyyy')
      AND X.VOU_CODE  = '9900016WXYRT01'
      AND X.VOU_SEQ = 1
      AND ROWNUM = 1) OPEN_BAL_ORIG,

and could be easily rewritten as:

first_value(OPEN_BAL_ORIG) 
      over (partition by VOU_CODE 
                Order by ASOF_DATE, decode(VOU_SEQ, 0, 99, VOU_SEQ)) VOU_SEQ,

The second and third are the number of Debit and Credit transactions respectively and can also be written as:

  count(decode(DR_BAL_ORIG, null, null, 0, null, 1)) 
      over (partition by  VOU_CODE) DR_BAL_ORIG_cnt,

and

  count(decode(CR_BAL_ORIG, null, null, 0, null, 1)) 
      over (partition by  VOU_CODE) CR_BAL_ORIG_cnt

Now, its time to execute the modified query and (1) compare the result set, (2) measure time taken, (3) and compare statistics with the original query.

Well, the result set was compared and was convincing. The enhanced query completes in less than 20 seconds while the original took just over 17 minutes. So, the new query is 173 times faster than the original one.

Lastly, let’s compare statistics.

Statistics of Original query:

SQL> set autotrace traceonly stat
SQL> @tuneme

661 rows selected.

Elapsed: 00:17:26.91

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2066944  consistent gets
     753812  physical reads
        116  redo size
      68506  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        661  rows processed

SQL>

Statistics of Modified query:

SQL> set autotrace traceonly exp stat
SQL> @tunedquery

661 rows selected.

Elapsed: 00:00:17.35
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
      22514  consistent gets
       2580  physical reads
          0  redo size
      68003  bytes sent via SQL*Net to client
        547  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        661  rows processed

SQL>

Instead of “2,066,944” consistent gets and “753,812” physical reads, it took merely “22,514” consistent gets and “2,580” physical reads. That’s 98% reduction in LIO’s and 99%+ reduction in PIO.

Again Analytics Functions is the winner over traditional query writing style.

How to read Data between different Characterset Databases - Part 2

noreply@blogger.com (Asif Momen) — Mon, 03 Nov 2008 09:49:00 +0000

Hazem Ameen has left a comment on my previous post, where he says he is able to read/write data over a database link between two databases having different charactersets.

We have your same environment and reading/writing over db link is fine. Oracle does character-set conversion between the 2 Arabic character-set as expected. Client NLS_LANG: America_America.ar8mswin1256 Server1: ar8mswin1256 Server 2: ar8iso8859p6 I can only suspect your client NLS_LANG Our version is 10.2.0.4 on Linux. Client is MS Windows Vista 10.2.0.4

reading/writing over db link is fine

What I would assert from this is, although you have databases with two different charactersets, your client characterset is SAME. This is the reason you are able to read/write over the db link. But, in our case, clients connecting to database A have same characterset as A, while clients connecting to database B have same characterset as B. Here's a test case with my client characterset set to "AR8MSWIN1256".

Create tables in both the databases:

Create database links in both databases and query data from other side:

In the above example, we were able to read data over db links meaning client characterset's are playing the vital role.

This means, all your client's characterset is set to "AR8MSWIN1256" for both the types of databases. This is the reason you are able to read/write over a db link.

I hope I was clear in my examples. Thanks for leaving your comment.

Regards

Regards Asif Momen

How to read Data between different Characterset Databases

noreply@blogger.com (Asif Momen) — Tue, 14 Oct 2008 11:48:00 +0000

We have two 24x7 production databases with different charactersets. Yes, both charactersets are related to Arabic language. Database A’s characterset is AR8MSWIN1256 while database B’s characterset is AR8ISO8859P6.

We had a requirement wherein database A will read Arabic data from database B. If you create a database link and try to query data from database B it will appear to be garbage, as shown below:

whereas when you run the same query on database B, this is how the output looks:

Even you are unaware of Arabic language, you may visually compare and see the output is a garbage when queried from database A.

DUMP function comes handy under these situations. It returns internal representation of an expression. When used to against the “name” failed, I get this output.

SQL> select dump(name) from test_iso@testdb;

DUMP(NAME)
--------------------------------------------------------------------
Typ=1 Len=13: 227,205,227,207,32,194,213,221,32,227,196,227,228

where, “Typ” refers to datatype, “Len” means the length of the string, and everything after “:” is comma separated ASCII values of all the characters in the “name” field.

I wrote a small procedure where convert all the comma separated ASCII values to character string.

create or replace function read_name (p_id in number) return varchar2 is
  t_str varchar2(2000);
  l_name varchar2(2000);
  l_output varchar2(2000);
begin
  select 'select '||'chr('||replace(substr(dump(name, 1010), instr(dump(name, 1010), ':')+2), ',', ')|| chr(')||') from dual' 
    into t_str from test_iso@testdb
   where id = p_id;
 
--  dbms_output.put_line(t_str);
  execute immediate t_str into l_output;  
  return l_output;
end;
/

Now upon using this function, I am able to query remote table in a readable format.

Here’s the output:

Analytic Functions: The most ignored ones

noreply@blogger.com (Asif Momen) — Sun, 21 Sep 2008 11:54:00 +0000

Dear Readers,

Analytic Functions were first introduced in Oracle 8i, way back in 1999. Tom Kyte’s book “Expert One-on-One Oracle” has a dedicated chapter on this topic. Not only Tom’s book, any book on SQL (I have seen so far) has a separate chapter written on Analytic Functions. Yet, the developer community seems to be paying least attention in trying to understand and use them. I think before educating students, tutors should be trained to emphasize the importance of Analytic Functions in day to day life of a developer.

Anyways, here’s a similar case on one of our very busy OLTP database.

This SQL statement topped in the AWR report consuming nearly 84% of database time and was executed more than 1200 times during a 45-minutes AWR report.

A query is required to fetch TECH_PAC_ID for a customer along with the number of records for that customer.

Here’s the original query, its Explain Plan and Statistics:

SQL> set autotrace on
SQL> SELECT Tech_PAC_ID, Users_USER_CD, TOT_PAC 
  2   FROM (SELECT Tech_PAC_ID, Users_USER_CD 
  3          FROM Tech, Users 
  4         WHERE Tech_STS = 'Y' AND Users_USER_CD = UPPER('ABX65842' )
  5           AND Users_USER_CD = Tech_ETI_USER_CD) DET, 
  6        (SELECT COUNT(*) TOT_PAC 
  7           FROM Tech, Users 
  8          WHERE Tech_STS = 'Y' AND Users_USER_CD = UPPER('ABX65842' ) 
  9            AND Users_USER_CD = Tech_ETI_USER_CD) TOT;

TECH_PAC_N USERS_USER_CD        TOT_PAC
---------- -------------------- ----------
236XXX123  ABX65842                     5

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 3349818188

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 |    54 |  1280   (1)| 00:00:16 |
|   1 |  MERGE JOIN CARTESIAN           |              |     1 |    54 |  1280   (1)| 00:00:16 |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | TECH         |     1 |    18 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |              |     1 |    41 |   640   (1)| 00:00:08 |
|*  4 |     TABLE ACCESS FULL           | USERS        |     1 |    23 |   636   (1)| 00:00:08 |
|*  5 |     INDEX RANGE SCAN            | TECH_USER_CD |     3 |       |     1   (0)| 00:00:01 |
|   6 |   BUFFER SORT                   |              |     1 |    13 |  1276   (1)| 00:00:16 |
|   7 |    VIEW                         |              |     1 |    13 |   640   (1)| 00:00:08 |
|   8 |     SORT AGGREGATE              |              |     1 |   104 |            |          |
|*  9 |      TABLE ACCESS BY INDEX ROWID| TECH         |     1 |    12 |     4   (0)| 00:00:01 |
|  10 |       NESTED LOOPS              |              |     1 |   104 |   640   (1)| 00:00:08 |
|* 11 |        TABLE ACCESS FULL        | USERS        |     1 |    92 |   636   (1)| 00:00:08 |
|* 12 |        INDEX RANGE SCAN         | TECH_USER_CD |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - filter("TECH_STS"='Y')
   4 - filter("USERS_USER_CD"='ABX65842')
   5 - access("USERS_USER_CD"="Tech_ETI_USER_CD")
       filter("TECH_ETI_USER_CD" IS NOT NULL)
   9 - filter("TECH_STS"='Y')
  11 - filter("USERS_USER_CD"='ABX65842')
  12 - access("USERS_USER_CD"="Tech_ETI_USER_CD")
       filter("TECH_ETI_USER_CD" IS NOT NULL)


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

SQL>

Instead of writing two queries to fetch TECH_PAC_ID and COUNT(USERS_USER_CD) we can achieve the same result by using Analytic Functions. The new query not only returns the required result but is also less resource intensive and more database-friendly.

Here goes the enhanced query using Analytic Function:

SQL> SELECT Tech_PAC_ID, Users_USER_CD, 
  2         count(1) over (partition by Users_USER_CD) TOT_PAC
  3          FROM Tech, Users 
  4         WHERE Tech_STS = 'Y' AND Users_USER_CD = UPPER('ABX65842' )
  5           AND Users_USER_CD = Tech_ETI_USER_CD
  6  GROUP BY Tech_PAC_ID, Users_USER_CD;

TECH_PAC_N USERS_USER_CD        TOT_PAC
---------- -------------------- ----------
236XXX123  ABX65842                     5

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1328229640

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |    41 |   641   (1)| 00:00:08 |
|   1 |  WINDOW BUFFER                |              |     1 |    41 |   641   (1)| 00:00:08 |
|   2 |   SORT GROUP BY               |              |     1 |    41 |   641   (1)| 00:00:08 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TECH         |     1 |    18 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |              |     1 |    41 |   640   (1)| 00:00:08 |
|*  5 |      TABLE ACCESS FULL        | USERS        |     1 |    23 |   636   (1)| 00:00:08 |
|*  6 |      INDEX RANGE SCAN         | TECH_USER_CD |     3 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   3 - filter("TECH_STS"='Y')
   5 - filter("USERS_USER_CD"='ABX65842')
   6 - access("USERS_USER_CD"="Tech_ETI_USER_CD")
       filter("TECH_ETI_USER_CD" IS NOT NULL)


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

SQL> 
SQL>

The new query seems to be doing a good job. The “consistent gets” have dropped from 4671 to 2335. That’s nearly half of the original, the reason being: instead of hitting the table twice, we are getting the work done in one hit. But still, the consistent gets seems to be reasonably high.

Although ROWS=1 is being shown in the Explain Plan for USERS table, but optimizer is spending most of its time doing a Full Table Scan at this step. Adding an index on USERS_USER_CD column of USERS table should do the trick.

After adding the index on USERS (USERS_USER_CD) column, the query seems to be flying.

SQL> SELECT Tech_PAC_ID, Users_USER_CD, 
  2         count(1) over (partition by Users_USER_CD) TOT_PAC
  3          FROM Tech, Users 
  4         WHERE Tech_STS = 'Y' AND Users_USER_CD = UPPER('ABX65842' )
  5           AND Users_USER_CD = Tech_ETI_USER_CD
  6  GROUP BY Tech_PAC_ID, Users_USER_CD;

TECH_PAC_N USERS_USER_CD        TOT_PAC
---------- -------------------- ----------
236XXX123  ABX65842                     5


Execution Plan
----------------------------------------------------------
Plan hash value: 1753916289

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |     1 |    33 |     7  (15)| 00:00:01 |
|   1 |  WINDOW BUFFER                  |                       |     1 |    33 |     7  (15)| 00:00:01 |
|   2 |   SORT GROUP BY                 |                       |     1 |    33 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID  | TECH                  |     1 |    15 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                       |     1 |    33 |     6   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| USERS                 |     1 |    18 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | USERS_MUB_USER_CD_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | TECH_USER_CD          |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

   3 - filter("TECH_STS"='Y')
   6 - filter("USERS_USER_CD"='ABX65842')
   7 - access("USERS_USER_CD"="Tech_ETI_USER_CD")
       filter("TECH_ETI_USER_CD" IS NOT NULL)


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

SQL>

This time merely 6 “consistent gets” were required. It’s a 99%+ reduction in overall consistent gets.

As I mentioned earlier in the post, I see SQL statements similar to this one over and over again. Analytic function is a nice alternative to the traditional way of writing these types of queries that works extremely well and provides the performance needed for high numbers of executions in a high data volume environment.

More information on Analytic Functions can (should) be obtained from here:

Oracle Database SQL Reference 10g Release 2

On Top-n and Pagination Queries by Tom Kyte

AskTom

One Analytic Function Can do More Than a 1000 Lines of Code by Alex Nuitjen

Happy reading.

Syntax Highlighter

noreply@blogger.com (Asif Momen) — Sat, 20 Sep 2008 08:52:00 +0000

Dear Readers,

Most of you might have experienced difficulies in placing code snippets in a nice formatted way on Blogger.com. I have been previously using Greg Houston's Code formatter, but when you have pipes ("|") in the code like in Explain Plan, then Blogger fails to display the code correctly.

But, recently I stumbled on one of the Karen Morton's post where she discusses a way to format your code snippets on blogger. Here are more details on highlightling your code in blogger.com using Syntax Highlighter.

The code below has been formatted using SyntaxHighlighter:


SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TEST_RUN                       TABLE
PLAN_TABLE                     TABLE
EMP_VIEW                       VIEW
EMP2                           TABLE

8 rows selected.

SQL>

More interesting stuff can by found on Fahd Shariff's blog and Morten Lyhr's blog.

Happy formating !!!

Dynamic Sampling and Table Partitions

noreply@blogger.com (Asif Momen) — Sat, 02 Aug 2008 11:54:00 +0000

Dynamic Sampling was first introduced in Oracle 9i Release 2 and the purpose is to improve query performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes.

Dynamic sampling is to be used predominantly with un-analyzed tables. Before hard parsing a query Oracle determines if dynamic sampling would improve query performance. If so, then the optimizer issues recursive SQL statements to estimate the necessary statistics dynamically.

Here’s an example of dynamic sampling:

Operating System: Windows XP Database: Oracle 10g Release 2 (10.2.0.4)

  
SQL> create table t (a number);

Table created.

SQL> set autotrace traceonly exp
SQL> select * from t;

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

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

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

SQL> set autotrace off

Its clear from the above Execution Plan that dynamic sampling was performed.

But it seems dynamic sampling is not partition friendly or rather dynamic sampling is partition unaware. What I mean is if you have a partitioned table and at least one partition is analyzed then dynamic sampling is NOT performed on any of the un-analyzed partitions. This is because Optimizer looks at columns like NUM_ROWS of DBA_TABLES view and considers that the table is analyzed.

Let us create a partitioned table with a local index.

  
SQL> create table part(
  2         sno     number,
  3         dt      date,
  4              remark     varchar2(30))
  5      partition by range(dt) (
  6      partition p1 values less than (to_date('01-02-2008', 'dd-mm-yyyy')),
  7      partition p2 values less than (to_date('01-03-2008', 'dd-mm-yyyy')),
  8      partition p3 values less than (to_date('01-04-2008', 'dd-mm-yyyy')),
  9      partition p4 values less than (to_date('01-05-2008', 'dd-mm-yyyy'))
 10     );

Table created.

SQL>
SQL> create index part_idx on part(dt, sno) local;

Index created.

SQL>

Now, populate all but the last partition with test data.

SQL> insert into part
  2      select level,
  3             to_date('01-01-2008', 'dd-mm-yyyy') + level - 1 ,
  4             'test'
  5        from dual
  6      connect by level <=90;

90 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Let us now collect statistics for partitions P1, P2, and P3.

SQL> exec dbms_stats.gather_table_stats(user, tabname => 'part', -
>       partname => 'p1', estimate_percent => null, -
>       method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(user, tabname => 'part', -
>       partname => 'p2', estimate_percent => null, -
>       method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(user, tabname => 'part', -
>       partname => 'p3', estimate_percent => null, -
>       method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, num_rows
  2    from user_tables
  3   where table_name ='PART';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
PART                                   90

SQL>
SQL>
SQL> select partition_name, num_rows
  2    from user_tab_partitions
  3   where table_name = 'PART';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     31
P2                                     29
P3                                     30
P4

SQL>
SQL>
SQL> select partition_name, num_rows
  2    from user_ind_partitions
  3   where index_name = 'PART_IDX';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                     31
P2                                     29
P3                                     30
P4

SQL>

It’s time to test whether optimizer considers dynamic sampling or not.

SQL> set autotrace traceonly exp
SQL>
SQL> select *
  2    from part
  3   where dt = to_date('01-04-2008', 'dd-mm-yyyy')
  4     and sno = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2633488982

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    16 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    16 |     2   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | PART |     1 |    16 |     2   (0)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("DT"=TO_DATE(' 2008-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "SNO"=1)

SQL>
SQL> set autotrace off
SQL>

No! Optimizer thinks that it has enough information available at hand to with it rather than sampling dynamically.

Now, let us populate the unanalyzed partition with data and re-run the same query.

SQL> insert into part select 1, to_date('01-04-2008', 'dd-mm-yyyy'), 'test'
  2        from dual connect by level <=1000;

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set autotrace traceonly exp
SQL>
SQL>
SQL> select * from part
  2   where dt = to_date('01-04-2008', 'dd-mm-yyyy')
  3     and sno = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2633488982

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    16 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    16 |     2   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | PART |     1 |    16 |     2   (0)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("DT"=TO_DATE(' 2008-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "SNO"=1)

SQL>
SQL> set autotrace off

Well, the optimizer took the same plan because statistics were not up to date. Consider gathering statistics on the partition in concern and execute the same query again.

 
SQL> exec dbms_stats.gather_table_stats(user, tabname => 'part', -
>       partname => 'p4', estimate_percent => null, -
>       method_opt => 'for all columns size 1', cascade => true);

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly exp
SQL>
SQL>
SQL> select * from part
  2   where dt = to_date('01-04-2008', 'dd-mm-yyyy')
  3     and sno = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2633488982

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |  1000 | 16000 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |  1000 | 16000 |     3   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | PART |  1000 | 16000 |     3   (0)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("DT"=TO_DATE(' 2008-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "SNO"=1)

SQL>
SQL> set autotrace off
SQL>

Well this time optimizer had updated statistics which gives a better and clear picture to pick the most appropriate access path. So, optimizer thinks “TABLE ACCESS FULL” on partition P4 is appropriate and which makes sense too.

I have purposely populated partition P4 with duplicate data so that we see a difference in optimizer access path.

Finally, the conclusions are:

• Dynamic sampling ignores partition level statistical information. • Always update statistics after data load process, or assign default statistics to unanalyzed partition. Happy reading.

Bugs in REMAINDER Function (Updated)

noreply@blogger.com (Asif Momen) — Fri, 01 Aug 2008 16:32:00 +0000

Dear all,

Regarding Bugs in REMAINDER Function, I have opened a call with Oracle Support and they say "REMAINDER function works as intended and this is a documentation issue which requires minor clarification".

Oracle documentation has modified the text as follows:

"If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1. If n2/n1 equals x.5, then N is the nearest even integer."

A bug (7236077) has also been logged.

Regards

Funny “ORA-00942: table or view does not exist” Error Message

noreply@blogger.com (Asif Momen) — Sun, 27 Jul 2008 10:33:00 +0000

Dear all,

We all know when “ORA-00942” error message is returned. For instance, if I try to query a non-existent table then Oracle joyfully returns this errors message.

  
SQL> select * from funny;
select * from funny
              *
ERROR at line 1:
ORA-00942: table or view does not exist

“ORA-00942” error message is thrown on our face whenever we try to perform any action like CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ... against a non-existent table. But this error is sometimes misleading, like the one below:

  
SQL> create table t(a number);

Table created.

SQL> create view v as select * from t;

View created.

SQL> drop table v;
drop table v
           *
ERROR at line 1:
ORA-00942: table or view does not exist

Error message reports “table or view does not exist”, but definitely a view named “V” exists in the same schema.

Wouldn’t it be more appropriate if Oracle reports “table does not exist”?

MIN and MAX Functions in a Single Query are Disastrous

noreply@blogger.com (Asif Momen) — Thu, 10 Jul 2008 11:01:00 +0000

Dear Readers,

I would like to discuss a very interesting point about indexes in this post. When we are interested in finding out the minimum value of an indexed column, instead of reading entire table or the index, Oracle intelligently uses the index to navigate to the first index leaf block (leftmost index block) and quickly finds the minimum value of an indexed column.

A simple demo proves this:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL>
SQL> create table t as select level sno, 'name ' || level name 
  2  from dual connect by level <= 10000000;

Table created.

SQL>

SQL> create unique index t_idx on t(sno);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL>
SQL> select min(sno) from t;

  MIN(SNO)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL>
SQL> set autotrace off
SQL>

Although this table contains 10 Million rows, Oracle required only 3 consistent gets to fetch the minimum value. Superb !!!

Similarly, when finding out the maximum value, Oracle reads the last block on the right-hand side of the index structure.

SQL> set autotrace on
SQL> select max(sno) from t;

  MAX(SNO)
----------
  10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL> set autotrace off
SQL>

Once again only 3 consistent gets were required to fetch the maximum value.

But things get messy when you use both MIN and MAX functions in the same query. Instead of using same "INDEX FULL SCAN (MIN/MAX)" path to read the left-most block and right-most block to arrive at the minimum and maximum values, Oracle goes with FULL TABLE SCAN. A Full Table Scan on 10 Million rows !!!

SQL> set autotrace on
SQL> select min(sno), max(sno) from t;

  MIN(SNO)   MAX(SNO)
---------- ----------
         1   10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS FULL| T    |    10M|    57M|  9034   (2)| 00:01:49 |
---------------------------------------------------------------------------


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

SQL> set autotrace off
SQL>

From this terrible behavior of Oracle Optimizer, What I infer and suggest is:

"Write separate queries to fetch MIN and MAX values instead of combining them into one query".

Bugs in REMAINDER Function

noreply@blogger.com (Asif Momen) — Sat, 05 Jul 2008 15:09:00 +0000

While browsing through SQL Reference documentation (Oracle 10g Release 2), I stopped at REMAINDER function. The REMAINDER returns the remainder of n2 divided by n1 and internally uses ROUND function to arrive at the remainder value.

Oracle documentation also says, "If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1."

SQL> Select REMAINDER(501, 5) from dual;

REMAINDER(501,5)
----------------
               1

SQL>

But, because of an internal bug, REMAINDER function returns incorrect results when "n2/n1" results in 0.5, 1.5, 2.5, .....

SQL> Select REMAINDER(5, 2) from dual;

REMAINDER(5,2)
--------------
             1

SQL>

According to the formula,


n2 - (n1 * N) = 5 - (2 * ROUND(5/2)) = 5 - (2 * 3) = 5 - 6 = -1

the value returned should be "-1" where as the REMAINDER function returns "+1".

My observation is that, for example "1.5" is getting rounded to "1" instead of "2" and is spoiling the outcome of REMAINDER.

I wrote a small PL/SQL block to screen my observation:

SQL> set linesize 1000
SQL> set pages     100
SQL> set serveroutput on
SQL>
SQL>    declare
  2       n1  number;
  3       n2  number;
  4       N   number;
  5
  6       sys_rem number;
  7       my_rem number;
  8     begin
  9       for n1 in 1..5 loop
 10        --for n2 in 1..50 loop
 11        n2 := 1;
 12        while n2 <= 20 loop
 13          Sys_rem := REMAINDER(n2, n1);
 14          N := Round(n2/n1);
 15          My_rem := n2 - (n1 * N);
 16          If sys_rem <> my_rem then
 17            Dbms_output.put_line('n2 : ' || rpad(to_char(n2), 4, ' ') ||
 18                                 '  n1 : ' || to_char(n1) ||
 19                                 '   ' ||
 20                                 ' n2/n1 = ' || rpad(to_char(n2/n1), 4, ' ') ||
 21                                 '     ' ||
 22                                 ' ORCL rem = ' || rpad(to_char(sys_rem), 4, ' ') ||
 23                                 '     ' ||
 24                                 ' Calc Rem [' || rpad(to_char(n2), 4, ' ') ||
 25                                 ' - (' || to_char(n1) || '*' ||
 26                                 rpad(to_char(N), 4, ' ') || ')] = '||
 27                                 to_char(my_rem));
 28          End If;
 29          n2 := n2 + 0.1;
 30        end loop;
 31      end loop;
 32    end;
 33    /
n2 : 2.5   n1 : 1    n2/n1 = 2.5       ORCL rem = .5        Calc Rem [2.5  - (1*3   )] = -.5
n2 : 4.5   n1 : 1    n2/n1 = 4.5       ORCL rem = .5        Calc Rem [4.5  - (1*5   )] = -.5
n2 : 6.5   n1 : 1    n2/n1 = 6.5       ORCL rem = .5        Calc Rem [6.5  - (1*7   )] = -.5
n2 : 8.5   n1 : 1    n2/n1 = 8.5       ORCL rem = .5        Calc Rem [8.5  - (1*9   )] = -.5
n2 : 10.5  n1 : 1    n2/n1 = 10.5      ORCL rem = .5        Calc Rem [10.5 - (1*11  )] = -.5
n2 : 12.5  n1 : 1    n2/n1 = 12.5      ORCL rem = .5        Calc Rem [12.5 - (1*13  )] = -.5
n2 : 14.5  n1 : 1    n2/n1 = 14.5      ORCL rem = .5        Calc Rem [14.5 - (1*15  )] = -.5
n2 : 16.5  n1 : 1    n2/n1 = 16.5      ORCL rem = .5        Calc Rem [16.5 - (1*17  )] = -.5
n2 : 18.5  n1 : 1    n2/n1 = 18.5      ORCL rem = .5        Calc Rem [18.5 - (1*19  )] = -.5
n2 : 1     n1 : 2    n2/n1 = .5        ORCL rem = 1         Calc Rem [1    - (2*1   )] = -1
n2 : 5     n1 : 2    n2/n1 = 2.5       ORCL rem = 1         Calc Rem [5    - (2*3   )] = -1
n2 : 9     n1 : 2    n2/n1 = 4.5       ORCL rem = 1         Calc Rem [9    - (2*5   )] = -1
n2 : 13    n1 : 2    n2/n1 = 6.5       ORCL rem = 1         Calc Rem [13   - (2*7   )] = -1
n2 : 17    n1 : 2    n2/n1 = 8.5       ORCL rem = 1         Calc Rem [17   - (2*9   )] = -1
n2 : 1.5   n1 : 3    n2/n1 = .5        ORCL rem = 1.5       Calc Rem [1.5  - (3*1   )] = -1.5
n2 : 7.5   n1 : 3    n2/n1 = 2.5       ORCL rem = 1.5       Calc Rem [7.5  - (3*3   )] = -1.5
n2 : 13.5  n1 : 3    n2/n1 = 4.5       ORCL rem = 1.5       Calc Rem [13.5 - (3*5   )] = -1.5
n2 : 19.5  n1 : 3    n2/n1 = 6.5       ORCL rem = 1.5       Calc Rem [19.5 - (3*7   )] = -1.5
n2 : 2     n1 : 4    n2/n1 = .5        ORCL rem = 2         Calc Rem [2    - (4*1   )] = -2
n2 : 10    n1 : 4    n2/n1 = 2.5       ORCL rem = 2         Calc Rem [10   - (4*3   )] = -2
n2 : 18    n1 : 4    n2/n1 = 4.5       ORCL rem = 2         Calc Rem [18   - (4*5   )] = -2
n2 : 2.5   n1 : 5    n2/n1 = .5        ORCL rem = 2.5       Calc Rem [2.5  - (5*1   )] = -2.5
n2 : 12.5  n1 : 5    n2/n1 = 2.5       ORCL rem = 2.5       Calc Rem [12.5 - (5*3   )] = -2.5

PL/SQL procedure successfully completed.

SQL>

I have tested this code on Oracle 10g Release 2. Unfortunately, this bug also exists in Oracle 11g.

I have raised a Service Request and also filed a bug with Oracle Support.

Regards

OTN - Discussion Forums in a great new look

noreply@blogger.com (Asif Momen) — Sat, 28 Jun 2008 14:27:00 +0000

Dear Readers,

Morning (GMT+3) when I tried to login to OTN-Discussion forum, it was down. But later when I checked it again, it's with a great new look.

With the new look comes new features. Following are the new features I have noticed:

* Great new look

* Add smiley's to messages

* Spell Check

* Formatting

* Reward points

* Save messages

* Tag messages and

* Abuse alerts

It should be easier for the OTN-newbie's to post their questions in a well formatted and readable format.

Enjoy discussions !!!

Why my index is not used?

noreply@blogger.com (Asif Momen) — Tue, 24 Jun 2008 10:54:00 +0000

Well, this question keeps popping up now and then. Yesterday, one of my colleagues also came up with this question: "Why is it that Oracle is not using index even though I am selecting less than 10% of data?".

We ran the query with autotrace enabled and the execution plan showed a Full Table Scan. This table contains over 29 Million records and by adding the predicate, result set is reduced to 2.3 Million records, which is 8% of total records.

SQL> set autotrace traceonly exp
SQL> SELECT *
  2      FROM quint_sec_tbl
  3      WHERE quint_type = 'XX06FR';

Execution Plan
----------------------------------------------------------
Plan hash value: 3917650069

----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |  3440 |   618K|   454K  (1)| 01:30:55 |
|*  1 |  TABLE ACCESS FULL| QUINT_SEC_TBL      |  3440 |   618K|   454K  (1)| 01:30:55 |
----------------------------------------------------------------------------------------

Cary Millsap's very old (but still valid) article "When to Use an Index" greatly helped me in this regard. This article unveils that Index consideration is based on block selectivity and not on row selectivity.

He also defines what Row Selectivity and Block Selectivity are in the article.

Row Selectivity:

You can define the row selectivity of a given where-clause predicate as the number of rows returned by the predicate (r) divided by the total number of rows in the table (R):


        P(r) =  r / R

Block Selectivity:

You can define the block selectivity of a given where-clause predicate analogously, as the number of data blocks containing at least one row matching the predicate condition (b) divided by the total number of data blocks below the high-water mark (B):


        P(b) = b / B

We can calculate block selectivity and row selectivity using SQL provided in this article. I used this SQL against my query and following are the results:

SQL> @hds
TableOwner : MYPRODUSER
TableName : QUINT_SEC_TBL
ColumnList : QUINT_TYPE
WhereClause: 
PageSize : 100

Table blocks below hwm    Table rows
         (B)                 (R)
---------------------- ----------------
             1,672,704       29,270,757
More: 

       Block selectivity  Block count    Row selectivity     Row count
QUINT_    (pb = b/B)          (b)          (pr = r/R)           (r)
------ ----------------- -------------- ----------------- ----------------
TT34DV            45.03%        753,277            37.99%       11,120,869
FG76SC            44.47%        743,788            13.67%        4,000,205
LH23Q2            42.78%        715,558             9.44%        2,762,284
XX06FR            42.32%        707,894             8.02%        2,346,846
:
:
:

Output of this SQL is sorted in descending order of block selectivity.

Looking at this output, row selectivity is only 8% but to fetch these 8% of rows Oracle has to visit 42% of blocks (block selectivity). That means, nearly half of the table's blocks contain at least one row for which QUINT_TYPE='XX06FR'. Instead of going through the hard path of Index Access, it’s more efficient for the optimizer to do a Full Table Scan.

So, now we know why the index was ignored and a Full Table Scan was preferred.

P.S.: Due to security reasons Username, Table name, column name and column values are modified.

SQL for Loan Payment

noreply@blogger.com (Asif Momen) — Wed, 18 Jun 2008 11:36:00 +0000

Dear all,

Inspired by "SQL for Buying a New Car", I thought of converting a very old excel sheet of mine into an SQL query. This excel sheet lists equated monthly installment (EMI), principal amount and the interest amount for duration of loan payment. I feed Rate of Interest, Loan Amount and Period of Loan (in years) to the excel sheet. This was very convenient for me to analyze what part of my EMI is going towards interest payment and what portion goes as principal repayment. It is extremely important to understand what goes for interest and what goes for principal repayment when you are planning for a loan.

Output from Excel sheet:

This can easily be converted into an SQL query using MODEL clause introduced in Oracle 10g.

The output of this query includes a summary of total interest to be paid, principal to be paid and the net payment to be made. Ofcourse, we need to ignore the Summary on "Remaining" column as this is meaningless.

SQL> set line 10000
SQL> set pages 500
SQL> set verify off
SQL>
SQL> column interest  format 999,999,999.99
SQL> column principal format 999,999,999.99
SQL> column remaining format 999,999,999.99
SQL>
SQL> define roi = 10          -- Rate of Interest
SQL> define years = 2         -- Payment Period
SQL> define amt = 100000      -- Loan Amount
SQL> define period = 12       -- Mode of payment.
SQL>                          --     Monthly=12, Bi-monthly=48, Quarterly=4, Half-yearly=2, Yearly=1, Adhoc=n
SQL>
SQL>
SQL> Select inst_no,
  2         sum(new_emi) emi,
  3         sum(new_interest) interest,
  4         sum(principal) principal,
  5         sum(remaining) remaining
  6    From ( Select inst_no + 1 inst_no,
  7                  new_emi,
  8                  interest,
  9                  decode( inst_no + 1, &period*&years, interest - remaining, interest)  new_interest,
 10                  decode( inst_no + 1, &period*&years, principal + remaining, principal)  principal,
 11                  decode( inst_no + 1, &period*&years, 0, remaining) remaining
 12             From  (select  rownum inst_no, roi, yrs, amt, period,
 13                            round( (roi * amt/(period * 100)) /
 14                                   (1 - power((1 + roi/(period * 100)), -yrs * period)), 2) emi
 15                      From (select &roi roi, &years yrs, &amt amt, &period period
 16                              From Dual) )
 17    MODEL
 18    DIMENSION BY ( inst_no )
 19    MEASURES     ( roi, yrs, amt, emi , period, 0 as new_emi, 0 as interest, 0 as principal, 0 as remaining)
 20    RULES
 21      UPSERT
 22      ITERATE (1000)
 23      (
 24        new_emi[ITERATION_NUMBER] = emi[1],
 25        interest[ITERATION_NUMBER]  = round(&roi/(period[1] * 100) *
 26                                      nvl(remaining[ITERATION_NUMBER-1], amt[1]), 2),
 27        principal[ITERATION_NUMBER] = emi[1] - interest[cv()],
 28        remaining[ITERATION_NUMBER] = nvl(remaining[ITERATION_NUMBER-1], amt[1]) - principal[cv()]))
 29  Where interest > 0
 30  Group by rollup( inst_no)
 31  order by  inst_no;

   INST_NO        EMI        INTEREST       PRINCIPAL       REMAINING
---------- ---------- --------------- --------------- ---------------
         1    4614.49          833.33        3,781.16       96,218.84
         2    4614.49          801.82        3,812.67       92,406.17
         3    4614.49          770.05        3,844.44       88,561.73
         4    4614.49          738.01        3,876.48       84,685.25
         5    4614.49          705.71        3,908.78       80,776.47
         6    4614.49          673.14        3,941.35       76,835.12
         7    4614.49          640.29        3,974.20       72,860.92
         8    4614.49          607.17        4,007.32       68,853.60
         9    4614.49          573.78        4,040.71       64,812.89
        10    4614.49          540.11        4,074.38       60,738.51
        11    4614.49          506.15        4,108.34       56,630.17
        12    4614.49          471.92        4,142.57       52,487.60
        13    4614.49          437.40        4,177.09       48,310.51
        14    4614.49          402.59        4,211.90       44,098.61
        15    4614.49          367.49        4,247.00       39,851.61
        16    4614.49          332.10        4,282.39       35,569.22
        17    4614.49          296.41        4,318.08       31,251.14
        18    4614.49          260.43        4,354.06       26,897.08
        19    4614.49          224.14        4,390.35       22,506.73
        20    4614.49          187.56        4,426.93       18,079.80
        21    4614.49          150.67        4,463.82       13,615.98
        22    4614.49          113.47        4,501.02        9,114.96
        23    4614.49           75.96        4,538.53        4,576.43
        24    4614.49           38.06        4,576.43             .00
            110747.76       10,747.76      100,000.00    1,189,739.34

25 rows selected.

SQL>

Results of this query can be verified here.

References:

1. Loan Calculator.

2. How to calculate EMI.

Happy reading.

Create a Unique Constraint Ignoring Existing Duplicate Data

noreply@blogger.com (Asif Momen) — Fri, 06 Jun 2008 00:59:00 +0000

Hi,

There was an interesting post on OTN forums, where the OP asks:

Can we create a unique index on a column having duplicate values which can enforce the uniqueness for future values.?

Well, this is possible as Oracle is rich in features. It can be achieved by creating a unique constraint with DEFERRABLE and NOVALIDATE keywords. By default, constraints are created as NON DEFERRABLE and VALIDATE. When we create a constraint with DEFERRABLE and NOVALIDATE, we instruct Oracle to defer checking existing data for uniqueness but always check the new or modified rows.

Here's how we can do this:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t
  2  ( x int );

Table created.

SQL>
SQL>
SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1);

1 row created.

SQL>
SQL> alter table t add constraint t_uk unique (x)
  2    deferrable initially immediate novalidate;

Table altered.

SQL> select * from t;

         X
----------
         1
         1

SQL>
SQL> alter table t modify constraint t_uk enable novalidate;

Table altered.

SQL>
SQL> insert into t values ( 1);
insert into t values ( 1)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T_UK) violated


SQL> select * from t;

         X
----------
         1
         1

SQL> insert into t values (2);

1 row created.

SQL>

Happy reading !!!

Database is very slow!!!

noreply@blogger.com (Asif Momen) — Mon, 26 May 2008 11:40:00 +0000

Dear Readers,

I received a call from one of our developers and following was the conversation that took place between us:

He: “Database is very slow”

Me: What is very slow? Can you tell me what actually you are doing?

He: I am running a simple report which is supposed to return less than 100 records. But it’s been more than 4 hours and the query is still running.

Me: How long it use to take before?

He: This is the first time we are running this query.

Me: Ok, let me log in to the database.

I logged into the database and ran my set of commands to trace the culprit SQL. I was able to identify the query and here it is:

SELECT
      TO_CHAR(TRN_DATE, 'YYYY-MM-DD HH24:MI:SS') TRN_DATE,
      TRAN_TYPE,
      TRAN_REF,
      KEY_NO,
      IN_AMT A1,
      0 A2
 FROM TRANSACTION_DAILY
WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' )
      AND SRC_FLAG ='L'
      AND (SUBSTR(OUT_KEY_NO,1,10) = 'DXIY0-19XV' or SUBSTR(IN_KEY_NO,1,10) = 'DXIY0-19XV')
      AND TRAN_TYPE  NVL(KEY_NO,'A')  NVL(IN_AMT,0) NOT IN (
                    SELECT  TD_TRAN_TYPE  TD_KEY_NO_REF  NVL(OUT_AMT,0)
                      FROM TRANSACTION_DETAILS
                     WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' ) 
                       AND RM_CODE = 'XXX123'  )
UNION
SELECT
      TO_CHAR(TRN_DATE, 'YYYY-MM-DD HH24:MI:SS') TRN_DATE,
      TD_TRAN_TYPE,
      TRAN_REF, 
      TD_KEY_NO_REF, 
      0 A1,
      OUT_AMT A2
 FROM TRANSACTION_DETAILS
WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' )
  AND RM_CODE = 'XXX123' 
  AND TD_TRAN_TYPE  TD_KEY_NO_REF  NVL(OUT_AMT,0)   NOT IN (
                    SELECT TRAN_TYPE  NVL(KEY_NO,'A')  NVL(IN_AMT,0)
                      FROM TRANSACTION_DAILY
                     WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' )
                       AND SRC_FLAG ='L'
                       AND (SUBSTR(OUT_KEY_NO,1,10) = 'DXIY0-19XV' 
                            or SUBSTR(IN_KEY_NO,1,10) = 'DXIY0-19XV') )

and this is the execution plan:

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                       |       |       |    51M(100)|          |       |       |
|   1 |  SORT UNIQUE                          |                       |  2842 |   159K|    51M (70)|198:47:09 |       |       |
|   2 |   UNION-ALL                           |                       |       |       |            |          |       |       |
|*  3 |    FILTER                             |                       |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE            |                       |  2042 |   135K|  2854   (2)| 00:00:40 |   KEY |   KEY |
|*  5 |      TABLE ACCESS FULL                | TRANSACTION_DAILY     |  2042 |   135K|  2854   (2)| 00:00:40 |   KEY |   KEY |
|   6 |     PARTITION RANGE SINGLE            |                       |   760 | 19000 | 15269   (1)| 00:03:34 |   KEY |   KEY |
|*  7 |      TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_DETAILS   |   760 | 19000 | 15269   (1)| 00:03:34 |   KEY |   KEY |
|*  8 |       INDEX RANGE SCAN                | TRAN_DET_IDX          |   434K|       |   980   (1)| 00:00:14 |   KEY |   KEY |
|*  9 |    FILTER                             |                       |       |       |            |          |       |       |
|  10 |     PARTITION RANGE SINGLE            |                       |   800 | 24800 | 15269   (1)| 00:03:34 |   KEY |   KEY |
|* 11 |      TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_DETAILS   |   800 | 24800 | 15269   (1)| 00:03:34 |   KEY |   KEY |
|* 12 |       INDEX RANGE SCAN                | TRAN_DET_IDX          |   434K|       |   980   (1)| 00:00:14 |   KEY |   KEY |
|* 13 |     TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_DAILY     |  1940 |   111K| 88735   (1)| 00:20:43 | ROW L | ROW L |
|* 14 |      INDEX RANGE SCAN                 | PK_TRANSACTION_DAILY  |   102K|       |   413   (1)| 00:00:06 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

From the above execution plan, Optimizer thinks it needs “198:47:09” hours to “Sort Unique” the result set. But the actual time the query took to complete was 6 hours and 49 minute (24540 seconds).

I then simplified the query in order to understand it clearly by removing some of the predicates. The simplified query is:

 
SELECT *
 FROM TRANSACTION_DAILY
WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' ) 
      AND TRAN_TYPE || NVL(KEY_NO,'A') || NVL(IN_AMT,0) NOT IN (
                    SELECT  TD_TRAN_TYPE || 
                            TD_KEY_NO_REF || 
                            NVL(OUT_AMT,0) 
                      FROM TRANSACTION_DETAILS
                     WHERE TRN_DATE = 
                           TO_DATE ( '#BUSDATE#', 'DDMMYYYY' )  )
UNION
SELECT *
 FROM TRANSACTION_DETAILS
WHERE TRN_DATE = TO_DATE ( '#BUSDATE#', 'DDMMYYYY' ) 
  AND TD_TRAN_TYPE || TD_KEY_NO_REF || NVL(OUT_AMT,0)   NOT IN (
                    SELECT TRAN_TYPE || 
                           NVL(KEY_NO,'A') || 
                           NVL(IN_AMT,0)
                      FROM TRANSACTION_DAILY
                     WHERE TRN_DATE = 
                            TO_DATE ( '#BUSDATE#', 'DDMMYYYY' ) )

Hmmmm, this query seems to be doing a full outer join on “TRANSACTION_DAILY” and “TRANSACTION_DETAILS” tables. I called him back:

Me: What is the objective of this report?

He: We need to find all the information from “TRANSACTION_DAILY” table which does not exist in “TRANSACTION_DETAILS” table and vice versa.

Yes, I was right. It’s doing a “Full Outer Join”. I modified his original query and proposed him a new query which is executing at lighting speed and more importantly yielding the same result:

 
SELECT  nvl(a.TRN_DATE, b.TRN_DATE) TRN_DATE,
        a.TRN_TYPE, b.OTH_TRN_TYPE,
        a.KEY_NO, b.KEY_NO_REF,
        nvl(a.IN_AMT, 0) A1, 
        nvl(b.OUT_AMT, 0) A2
FROM  (select TRN_DATE, TRN_TYPE, KEY_NO, IN_AMT, 
         from TRANSACTION_DAILY 
        where TRN_DATE = to_date('16-05-2008', 'DD-MM-YYYY')
          and (substr(IN_KEY_NO, 1, 10) = 'DXIY0-19XV' OR
               substr(OUT_KEY_NO, 1, 10) = 'DXIY0-19XV')) a 
  FULL OUTER JOIN 
      (select TRN_DATE, OTH_TRN_TYPE, KEY_NO_REF, OUT_AMT, 
         from TRANSACTION_DETAILS 
        where RM_CODE = 'XXX123'
          and TRN_DATE = to_date('16-05-2008', 'DD-MM-YYYY')) b
  ON (a.TRN_DATE = b.TRN_DATE and 
      a.TRN_TYPE = b.OTH_TRN_TYPE and 
      a.KEY_NO = b.KEY_NO_REF and 
      a.IN_AMT = b.OUT_AMT and 
      a.rno = b.rno)
WHERE (a.TRN_TYPE is null or 
       b.OTH_TRN_TYPE is null or 
       a.KEY_NO is null or 
       b.KEY_NO_REF is null or 
       a.IN_AMT is null or 
       b.OUT_AMT is null);

I called him back with over-excitement announcing him that the query is ready and let us test it:

Me: I have re-written the query, kindly come over to my office so that we can test it together.

He: How long does it take to execute?

Me: It completes in less than 4 seconds.

He: What...? … less than 4 seconds … ? … Are you sure, have you included all the condition that I have mentioned?

Me: Yes, let us test it.

He made couple of tests and the query was perfect to all the test cases but except one. Requirements started getting little messy so, I decided to create two tables and test the query against them. I created two tables, A and B, of same table structure with sample data:

 
create table a(id number, amt number);
insert into a values (1, 10);
insert into a values (2, 20);
insert into a values (3, 30);
insert into a values (4, 40);

create table b(id number, amt number);
insert into b values (2, 20);
insert into b values (3, 30);
insert into b values (5, 50);
insert into b values (6, 60);

commit;

Basically, we need to find out data that exists in A and is not in B and also records that exist in B which are not A. Here’s the query:

 
SQL> select *
  2    from a FULL OUTER JOIN b
  3      on a.id = b.id
  4   where a.id is null or b.id is null;

        ID        AMT         ID        AMT
---------- ---------- ---------- ----------
         4         40
                               6         60
                               5         50

SQL>

He said: “Yes, the query is fetching right data, but what happens when you have two records with same id and amt in table A and only a single record in B? In this case, the query should display one record from table A.”

Adding more to the complexity he said: “There is no third column in either table to distinguish this occurrence.”

So, we inserted a record in each table and ran the same query:

 
insert into a values (1, 10);
insert into b values (1, 10);


SQL> select *
  2    from a FULL OUTER JOIN b
  3      on a.id = b.id
  4   where a.id is null or b.id is null;

        ID        AMT         ID        AMT
---------- ---------- ---------- ----------
         4         40
                               6         60
                               5         50

SQL>

Oops!! The query fails at this point. Ok, I then decided to re-write this query and this time use analytical functions to rescue me from the current problematic situation.

 
SQL> select x.id, x.amt, y.id, y.amt
  2    from (select id, amt, 
  3                 row_number() over (partition by id, amt 
  4                                    order by id, amt) rno 
  5            from a) x
  6         FULL OUTER JOIN
  7         (select id, amt, 
  8                 row_number() over (partition by id, amt 
  9                                     order by id, amt) rno 
 10                 from b) y
 11      on x.id = y.id and x.rno = y.rno
 12   where x.id is null 
 13      or y.id is null 
 14      or x.rno is null 
 15      or y.rno is null;

        ID        AMT         ID        AMT
---------- ---------- ---------- ----------
         1         10
         4         40
                               5         50
                               6         60

SQL>

Yippy!!! This query rocks. I then quickly transformed the original query into this form and ran it again:

 
SELECT  nvl(a.TRN_DATE, b.TRN_DATE) TRN_DATE,
        a.TRN_TYPE, b.OTH_TRN_TYPE,
        a.KEY_NO, b.KEY_NO_REF,
        nvl(a.IN_AMT, 0) A1, 
        nvl(b.OUT_AMT, 0) A2
FROM  (select TRN_DATE, TRN_TYPE, KEY_NO, IN_AMT, 
              row_number() over (partition by KEY_NO, 
                                              TRN_TYPE, 
                                              IN_AMT 
                                     order by KEY_NO, 
                                              TRN_TYPE, 
                                              IN_AMT) rno 
         from TRANSACTION_DAILY 
        where TRN_DATE = to_date('16-05-2008', 'DD-MM-YYYY')
          and (substr(IN_KEY_NO, 1, 10) = 'DXIY0-19XV' OR
               substr(OUT_KEY_NO, 1, 10) = 'DXIY0-19XV')) a 
  FULL OUTER JOIN 
      (select TRN_DATE, OTH_TRN_TYPE, KEY_NO_REF, OUT_AMT, 
              row_number() over (partition by KEY_NO_REF, 
                                              OTH_TRN_TYPE, 
                                              OUT_AMT 
                                     order by KEY_NO_REF, 
                                              OTH_TRN_TYPE, 
                                              OUT_AMT) rno    
         from TRANSACTION_DETAILS 
        where RM_CODE = 'XXX123'
          and TRN_DATE = to_date('16-05-2008', 'DD-MM-YYYY')) b
  ON (a.TRN_DATE = b.TRN_DATE and 
      a.TRN_TYPE = b.OTH_TRN_TYPE and 
      a.KEY_NO = b.KEY_NO_REF and
      a.IN_AMT = b.OUT_AMT and 
      a.rno = b.rno)
WHERE (a.TRN_TYPE is null or 
       b.OTH_TRN_TYPE is null or 
       a.KEY_NO is null or 
       b.KEY_NO_REF is null or 
       a.IN_AMT is null or 
       b.OUT_AMT is null or
       a.rno is null or
       b.rno is null);

Execution plan of this query is:

 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                       |  2230 |   409K| 48722   (1)| 00:11:23 |       |       |
|   1 |  VIEW                                    |                       |  2230 |   409K| 48722   (1)| 00:11:23 |       |       |
|   2 |   UNION-ALL                              |                       |       |       |            |          |       |       |
|*  3 |    FILTER                                |                       |       |       |            |          |       |       |
|*  4 |     HASH JOIN RIGHT OUTER                |                       |  1552 |   175K| 24361   (1)| 00:05:42 |       |       |
|   5 |      VIEW                                |                       |   678 | 40002 | 14852   (1)| 00:03:28 |       |       |
|   6 |       WINDOW SORT                        |                       |   678 | 12882 | 14852   (1)| 00:03:28 |       |       |
|   7 |        PARTITION RANGE SINGLE            |                       |   678 | 12882 | 14851   (1)| 00:03:28 |    14 |    14 |
|*  8 |         TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_DETAILS   |   678 | 12882 | 14851   (1)| 00:03:28 |    14 |    14 |
|*  9 |          INDEX RANGE SCAN                | TRAN_DET_IDX          |   422K|       |   897   (1)| 00:00:13 |    14 |    14 |
|  10 |      VIEW                                |                       |  1552 | 88464 |  9508   (1)| 00:02:14 |       |       |
|  11 |       WINDOW SORT                        |                       |  1552 | 69840 |  9508   (1)| 00:02:14 |       |       |
|  12 |        PARTITION RANGE SINGLE            |                       |  1552 | 69840 |  9507   (1)| 00:02:14 |     7 |     7 |
|* 13 |         TABLE ACCESS FULL                | TRANSACTION_DAILY     |  1552 | 69840 |  9507   (1)| 00:02:14 |     7 |     7 |
|* 14 |    HASH JOIN ANTI                        |                       |   678 | 78648 | 24361   (1)| 00:05:42 |       |       |
|  15 |     VIEW                                 |                       |   678 | 40002 | 14852   (1)| 00:03:28 |       |       |
|  16 |      WINDOW SORT                         |                       |   678 | 12882 | 14852   (1)| 00:03:28 |       |       |
|  17 |       PARTITION RANGE SINGLE             |                       |   678 | 12882 | 14851   (1)| 00:03:28 |    14 |    14 |
|* 18 |        TABLE ACCESS BY LOCAL INDEX ROWID | TRANSACTION_DETAILS   |   678 | 12882 | 14851   (1)| 00:03:28 |    14 |    14 |
|* 19 |         INDEX RANGE SCAN                 | TRAN_DET_IDX          |   422K|       |   897   (1)| 00:00:13 |    14 |    14 |
|  20 |     VIEW                                 |                       |  1552 | 88464 |  9508   (1)| 00:02:14 |       |       |
|  21 |      WINDOW SORT                         |                       |  1552 | 69840 |  9508   (1)| 00:02:14 |       |       |
|  22 |       PARTITION RANGE SINGLE             |                       |  1552 | 69840 |  9507   (1)| 00:02:14 |     7 |     7 |
|* 23 |        TABLE ACCESS FULL                 | TRANSACTION_DAILY     |  1552 | 69840 |  9507   (1)| 00:02:14 |     7 |     7 |
----------------------------------------------------------------------------------------------------------------------------------

The results were convincing and it still takes 4 seconds to fetch the data compared to 6 hours and 49 minutes. It is 6135 times faster than the original one.

This query left End-Users rejoicing and he left my desk smiling but without answering my question, “Is the database very slow?”

P.S.: I have renamed all the table names and columns names so as not to reveal official and sensitive information on my personal blog, yet preserving the reality.

Regards

Automatic statistics gathering during Index Creation and Rebuilds

noreply@blogger.com (Asif Momen) — Fri, 23 May 2008 06:55:00 +0000

Dear all,

While testing dynamic sampling in Oracle 10g, I came to learn a 10g new feature (very late though).

My test case for dynamic sampling goes like this:

(a) Create table with data (b) Create an index, and (c) Execute the query and note the execution plan

SQL> Create table t as select * from scott.emp;

Table created.

SQL> Create index t_idx on t(empno);

Index created.

SQL> set autotrace traceonly explain
SQL> Select * from t where empno = 7788;

Execution Plan
----------------------------------------------------------
Plan hash value: 1020776977

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

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

   2 - access("EMPNO"=7788)

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

SQL> Set autotrace off

Yes, the optimizer did dynamic sampling and picked up the index plan as the optimal execution plan. Now, let me delete the statistics and re-run the same SQL.

SQL> Exec dbms_stats.delete_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> Select * from t where empno = 7788;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

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

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

   1 - filter("EMPNO"=7788)

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

SQL> Set autotrace off

Oops, optimizer did consider dynamic sampling but this time chose a Full Table Scan. How’ come the same optimizer picked index access plan on the first run?

Ok, let me do it again and this time being very careful.

(a) Create table with data

SQL> Drop table t purge;

Table dropped.

SQL> Create table t as select * from scott.emp;

Table created.

SQL> Select num_rows, last_analyzed from user_tables where table_name = 'T';

  NUM_ROWS LAST_ANALYZED
---------- --------------


SQL>

(b) Create an index, and

SQL> Create index t_idx on t(empno);

Index created.

SQL> column index_name format a10
SQL> set line 10000

SQL> select blevel, leaf_blocks, distinct_keys, clustering_factor, 
  2  num_rows, last_analyzed, user_stats, global_stats
  3  from user_indexes
  4  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS LAST_ANALYZED        USE GLO
---------- ----------- ------------- ----------------- ---------- -------------------- --- ---
         0           1            14                 1         14 05-May-2008 10:14:48 NO  NO

SQL>

Oh! I did not gather statistics.

SQL> set autotrace traceonly explain
SQL> Select * from t where empno = 7788;

Execution Plan
----------------------------------------------------------
Plan hash value: 1020776977

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

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

   2 - access("EMPNO"=7788)

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

SQL> Set autotrace off

Now it’s clear why optimizer finds Index Access Path to be best plan. Let’s continue and delete the statistics and then execute the query again:

SQL> Exec dbms_stats.delete_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL> select blevel, leaf_blocks, distinct_keys, clustering_factor, 
  2  num_rows, last_analyzed, user_stats, global_stats
  3  from user_indexes
  4  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS LAST_ANALYZED        USE GLO
---------- ----------- ------------- ----------------- ---------- -------------------- --- ---
                                                                                       NO  NO

SQL> set autotrace traceonly explain
SQL> Select * from t where empno = 7788;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

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

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

   1 - filter("EMPNO"=7788)

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

SQL>  Set autotrace off

This time the optimizer did dynamic sampling and FTS seems to be less expensive.

In Oracle 10g, when an index is created, Oracle automatically gathers statistics on the newly created index. This is also true in case of index rebuild. Because of this reason, optimizer picked Index Path to be the best plan.

References:

Oracle® Database SQL Reference 10g Release 2 (10.2)

Regards