advanced tips of dbms statas

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 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.


SQL> SQL>

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL>

1 row created.

SQL>


1 row created.

SQL>

1 row created.

SQL>

Commit complete.

Now gather table statistics


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


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

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


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

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):

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


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.


SQL> EXPLAIN PLAN FOR SELECT * FROM sampletable WHERE country = 'Spain';

Explained.


PLAN_TABLE_OUTPUT

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

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


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



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

| 0 | SELECT STATEMENT | | 3 |


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


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

PLAN_TABLE_OUTPUT

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

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


1 - filter("COUNTRY"='Spain')

Note

-----




19 rows selected.


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 /


SQL> EXPLAIN PLAN FOR SELECT * FROM sampletable WHERE city = 'Madrid' and country = 'Spain';

Explained.



PLAN_TABLE_OUTPUT

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

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


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


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

| 0 | SELECT STATEMENT | | 1 | ------------------->incorrect


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



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

PLAN_TABLE_OUTPUT

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

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

1 - filter("CITY"='Madrid' AND "COUNTRY"='Spain')

Note

-----





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:


.......

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

...............................................................................


###############################################################################

COLUMN USAGE REPORT FOR TEST.SAMPLETABLE

........................................

1. CITY : EQ

2. COUNTRY : EQ

3. (CITY, COUNTRY) : FILTER

###############################################################################

Add extended col statistics for columns (CITY,COUNTRY)


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';


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


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

ID 1 1

ID 2 2

ID 3 3

ID 4 4

ID 5 5

ID 6 6

HOTELNAME 1 3.3972E+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



19 rows selected.


SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'TEST', TabName => 'sampletable', Method_Opt =>

'FOR ALL COLUMNS', Cascade => TRUE );


SQL> select column_name, ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where table_name =

'SAMPLETABLE';


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

ID 1 1

ID 2 2

ID 3 3


ID 4 4

ID 5 5

ID 6 6







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


CITY 1 3.4467E+35


CITY 3 4.0178E+35

CITY 5 4.0706E+35

CITY 6 4.2803E+35




SYS_STU3UJN5IT#IIA5ASKY72Q8V6Z 1 511644451




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



23 rows selected.

SQL> EXPLAIN PLAN FOR SELECT * FROM sampletable WHERE country = 'Spain' and city = 'Madrid' ;

Explained.


PLAN_TABLE_OUTPUT

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

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



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


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

| 0 | SELECT STATEMENT | | 2 | -----------------------------> correct


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


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

PLAN_TABLE_OUTPUT

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


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

1 - filter("CITY"='Madrid' AND "COUNTRY"='Spain')

Note

-----




19 rows selected.


use 10053 event to trace correlation of these two columns.

SQL> conn /as sysdba

Connected.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit


SQL> oradebug event 10053 trace name context forever, level 1


SQL>

SQL> EXPLAIN PLAN FOR SELECT * FROM TEST.sampletable WHERE country = 'Spain' and city = 'Madrid' ;


Explained.

SQL> oradebug event 10053 trace name context off


SQL>

SQL> oradebug tracefile_name

/data/app1/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_8233.trc


Rounded:2 Computer: 2.00


Delete histograms

SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'TEST', TabName => 'sampletable', Method_Opt => 'FOR

ALL COLUMNS size 1',Cascade => TRUE );


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)');



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

advanced tips of dbms statas

Technology

louis liu

table statistics

sampletable values

table sampletable id

allstats plan

basic plan statistics

table access

sql1 row