Share
Embed
Transcript
33 slides
-
By Louis liu www.vmcd.org DBMS_STATS Advanced Tips This article is
major to introduce advanced usage of dbms_stats for CG(column group ) of extened statistics. We start this test on oracle 11.2.0.3 oel5.8 64bit : Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "TEST" SQL> CREATE TABLE sampletable( id NUMBER, -
By Louis liu www.vmcd.org 2 hotelname VARCHAR2(50), city VARCHAR2(50), country
VARCHAR2(20)); INSERT INTO sampletable VALUES( 1, 'Pullman','Barcelona','Spain' ); INSERT INTO sampletable VALUES( 2, 'Gran Melia Fenix','Madrid','Spain' ); INSERT INTO sampletable VALUES( 3, 'Melia Castilla','Madrid','Spain' ); INSERT INTO sampletable VALUES( 4, 'Trump International','New York','US' ); INSERT INTO sampletable VALUES( 5, 'Four Seasons','New York','US' ); INSERT INTO sampletable VALUES( 6, 'Ambasciatori Palace','Rome','Italy' ); COMMIT; 3 4 Table created. -
By Louis liu www.vmcd.org SQL> SQL> 1 row created. SQL>
1 row created. SQL> 1 row created. SQL> 1 row created. SQL> -
By Louis liu www.vmcd.org 1 row created. SQL> 1 row
created. SQL> Commit complete. Now gather table statistics -
By Louis liu www.vmcd.org SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'TEST',
TabName => 'sampletable', Method_Opt => 'FOR ALL COLUMNS size 254', Cascade => TRUE ); PL/SQL procedure successfully completed. SQL> SELECT COLUMN_NAME, NUM_DISTINCT FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'SAMPLETABLE'; COLUMN_NAME NUM_DISTINCT ------------------------------ ------------ COUNTRY 3 CITY 4 -
By Louis liu www.vmcd.org HOTELNAME 6 ID 6 SQL> conn
test/test Connected. SQL> EXPLAIN PLAN FOR SELECT * FROM sampletable WHERE city = 'Madrid'; Explained. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY( format => 'ALLSTATS' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ -
By Louis liu www.vmcd.org ------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2813936524 --------------------------------------------------
| Id | Operation | Name | E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | |* 1 | TABLE ACCESS FULL| SAMPLETABLE | 2 | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- -
By Louis liu www.vmcd.org PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ 1 - filter("CITY"='Madrid')
Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 19 rows selected. -
By Louis liu www.vmcd.org SQL> EXPLAIN PLAN FOR SELECT *
FROM sampletable WHERE country = 'Spain'; Explained. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY( format => 'ALLSTATS' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2813936524 -------------------------------------------------- -
By Louis liu www.vmcd.org | Id | Operation | Name
| E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | |* 1 | TABLE ACCESS FULL| SAMPLETABLE | 3 | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ -
By Louis liu www.vmcd.org 1 - filter("COUNTRY"='Spain') Note ----- -
Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 19 rows selected. -
By Louis liu www.vmcd.org Use dbms_stats.seed_col_usage turns on monitoring for
5 minutes or 300 seconds SQL> begin 2 dbms_stats.seed_col_usage(null,null,300); 3 end; 4 / PL/SQL procedure successfully completed. SQL> EXPLAIN PLAN FOR SELECT * FROM sampletable WHERE city = 'Madrid' and country = 'Spain'; Explained. -
By Louis liu www.vmcd.org SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY( format
=> 'ALLSTATS' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2813936524 -------------------------------------------------- | Id | Operation | Name | E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | ------------------->incorrect |* 1 | TABLE ACCESS FULL| SAMPLETABLE | 1 | -------------------------------------------------- -
By Louis liu www.vmcd.org Predicate Information (identified by operation id):
--------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------ 1 - filter("CITY"='Madrid' AND "COUNTRY"='Spain') Note ----- - Warning: basic plan statistics not available. These are only collected when: -
By Louis liu www.vmcd.org * hint 'gather_plan_statistics' is used for
the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 19 rows selected. SQL> set long 90000 SQL> set lines 2000 SQL> set pages 500 SQL> select dbms_stats.report_col_usage('TEST','SAMPLETABLE') from dual; DBMS_STATS.REPORT_COL_USAGE('TEST','SAMPLETABLE') -------------------------------------------------------------------------------- LEGEND: -
By Louis liu www.vmcd.org ....... EQ : Used in single
table EQuality predicate RANGE : Used in single table RANGE predicate LIKE : Used in single table LIKE predicate NULL : Used in single table is (not) NULL predicate EQ_JOIN : Used in EQuality JOIN predicate NONEQ_JOIN : Used in NON EQuality JOIN predicate FILTER : Used in single table FILTER predicate JOIN : Used in JOIN predicate GROUP_BY : Used in GROUP BY expression ............................................................................... -
By Louis liu www.vmcd.org ############################################################################### COLUMN USAGE REPORT FOR TEST.SAMPLETABLE
........................................ 1. CITY : EQ 2. COUNTRY : EQ 3. (CITY, COUNTRY) : FILTER ############################################################################### Add extended col statistics for columns (CITY,COUNTRY) -
By Louis liu www.vmcd.org SQL>select dbms_stats.create_extended_stats(ownname=>'TEST',tabname=>'SAMPLETABLE',extension=>'(CITY,COUNTRY)') as sample_extended from dual;
SAMPLE_EXTENDED ------------------------------------------------------------------------------------------------------------------------------------------------------------------ SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'SAMPLETABLE'; -
By Louis liu www.vmcd.org Virtual column “SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z” use function “SYS_OP_COMBINED_HASH”
SQL> select column_name, ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where table_name = 'SAMPLETABLE'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE -
By Louis liu www.vmcd.org ------------------------------------------------------------ --------------- -------------- ID 1 1
ID 2 2 ID 3 3 ID 4 4 ID 5 5 ID 6 6 HOTELNAME 1 3.3972E+35 HOTELNAME 2 3.6572E+35 HOTELNAME 3 3.7097E+35 HOTELNAME 4 4.0186E+35 HOTELNAME 5 4.1777E+35 -
By Louis liu www.vmcd.org COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------------------------------------ --------------- --------------
HOTELNAME 6 4.3847E+35 CITY 1 3.4467E+35 CITY 3 4.0178E+35 CITY 5 4.0706E+35 CITY 6 4.2803E+35 COUNTRY 1 3.8140E+35 COUNTRY 4 4.3324E+35 COUNTRY 6 4.4303E+35 19 rows selected. -
By Louis liu www.vmcd.org SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'TEST',
TabName => 'sampletable', Method_Opt => 'FOR ALL COLUMNS', Cascade => TRUE ); PL/SQL procedure successfully completed. SQL> select column_name, ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where table_name = 'SAMPLETABLE'; COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------------------------------------ --------------- -------------- ID 1 1 ID 2 2 ID 3 3 -
By Louis liu www.vmcd.org ID 4 4 ID 5 5
ID 6 6 HOTELNAME 1 3.3972E+35 HOTELNAME 2 3.6572E+35 HOTELNAME 3 3.7097E+35 HOTELNAME 4 4.0186E+35 HOTELNAME 5 4.1777E+35 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------------------------------------ --------------- -------------- HOTELNAME 6 4.3847E+35 CITY 1 3.4467E+35 -
By Louis liu www.vmcd.org CITY 3 4.0178E+35 CITY 5 4.0706E+35
CITY 6 4.2803E+35 COUNTRY 1 3.8140E+35 COUNTRY 4 4.3324E+35 COUNTRY 6 4.4303E+35 SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 1 511644451 SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 3 3090128096 SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 5 9086239935 COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ------------------------------------------------------------ --------------- -------------- SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 6 9933106351 -
By Louis liu www.vmcd.org 23 rows selected. SQL> EXPLAIN PLAN
FOR SELECT * FROM sampletable WHERE country = 'Spain' and city = 'Madrid' ; Explained. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY( format => 'ALLSTATS' )); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- Plan hash value: 2813936524 -
By Louis liu www.vmcd.org -------------------------------------------------- | Id | Operation |
Name | E-Rows | -------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | -----------------------------> correct |* 1 | TABLE ACCESS FULL| SAMPLETABLE | 2 | -------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------ -
By Louis liu www.vmcd.org -------------------------------------- 1 - filter("CITY"='Madrid' AND "COUNTRY"='Spain')
Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 19 rows selected. -
By Louis liu www.vmcd.org use 10053 event to trace correlation
of these two columns. SQL> conn /as sysdba Connected. SQL> oradebug setmypid Statement processed. SQL> oradebug unlimit Statement processed. SQL> oradebug event 10053 trace name context forever, level 1 Statement processed. SQL> SQL> EXPLAIN PLAN FOR SELECT * FROM TEST.sampletable WHERE country = 'Spain' and city = 'Madrid' ; -
By Louis liu www.vmcd.org Explained. SQL> oradebug event 10053 trace
name context off Statement processed. SQL> SQL> oradebug tracefile_name /data/app1/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_8233.trc -
By Louis liu www.vmcd.org Delete histograms SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName
=> 'TEST', TabName => 'sampletable', Method_Opt => 'FOR ALL COLUMNS size 1',Cascade => TRUE ); -
By Louis liu www.vmcd.org Rounded: 2 Computed : 1.50 of
rows~= total # of rows * (1/NDV for CITY) * (1/NDV for COUNTRY)*corStrength(correlation strength) = 6*(1/4)*(1/3)*2=1.5 Delete extended stats SQL> exec dbms_stats.DROP_EXTENDED_STATS('TEST','SAMPLETABLE','(CITY,COUNTRY)'); PL/SQL procedure successfully completed. -
By Louis liu www.vmcd.org Rounded: 1 Computed : 0.50 of
rows~= total # of rows * (1/NDV for CITY) * (1/NDV for COUNTRY) /*corStrength(correlation strength)*/ = 6*(1/4)*(1/3)*1=0.5