advanced-tips of dbms statas

oracle extended stats for CG

Transcript

1. 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,
2. 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.
3. By Louis liu www.vmcd.org SQL> SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL>
4. By Louis liu www.vmcd.org 1 row created. SQL> 1 row created. SQL> Commit complete. Now gather table statistics
5. 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
6. 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------
7. 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): ---------------------------------------------------
8. 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.
9. 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 --------------------------------------------------
10. 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------
11. 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.
12. 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.
13. 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 | --------------------------------------------------
14. 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:
15. 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:
16. 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 ...............................................................................
17. 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)
18. 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';
19. 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
20. 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
21. 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.
22. 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
23. 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
24. 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
25. 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
26. 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------
27. 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.
28. 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' ;
29. 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
30. By Louis liu www.vmcd.org Rounded:2 Computer: 2.00
31. 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 );
32. 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.
33. 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