First Use SQL Tuning recommendation Tool--sql Tuning Advisor

Source: Internet
Author: User
Tags sorts

in 10g, Oracle launched its own SQL optimization aids :  SQL Optimizer (SQL Tuning Advisor :STA) , it is the new Dbms_sqltune package. Use the STA to make sure that the optimizer is in CBO mode. but I think that using this tool is only suitable for people who don't understand SQL at all, and don't think that tools can solve problems. SQL is ultimately an expression of a business, how the tool might understand the business. SQL tuning is the best way to diagnose with autotrace,10046,10053,display_cursor these excellent tools and then tune based on the knowledge of the business and the Oracle Foundation that you have. playing this tool here today is just a ensemble.

1. Create the data, deliberately do not build the index, and then do the query

Sql> CREATE TABLE Test1 as SELECT * from Dba_objects;
Sql> CREATE TABLE Test2 as SELECT * from Dba_objects;
sql> exec dbms_stats.gather_table_stats (user, ' test1 ');
sql> exec dbms_stats.gather_table_stats (user, ' test2 ');
Sql> Set Timing on
Sql> Set Autotrace traceonly
Sql> Select COUNT (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id;
Time used: 00:00:00.07
Execution plan
----------------------------------------------------------
Plan Hash value:2544416891
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |   10 | 298 (1) | 00:00:04 |
|  1 |       SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |       HASH JOIN | |   50981 |   497k| 298 (1) | 00:00:04 |
|    3 | TABLE ACCESS full| TEST1 |   50982 |   248k| 149 (1) | 00:00:02 |
|    4 | TABLE ACCESS full| TEST2 |   50983 |   248k| 149 (1) | 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
1410 consistent gets
0 physical Reads
0 Redo Size
410 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed
Sql> set Autotrace off


2. Create an optimization task through the create_tuning_task of the Dbms_sqltune package, and then through the Dbms_sqltune. Execute_tuning_task to perform tuning tasks and generate tuning recommendations.
Sql> DECLARE
My_task_name VARCHAR2 (300);
My_sqltext CLOB;
BEGIN
My_sqltext: = ' SELECT count (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id ';
My_task_name: = Dbms_sqltune. Create_tuning_task (
Sql_text = My_sqltext,
user_name = ' TEST ',--must be capitalized
Scope = ' comprehensive ',
Time_limit = 10,
Task_name = ' Tuning_sql_test ',
Description = ' Task to tune a query on a specified table ');
Dbms_sqltune. Execute_tuning_task (task_name = ' tuning_sql_test ');
END;
/

Official document parsing:
Sql_text the text of a SQL statement
User_name the username for whom the statement are to be tuned
Scope Tuning scope (limited/comprehensive)
Time_limit the maximum duration in seconds for the tuning session
Task_name an optional tuning task name
Description A task of the SQL tuning session to A maximum of characters

3. Check the status of the optimization task
Sql> select Task_name,advisor_name,status from User_advisor_tasks;
Task_name Advisor_name STATUS
------------------------------ ------------------------------ -----------
Tuning_sql_test SQL Tuning Advisor completed

4. View Optimization Results
Sql> Set Longchunksize 999999
Sql> set serveroutput on size 999999
Sql> Set Long 999999
Sql> Select Dbms_sqltune.report_tuning_task (' Tuning_sql_test ') from dual;
Dbms_sqltune. Report_tuning_task (' Tuning_sql_test ')
------------------------------------------------------------------------------------------------
General Information Sections
-------------------------------------------------------------------------------
Tuning Task Name:tuning_sql_test
Tuning Task Owner:test
Scope:comprehensive
Time Limit (seconds): 10
Completion status:completed
Started at:05/23/2014 08:50:40
Completed at:05/23/2014 08:50:41
Number of Index findings:1
-------------------------------------------------------------------------------
Schema Name:test
SQL Id:afjq3us3nf5dt
SQL text:select Count (*) from test1 T1, test2 t2 where t1.object_id =
t2.object_id
-------------------------------------------------------------------------------
Findings section (1 finding)
-------------------------------------------------------------------------------
1-index finding (see Explain plans section below)
--------------------------------------------------
By creating one or more indexes, you can
Recommendation (estimated benefit:100%)
----------------------------------------
-Consider running Access advi that can improve the physical schema design
CREATE INDEX TEST. idx$$_0c890001 on TEST. TEST1 (' object_id ');

-Consider running Access advi that can improve the physical schema design
CREATE INDEX TEST. idx$$_0c890002 on TEST. TEST2 (' object_id ');

Rationale
---------
Creating a recommended index can significantly improve the execution plan for this statement. However, using a typical
may be preferable than a single statement. This approach allows for comprehensive indexing
-------------------------------------------------------------------------------
EXPLAIN Plans Section
-------------------------------------------------------------------------------
1-original
-----------
Plan Hash value:2544416891
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |   10 | 298 (1) | 00:00:04 |
|  1 |       SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |       HASH JOIN | |   50981 |   497k| 298 (1) | 00:00:04 |
|    3 | TABLE ACCESS full| TEST1 |   50982 |   248k| 149 (1) | 00:00:02 |
|    4 | TABLE ACCESS full| TEST2 |   50983 |   248k| 149 (1) | 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")

2-using New Indices
--------------------
Plan Hash value:3060659111
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------
| 0 |                SELECT STATEMENT |     |    1 |    10 | 53 (2) | 00:00:01 |
|  1 |                SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |                HASH JOIN | |   50981 |    497k| 53 (2) | 00:00:01 |
|    3 | INDEX FAST Full scan| idx$$_0c890001 |   50982 |    248k| 26 (0) | 00:00:01 |
|    4 | INDEX FAST Full scan| idx$$_0c890002 |   50983 |    248k| 26 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
-------------------------------------------------------------------------------

5. Verify

Tuning According to Advisor recommendations, 1410 to 204, it's a bit of a effect.

Sql>create index ind_t1_object_id on test1 (object_id);

Sql> CREATE index ind_t2_object_id on test2 (object_id);

Sql> Set Autotrace traceonly
Sql> Select COUNT (*) from test1 T1, test2 t2 where t1.object_id = t2.object_id;
Time used: 00:00:00.06
Execution plan
----------------------------------------------------------
Plan Hash value:1069114244
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 |                  SELECT STATEMENT |     |    1 |    10 | 51 (2) | 00:00:01 |
|  1 |                  SORT AGGREGATE |     |    1 |            10 |          | |
|* 2 |                  HASH JOIN | |   50981 |    497k| 51 (2) | 00:00:01 |
|    3 | INDEX FAST Full scan| ind_t1_object_id |   50982 |    248k| 25 (0) | 00:00:01 |
|    4 | INDEX FAST Full scan| ind_t2_object_id |   50983 |    248k| 25 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
Consistent gets
226 physical Reads
0 Redo Size
410 Bytes sent via sql*net to client
385 bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed


6. Delete the optimization task
sql> exec dbms_sqltune.drop_tuning_task (' tuning_sql_test ');

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.