Shulou(Shulou.com)05/31 Report--

This article is about how to detect useful multi-column statistics for the new features of Oracle 12c. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. Environmental preparation

First, we create the test table customers_test, based on the customers table under the sh sample user.

SQL > select banner from v$version BANNER----Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionPL/SQL Release 12.1.0.2.0-ProductionCORE 12.1.0.2 .0 ProductionTNS for Linux: Version 12.1.0.2.0-ProductionNLSRTL Version 12.1.0.2.0-ProductionSQL > SQL > conn sh/sh@HOEGHConnected.SQL > SQL > DROP TABLE customers_test DROP TABLE customers_test * ERROR at line 1:ORA-00942: table or view does not existSQL > CREATE TABLE customers_test AS SELECT * FROM customers;Table created.SQL > select count (*) from customers_test; COUNT (*)-55500SQL >

Second, collect statistical information

SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (user, 'customers_test'); PL/SQL procedure successfully completed.SQL >

Third, turn on load monitoring

Open another session, log in through the sys user, and turn on load monitoring. The third parameter of SEED_COL_USAGE indicates the monitoring time in seconds, and 300 represents 5 minutes.

SQL > show userUSER is "SYS" SQL > BEGIN DBMS_STATS.SEED_COL_USAGE (null,null,300); END;/ 2 3 4PL/SQL procedure successfully completed.SQL >

Use explain plan for to query the execution plan

SQL > SQL > EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province =' CA' AND country_id = 52790; 23 456 Explained.SQL > SQL > SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ('plan_table', null,'basic rows')) 2 PLAN_TABLE_OUTPUT----Plan hash value: 2112738156 muri- -| Id | Operation | Name | Rows |-| 0 | SELECT STATEMENT | | 1 | | 1 | TABLE ACCESS FULL | CUSTOMERS_TEST | 1 |- -- 8 rows selected.SQL >

From the execution plan, the query result has only 1 column. Let's write down the result for the time being.

5. View column usage information

At this point, we can view the column usage information through REPORT_COL_USAGE.

We see that Oracle helps us detect a useful column group information, including customers_test, cust_city, and cust_state_province.

SQL > SQL > SET LONG 100000SQL > SET LINES 120SQL > SET PAGES 0SQL > SELECT DBMS_STATS.REPORT_COL_USAGE (user, 'customers_test') 2 FROM DUAL LEGEND:.EQ: Used in single table EQuality predicateRANGE: Used in single table RANGE predicateLIKE: Used in single table LIKE predicateNULL: Used in single table is (not) NULL predicateEQ_JOIN: Used in EQuality JOIN predicateNONEQ_JOIN: Used in NON EQuality JOIN predicateFILTER: Used in single table FILTER predicateJOIN: Used in JOIN predicateGROUP_BY: Used in GROUP BY expression.. .. # COLUMN USAGE REPORT FOR SH.CUSTOMERS_TEST..1. COUNTRY_ID: EQ2. CUST_CITY: EQ3. CUST_STATE_PROVINCE: EQ4. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID): FILTER####SQL >

VI. Create extended statistics

After the detection is complete, we can use the CREATE_EXTENDED_STATS method to create extended statistics. Among them, the yellow dimension part is the name of the created object.

SQL > SQL > SELECT DBMS_STATS.CREATE_EXTENDED_STATS (user, 'customers_test') FROM DUAL # EXTENSIONS FOR SH.CUSTOMERS_TEST..1. (CUST_CITY, CUST_STATE_PROVINCE, COUNTRY_ID): SYS_STUMZ$C3AIHLPBROI#SKA58H_N created####SQL >

VII. Re-collect statistical information

SQL > SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (user,'customers_test'); PL/SQL procedure successfully completed.SQL >

Check the USER_TAB_COL_STATISTICS to confirm the column statistics

By querying USER_TAB_COL_STATISTICS, we can get the column group object we just created, which is consistent with the output of step 6.

SQL > SQL > COL COLUMN_NAME FOR A30SQL > SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAMFROM USER_TAB_COL_STATISTICSWHERE TABLE_NAME = 'CUSTOMERS_TEST'ORDER BY 1 23 4 COUNTRY_ID 19 FREQUENCYCUST_CITY 620 HYBRIDCUST_CITY_ID 620 NONECUST_CREDIT_LIMIT 8 NONECUST_EFF_FROM 1 NONECUST_EFF_TO 0 NONECUST_EMAIL 1699 NONECUST_FIRST_NAME 1300 NONECUST_GENDER 2 NONECUST_ID 55500 NONECUST_INCOME_LEVEL 12 NONECUST_LAST_NAME 908 NONECUST_MAIN_PHONE_NUMBER 51344 NONECUST_MARITAL_STATUS 11 NONECUST_POSTAL_CODE 623 NONECUST_SRC_ID 0 NONECUST_STATE_PROVINCE 145 FREQUENCYCUST_STATE_PROVINCE_ID 145 NONECUST_STREET_ADDRESS 49900 NONECUST_TOTAL 1 NONECUST_TOTAL_ID 1 NONECUST_VALID 2 NONECUST_YEAR_OF_BIRTH 75 NONESYS_STUMZ$C3AIHLPBROI#SKA58H_N 620 HYBRID24 rows selected.SQL >

9. Re-inquire about the implementation plan

We see that in step 4, in the query execution plan, Rows is 1; now, it is 867. The gap is a little too big.

SQL > SQL > EXPLAIN PLAN FOR SELECT * FROM customers_test WHERE cust_city = 'Los Angeles' AND cust_state_province =' CA' AND country_id = 52790; 23 456 Explained.SQL > SQL > SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ('plan_table', null,'basic rows')) 2 Plan hash value: 2112738156 UV-| Id | Operation | Name | Rows |-- | 0 | SELECT STATEMENT | | 867 | 1 | TABLE ACCESS FULL | CUSTOMERS_TEST | 867 |-8 rows selected.SQL > Thank you for reading! This is the end of this article on "how to detect useful multi-column statistics for the new features of Oracle 12c". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.