facebook twitter youtube facebook facebook facebook

E-Mail : info@askmlabs.com

Phone : +1.215.353.8306

Latest Post
7:42 AM

Manageability – Extended Statistics

Written By askMLabs on Sunday, August 30, 2009 | 7:42 AM

Extended Statistics :


In releases prior to 11g , if we execute a query which includes multiple predicates in where conditions,it is impossible for the optimizer to calculate the correct selectivity of these predicates as it had no way of knowing if the columns were related or not.This makes working out the selectivity of the column group very difficult.

In Oracle Database 11g, there is a new kind of statistic, called multi-column statistics, which is a type of extended stat. Using this feature, you can actually create associations between different columns to help the optimizer make better decisions

Extended statistics
1) Multicolumn stats
2) Function-Based Stats

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> create table scott.multicol_stats as select * from dba_objects;
Table created.

SQL> conn scott/tiger
Connected.
SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 NONE
OBJECT_NAME                           41192 NONE
SUBOBJECT_NAME                          120 NONE
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 NONE
OBJECT_TYPE                              42 NONE
CREATED                                1493 NONE
LAST_DDL_TIME                          1552 NONE
TIMESTAMP                              1647 NONE
STATUS                                    1 NONE
TEMPORARY                                 2 NONE
GENERATED                                 2 NONE
SECONDARY                                 2 NONE
NAMESPACE                                20 NONE
EDITION_NAME                              1 NONE

15 rows selected.

SQL> select count(1) from MULTICOL_STATS;

COUNT(1)
----------
68851

SQL> create index TEST1_IDX1 on multicol_stats (owner, object_type);

Index created.

SQL> select owner ,  count(1) from multicol_stats group by owner order by owner;

OWNER                            COUNT(1)
------------------------------ ----------
BI                                      8
CTXSYS                                342
DBSNMP                                 55
EXFSYS                                303
FLOWS_030000                         1528
FLOWS_FILES                            12
HR                                     34
IX                                     53
MDSYS                                1281
OE                                    125
OLAPSYS                               720
ORACLE_OCM                              8
ORDPLUGINS                             10
ORDSYS                               2349
OUTLN                                   9
PM                                     26
PUBLIC                              26604
SCOTT                                  12
SH                                    306
SI_INFORMTN_SCHEMA                      8
SYS                                 29711
SYSMAN                               3285
SYSTEM                                516
TSMSYS                                  3
WKSYS                                 371
WK_TEST                                47
WMSYS                                 315
XDB                                   810

28 rows selected.

SQL> select object_type , count(1) from multicol_stats group by object_type order by object_type;

OBJECT_TYPE           COUNT(1)
------------------- ----------
CLUSTER                     10
CONSUMER GROUP              14
CONTEXT                      7
DIMENSION                    5
DIRECTORY                    9
EDITION                      1
EVALUATION CONTEXT          13
FUNCTION                   298
INDEX                     3278
INDEX PARTITION            304
INDEXTYPE                   11
JAVA CLASS               22103
JAVA DATA                  305
JAVA RESOURCE              833
JOB                         11
JOB CLASS                   13
LIBRARY                    179
LOB                        785
LOB PARTITION                7
MATERIALIZED VIEW            3
OPERATOR                    57
PACKAGE                   1267
PACKAGE BODY              1206
PROCEDURE                  133
PROGRAM                     18
QUEUE                       37
RESOURCE PLAN                7
RULE                         1
RULE SET                    21
SCHEDULE                     2
SEQUENCE                   233
SYNONYM                  26690
TABLE                     2589
TABLE PARTITION            145
TRIGGER                    488
TYPE                      2657
TYPE BODY                  227
UNDEFINED                    6
VIEW                      4773
WINDOW                       9
WINDOW GROUP                 4
XML SCHEMA                  92

42 rows selected.

SQL> select owner , object_type , count(1) from multicol_stats group by owner,object_type order by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(1)
------------------------------ ------------------- ----------
..
..

PM                             LOB                         17
PM                             TABLE                        3
PM                             TYPE                         3
PUBLIC                         SYNONYM                  26604
SCOTT                          INDEX                        4
SCOTT                          TABLE                        8
SH                             DIMENSION                    5
SH                             INDEX                       27
SH                             INDEX PARTITION            196
..

..
..
277 rows selected.

SQL> select count(1) from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';
COUNT(1)
----------
26604

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

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

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

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

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

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

14 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

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

|   0 | SELECT STATEMENT            |                |   249 | 25149 |    13   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |   249 | 25149 |    13   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |   249 |       |     1   (
0)| 00:00:01 |

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

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

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR ALL COLUMNS')

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY

15 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT  |                |  9794 |   966K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  9794 |   966K|   285   (1)| 00:00:
04 |

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

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

1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

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

|   0 | SELECT STATEMENT            |                |    74 |  7474 |     5   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    74 |  7474 |     5   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    74 |       |     1   (
0)| 00:00:01 |

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

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

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt=>'FOR COLUMNS (owner,object_type)')

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';
COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 HEIGHT BALANCED
OBJECT_ID                             68851 HEIGHT BALANCED
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED

16 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SYNONYM';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT  |                | 25704 |  2635K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS | 25704 |  2635K|   285   (1)| 00:00:
04 |

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

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

1 - filter("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SYNONYM')

13 rows selected.

SQL> explain plan for select * from multicol_stats where owner = 'PUBLIC' and object_type = 'SEQUENCE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 928537683

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

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
U)| Time     |

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

|   0 | SELECT STATEMENT            |                |    64 |  6720 |     4   (
0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MULTICOL_STATS |    64 |  6720 |     4   (
0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TEST1_IDX1     |    64 |       |     1   (
0)| 00:00:01 |

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

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

2 - access("OWNER"='PUBLIC' AND "OBJECT_TYPE"='SEQUENCE')

14 rows selected.

SQL> select count(*) from multicol_stats where lower(object_type) = 'index';

COUNT(*)
----------
3278

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT  |                |   689 | 72345 |   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |   689 | 72345 |   285   (1)| 00:00:
04 |

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

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

1 - filter(LOWER("OBJECT_TYPE")='index')

13 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','MULTICOL_STATS',method_opt => 'for all columns size skewonly for columns (lower(object_type))');

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'MULTICOL_STATS';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
OWNER                                    28 FREQUENCY
OBJECT_NAME                           41192 HEIGHT BALANCED
SUBOBJECT_NAME                          120 FREQUENCY
OBJECT_ID                             68851 NONE
DATA_OBJECT_ID                         6632 HEIGHT BALANCED
OBJECT_TYPE                              42 FREQUENCY
CREATED                                1493 HEIGHT BALANCED
LAST_DDL_TIME                          1552 HEIGHT BALANCED
TIMESTAMP                              1647 HEIGHT BALANCED
STATUS                                    1 FREQUENCY
TEMPORARY                                 2 FREQUENCY
GENERATED                                 2 FREQUENCY
SECONDARY                                 2 FREQUENCY
NAMESPACE                                20 FREQUENCY
EDITION_NAME                              1 FREQUENCY
SYS_STUXJ8K0YTS_5QD1O0PEA514IY          277 HEIGHT BALANCED
SYS_STUVJ5LMUFWDLVURW#_ROXEK1U           42 FREQUENCY

17 rows selected.

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select dbms_metadata.get_ddl('TABLE','MULTICOL_STATS') from dual;

DBMS_METADATA.GET_DDL('TABLE','MULTICOL_STATS')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."MULTICOL_STATS"
(    "SYS_STUXJ8K0YTS_5QD1O0PEA514IY"

SQL> explain plan for select * from multicol_stats where lower(object_type) = 'index';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2851574935

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT  |                |  3344 |   372K|   285   (1)| 00:00:
04 |

|*  1 |  TABLE ACCESS FULL| MULTICOL_STATS |  3344 |   372K|   285   (1)| 00:00:
04 |

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

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

1 - filter("MULTICOL_STATS"."SYS_STUVJ5LMUFWDLVURW#_ROXEK1U"='index')

13 rows selected.

SQL>
Multi column Statistics



Labels: ,
1:39 PM

Manageability - Pending Statistics

Written By askMLabs on Saturday, August 29, 2009 | 1:39 PM

Pending Statistics :


Oracle Database 11g introduced a new concept of pending statistics. In prior releases , the statistics are available to optimizer immediately after gathering the stats. In 11g we have the option to keep the stats pending until we choose to publish them to the optimizer. We now have an opportunity to test the newly gathered statistics before they are published.

The default value in 11g is to publish the stats.

Important Views and Packages

==========================

dba_tables
dba_indexes
dba_tab_columns
user_tab_pending_stats
user_ind_pending_stats
user_col_pending_stats

dbms_stats.get_prefs
dbms_stats.set_table_prefs
dbms_stats.gather_table_stats
dbms_stats.publish_pending_stats
dbms_stats.delete_pending_stats

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is "SCOTT"
SQL> desc STATS_TEST
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CUST_ID                                            NUMBER
CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
CUST_LAST_NAME                            NOT NULL VARCHAR2(40)
CUST_GENDER                                        CHAR(1)
CUST_YEAR_OF_BIRTH                                 NUMBER(4)
CUST_MARITAL_STATUS                                VARCHAR2(20)
CUST_STREET_ADDRESS                       NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE                          NOT NULL VARCHAR2(10)
CUST_CITY                                 NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE                                VARCHAR2(40)
COUNTRY_ID                                NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER                             VARCHAR2(25)
CUST_INCOME_LEVEL                                  VARCHAR2(30)
CUST_CREDIT_LIMIT                                  NUMBER
CUST_EMAIL                                         VARCHAR2(30)

SQL> select count(1) from STATS_TEST;

COUNT(1)
----------
630

2) Check the available public statistics for table "STATS_TEST"
SQL> select index_name,table_name from user_indexes where TABLE_NAME='STATS_TEST';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
CUST_CRED_LMT_IND              STATS_TEST

SQL> alter session set nls_date_format='mm/dd hh24:mi:ss';

Session altered.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = 'STATS_TEST';

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
------------------------------ -------------- ---------- -----------
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = 'STATS_TEST' order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
-------------------- -------------- ---------- ----------- -------------
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = 'STATS_TEST' order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

SQL>

3) Check the pending statistics for table  "STATS_TEST"
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = 'STATS_TEST' and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by column_name;

no rows selected

SQL>

4) Modify the table's publish value to false and then gather stats
SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;

PUBLISH
--------------------------------------------------------------------------------
TRUE

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'STATS_TEST') publish from dual;

PUBLISH
--------------------------------------------------------------------------------
TRUE

SQL> exec dbms_stats.set_table_prefs('SCOTT', 'STATS_TEST', 'PUBLISH', 'false');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('PUBLISH', 'SCOTT', 'STATS_TEST') publish from dual;

PUBLISH
--------------------------------------------------------------------------------
FALSE

SQL> execute dbms_stats.gather_table_stats('SCOTT', 'STATS_TEST');

PL/SQL procedure successfully completed.

5) Check the available public statistics for table "STATS_TEST" and you will find that the stats are not published
SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = 'STATS_TEST';

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
------------------------------ -------------- ---------- -----------
STATS_TEST

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = 'STATS_TEST' order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
-------------------- -------------- ---------- ----------- -------------
CUST_CRED_LMT_IND

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = 'STATS_TEST' order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.

6) Check the pending statistics for table  "STATS_TEST" and you will find that the stats are gathered and are pending
SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = 'STATS_TEST' and partition_name is null;

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
------------------------------ -------------- ---------- -----------
STATS_TEST                     08/29 03:25:34        630  137.646032

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
-------------------- -------------- ---------- ----------- -------------
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL>

Testing the pending stats

7) Turn off using the pending stats by setting init.ora parameter
SQL> alter session set optimizer_use_pending_statistics = false;

Session altered.

SQL> alter session set optimizer_dynamic_sampling = 0;

Session altered.

SQL> select count(1) from STATS_TEST;

COUNT(1)
----------
630

SQL> select count(1) from STATS_TEST where CUST_CREDIT_LIMIT=1500;

COUNT(1)
----------
129

SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

--- Trying to query the 20% of table data , and it should have full table scan.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2806615597

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

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (
%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                   |    13 |  2704 |     5
(0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| STATS_TEST        |    13 |  2704 |     5
(0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | CUST_CRED_LMT_IND |     5 |       |     1
(0)| 00:00:01 |

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

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

2 - access("CUST_CREDIT_LIMIT"=1500)

14 rows selected.
-- As the number of rows matching query increases , the query still gets slower and slower.

8 ) To see if the optimizer does better when it uses the statistics in the pending statistic tables.
SQL> alter session set optimizer_use_pending_statistics = true;

Session altered.

SQL> set linesize 120
SQL> explain plan for select * from STATS_TEST where CUST_CREDIT_LIMIT=1500;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3505017564

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    79 | 10823 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STATS_TEST |    79 | 10823 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

1 - filter("CUST_CREDIT_LIMIT"=1500)

13 rows selected.

SQL>

9) If the statistics prove to be acceptable, you can make them public
SQL> exec dbms_stats.publish_pending_stats('SCOTT','STATS_TEST')

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed , num_rows, avg_row_len from user_tables where table_name = 'STATS_TEST';

TABLE_NAME                     LAST_ANALYZED    NUM_ROWS AVG_ROW_LEN
------------------------------ -------------- ---------- -----------
STATS_TEST                     08/29 03:25:34        630         137

SQL> select index_name, last_analyzed , num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = 'STATS_TEST' order by index_name;

INDEX_NAME           LAST_ANALYZED    NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
-------------------- -------------- ---------- ----------- -------------
CUST_CRED_LMT_IND    08/29 03:25:35        630           2             8

SQL> select column_name, last_analyzed , num_distinct,num_nulls, density from user_tab_columns where table_name = 'STATS_TEST' order by column_name;

COLUMN_NAME                    LAST_ANALYZED  NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID                     08/29 03:25:33           19          0 .052631579
CUST_CITY                      08/29 03:25:33          300          0 .003333333
CUST_CREDIT_LIMIT              08/29 03:25:33            8          0       .125
CUST_EMAIL                     08/29 03:25:33          400          0      .0025
CUST_FIRST_NAME                08/29 03:25:33          450          0 .002222222
CUST_GENDER                    08/29 03:25:33            2          0         .5
CUST_ID                        08/29 03:25:33          630          0 .001587302
CUST_INCOME_LEVEL              08/29 03:25:33           12          0 .083333333
CUST_LAST_NAME                 08/29 03:25:33          400          0      .0025
CUST_MAIN_PHONE_NUMBER         08/29 03:25:33          630          0 .001587302
CUST_MARITAL_STATUS            08/29 03:25:33            2        234         .5
CUST_POSTAL_CODE               08/29 03:25:33          301          0 .003322259
CUST_STATE_PROVINCE            08/29 03:25:33          120          0 .008333333
CUST_STREET_ADDRESS            08/29 03:25:33          630          0 .001587302
CUST_YEAR_OF_BIRTH             08/29 03:25:33           66          0 .015151515

15 rows selected.

SQL> select table_name, last_analyzed , num_rows,  avg_row_len from user_tab_pending_stats where table_name = 'STATS_TEST' and partition_name is null;

no rows selected

SQL> select index_name, last_analyzed , num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by index_name;

no rows selected

SQL> select column_name, last_analyzed,num_distinct, num_nulls, density from user_col_pending_stats where table_name = 'STATS_TEST' and partition_name is null order by column_name;

no rows selected

SQL>
Pending Statistics Demo



Labels:
11:00 AM

Manageability - Memory Management part 2

Written By askMLabs on Friday, August 28, 2009 | 11:00 AM

Automaric Memory Management Internals - 11g

Labels:
7:49 AM

Manageability - Memory Management part 1

Automatic Memory Management  ( AMM )


Automatic Memory Management allows both SGA and PGA memory to be allocated automatically by Oracle.

Oracle 9i -> PGA_AGGREGATE_TARGET parameter  -> PGA automated
Oracle 10g -> SGA_TARGET -> SGA automated.
Oracle 11g -> MEMORY_TARGET -> ( PGA + SGA ) automated.

AMM uses two parameters MEMORY_TARGET and MEMORY_MAX_TARGET.

MEMORY_TARGET : Dynamic parameter that specifies the amount of memory that is currently  available  to  the instance.  The default value is "0".
MEMORY_MAX_TARGET: Static parameter that specifies the maximum amount of memory the instance can ever use. It defaults to MEMORY_TARGET setting.

AMM manages all SGA + PGA memory together, allowing it to shift memory from SGA to PGAs and vice versa. You only need to set a MEMORY_TARGET. If PGA requires memory , oracle releases some SGA memory to OS and server processes are allowed to increase their aggregate PGA size up to the amount of memory released.
Oracle 11g is using a new mechanism for managing shared memory which is shared file system concept. AMM memory segments are memory mapped files in /dev/shm.

11g memory management = SGA auto tune + PGA auto tune

How to enable AMM :

1) Set the following init.ora parameters

MEMORY_MAX_TARGET=xx G

MEMORY_TARGET= xx G    -->    set <= memory_max_target

PGA_AGGREGATE_TARGET=0

SGA_TARGET=0

2) Bounce database to effect the above values ( memory_max_target is static parameter )

( More internal concepts about how this AMM is working with shared memory file system will be covered in another article )

askm

AMM-diag

Important  AMM Views
===================
V$MEMORY_CURRENT_RESIZE_OPS ->  provide information on current and previous component resize operations.
V$MEMORY_DYNAMIC_COMPONENTS -> The amount of memory allocated to each dynamic component is displayed
V$MEMORY_RESIZE_OPS -> provide information on current and previous component resize operations.
V$MEMORY_TARGET_ADVICE -> provides information to help tune the MEMORY_TARGET parameter

Important  ASMM Views
====================

V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_CURRENT_RESIZE_OPS
V$SGA_RESIZE_OPS
V$SGAINFO

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

<  Pending >

Automatic Memory Management (AMM)



Labels:
8:46 AM

Schema Management – Virtual Indexes - Invisible indexes

Written By askMLabs on Tuesday, August 25, 2009 | 8:46 AM

Virtual Indexes - Invisible Indexes

Virtual Indexes :-
To simulate the existence of an index and test its impact without actually building actual index. This saves lot of time and most important I/O from and to Oracle resources (Disk, CPU, etc).

Once the Virtual Index is created, we can run an explain plan as if the index is present. Once we have decided to choose what indexes to create, we can proceed to creation of actual indexes.

Invisible Indexes :-
The invisible indexes are just opposite to virtual indexes. The index already exist on the table, but we have a doubt that it might be having a negative impact on few queries. It might be helping one query but hurting 10 others.

In earlier releases ( prior to 11g) we have two options
a) Drop the index and test the other queries for performance
b) Make the index unusable

-- If we drop it and then later we have to recreate it after testing has been done, this will result in unnecessary wastage of time and oracle resources.
-- If we make it UNUSABLE , it will result in all DML failing which is totally un expectable in 99% of the situation.

Then how do we go ....

Oracle 11g gives us "Invisible Indexes"

SQL> ALTER INDEX emp_idx INVISIBLE;
SQL> ALTER INDEX emp_idx VISIBLE;

Once the index is invisible it will not be used in any query unless explicitly mention as hint in the query, but will not have any impact on DML operations i.e DML operation will continue to update and delete from index as usual.Technically it will only become invisible to optimizer unless SQL statement explicitly specify the index with a hint.

So, to "what" exactly is this index invisible? Well, it's not invisible to the user. It's invisible to the optimizer only. Regular database operations such as inserts, updates, and deletes will continue to update the index. Be aware of that when you create invisible indexes; you will not see the performance gain due to the index while at the same time you may pay a price during DML operations.

NOTE : If we rebuild the index it will be visible again.

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

Virtual Index Demo
==============
SQL> create table test ( id number ) ;

Table created.

SQL> BEGIN
FOR i IN 1 .. 10000 LOOP
INSERT INTO test VALUES (i);
END LOOP;
COMMIT;
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
----------
10000

SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS', 'TEST', CASCADE=> true);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

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

1 - filter("ID"=9999)

13 rows selected.
--- Full table scan, Because there are no indexes.
SQL> sho parameter _use_nosegment_indexes

-- It is invisible init parameter, So following query is used to check the value
SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN "no", KSPPINM "name", KSPPITY "typ", KSPFTCTXVL "value",
KSPFTCTXDF "dflt"
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like '%&1%';SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like '%&1%'
new   5: and KSPPINM like '%use_nosegment_indexes%'

no name                            typ value                     dflt
---- ------------------------------ ---- ------------------------- -----
1391 _use_nosegment_indexes            1 FALSE                     TRUE

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> col no format 99999
col name format a30
col typ format 999
col value format a25
col dflt format a5
col flg format 999999
set pagesize 2000
SELECT KSPFTCTXPN "no", KSPPINM "name", KSPPITY "typ", KSPFTCTXVL "value",
KSPFTCTXDF "dflt"
FROM X$KSPPI A, X$KSPPCV2 B
WHERE A.INDX + 1 = KSPFTCTXPN
and KSPPINM like '%&1%';SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5
Enter value for 1: use_nosegment_indexes
old   5: and KSPPINM like '%&1%'
new   5: and KSPPINM like '%use_nosegment_indexes%'

no name                            typ value                     dflt
------ ------------------------------ ---- ------------------------- -----
1391 _use_nosegment_indexes            1 TRUE                      TRUE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

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

1 - filter("ID"=9999)

13 rows selected.

SQL> create index test_ind on test(id) nosegment;

Index created.
-- Virtual Index created , key word used is nosegment.
SQL> select segment_name,segment_type from dba_segments where segment_name='TEST_IND';

no rows selected
--  No segments for the created virtual index.
SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - access("ID"=9999)

13 rows selected.

-- The query now uses the index. If we have the expected performance gain , we can drop the virtual index and create the actual index.

Invisible Index Demo
===============

Case 1 :
optimizer_use_invisible_indexes=FALSE  and  index=INVISIBLE

Case 2 :
optimizer_use_invisible_indexes=FALSE  and  index=VISIBLE

Case 3 :
optimizer_use_invisible_indexes=TRUE  and  index=INVISIBLE

Case 4 :
optimizer_use_invisible_indexes=TRUE  and  index=VISIBLE

SQL> create table test (id  number);

Table created.

SQL> begin
for i in 1 .. 10000 loop
insert into test values (i);
end loop;
commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

COUNT(1)
----------
10000

SQL> create index test_ind on test(id) invisible;

Index created.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> exec dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true);
BEGIN dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats('SYS','TEST_IND');
BEGIN dbms_stats.gather_index_stats('SYS','TEST_IND'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1
--- It is an intended behaviour that we cant collect statistics for an invisible index. The only way to collect  the statistics for invisible indexes is by setting the parameter OPTIMIZER_USE_INVISIBLE_INDEXES.

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

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

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

1 - filter("ID"=9999)

13 rows selected.

CASE 1 :

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

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

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

1 - filter("ID"=9999)

13 rows selected.
-- Full table scan. Optimizer will not use the index.
SQL> explain plan for select /*+ INDEX (test test_ind) */ * from test WHERE id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020

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

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

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

1 - filter("ID"=9999)

13 rows selected.
-- Optimizer should use index with hint. But it is not using it. I need to further test on it.

CASE 2

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
SQL> alter index test_ind visible;

Index altered.

SQL> exec dbms_stats.gather_table_stats('SYS', 'TEST', cascade=> true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats('SYS','TEST_IND');

PL/SQL procedure successfully completed.

-- We are able to gather statistics , because the index is now visible.
SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

1 - access("ID"=9999)

13 rows selected.
-- Optimizer is now using the index

CASE 3

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

Session altered.

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind invisible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       INVISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

1 - access("ID"=9999)

13 rows selected.
--- Optimizer used the invisible index by setting the init parameter TRUE

CASE 4

SQL> sho parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter index test_ind visible;

Index altered.

SQL> select index_name,visibility from user_indexes where index_name = 'TEST_IND';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
TEST_IND                       VISIBLE

SQL> explain plan for select * from test where id = 9999;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3357096749

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     3 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     3 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

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

1 - access("ID"=9999)

13 rows selected.

-- Optimizer uses the invisible index.
Labels:
5:38 AM

Schema Management – Virtual Column

Virtual Column

Let us say we have the following table

Item_id  number
item_name varchar2(50)
item_cost number
Item_desc varchar2(100)

SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER

We want to add a column called GRADE to the table which identifies the grade of item based on the cost ( G1,G2,G3 etc ). This will help in identifyng the quality of the item based on the grade.

The logic to implement is :

ITEM_COST                                    --   GRADE
<=10000                                           --    G1
>10000 AND <100000                     --    G2
>100000 AND <1000000                 --    G3
Else                                                   --    G4

The oracle internally should decide the grade based on the cost of the item while inserting the row into the table. The only option we have till now is to write a trigger which will fire while inserting the row to the table and that trigger code will decide the grade with the above logic and inserts the record to the table. This approach is very tedious and performance issues would arise due to context switching from and into the trigger code.

Oracle 11g gives the concept of virtual column. Virtual columns offer the flexibility to add columns that convey business sense without adding any complexity or performance impact. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.

===========================================================


DEMO         DEMO            DEMO           DEMO           DEMO            DEMO             DEMO
===========================================================

SQL> create table test (
2  item_id number,
3  item_name varchar2(25),
4  item_cost number);

Table created.

SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER

SQL> insert into test (item_id, item_name, item_cost) values (301, 'HARDDISK', 3000);

1 row created.

SQL> insert into test (item_id, item_name, item_cost) values (302, 'LAPTOP', 60000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST
---------- ------------------------- ----------
301 HARDDISK                        3000
302 LAPTOP                         60000

SQL> alter table test add grade varchar2(6)
2  generated always as
3  (
4  case
5       when item_cost <= 10000 then 'G1'
6       when item_cost > 10000 and item_cost <= 100000 then 'G2'
7       when item_cost > 100000 and item_cost <= 1000000 then 'G3'
8       else 'G4'
9     end
10  ) virtual ;

Table altered.

---  Adding virtual column to table
SQL> desc test
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
ITEM_ID                                                        NUMBER
ITEM_NAME                                                      VARCHAR2(25)
ITEM_COST                                                      NUMBER
GRADE                                                          VARCHAR2(6)

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST GR
---------- ------------------------- ---------- --
301 HARDDISK                        3000 G1
302 LAPTOP                         60000 G2
---  Data is already populated to the virtual column.
SQL> insert into test (item_id, item_name, item_cost) values (302, 'LAPTOP', 120000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

ITEM_ID ITEM_NAME                  ITEM_COST GR
---------- ------------------------- ---------- --
301 HARDDISK                        3000 G1
302 LAPTOP                         60000 G2
302 LAPTOP                        120000 G3

SQL> col data_default format a50
SQL> set linesize 100
SQL> select column_name, data_default from   user_tab_columns where  table_name = 'TEST';

COLUMN_NAME               DATA_DEFAULT
------------------------- --------------------------------------------------
ITEM_ID
ITEM_NAME
ITEM_COST
GRADE                     CASE  WHEN "ITEM_COST"<=10000 THEN 'G1' WHEN ("ITEM_COST">10000 AND "ITEM_COST"<

SQL>

SQL> create index ind_grade on test (grade);

Index created.

SQL> select index_type from user_indexes where index_name = 'IND_GRADE';

INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
--- The index created is function based index
SQL> select column_expression from user_ind_expressions where index_name = 'IND_GRADE';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "ITEM_COST"<=10000 THEN 'G1' WHEN ("ITEM_COST">10000 AND "ITEM_COST"<

SQL>  insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3');
insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

--We cant insert data to the virtual column.There is no storage for virtual column.

Limitations of Virtual Columns
==========================
1) Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
2) It can only refer to columns defined in the same table.
3) You cant perform a delete or insert operation on a virtual column.
4) You cant update a virtual column by using it in the SET clause of an update command.

Ex :
SQL>  insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3');
insert into test (item_id, item_name, item_cost , grade) values (302, 'LAPTOP', 120000,'G3')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
Labels:
3:34 AM

Schema Management - adding column with default value

Adding a column with default value

Objective :
===========
I want to add a NOT NULL column with some default value to a table which is not empty.

Oracle 11g provides a command something like this ..

SQL> alter table product add Item_code varchar2(20) default 'AAAAA' not null;

Here i am trying to add a column item_code which is not null to a non empty table, and i am specifying a default column value as 'AAAAA'.

It will add a column and for subsequent record inserts it will give the default value to the column item_code if i dont provide a value.

Then what about the column value for already existing records in the table. Will it be NULL ?

Will it try to update the default value for the existing rows. Suppose if there are some millions of records in the table and updating millions of rows will not only take a very long time, it will also fill up the undo segments, generate a large amount of redo, and create massive performance overhead.

Is there any better approach in oracle 11g.

Well we have ....

The above statement will not issue an update to all the existing records of the table. When a user selects the column for an existing record, Oracle gets the fact about the default value from the data dictionary and returns it to the user.

So there is not storage involved , no redo and undo generation and no performance overhead.

Conclusion :
=============
No storage involved. Default value information for existing rows is stored in data dictionary.
Labels:
 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger