Shulou(Shulou.com)06/01 Report--

You can often encounter the need to optimize sql, developers directly throw a SQL to DBA optimization, and then what to do?

Of course, experienced DBA can start in a variety of directions, sometimes by building the right index to achieve good optimization results, but those complex SQL complex table associations, but the DBA people sweat.

The following is a special introduction to STA, a scientific optimization method officially provided by oracle. After practice, I dare not say that this feature is absolutely effective, but I can broaden my mind and learn a lot from it, instead of using "guessing" to create an index.

SQL optimizer SQL Tuning Advisor (STA) is oracle's sql optimization subsidy tool.

In fact, there are two main ways to optimize sql. One is to rewrite sql itself. Rewriting sql requires a good understanding of sql syntax and database execution.

The second is this STA, which belongs to the DBMS_SQLTUNE package, and its main purpose is to create the correct index on the tables used by sql.

Prerequisites for using STA:

Make sure the optimizer is in CBO mode. Show parameter OPTIMIZER_MODEall_rows / * CBO,sql all return rows run * / first_rows / * CBO in a cost-based manner, using a combination of cost and heuristics to find a way to return the first few rows * / first_rows_n / * CBO as soon as possible, all using the cost-based optimization method CBO, and with the fastest speed, return the first N rows of records * / choose / * if statistics are available, use CBO Otherwise, using RBO*/rule / * RBO*/ to execute DBMS_SQLTUNE package for sql optimization requires the permission of advisor: grant advisor to scott

The following is an example of using STA:

1. First, create two exercise tables, obj and ind, and create only the table without creating an index:

The SQL > create table obj as select * from dba_objects; table has been created. The SQL > create table ind as select * from dba_indexes; table has been created. SQL > insert into obj select * from obj; has created 74603 lines. SQL > insert into obj select * from obj; has created 149206 lines. SQL > insert into obj select * from obj; has created 298412 lines. SQL > insert into ind select * from ind; has created 5134 lines. SQL > insert into ind select * from ind; has created 10268 lines. SQL > insert into ind select * from ind; has created 20536 lines.

two。 Then make a joint query on the two tables, obj and ind, and view their execution plan through autotrace:

SQL > set timing onSQL > set autot traceSQL > select count (*) from obj o, ind i where o.object_name=i.index_name Time spent: 00: 00: 00.15 to execute the plan-Plan hash value: 380737209muri- | 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 | | 379K | 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) Statistics-9 recursive calls 4 db block gets 10406 consistent gets 0 physical reads 0 redo size 425 bytes Sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

By executing the plan, you can clearly see the full table scan and hash join of the two tables when executing the federated query of the above two tables.

Formally use STA for optimization:

Step 1: create an optimization task

Create the optimization task by calling the function DBMS_SQLTUNE.CREATE_TUNING_TASK, and call the stored procedure DBMS_SQLTUNE.EXECUTE_TUNING_TASK to perform 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.objecttaskingnamename, ind i where o.objectroomnamename: = DBMS_SQLTUNE.CREATE_TUNING_TASK (sql_text = > my_sqltext,user_name = >' SCOTT', scope = > 'COMPREHENSIVE',time_limit = > 30 tuning_sql_test',description = >' tuning') DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name = > 'tuning_sql_test'); END;/PL/SQL process completed successfully.

The parameters are explained as follows:

Function CREATE_TUNING_TASK

Sql_text is a statement that needs to be optimized

User_name is the user through which the statement is executed, with an uppercase user name

Scope is the scope of optimization (limited or comprehensive)

Time limit of time_limit Optimization process

Task_name optimization task name

Description of the description optimization task.

Step 2: perform optimization tasks

Perform the previously created optimization task by calling the dbms_sqltune.execute_tuning_task procedure.

SQL > exec dbms_sqltune.execute_tuning_task ('tuning_sql_test'); PL/SQL process completed successfully.

Step 3: check the status of the optimization task

You can see the current status of the tuning task by looking at 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---tuning_sql_test COMPLETED

Step 4: view the optimization results

The result of the optimization task can be obtained through the dbms_sqltune.report_tning_task function.

SQL > set long 999999SQL > set serveroutput on size 999999SQL > set line 120SQL > select DBMS_SQLTUNE.REPORT_TUNING_TASK ('tuning_sql_test') from dual

The results of the optimization are shown below:

DBMS_SQLTUNE.REPORT_TUNING_TASK ('TUNING_SQL_TEST')-GENERAL INFORMATION SECTION- -Tuning Task Name: tuning_sql_testTuning Task Owner: SCOTTWorkload Type: Single SQL StatementExecution Count: 2Current Execution: EXEC_112Execution Type: TUNE SQLScope: COMPREHENSIVETime Limit (seconds): 30Completion Status: COMPLETEDStarted at : 08 11:10:10Completed at 29 11:10:10Completed at 2013 11:10:12----Schema Name: SCOTTSQL ID: 6wruu2mxyu8g3SQL Text: select count (*) from obj o Indi where o.object_name=i.index_name----FINDINGS SECTION (3 findings)- -1-Statistics Finding- has not analyzed the table "SCOTT". "IND". Recommendation-consider collecting optimizer statistics 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-in order to select a good execution plan, the optimizer needs the latest statistics for this table. 2-Statistics Finding- has not analyzed the table "SCOTT". "OBJ". Recommendation-consider collecting optimizer statistics 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-in order to select a good execution plan, the optimizer needs the latest statistics for this table. 3-Index Finding (see explain plans section below)-you can improve the execution plan of this statement by creating one or more indexes. Recommendation (estimated benefit: 75.74%)-consider running access guides or creating recommended indexes that can improve physical solution design. Create index SCOTT.IDX$$_00790001 on SCOTT.OBJ ("OBJECT_NAME");-consider running access guides or creating recommended indexes that can improve physical solution design. 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 the access Guide with a typical SQL workload may be preferable to a single statement. In this way, a comprehensive index proposal can be obtained, including calculating the cost of index maintenance and additional space consumption.

-EXPLAIN PLANS SECTION-- -- 1-Original-Plan hash value: 380737209 Murray | 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-| Id | Operation | Name | | Rows | Bytes | TempSpc | Cost (% CPU) | Time |-| 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) | 00:00:04 | 5 | INDEX FAST FULL SCAN | IDX$$_00790002 | 44789 | 743k | | 18 (0) | 00:00:01 |-| -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.

After reading this report carefully, two main suggestions are given: 1. Collect the statistical information of obj and ind in two tables; 2. Create the corresponding index on these two tables.

And it's very sweet to give all the sentences out, so you can run them directly.

What is more intimate is that the report shows the expected results before and after the optimization, which is completely clear at a glance, which makes it more convenient for DBA to consider whether to recommend the optimization plan.

Delete optimization tasks

By calling dbms_sqltuen.drop_tuning_task, you can delete existing optimization tasks and release resources.

SQL > exec dbms_sqltune.drop_tuning_task ('tuning_sql_test')

Conclusion: the above is the use of SQL optimizer SQL Tuning Advisor (STA), the syntax may be a little complicated, but it does bring great help to the optimization work, often use more practice can be proficient.

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.