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

This article mainly introduces "how to use the command line to evolve sql plan baselines". In the daily operation, I believe many people have doubts about how to use the command line to evolve sql plan baselines. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to use the command line to evolve sql plan baselines". Next, please follow the editor to study!

To evolve a specific sql execution plan, do the following:

1. Create an evolve task

two。 Set evolve task parameters

3. Perform evolve tasks

4. Implement the recommendations given in the task

5. Display the results of task execution

The following examples are given, assuming that the following conditions are met

. Automatic evolve tasks are not enabled in the database

. Create a SQL Plan Baseline for the following query

SELECT / * q2_group_by * / prod_name, sum (quantity_sold) FROM products p, sales sWHERE p.prod_id = s.prod_idAND p.prod_category_id = 204GROUP BY prod_name

. If you want to create two indexes to improve the performance of the query statement, if the performance of using the index is better than that of the current execution plan in SQL Plan Baseline, then evolve should execute the plan

To evolve a specific execution plan, you need to do the following

1. Perform initialization setting operation

Clear shared pools and buffer caches

SQL > ALTER SYSTEM FLUSH SHARED_POOL;System altered.SQL > ALTER SYSTEM FLUSH BUFFER_CACHE;System altered.

Enable automatic capture of SQL Plan Baselines

SQL > ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true System altered.SQL > show parameter sql_planNAME TYPE VALUE-- optimizer_capture_sql_plan_baselines boolean TRUEoptimizer_use_sql_plan_baselines boolean TRUE

Log in to the database as a sh user and set the display parameters for SQLPLUS

[oracle@jytest1] $sqlplus sh/sh@jypdbSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Last Successful login time: Thu Feb 14 2019 23:01:23 + 08:00Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > SET PAGES 10000 LINES 140SQL > SET SERVEROUTPUT ONSQL > COL SQL_TEXT FORMAT A20SQL > COL SQL_HANDLE FORMAT A20SQL > COL PLAN_NAME FORMAT A30SQL > COL ORIGIN FORMAT A12SQL > SET LONGC 60535SQL > SET LONG 60535SQL > SET ECHO ON

two。 Execute the SQL statement, so it can be captured automatically

Execute the following SQL statement

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

The query data dictionary confirms that there is no execution plan in SQL Plan Baseline because only repeated SQL statements are captured

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 2 ACCEPTED, FIXED, AUTOPURGE 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE'% Q1 roomgroup% destroy no rows selected

Execute the SQL statement again

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

3. Query the data dictionary to ensure that the execution plan has been loaded into SQL Plan Baseline, and the following query shows that the execution plan has been accepted, which means that the execution plan has been stored in SQL Plan Baselines. The origin column is displayed as AUTO-CAPTURE, which means that the execution plan is automatically captured

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED, FIXED 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE'% Q1 groups%' SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

4. The following parses the SQL statement and verifies that the optimizer uses the execution plan in SQL Plan Baseline

SQL > EXPLAIN PLAN FOR 2 SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id = 2036 GROUP BY prod_name;Explained.SQL > SELECT * FROM TABLE (null, null, 'basic + note')) PLAN_TABLE_OUTPUT- -Plan hash value: 3535171836-| Id | Operation | Name |-- -| 0 | SELECT STATEMENT | | 1 | HASH GROUP BY | 2 | HASH JOIN | | 3 | TABLE ACCESS FULL | PRODUCTS | 4 | PARTITION RANGE ALL | 5 | TABLE ACCESS FULL | SALES |-- | Note--SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement16 rows selected.

You can see from the Note section of the execution plan that SQL Plan Baseline has been applied to this SQL statement

5. Create two indexes to improve the performance of the above SQL statement

SQL > CREATE INDEX ind_prod_cat_name ON products (prod_category_id, prod_name, prod_id); Index created.SQL > CREATE INDEX ind_sales_prod_qty_sold ON sales (prod_id, quantity_sold); Index created.

6. Execute the SQL statement again, and because automatic capture is enabled, the new execution plan will be loaded into the SQL Plan Baseline

SQL > SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id = 2035 GROUP BY prod_name PROD_NAME SUM (QUANTITY_SOLD)-Envoy External 6X CD-ROM 11526Model SM26273 Black Ink Cartridge 15910PCMCIA modem/fax 28800 baud 19278Multimedia speakers- 3 "cones 10969Internal 8X CD-ROM 11197Deluxe Mouse 11609Model CD13272 Tricolor Ink Cartridge 12321Model NM500X High Yield Toner Cartridge 646618 "Flat Panel Graphics Monitor 4415External 8X CD-ROM 13886SIMM-8MB PCMCIAII card 17544PCMCIA modem/fax 19200 baud 20467Envoy External 8X CD-ROM 14850Envoy External Keyboard 2857External 6X CD-ROM 11732Model A3827H Black Image Cartridge 17314Internal 6X CD-ROM 853317 "LCD w/built-in HDTV Tuner 4874 Simm-16MB PCMCIAII card 14191Multimedia speakers- 5" cones 10419Standard Mouse 871421 rows selected.

7. Query the data dictionary to ensure that the new execution plan is loaded into SQL Plan Baseline

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED 2 FROM DBA_SQL_PLAN_BASELINES 3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342') 4 ORDER BY SQL_HANDLE, ACCEPTED SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO y * / prod_name Sum (quantity_sold) FROM products p Sal es s WHERE p.prod_id = s. Prod_id AND p. Produdes id = 203GROUP BY prod_nameSQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

The above query results show that the new execution plan is accepted.

8. Parse the SQL statement again and verify that the optimizer uses the original unindexed execution plan

SQL > EXPLAIN PLAN FOR 2 SELECT / * q1_group_by * / prod_name, sum (quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id = 2036 GROUP BY prod_name;Explained.SQL > SELECT * FROM TABLE (null, null, 'basic + note')) PLAN_TABLE_OUTPUT- -Plan hash value: 3535171836-| Id | Operation | Name |-- -| 0 | SELECT STATEMENT | | 1 | HASH GROUP BY | 2 | HASH JOIN | | 3 | TABLE ACCESS FULL | PRODUCTS | 4 | PARTITION RANGE ALL | 5 | TABLE ACCESS FULL | SALES |-- | Note--SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement16 rows selected.

The Note section above indicates that the optimizer uses the original unindexed execution plan

9. Log in to the database as an administrator user, and then create an evolve task that contains all SQL statements related to the execution plan that are not accepted

[oracle@jytest1] $sqlplus sys/xxzx7817600@jypdb as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit ProductionSQL > VARIABLE cnt NUMBERSQL > VARIABLE tk_name VARCHAR2 (50) SQL > VARIABLE exe_name VARCHAR2 (50) SQL > VARIABLE evol_out CLOBSQL > EXECUTE: tk_name: = DBMS_SPM.CREATE_EVOLVE_TASK (sql_handle = > 'SQL_07f16c76ff893342',plan_name = >' SQL_PLAN_0gwbcfvzskcu20135fd6c'); PL/SQL procedure successfully completed.SQL > SELECT: tk_name FROM DUAL : TK_NAME- -TASK_1110. Execute evolve task SQL > EXECUTE: exe_name: = DBMS_SPM.EXECUTE_EVOLVE_TASK (task_name= >: tk_name); PL/SQL procedure successfully completed.SQL > SELECT: exe_name FROM DUAL;:EXE_NAME----EXEC_1

11. View report

EXECUTE: evol_out: = DBMS_SPM.REPORT_EVOLVE_TASK (task_name= >: tk_name,execution_name= >: exe_name); SELECT: evol_out FROM DUAL GENERAL INFORMATION SECTION----Task Information:-- -Task Name: TASK_11Task Owner: SYSExecution Name: EXEC_1Execution Type: SPM EVOLVEScope: COMPREHENSIVEStatus: COMPLETEDStarted: 02 17:49:32Finished: 02 17:49:35Global Time Limit 2019 17:49:35Global Time Limit: 2147483646Per-Plan Time Limit: UNUSEDNumber of Errors: 0- -SUMMARY SECTION -- Number of plans processed: 1Number of findings: 1Number of recommendations: 1Number of errors: 0Murray- DETAILS SECTION----Object ID: 2Test Plan Name: SQL_PLAN_0gwbcfvzskcu20135fd6cBase Plan Name: SQL_PLAN_0gwbcfvzskcu242949306SQL Handle: SQL_07f16c76ff893342Parsing Schema: SHTest Plan Creator: SHSQL Text: SELECT / * q1_group_by*/ prod_name Sum (quantity_sold) FROM products p Sales sWHERE p.prod_id=s.prod_id AND p.prod_category_id=203GROUP BY prod_nameExecution Statistics:--Base Plan Test Plan---Elapsed Time (s): .044336 .012649 Time (s): .012445 buffer Gets: 044003 99Optimizer Cost: 924 891Disk Reads: 341CPU: 0 0Rows Processed: 4 2Executions: 5 9FINDINGS SECTION----Findings (1): -1. The plan was verified in 2.18 seconds. It passed the benefit criterionbecause its verified performance was 2.01 times better than that of thebaseline plan.Recommendation:--Consider accepting the plan. Executedbms_spm.accept_sql_plan_baseline (task_name = > 'TASK_11', object_id = > 2pm taskkeeper owner = >' SYS') EXPLAIN PLANS SECTION----Baseline Plan--Plan Id: 1Plan Hash Value: 1117033222 Murray- -| Id | Operation | Name | Rows | Bytes | Cost | Time |- -| 0 | SELECT STATEMENT | | 21 | 861 | 924 | 00:00:12 | | 1 | HASH GROUP BY | | 21 | 861 | 924 | 00:00:12 | * 2 | HASH JOIN | | | | 267996 | 10987836 | 742 | 00:00:09 | | * 3 | TABLE ACCESS FULL | PRODUCTS | 21 | 714 | 2 | 00:00:01 | 4 | PARTITION RANGE ALL | | 918843 | 6431901 | 662 | 00:00:08 | | 5 | TABLE ACCESS FULL | SALES | 918843 | 6431901 | 662 | 00:00:08 |-| -- Predicate Information (identified by operation id):-- * 2-access ("P". "PROD_ID" = "S". "PROD_ID") * 3-filter ("P" . "PROD_CATEGORY_ID" = 203) Test Plan--Plan Id: 2Plan Hash Value: 20315500 Murray- | Id | Operation | Name | Rows | Bytes | Cost | Time |-- | 0 | SELECT STATEMENT | | 21 | | 1 | SORT GROUP BY NOSORT | | 21 | 861 | 00:00:11 | | 2 | NESTED LOOPS | | 267996 | 10987836 | 891 | 00:00:11 | | * 3 | INDEX RANGE SCAN | IND_PROD_CAT_NAME | 21 | 714 | 1 | 00:00:01 | * 4 | INDEX RANGE SCAN | IND_SALES_PROD_QTY | 12762 | 89334 | 42 | 00:00:01 |-| -Predicate Information (identified by operation id):-* 3-access ("P". "PROD_CATEGORY_" ID "= 203) * 4-access (" P "." PROD_ID "=" S "." PROD_ID ")

The report shows that the execution plan using two indexes performs better than the original execution plan.

twelve。 Implement the recommendations given by evolve tasks

SQL > EXECUTE: cnt: = DBMS_SPM.IMPLEMENT_EVOLVE_TASK (task_name= >: tk_name,execution_name= >: exe_name); PL/SQL procedure successfully completed.

13. Query the data dictionary to ensure that the new execution plan is accepted

SQL > SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED 2 FROM DBA_SQL_PLAN_BASELINES 3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342') 4 ORDER BY SQL_HANDLE, ACCEPTED SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p Sal es s WHERE p.prod_id = s. Prod_id AND p. Produdes _ id = 203 GROUP BY prod_nameSQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC--SQL_07f16c76ff893342 SELECT / * q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES YES y * / prod_name Sum (quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. Prod_id AND p. Prodleware id = 203 GROUP BY prod_name

14. Perform a cleanup operation

SQL > VARIABLE cnt NUMBERSQL > EXEC: cnt: = DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_07f16c76ff893342'); PL/SQL procedure successfully completed.SQL > DELETE FROM SQLLOG$;13 rows deleted.SQL > commit;Commit complete.SQL > DROP INDEX IND_SALES_PROD_QTY_SOLD;Index dropped.SQL > DROP INDEX IND_PROD_CAT_NAME;Index dropped. At this point, the study on "how to use the command line to evolve sql plan baselines" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.