What Does Oracle SQLTuningAdvisor (STA) do?

SQLTuingAdvisor (STA) is part of AutomaticTuningOptimizer. In the previous article, use SQLtuningadvisor (STA) to automatically

SQL Tuing Advisor (STA) is part of the automation Tuning Optimizer. In the previous article, use SQL tuning advisor (STA) to automatically

SQL Tuing Advisor (STA) is part of the automation Tuning Optimizer. In the previous article, I used SQL tuning advisor (STA) to automatically optimize SQL statements to describe the background of SQL Tuing Advisor (STA) and provide examples. This article mainly describes what the bottom layer of the STA has done for us to optimize SQL statements. It also shows how to accept the SQL profile when binding variables, whether the SQL statement follows the execution plan of the corresponding SQL profile. Finally, the SQL script in awr is provided through STA tuning.

1. Use STA to optimize the SQL statements in the library cache

-- Demo Environment
Hr @ CNMMBO> select * from v $ version where rownum <2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0-64bit Production

-- The SQL statement in library cache is optimized based on SQL _id.
Hr @ CNMMBO> @ tune_cache_ SQL
Enter value for input_ SQL _id: 8rnmr2dpnjvk8
Enter value for input_task_name: hr_query

RECS
Bytes ---------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name: hr_query
Tuning Task Owner: HR
Scope: COMPREHENSIVE
Time Limit (seconds): 1800
Completion Status: COMPLETED
Started at: 11:40:27, 06/07/2013
Completed at: 11:40:28, 06/07/2013
Number of SQL Profile Findings: 1
Number of SQL Restructure Findings: 1

-------------------------------------------------------------------------------
Schema Name: HR
SQL ID: 8rnmr2dpnjvk8
SQL Text: SELECT/* + ORDERED */
*
FROM employees e, locations l, departments d
WHERE e. department_id = d. department_id AND l. location_id =
D. location_id AND e. employee_id <: bnd

-------------------------------------------------------------------------------
Findings section (2 findings)
-------------------------------------------------------------------------------

1-SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 90.74%)
------------------------------------------
-Consider accepting the recommended SQL profile.
Execute dbms_sqltune.accept_ SQL _profile (task_name => 'hr _ query', replace
=> TRUE );

2-Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 3 of
Execution plan.

Recommendation
--------------
-Consider removing the "ORDERED" hint.

Rationale
---------
The "ORDERED" hint might force the optimizer to generate a cartesian
Product. A cartesian product shocould be avoided whenever possible because
It is an expensive operation and might produce a large amount of data.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1-Original With Adjusted Cost
------------------------------
Plan hash value: 3871948714

Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 85 | 11645 | 103 (1) | 00:00:02 |
| * 1 | hash join | 85 | 11645 | 103 (1) | 00:00:02 |
| 2 | table access full | ments | 27 | 540 | 3 (0) | 00:00:01 |
| 3 | merge join cartesian | 1973 | 225K | 99 (0) | 00:00:02 |
| 4 | table access by index rowid | EMPLOYEES | 86 | 5848 | 3 (0) | 00:00:01 |
| * 5 | index range scan | EMP_EMP_ID_PK | 86 | 1 (0) | 00:00:01 |
| 6 | buffer sort | 23 | 1127 | 96 (0) | 00:00:02 |
| 7 | table access full | LOCATIONS | 23 | 1127 | 1 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------