Extended Optimizer Statistics in Oracle 11g Improve memory Ma

The oracle optimizer uses statistics to select the optimal or sub-optimal execution plan. During the execution plan determination process, oracle can analyze the data distribution characteristics of each table column very well, however, oracle is powerless for some associations between columns, such as the correspondence between city names and zip codes. Fortunately, the statistical analysis Feature Based on column combinations and relational expressions is introduced in oracle11g, which undoubtedly provides powerful support for the optimizer to make a more correct choice. We call it the extended statistics function.

The extended statistical analysis function can be divided into two types:Column-based combinationAndExpression-based. There are two methods to achieve this:Use DBMS_STATS.CREATE_EXTENDED_STATSAndUse the method_opt Parameter

DBMS_STATS.CREATE_EXTENDED_STATS and METHOD_OPT

After creating the extended statistics, we can query the information in the DBA_STAT_EXTENSIONS and dba_tab_col_statistics views. Example:

First, let's take a look at the METHOD_OPT method.

SQL> show userUSER is "SH" SQL> exec dbms_stats.delete_table_stats ('sh', 'customer'); PL/SQL is successfully completed. SQL> exec Dbms_Stats.drop_Extended_Stats ('sh', 'customer', '(cust_state_province, country_id)'); PL/SQL has been completed successfully. SQL> Select * From User_Stat_Extensions; unselected row SQL> select * from user_tab_col_statistics where table_name = 'customer'; unselected row SQL> exec dbms_stats.gather_table_stats ('sh', 'customer ', METHOD_OPT => 'for all columns size 1, for columns (cust_state_province, country_id) size auto'); PL/SQL process completed successfully. SQL> select table_name, column_name from orders where table_name = 'customer'; TABLE_NAME COLUMN_NAME Comment comment MERs SYS_STU # S # WF25Z # QAHIHE # moffmm_mers MERs CUST_IDCUSTOMERS comment into cust_gendermers MERs comment into orders CU ST_POSTAL_CODECUSTOMERS cust_citymers MERs primary COLUMN_NAME primary MERs primary COUNTRY_IDCUSTOMERS primary capacity CUST_CREDIT_LIMITCUSTOMERS CUST_EMAILCUSTOMERS CUST_TOTALCUSTOMERS primary CUST_SRC_IDCUSTOMERS C UST_EFF_FROMTABLE_NAME COLUMN_NAME ------------------------------ ---------------------------- MERs CUST_EFF_TOCUSTOMERS CUST_VALID has selected 24 rows. SQL> Select * From User_Stat_Extensions; TABLE_NAME EXTENSION_NAME extend EXTENSION CREATO DRO upgrade ------ --- MERs SYS_STU # S # WF25Z # QAHIHE # MOFFMM _ ("CUST_STATE_PROVINCE", "COUNTRY_ID") USERYES

Using method_opt will generate extended columns and directly count the extended information

Let's take a look at DBMS_STATS.CREATE_EXTENDED_sTATS.

SQL> exec dbms_stats.delete_table_stats ('sh', 'customer'); PL/SQL process completed successfully. SQL> exec Dbms_Stats.drop_Extended_Stats ('sh', 'customer', '(cust_state_province, country_id)'); PL/SQL has been completed successfully. SQL> Select * From User_Stat_Extensions; unselected rows SQL> select table_name, column_name from user_tab_col_statistics where table_name = 'customer'; unselected rows SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS ('sh ', 'customer', '(cust_state_province, country_id)') from dual; DBMS_STATS.CREATE_EXTENDED_STATS ('sh', 'customer', '(CUST_STATE_PROVINCE, COUNTRY_ID)') Province )')------------------------------------------------------------------ Revoke ------- SYS_STU # S # WF25Z # QAHIHE # MOFFMM_ SQL> select table_name, column_name from user_tab_col_statistics where table_name = 'customer'; unselected row SQL> Select * From User_Stat_Extensions; TABLE_NAME EXTENSION_NAME extension creato dro ---------------------------------------------------- -------- Users ------ --- MERs SYS_STU # S # WF25Z # QAHIHE # MOFFMM _ ("CUST_STATE_PROVINCE", "COUNTRY_ID") user yessql> exec values ('sh', 'customer ', METHOD_OPT => 'for all columns size 1'); the PL/SQL process has been completed successfully. SQL> select table_name, column_name from orders where table_name = 'customer'; TABLE_NAME COLUMN_NAME Comment comment MERs SYS_STU # S # WF25Z # QAHIHE # moffmm_mers MERs CUST_IDCUSTOMERS comment into cust_gendermers MERs comment into orders CU ST_POSTAL_CODECUSTOMERS cust_citymers MERs primary COLUMN_NAME primary MERs primary COUNTRY_IDCUSTOMERS primary capacity CUST_CREDIT_LIMITCUSTOMERS CUST_EMAILCUSTOMERS CUST_TOTALCUSTOMERS primary CUST_SRC_IDCUSTOMERS C UST_EFF_FROMTABLE_NAME COLUMN_NAME ------------------------------ ---------------------------- MERs CUST_EFF_TOCUSTOMERS CUST_VALID has selected 24 rows.

We can see that DBMS_STATS.CREATE_EXTEND_sTATS does not directly create statistical information. We need to manually call the DBMS_STATS.GATHER_TABLE_STATS process.

EXPRESSION

In addition to column combinations, you can also create extended statistics by expression, which is very useful for function-based indexes.

SQL> select dbms_Stats.create_extended_Stats('SH','CUSTOMERS','(MOD(CUST_ID,10))') FROM DUAL;DBMS_STATS.CREATE_EXTENDED_STATS('SH','CUSTOMERS','(MOD(CUST_ID,10))')----------------------------------------------------------------------------------------------------SYS_STU1D2S2K6$TFSJ$24PUR2SN1ESQL> Select * From User_Stat_Extensions;TABLE_NAME       EXTENSION_NAME------------------------------ ------------------------------EXTENSION CREATO DRO-------------------------------------------------------------------------------- ------ ---CUSTOMERS       SYS_STU1D2S2K6$TFSJ$24PUR2SN1E(MOD("CUST_ID",10)) USERYES

How does extended statistics help the optimizer?

The following example demonstrates how the extended statistical information helps the optimizer correctly evaluate the selectivity of SQL statements,

SQL> Select * From User_Stat_Extensions; -- no extended statistics is enabled. No row is selected. SQL> exec dbms_stats.gather_table_stats ('sh', 'customer', METHOD_OPT => 'FOR ALL COLUMNS SIZE 1, for columns cust_state_province size 250, for columns country_id size 250 '); PL/SQL process completed successfully. SQL> set autotrace on explainSQL> Select Count (*) From Customers Where Cust_State_Province = 'CA'; COUNT (*) ---------- 3341 -- the actual number of instances is 3341 execution Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 11 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 11 | * 2 | table access full | CUSTOMERS | 3359 | 36949 | 413 (1) | 00:00:05 | -- the number of optimizer estimates is 3359, which is basically close to that of optimizer 3341 (identified by operation id): Listen 2-filter ("CUST_STATE_PROVINCE" = 'CA ') SQL> Select Count (*) From MERs Where Country_Id = 52790; COUNT (*) ---------- 18520 -- the actual number of executions is 18520 ---------------------------------------------------------- Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 5 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 5 | * 2 | table access full | CUSTOMERS | 18863 | 94315 | 413 (1) | 00:00:05 | -- the number of optimizer estimates is 18863, which is basically close to that of optimizer 18520 (identified by operation id): --------------------------------------- 2-filter ("COUNTRY_ID" = 52790) SQL> SELECT COUNT (*) FROM MERs WHERE CUST_STATE_PROVINCE = 'CA' AND COUNTRY_ID = 52790; COUNT (*) ---------- 3341 -- the actual number of values is 3341, this is because there is a numerical relationship between country_id and CUST_STAT_PROVINCE. In this relationship, the oracle database has an unknown execution Plan -------------------------------------------------- Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 16 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 16 | * 2 | table access full | CUSTOMERS | 1142 | 18272 | 413 (1) | 00:00:05 |

-- Since oracle does not know the required Predicate Information (identified by operation id): ------------------------------------------------- 2-filter ("CUST_STATE_PROVINCE" = 'CA' AND "COUNTRY_ID" = 52790) SQL> Select Count (*) From MERs Where Country_Id = 52770; COUNT (*) ---------- 7780 execution Plan -------------------------------------------------------- Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 5 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 5 | * 2 | table access full | CUSTOMERS | 7380 | 36900 | 413 (1) | 00:00:05 | identified by operation id: --------------------------------------------------------------- 2-filter ("COUNTRY_ID" = 52770) SQL> SELECT COUNT (*) FROM MERs WHERE CUST_STATE_PROVINCE = 'CA' AND COUNTRY_ID = 52770; COUNT (*) ---------- 0 -- actual value is 0 execution Plan ------------------------------------------------------ Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 16 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 16 | * 2 | table access full | CUSTOMERS | 447 | 7152 | 413 (1) | 00:00:05 | average --------------------------------------------------------------------------------
-- Oracle does not know

Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52770)

It can be seen that if oracle does not know the quantitative relationship between country_id and CUST_STAT_PROVINCE, there is a gap between the optimizer's estimation results and the actual values. The following describes the results after the extended statistics are added.
SQL> SELECT evaluate ('sh', 'customer', '(cust_state_province, country_id)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS ('sh', 'customer', '(CUST_STATE_PROVINCE, COUNTRY_ID) ') define SYS_STU # S # WF25Z # QAHIHE # MOFFMM_ SQL> exec dbms_stats.gather_table_stats ('sh', 'customer', METHOD_OPT =>' FOR ALL Columns size 1, for columns cust_state_province size 250, for columns country_id size 250 '); PL/SQL process completed successfully. SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name = 'customer' AND column_name like 'sys % '; TABLE_NAME COLUMN_NAME HISTOGRAM partition ---------------------------- --------------- MERs SYS_STU # S # WF25Z # QAHIHE # MOFFMM _ NONESQL> set autotrace on explainSQL> select count (*) FROM MERs WHERE CUST_STATE_PROVINCE = 'CA' AND COUNTRY_ID = 52770; COUNT (*) ---------- 0 execution Plan -------------------------------------------------------- Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------------- | 0 | select statement | 1 | 16 | 413 (1) | 00:00:05 | 1 | SORT AGGR EGATE | 1 | 16 | * 2 | table access full | CUSTOMERS | 432 | 6912 | 413 (1) | 00:00:05 | descripredicate Information (identified by operation id ): limit 2-filter ("CUST_STATE_PROVINCE" = 'CA' AND "COUNTRY_ID" = 52770) SQL> set autotrace offSQL> exec dbms_stats.gather_table_stats ('s H ', 'customer', METHOD_OPT =>' for all columns size auto, for columns cust_state_province size 250, for columns country_id size 250 '); PL/SQL process completed successfully. SQL> select table_name, column_name, histogram from user_tab_col_statistics where table_name = 'customer' AND column_name like 'sys % '; TABLE_NAME COLUMN_NAME HISTOGRAM detail ---------------------------- --------------- MERs SYS_STU # S # WF25Z # QAHIHE # MOFFMM _ FREQUENCYSQL> set autotrace on explainSQL> select count (*) FROM MERs WHERE CUST_STATE_PROVINCE = 'CA' AND COUNTRY_ID = 52770; COUNT (*) ---------- 0 execution Plan ---------------------------------------------------------- Plan hash value: 296924608 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 16 | 413 (1) | 00:00:05 | 1 | sort aggregate | 1 | 16 | * 2 | table access full | CUSTOMERS | 5 | 80 | 413 (1) | 00:00:05 | identified by operation id: --------------------------------------------------------------- 2-filter ("CUST_STATE_PROVINCE" = 'CA' AND "COUNTRY_ID" = 52770)

The expanded statistical information plays a role and helps the optimizer make a correct assessment.

Extended Statistics Usage Notes:
The Oracle documentation notes these limitations onDbms_stats.create_extended_statsExtension argument:
-The extension cannot contain a virtual column.

-Extensions cannot be created on tables owned by SYS.

-Extensions cannot be created on cluster tables, index organized tables, temporary tables or external tables.

-The total number of extensions in a table cannot be greater than a maximum of (20, 10% of number of non-virtual columns in the table ).

-The number of columns in a column group must be in the range [2, 32].

-A column cannot appear more than once in a column group.

-A column group can not contain expressions.

-An expression must contain at least one column.

-An expression cannot contain a subquery.

-The COMPATIBLE parameter needs to be 11.0.0.0.0 or greater