Friday, December 30, 2011

Check Constraint & NOT NULL Constraint

Learnt something newly in Oracle database, its about Check constraint and NOT NULL constraints.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  (
  3     x number not null,
  4     y number check (y is not null),
  5     z number,
  6     constraint t_chk check(z is not null)
  7  )
  8  nologging;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert /*+ append */ into h
  2  select level,level,level
  3  from dual
  4  connect by level <=1000000;

1000000 rows created.

Elapsed: 00:00:01.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.04
rajesh@ORA11GR2>

Now, we have defined NOT NULL constraint on column 'x' and column level check constraint in 'y' and table level check constraint on column 'z'.


rajesh@ORA11GR2>
rajesh@ORA11GR2> select constraint_type,
  2  search_condition
  3  from user_constraints
  4  where table_name ='T'
  5  /

CONST SEARCH_CONDITION
----- -------------------------
C     "X" IS NOT NULL
C     y is not null
C     z is not null

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2>

The NOT NULL constraint is Technically implemented in the database as CHECK constraint.  But the real difference comes into picture how the optimizer understand NOTNULL constraint and CHECK constraint.


rajesh@ORA11GR2> create index t_ind on t(x) nologging;

Index created.

Elapsed: 00:00:00.64
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 646498162

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |  2239   (1)| 00:00:27 |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_IND |  1000K|  2239   (1)| 00:00:27 |
------------------------------------------------------------------

rajesh@ORA11GR2>

With the index on column X, the optimizer knew that X was not null and the index on X therefore pointed to every row in the table, so its safe to count the rows via the Index.

If we drop and recreate the index on column Y, the optimizer cannot use this new index.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.06
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.68
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

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

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>

In this case the optimizer is not aware of that Y was NOT NULL, because this check constraint doesn't provide enough information for the optimizer & entirely null entries will not be preserved in B*Tree index. and because Y was assumed by the optimizer to allow for NULL values, So the optimizer could not use this index.

The same applies to column 'Z'.


rajesh@ORA11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> create index t_ind on t(z) nologging;

Index created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select /*+ index(t,t_ind) */ count(*)
  2  from t
  3  /
Elapsed: 00:00:00.00

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

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   771   (2)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|   771   (2)| 00:00:10 |
-------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>





Posted by at 2 comments:

Tuesday, December 6, 2011

Partitioned Mview Refresh

Materialized views are partitioned for

1) PCT Refresh, there by improving refresh performance.

2) Partition pruning: only relevant aggregated groups are accessed, there by reducing query processing cost.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table emp
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  nologging
 12  as
 13  select level as empno,
 14     dbms_random.string('A',30) as ename,
 15     dbms_random.string('B',7) as job,
 16     sysdate - mod(level,100) as hire_date,
 17     dbms_random.value(1,1000) as comm,
 18     dbms_random.value(1,10000) as salary,
 19     mod(level,5)+1 as deptno
 20  from dual
 21  connect by level <= 1000000;

Table created.

Elapsed: 00:01:29.65
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_pk primary key(empno);

Table altered.

Elapsed: 00:00:14.29
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'EMP',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.15
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table dept
  2  as
  3  select level as deptno,
  4     dbms_random.string('C',10) as dname,
  5     dbms_random.string('E',7) as loc
  6  from dual
  7  connect by level <= 5;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk primary key(deptno);

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'DEPT',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
  2  constraint emp_fk
  3  foreign key(deptno) references dept;

Table altered.

Elapsed: 00:00:07.79
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>

rajesh@ORA11GR2> create materialized view emp_dept_mv
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  build immediate
 12  refresh on demand
 13  enable query rewrite
 14  as
 15  select e.deptno,count(*)
 16  from dept d,
 17      emp e
 18  where e.deptno = d.deptno
 19  group by e.deptno;

Materialized view created.

Elapsed: 00:00:07.56
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.39
rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1215826357

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |     1 |     6 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                |             |     1 |     6 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE        |             |     1 |     6 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;

The query rewrite happens as expected. Now we will make the partition p1 to Stale by deleting some data's from the detailed table.

rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                              6 FRESH

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>

Refreshing the (partitioned) materialized view, will make its stale segments to Fresh.

rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.57
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

But what happened behind the scene is, Oracle did a delete on Mview and re-inserted the new data's from detail table. 

So what happens if the Stale partition in Mview has many records? then we will spend more time in deleting the data from stale partition.

SQL ID: 88sv43hhjtc0y
Plan Hash: 1443979106
DELETE FROM "RAJESH"."EMP_DEPT_MV" 
WHERE
  ( ( ( ("DEPTNO" IN (1))  )) ) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          4           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          4           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us)
      1   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)
      1    MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)

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

SQL ID: 2hk25tz0vayzs
Plan Hash: 3660430689
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "RAJESH"."EMP_DEPT_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "E"."DEPTNO" , COUNT(*)  FROM "EMP" "E","DEPT" "D" WHERE 
  ("E"."DEPTNO"="D"."DEPTNO") AND  ( ( ( ("E"."DEPTNO" IN (1)) ) )  )GROUP BY 
  "E"."DEPTNO"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.10       1.25       2894       2906          5           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.10       1.25       2894       2906          5           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=2906 pr=2894 pw=0 time=0 us)
      1   SORT GROUP BY NOSORT (cr=2905 pr=2894 pw=0 time=0 us cost=806 size=26000 card=2000)
 199999    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2894 pw=0 time=3421974 us cost=806 size=26000 card=2000)
 199999     TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2894 pw=0 time=2845781 us cost=806 size=26000 card=2000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                         82        0.03          1.15
--------------------------------------------------------------------------------

So the right way to refresh (partitioned) Mview is to go nonatomic (atomic=>false), So when you do this Oracle will Truncate the partitions rather than deleting it.

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql;
rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.04
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
rajesh@ORA11GR2>

Tkprof shows me this.

SQL ID: a7rykky0gvrq9
Plan Hash: 0
LOCK TABLE "RAJESH"."EMP_DEPT_MV" PARTITION ("P1")  IN EXCLUSIVE MODE  NOWAIT 
  


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 2)
--------------------------------------------------------------------------------

SQL ID: c3yz7q6n5d2h0
Plan Hash: 1999525523
ALTER TABLE "RAJESH"."EMP_DEPT_MV" TRUNCATE  PARTITION P1 UPDATE GLOBAL 
  INDEXES 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          3          1           0
Execute      1      0.04       0.21          4          0         38           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.24          4          3         39           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.03          0.05
  reliable message                                2        0.00          0.00
  enq: RO - fast object reuse                     2        0.00          0.00
  db file sequential read                         4        0.02          0.02
  local write wait                                3        0.00          0.00
--------------------------------------------------------------------------------

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

SQL ID: 80rm69rmu73s5
Plan Hash: 1312300534
insert /*+ RELATIONAL("EMP_DEPT_MV") PARALLEL("EMP_DEPT_MV",1) APPEND 
  NESTED_TABLE_SET_SETID NO_REF_CASCADE */ into "RAJESH"."EMP_DEPT_MV" 
  partition ("P1") (select /*+ RELATIONAL("EMP_DEPT_MV") 
  PARALLEL("EMP_DEPT_MV",1) */  *  from "RAJESH"."EMP_DEPT_MV" partition 
  ("P1") ) delete global indexes


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.02          0          3          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.03          0          3          4           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=3 pr=0 pw=0 time=0 us)
      1   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
      1    MAT_VIEW ACCESS FULL EMP_DEPT_MV PARTITION: 1 1 (cr=3 pr=0 pw=0 time=0 us cost=3 size=10 card=1)

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




Posted by at No comments:

Monday, December 5, 2011

Partition Change Tracking in Mview

Partition change Tracking is the ability to identify which rows in materialized view's are affected by certain fact table partition changes. When a partition is modified in fact table those rows become stale and all others remain fresh, rather than considering entire materialized view as stale this allows query rewrite to use those rows that are fresh while the query_rewrite_integrity is set to ENFORCED or TRUSTED.

A new data dictionary dba_mview_detail_partition was introduced in Oracle 11g will show which partitions are are stale or fresh.

Here is the snipped of code to demonstrate Partition change Tracking


rajesh@ORA11GR2> create table emp
  2  partition by list(deptno)
  3  (
  4     partition p1 values (1),
  5     partition p2 values (2),
  6     partition p3 values (3),
  7     partition p4 values (4),
  8     partition p5 values (5),
  9     partition pmax values (default)
 10  )
 11  nologging
 12  as
 13  select level as empno,
 14     dbms_random.string('A',30) as ename,
 15     dbms_random.string('B',7) as job,
 16     sysdate - mod(level,100) as hire_date,
 17     dbms_random.value(1,1000) as comm,
 18     dbms_random.value(1,10000) as salary,
 19     mod(level,5)+1 as deptno
 20  from dual
 21  connect by level <= 1000000;

Table created.

Elapsed: 00:01:21.53
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_pk primary key(empno);

Table altered.

Elapsed: 00:00:11.25
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'EMP',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.25
rajesh@ORA11GR2> create table dept
  2  as
  3  select level as deptno,
  4     dbms_random.string('C',10) as dname,
  5     dbms_random.string('E',7) as loc
  6  from dual
  7  connect by level <= 5;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk primary key(deptno);

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'DEPT',
  5     estimate_percent=>dbms_stats.auto_sample_size,
  6     cascade=>false,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2> alter table emp add
  2  constraint emp_fk
  3  foreign key(deptno) references dept;

Table altered.

Elapsed: 00:00:05.96
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>

The fact table is partition by list one for each department.



rajesh@ORA11GR2> create materialized view emp_dept_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select e.deptno,count(*)
  7  from dept d,
  8      emp e
  9  where e.deptno = d.deptno
 10  group by e.deptno;

Materialized view created.

Elapsed: 00:00:06.34
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

rajesh@ORA11GR2> show parameter query_rewrite;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2>


rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - filter("EMP_DEPT_MV"."DEPTNO"=1)

rajesh@ORA11GR2> set autotrace off;

Now the query got rewritten to use materialized view to produce it's results.

Marking any changes to fact table, those rows become stale in Materialized views.

rajesh@ORA11GR2> delete from emp
  2  where deptno  = 1
  3  and rownum = 1;

1 row deleted.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 STALE
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.04

Now the query rewrite is not possible for partition p1 since it became STALE. Accessing data from Mview will produce incorrect results, so the optimizer went scanning the base tables to answer this query.

rajesh@ORA11GR2> set autotrace traceonly explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =1;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2365616264

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    13 |   806   (1)| 00:00:10 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    13 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |  2000 | 26000 |   806   (1)| 00:00:10 |   KEY |   KEY |
|   3 |    TABLE ACCESS FULL   | EMP  |  2000 | 26000 |   806   (1)| 00:00:10 |     1 |     1 |
-----------------------------------------------------------------------------------------------

But still query rewrite is possible for FRESH partitions.


rajesh@ORA11GR2> select count(*)
  2  from emp
  3  where deptno =2;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2723375566

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |             |     1 |     6 |            |          |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_DEPT_MV |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - filter("EMP_DEPT_MV"."DEPTNO"=2)

What happens when we refresh Mview? The affected rows in Materialized view are deleted and re-inserted.

rajesh@ORA11GR2>
rajesh@ORA11GR2> @trace.sql
rajesh@ORA11GR2> alter session set timed_statistics=true;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> alter session set events '10046
  2  trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2> set echo off;
rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_DEPT_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.59
rajesh@ORA11GR2> select mview_name, detail_partition_name, detail_partition_position, freshness
  2  from dba_mview_detail_partition
  3  where mview_name ='EMP_DEPT_MV'
  4  /

MVIEW_NAME                     DETAIL_PARTITION_NAME          DETAIL_PARTITION_POSITION FRESH
------------------------------ ------------------------------ ------------------------- -----
EMP_DEPT_MV                    P1                                                     1 FRESH
EMP_DEPT_MV                    P2                                                     2 FRESH
EMP_DEPT_MV                    P3                                                     3 FRESH
EMP_DEPT_MV                    P4                                                     4 FRESH
EMP_DEPT_MV                    P5                                                     5 FRESH
EMP_DEPT_MV                    PMAX                                                   6 FRESH

6 rows selected.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>

Tkprof shows me this:

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

SQL ID: 88sv43hhjtc0y
Plan Hash: 3770894312
DELETE FROM "RAJESH"."EMP_DEPT_MV" 
WHERE
  ( ( ( ("DEPTNO" IN (1))  )) ) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          3           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us)
      1   MAT_VIEW ACCESS FULL EMP_DEPT_MV (cr=3 pr=0 pw=0 time=0 us cost=3 size=3 card=1)

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

SQL ID: 2hk25tz0vayzs
Plan Hash: 3660430689
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "RAJESH"."EMP_DEPT_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "E"."DEPTNO" , COUNT(*)  FROM "EMP" "E","DEPT" "D" WHERE 
  ("E"."DEPTNO"="D"."DEPTNO") AND  ( ( ( ("E"."DEPTNO" IN (1)) ) )  )GROUP BY 
  "E"."DEPTNO"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.18       1.37       2894       2910         24           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.18       1.37       2894       2910         24           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=2910 pr=2894 pw=0 time=0 us)
      1   SORT GROUP BY NOSORT (cr=2905 pr=2893 pw=0 time=0 us cost=806 size=26000 card=2000)
 199999    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2905 pr=2893 pw=0 time=2304007 us cost=806 size=26000 card=2000)
 199999     TABLE ACCESS FULL EMP PARTITION: 1 1 (cr=2905 pr=2893 pw=0 time=1748159 us cost=806 size=26000 card=2000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                         81        0.03          1.25
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         2        0.00          0.00
--------------------------------------------------------------------------------




Posted by at 4 comments:

Monday, October 10, 2011

RELY Constraint

Learnt something newly from Oracle today. Its about RELY constraints. This RELY option tells oracle to trust (rely) on the data integrity


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table emp as select * from scott.emp;

Table created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> create table dept as select * from scott.dept;

Table created.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'EMP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'DEPT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create materialized view emp_mv
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select dname,count(*)
  7  from emp, dept
  8  where emp.deptno = dept.deptno
  9  group by dname
 10  /

Materialized view created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

rajesh@ORA11GR2>
rajesh@ORA11GR2>

Materialized view is not used because Oracle doesn't understand the relationship between EMP and DEPT tables. Once we provide the relationship Oracle is able to perform query rewrite.

rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno not null;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1956236023


----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_MV |     3 |    39 |     3   (0)|
-----------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)

rajesh@ORA11GR2>

Now, what happens in true data warehouse and there are millions of records in your fact tables? you really don't want the additional effort of verifying the Foreign key relationship- In which case you create a non-validate constraint- once that is used to inform about the relationship but that has not been validated by database.


rajesh@ORA11GR2> alter table emp
  2  drop constraint emp_fk
  3  /

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  drop constraint dept_pk
  3  /

Table altered.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp modify deptno null;

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

Inserted purposely a row into emp that violates the constraint to show the Impact of RELY constraints.


rajesh@ORA11GR2> insert into emp(empno,deptno) values(1,1);

1 row created.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno)
  4  RELY enable novalidate;

Table altered.

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept
  5  RELY enable novalidate;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

The NOVALIDATE bypasses the checking of the existing data & RELY tells Oracle to "trust" the data integrity.

rajesh@ORA11GR2> alter table emp modify deptno not null novalidate;

Table altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

rajesh@ORA11GR2> exec dbms_mview.refresh('EMP_MV');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
rajesh@ORA11GR2>
rajesh@ORA11GR2>

rajesh@ORA11GR2> show parameter query_rewrite_inte;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------
query_rewrite_integrity              string      enforced
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        15

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------

rajesh@ORA11GR2>

Enforced - This is the default mode. The optimizer only uses fresh data from the materialized views and only use those relationships that are based on ENABLED VALIDATED primary, unique, or foreign key constraints.

Oracle did not rewrite the query to use materialized view because of this. We must go down a level in query integrity to inform oracle to "trust" us.

<quote>
In TRUSTED mode, the optimizer trusts that the relationships declared in dimensions and RELY constraints are correct. In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. In this mode, the optimizer also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions. This mode offers greater query rewrite capabilities but also creates the risk of incorrect results if any of the trusted relationships you have declared are incorrect.
</quote>

rajesh@ORA11GR2> alter session set query_rewrite_integrity=trusted;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace on explain;
rajesh@ORA11GR2> select count(*) from emp;

  COUNT(*)
----------
        14

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1956236023


-----------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |    13 |     3   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |    13 |            |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| EMP_MV |     3 |    39 |     3   (0)|
-----------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)

rajesh@ORA11GR2>

Here Oracle did the query rewrite the side effect is that in this case wrong answer is returned. The reason we got the wrong answer is when the MV is refreshed, It didnot get the newly added row. The data we told oracle to "rely" was not at all reliable.

Be 100% sure that your data is reliable when you ask oracle to "rely" on it.

In short,

RELY ENABLE NOVALIDATE and ENABLE NOVALIDATE - Both enable contraint but dont validate existing data.

RELY ENABLE NOVALIDATE - Will peform query rewrite if query_rewrite_integrity=trusted.
ENABLE NOVALIDATE - Will **not** peform query rewrite if query_rewrite_integrity=trusted/enforced/stale_tolerated

RELY ENABLE NOVALIDATE - May yeild wrong results in answering query. So we have to be sure that data is 100% pure when defining RELY option

Posted by at 1 comment:

Wednesday, September 7, 2011

Pagination

In the Pagination you are generally interested in taking some complex query, sorting it and then retrieving just the first N rows. ROWNUM has an optimization that facilitates this type of query to be processed efficiently by avoiding a massive sort of result sets.

Suppose you have the query in this form:

select <list of columns>
from <table_name>
where <condition>
order by <column_name>

Assume this query returns a lot of data and you are interested only in subset of data (say 150 to 160), then there are two ways to this approach.

select *
from
(
 select a.*,rownum as r
 from big_table a
 order by object_name,rowid
) where r between 150 and 160


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     45.79     157.30     379697     186114        721          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     45.81     157.33     379697     186114        721          11

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW  (cr=186114 pr=379697 pw=217115 time=124839576 us)
12773888   SORT ORDER BY (cr=186114 pr=379697 pw=217115 time=197569458 us)
12773888    COUNT  (cr=186114 pr=162582 pw=0 time=114965056 us)
12773888     TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=162582 pw=0 time=51095606 us)


Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.01          0.02
  direct path write temp                        388        0.08          3.26
  db file sequential read                        65        0.03          0.07
  db file scattered read                      10332        0.04         28.01
  local write wait                              669        0.00          0.34
  direct path read temp                        8153        0.04         77.10

Oracle database did the following.

1) Run a Full scan on big_table
2) Assign the rownum values to each row it read.
3) Sorted the results by and un-indexed column on the entire result sets.
4) ran out of sort area memory and need to swap the temporary extents to disk ( This is identified by pw=217115 (physical writes) available in step 3 of the Row Source operation )
5) Merge these temporary extents to get the needed results.
6) release these temporary extents as you finish them.


The other approach is

select * from (
 select t.*,rownum as r
 from
 (
  select *
  from big_table
  order by object_name,rowid
 )t where rownum <= 160
) where r >= 150

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      8.07      34.20     173388     186114          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      8.07      34.20     173388     186114          0          11

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
     11  VIEW  (cr=186114 pr=173388 pw=0 time=34199867 us)
    160   COUNT STOPKEY (cr=186114 pr=173388 pw=0 time=34201314 us)
    160    VIEW  (cr=186114 pr=173388 pw=0 time=34200668 us)
    160     SORT ORDER BY STOPKEY (cr=186114 pr=173388 pw=0 time=34200183 us)
12773888      TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=173388 pw=0 time=51095597 us)


Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                       123        0.02          0.08
  db file scattered read                      11007        0.03         26.63
  SQL*Net message from client                     2        0.00          0.00


1) Run the full scan on the big_table
2) Sort only the rows needed (say 160 in above scenario) and not the entire result set.

The query read the entire table, but by using the SORT ORDER BY STOPKEY it sorted only the 160 records it was able to limit its use of temporary space for just 160 rows. This result is sorted in sort memory and there is NO disk sort (pw = 0)

This seemingly small detail sorting just N rows can lead to huge gains in performance and resource usage. It takes a lot less RAM to sort 160 rows than it does to sort one million rows (not to mention TEMP space usage).
Posted by at No comments:
Labels:

Saturday, August 13, 2011

Scalar subquery and Result Cache on 11g

Scalar subqueries are used to minimize the number of times a PL/SQL function is called from SQL. This is important because the overhead of going from SQL to PL/SQL over and over again can be quite expensive, and the scalar subquery caching database feature can dramatically decrease this overhead.

What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value.

rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select * from all_objects;

Table created.

Elapsed: 00:00:06.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  as
  4  begin
  5     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  6     return (length(x));
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(distinct owner) from t;

COUNT(DISTINCTOWNER)
--------------------
                  30

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable cpu number;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.73
Statistics
----------------------------------------------------------
         55  recursive calls
          0  db block gets
       1527  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')      DBMS_UTILITY.GET_CPU_TIME-:CPU
--------------------------- ------------------------------
71598                       67

Elapsed: 00:00:00.09
rajesh@ORA11GR2>

you can see that the function was called once per row even though the inputs to the function were repeated over and over again. If you use a scalar subquery you will notice a massive reduction in calls to the function

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,(select f(owner) from dual) from t;

71598 rows selected.

Elapsed: 00:00:00.18
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1495  consistent gets
          0  physical reads
          0  redo size
     808592  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')     DBMS_UTILITY.GET_CPU_TIME-:CPU
-------------------------- ------------------------------
64                         8

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

The function call went down from 71598  to 64 and CPU time got dropped. How about making the function DETERMINISTIC

rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  deterministic
  4  as
  5  begin
  6     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  7     return (length(x));
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.21
Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       1519  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')         DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
954                            16

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

DETERMINISTIC function reduced the function calls, but not as smart as scalar subquery caching. so going one step futher how about making the function as result_cache in Oracle 11g?

rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  result_cache
  4  as
  5  begin
  6     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  7     return (length(x));
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.54
Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       1519  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')       DBMS_UTILITY.GET_CPU_TIME-:CPU
---------------------------- ------------------------------
30                           44

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

The function called is equal to the number of distinct values present in the column OWNER. If you run the query again then function call becomes zero but still the cpu utilization time is longer than scalar subquery.

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.50

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

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')         DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
0                              42

Elapsed: 00:00:00.01
rajesh@ORA11GR2> 

This shows that even if the function is defined as DETERMINISTIC or RESULT_CACHE its better to have the function called as scalar subquery for efficient CPU utilization.
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)