SQL tuning advisor (STA) for Oracle SQL Optimization)

SQL tuning advisor (STA) for Oracle SQL Optimization)
SQL Tuning Advisor (STA) is an oracle SQL optimization subsidy tool. In fact, there are two main solutions to optimize SQL. One is to rewrite the SQL itself. To rewrite SQL, you must have a good understanding of SQL syntax and database execution methods. The second is the STA, which belongs to the DBMS_SQLTUNE package. Its main function is to create a correct index for the table used by SQL. Prerequisites for using STA: Make sure that the optimizer is in CBO mode. Show parameter OPTIMIZER_MODEall_rows/* CBO. All SQL return rows run in cost-based mode */first_rows/* CBO. The cost and test method are used, find one method that can return the first few rows */first_rows_n/* CBO as soon as possible. All use the cost-based optimization method CBO and use the fastest speed, return the first N rows of records */choose/*. If there is statistical information, use CBO, otherwise, you must have the advisor permission to grant advisor to scott to execute SQL optimization using the RBO */rule/* RBO */DBMS_SQLTUNE package. For example: SQL> create table obj as select * from dba_objects; the table has been created. SQL> create table ind as select * from dba_indexes; the table has been created. SQL> insert into obj select * from obj; 74603 rows have been created. SQL> insert into obj select * from obj; 149206 rows have been created. SQL> insert into obj select * from obj; 298412 rows have been created. SQL> insert into ind select * from ind; 5134 rows have been created. SQL> insert into ind select * from ind; 10268 rows have been created. SQL> insert into ind select * from ind; 20536 rows have been created. Create two tables and execute a query: SQL> set timing onSQL> set autot traceSQL> select count (*) from obj o, ind I where o. object_name = I. index_name; used time: 00: 00: 00.15 execution Plan -------------------------------------------------------- Plan hash value: 380737209 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | ---------- -------------------------------------------------------------------------- | 0 | select statement | 1 | 83 | 5063 (1) | 00:01:01 | 1 | sort aggregate | 1 | 83 | * 2 | hash join | 5861K | 463M | 1272K | 5063 (1) | 00:01:01 | 3 | table access full | IND | 44789 | 743K | 379 (1) | 00:00:05 | 4 | table access full | OBJ | 577K | 36M | 2472 (1) | 00:00:30 | -------------------------------------- ------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2-access ("O ". "OBJECT_NAME" = "I ". "INDEX_NAME") Note ------dynamic sampling used for this statement (level = 2) Statistical information limit 9 recursive call4 db block gets 10406 consistent gets 0 physical reads 0 re Do size 425 bytes sent via SQL * Net to client 415 bytes provisioned ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed and above, we can see that during the execution of the above two queries, the two tables go through the full table scan and hash join. Use STA: Step 1: Create an optimization task by calling the CREATE_TUNING_TASK function to create an optimization task. Call the Stored Procedure EXECUTE_TUNING_TASK to execute the task: SQL> set autot offSQL> set timing offDECLAREmy_task_name VARCHAR2 (30 ); my_sqltext CLOB; BEGINmy_sqltext: = 'select count (*) from obj o, ind I where o. object_name = I. index_name '; my_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (SQL _text => my_sqltext, user_name => 'Scott', scope => 'computing', time_limit => 30, task_name => 'Tuning _ SQL _test ', description => 'tuning'); DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'tuning _ SQL _test'); END;/PL/SQL process completed successfully. The CREATE_TUNING_TASK function, SQL _text is the statement to be optimized, user_name is the user through which the statement is executed, the username is capitalized, scope is the optimization range (limited or comprehensive), time_limit the time limit of the optimization process, task_name: the name of the optimization task. description: The description of the optimization task. Step 2: Execute the optimization task and use the dbms_sqltune.exe cute_tuning_task process to execute the optimization task created earlier. SQL> exec dbms_sqltune.execute_tuning_task ('tuning _ SQL _test '); the PL/SQL process has been completed successfully. Step 3: Check the status of the optimization task. You can view the current status of the optimization task in the user_advisor_tasks/dba_advisor_tasks view. SQL> SELECT task_name, status FROM USER_ADVISOR_TASKS WHERE task_name = 'tuning _ SQL _test '; TASK_NAME STATUS completion ----------- tuning_ SQL _test COMPLETED Step 4: view the optimization result and obtain the optimization task result through the optimize function. SQL> set long 999999SQL> set serveroutput on size 99999999sql> set line 120SQL> select evaluate ('tuning _ SQL _test ') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK ('tuning _ SQL _TEST ') -------------------------------------------------------------------------------- general information section extends Tuning Task Name: Completed Task Owner: SCOTTWorkload Type: Single SQL Execution Count: 2 Current Execution: exec_execution Execution Type: TUNE SQLScope: COMPREHENSIVETime Limit (seconds): 30 Completion Status: COMPLETEDStarted at: 08/29/2013 11: 10: 10 Completed at: 08/29/2013 11:10:12 --------------------------------------------------------------------------- Schema Name: scottsql id: 6wr Uu2mxyu8g3SQL Text: select count (*) from obj o, ind I where o. object_name = I. index_name into findings section (3 findings) into 1-Statistics Finding ------------------- the table "SCOTT" has not been analyzed ". "IND ". Recommendation ---------------consider collecting statistics of the optimization program for this table. Execute dbms_stats.gather_table_stats (ownname => 'Scott ', tabname => 'ind', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto '); rationale --------- to select a good execution plan, the optimizer needs the latest statistics for this table. 2-Statistics Finding --------------------- the table "SCOTT". "OBJ" has not been analyzed ". Recommendation ---------------consider collecting statistics of the optimization program for this table. Execute dbms_stats.gather_table_stats (ownname => 'Scott ', tabname => 'obj', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all columns size auto '); rationale --------- to select a good execution plan, the optimizer needs the latest statistics for this table. 3-Index Finding (see explain plans section below) ------------------------------------------------ the statement execution plan can be improved by creating one or more indexes. Recommendation (estimated benefit: 75.74%) -------------------------------------------consider running access guides that can improve the design of physical solutions or create recommended indexes. Create index SCOTT. IDX $ _ 00790001 on SCOTT. OBJ ("OBJECT_NAME");-consider running access guides that can improve physical solution design or creating recommended indexes. Create index SCOTT. IDX $ _ 00790002 on SCOTT. IND ("INDEX_NAME"); Rationale --------- creating a recommended index can significantly improve the execution plan of this statement. However, running "access guide" with a typical SQL workload may be more desirable than running a single statement. Using this method, you can obtain a comprehensive index proposal, including the overhead of index maintenance and additional space consumption. Explain plans section limit 1-Original ----------- Plan hash value: 380737209 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | -------------------- ---------------------------------------------------------------- | 0 | select statement | 1 | 83 | 5063 (1) | 00:01:01 | 1 | sort aggregate | 1 | 83 | * 2 | hash join | 5861K | 463M | 1272K | 5063 (1) | 00:01:01 | 3 | table access full | IND | 44789 | 743K | 379 (1) | 00:00:05 | 4 | table access full | OBJ | 577K | 36M | 2472 (1) | 00:00:30 | ------------------------------------------------ ---------------------------------- Predicate Information (identified by operation id): ------------------------------------------------- 2-access ("O ". "OBJECT_NAME" = "I ". "INDEX_NAME") 2-Using New Indices ------------------ Plan hash value: 4048334321 Bytes | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 83 | 1228 (2) | 00:00:15 | 1 | sort aggregate | 1 | 83 | 2 | merge join | 5861K | 463M | 1228 (2) | 00:00:15 | 3 | index full scan | IDX $ _ 00790001 | 577K | 36M | 944 (1) | 00:00:12 | * 4 | sort join | 44789 | 743K | 2120K | 268 (1) |: 04 | 5 | index fast full scan | IDX $ _ 00790002 | 44789 | 743K | 18 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 4-access ("O ". "OBJECT_NAME" = "I ". "INDEX_NAME") filter ("O ". "OBJECT_NAME" = "I ". "INDEX_NAME") The report is as above. This report mainly includes the recommended indexes and pre-and post-execution plans. Comparison, the visible effect is still acceptable. 5. Call dbms_sqltuen.drop_tuning_task to delete an existing optimization task SQL> exec dbms_sqltune.drop_tuning_task ('tuning _ SQL _test ');