Use of SQL _tune advisor, SQL _tuneadvisor

Use of SQL _tune advisor, SQL _tuneadvisor

Now we create a test table. The correct execution plan is now 13:11:53 scott @ orcl> select * from t2 where empno = 200; Elapsed: 00:00:00. 01 Execution Plan -------------------------------------------------------- Plan hash value: 2008370210 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | average ------------------------------------------------------------ -------------------------- | 0 | select statement | 1 | 39 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T2 | 1 | 39 | 2 (0) | 00:00:01 | * 2 | index range scan | IDX_T2 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id): --------------------------------------------- 2-acce Ss ("e-mapreduce" = 200) statistics defaults 0 recursive cballs 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1092 bytes sent via SQL * Net to client 520 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed we use hint to force an incorrect execution plan 13:11:58 scott @ orcl> select/* + full (t2) */* fro M t2 where empno = 200; Elapsed: 00:00:00. 01 Execution Plan -------------------------------------------------------- Plan hash value: 1513984157 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 1 | 39 | 15 (0) | 00:00:01 | * 1 | table access full | T2 | 1 | 39 | 15 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ----------------------------------------------- 1-filter ("EMPNO" = 200) Statistics limit 1 recursive cballs 0 db block gets 47 consistent gets 0 physical reads 0 Redo size 1088 bytes sent via SQL * Net to client 520 bytes provisioned ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed now we use sqltune advisor to adjust and create TUNING_TASK and execute declare l_task_name varchar2 (30); l_ SQL clob; begin l_ SQL: = 'select/* + full (t2) */* from t2 where empno = 200 '; l_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (SQL _text => l_ SQL, user _ Name => 'Scott ', scope => 'comphension', time_limit => 60, task_name => 'test01', description => null); end;/time_limit: the maximum execution time. The default value is 60. Scope: LIMITED. It takes about 1 second to optimize the SQL statement, but does not analyze the SQL Profiling. COMPREHENSIVE for COMPREHENSIVE analysis, including SQL Profiling analysis; longer than LIMITED. ** You can also use SQL _id to create an SQL tunning task, which is more convenient than SQL _text. Many functions CREATE_TUNING_TASK RETURNS VARCHAR2 Argument Name Type In/Out Default? Required bytes ------ -------- SQL _ID VARCHAR2 IN PLAN_HASH_VALUE NUMBER IN DEFAULT SCOPE VARCHAR2 IN DEFAULT TIME_LIMIT NUMBER IN DEFAULT TASK_NAME VARCHAR2 IN DEFAULT DESCRIPTION VARCHAR2 IN DEFAULTDECLARE my_task_name VARCHAR2 (30); BEGIN my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (SQL _ID => 'ddw7j6yfnw0vz', scope => 'comprehension', time_limit => 60, task_name => 'tunning _ SUCCESS ', description => 'Task to tune a query on ddw7j6yfnw0vz'); END;/we can view the Task status at 13:27:53 scott @ orcl> select task_name, EXECUTION_START, EXECUTION_END, STATUS from execution where task_name like 'test % '; TASK_NAME EXECUTION_START EXECUTION_END STATUS --------------------- ------------------- ----------- test01 INITIAL executes SQL tuning task BEGIN execution (task_name => 'test01'); END; /display SQL tunning result SET LONG 10000 SET LONGCHUNKSIZE 1000 SET LINESIZE 100 SELECT partition ('test01') FROM DUAL; DBMS_SQLTUNE.REPORT_TUNING_TASK ('test01') Explain GENERAL INFORMATION SECTION explain Tuning Task Name: test01Tuning Task Owner: SCOTTWorkload Type: Single SQL StatementScope: COMPREHENSIVETime Limit (seconds): 60 Completion Status: COMPLETEDStarted at: 12/21/2014 13: 29: 11 Completed at: 12/21/2014 13:29:15 explain Schema Name: scottsql id: 3bgc9fc2fp597SQL Text: select/* + full (t2) */* from t2 where empno = 200 ------------------------------- ---------------------------------------- --findings section (1 finding) statement 1-SQL Profile Finding (see explain plans section below) -------------------------------------------------------- A potentially better execution plan was found for this statement. recommendation (estimated benefit: 93.46%) -------------------------------------------Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_ SQL _profile (task_name => 'test01', task_owner => 'Scott ', replace => TRUE ); validation results ------------------ The SQL profile was tested by executing both its plan and the original plan and measuring their respective execution statistics. A plan may have been only partially executed if the other cocould be run to completion in less time. original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE Elapsed Time (s ):. 000378. 000098 74.07% CPU Time (s ):. 000299. 000099 66.88% User I/O Time (s): 0 0 Buffer Gets: 46 3 93.47% Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Rows Processed: 1 1 Fetches: 1 1 Executions: 1 1 Notes ----- 1. statistics for the original plan were averaged over 10 executions. 2. statistics for the SQL profile plan were averaged over 10 executions. ------------------------------------------------------------------------------- explain plans section ------------------------------------ ---------------------------------------- ---1-Original With Adjusted Cost -------------------------------- Plan hash value: 1513984157 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 39 | 15 (0) | 00:00:01 | * 1 | table access full | T2 | 1 | 39 | 15 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): interval 1-filter ("EMPNO" = 200) 2-Using SQL Profile ------------------ Plan hash value: 2008370210 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 39 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T2 | 1 | 39 | 2 (0) | 00:00:01 | * 2 | index range scan | IDX_T2 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): example 2-access ("EMPNO" = 200) example we can see his suggestions and execute SQL _profile. We can execute this profileexecute dbms_sqltune.accept_ SQL _profile (task_name => 'test01 ', task_owner => 'Scott ', replace => TRUE); then we will execute the original statement with hint select/* + full (t2) */* from t2 where empno = 200; 13:39:32 scott @ orcl> select/* + full (t2) */* from t2 where empno = 200; Elapsed: 00:00:00. 01 Execution Plan -------------------------------------------------------- Plan hash value: 2008370210 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 39 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T2 | 1 | 39 | 2 (0) | 00:00:01 | * 2 | index range scan | IDX_T2 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("EMPNO" = 200) note ------SQL profile "usage" used for this statementStatistics defaults 0 recursive cballs 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1092 bytes sent via SQL * Net to client 520 bytes stored ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed You Can See That SQL _profile is used to go through the correct execution plan. Now let's take a look at other situations. Our original table has no index, let's take a look at the suggestions provided by tune advisor at 13:42:44 scott @ orcl> select * from t4 where empno = 200; Elapsed: 00:00:00. 04 Execution Plan -------------------------------------------------------- Plan hash value: 2560505625 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 100 | 15 (0) | 00:00:01 | * 1 | table access full | T4 | 1 | 100 | 15 (0) | 00:00:01 | identified Predicate Information (identified by operation id): interval 1-filter ("EMPNO" = 200) Note ------dynamic sampling used for this statement (level = 2) statistics limit 14 recursive cballs 0 db block gets 114 consistent gets 50 physical reads 0 redo size 1088 bytes sent via SQL * Net to client 520 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed at this time the table is not indexed, take the full table scan we use SQL _id method to create task13: 45: 41 scott @ orcl> select SQL _text, SQL _id from v $ SQL where SQL _text like 'select * from t4 % '; SQL _TEXT SQL _ID collate ------------- select * from t4 where empno = 200 bytes my_task_name VARCHAR2 (30); BEGIN my_task_name: = require (SQL _ID => '5avs113b5fn8v ', scope => 'comphension', time_limit => 60, task_name => 'tunning _ task_5avs113b5fn8v ', description => 'Task to tune a query on 5avs113b5fn8v '); END;/start taskBEGIN restart (task_name => 'tunning _ task_5avs113b5fn8v'); END; /view reportSET LONG 10000 set longchunksize 1000 set linesize 100 SELECT values ('tunning _ task_5avs113b5fn8v') from dual; values ('tunning _ done') limit general information section limit Tuning Task Name: worker Task Owner: SCOTTWorkload Type: Single SQL StatementScope: COMPREHENSIVETime Limit (seconds): 60 Completion Status: COMPLETEDStarted at: 12/21/2014 13: 48: 02 Completed at: 12/21/2014 13:48:03 explain Schema Name: scottsql id: 5avs113b5fn8vSQL Text: select * from t4 where empno = 200 -------------------------------- ---------------------------------------- --findings section (2 findings) ------------------------------------------------------------------------------- 1-Statistics Finding ------------------- Table "SCOTT ". "T4" was not analyzed. recommendation ---------------Consider collecting optimizer statistics for this table. execute dbms_stats.gather_table_stats (ownname => 'Scott ', tabname => 't4', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto '); rationale --------- The optimizer requires up-to-date statistics for the table in order to select a good execution plan.2-Index Finding (see explain plans section below) ---------------------------------------------- The execution plan of this statement can be improved by creating one or more indices. recommendation (estimated benefit: 86.7%) ------------------------------------------Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index SCOTT. IDX $ _ 00540001 on SCOTT. t4 ("EMPNO"); Rationale --------- Creating the recommended indices significantly improves the execution plan of this statement. however, it might be preferable to run "Access Advisor" using a representative SQL workload as opposed to a single statement. this will allow to get comprehensive index recommendations which takes into account index maintenance overhead and additional space consumption. ------------------------------------------------------------------------------- explain plans section ------------------------------------ ---------------------------------------- ---1-Original ----------- Plan hash value: 2560505625 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 100 | 15 (0) | 00:00:01 | * 1 | table access full | T4 | 1 | 100 | 15 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): interval 1-filter ("EMPNO" = 200) 2-Using New Indices ------------------ Plan hash value: 3508715929 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 100 | 2 (0) | 00:00:01 | 1 | table access by index rowid | T4 | 1 | 100 | 2 (0) | 00:00:01 | * 2 | index range scan | IDX $ _ 00540001 | 1 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 2-access ("EMPNO" = 200) Then you can see that SQL _tune advisor provides the recommendation to create an index on the empno column, it can be seen that the analysis still accurately deletes tune_tastEXEC DBMS_SQLTUNE.DROP_TUNING_TASK ('test01'); after other -- SQL tunning tasks are created, you can also modify the parameter BEGIN tasks (task_name => 'test _ SQL _tuning ', parameter => 'time _ LIMIT ', value => 300); END;/-- view the progress of SQL Tuning Advisor (task execution for a long TIME) col opname for a20col ADVISOR_NAME for a20SELECT SID, SERIAL #, USERNAME, OPNAME, ADVISOR_NAME, TARGET_DESC, START_TIME SOFAR, totalwork from v $ ADVISOR_PROGRESS where username = 'test ';