导航：首页 > 数据库 >

oracle SQL优化器SQL Tuning Advisor （STA)

发表于：2025-01-21 作者：千家信息网编辑

千家信息网最后更新 2025年01月21日，一创建测试数据SQL> conn scott/oracleConnected.SQL> create table obj as select * from dba_objects;Table cre

千家信息网最后更新 2025年01月21日oracle SQL优化器SQL Tuning Advisor （STA)

一创建测试数据

SQL> conn scott/oracle

Connected.

SQL> create table obj as select * from dba_objects;

Table created.

SQL> create table ind as select * from dba_indexes;

Table created.

SQL> insert into obj select * from obj;

86965 rows created.

SQL> insert into obj select * from obj;

173930 rows created.

SQL> insert into obj select * from obj;

347860 rows created.

SQL> insert into obj select * from obj;

695720 rows created.

SQL> commit

2 ;

Commit complete.

SQL> insert into ind select * from ind;

5069 rows created.

SQL> insert into ind select * from ind;

10138 rows created.

SQL> insert into ind select * from ind;

20276 rows created.

SQL> commit;

Commit complete.

2.然后对这两个表，obj与ind进行联合查询，并通过autotrace查看其执行计划：

SQL> set timing on

SQL> set autot trace

SQL> set line 160

SQL> select count(*) from obj o, ind i where o.object_name=i.index_name;

Elapsed: 00:00:00.23

Execution Plan

----------------------------------------------------------

Plan hash value: 380737209

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 83 | | 11272 (1)| 00:02:16 |

| 1 | SORT AGGREGATE | | 1 | 83 | | | |

|* 2 | HASH JOIN | | 13M| 1086M| 1416K| 11272 (1)| 00:02:16 |

| 3 | TABLE ACCESS FULL| IND | 49775 | 826K| | 378 (0)| 00:00:05 |

| 4 | TABLE ACCESS FULL| OBJ | 1456K| 91M| | 5413 (1)| 00:01:05 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

21308 consistent gets

0 physical reads

0 redo size

528 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

通过执行计划，可以清晰的看到，在执行以上两个表的联合查询的时候，两张表走的全表扫和hash join。

3 查看该sql语句的sql_id

SQL> set autot off

SQL> set timing off

SQL> set line 160

SQL>col sql_text for a65

select sql_id, sql_text, optimizer_mode, plan_hash_value, child_number from v$sql where sql_text like 'select count(*) from obj o, ind i where o.object_name=i.index_name%';SQL> SQL>

SQL_ID SQL_TEXT OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER

------------- ------------------------------------------------------- ---------- --------------- ------------

8xwgyq8mkv86x select count(*) from obj o, ind i where o.object_name=i ALL_ROWS 380737209 0

.index_name

二使用SQL Tuning Advisor （STA)进行优化
1 创建优化任务
通过调用函数DBMS_SQLTUNE.CREATE_TUNING_TASK来创建优化任务，

调用存储过程DBMS_SQLTUNE.EXECUTE_TUNING_TASK执行该任务：

SQL> DECLARE

a_tuning_task VARCHAR2(30);

BEGIN

a_tuning_task := dbms_sqltune.create_tuning_task(sql_id => ' 8xwgyq8mkv86x',

task_name => ' sql_profile_test_SQLID');

dbms_sqltune.execute_tuning_task(a_tuning_task);

END;

PL/SQL procedure successfully completed.

2 执行优化任务

SQL> conn / as sysdba

Connected.

SQL> grant advisor to scott;

Grant succeeded.

SQL> conn scott/oracle

Connected.

SQL> exec dbms_sqltune.execute_tuning_task('sql_profile_test_SQLID');

PL/SQL procedure successfully completed.

3 检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态， COMPLETED表示完成

SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='sql_profile_test_SQLID';

TASK_NAME STATUS

------------------------------ -----------

sql_profile_test_SQLID COMPLETED

4 查看优化结果

set long 999999

set serveroutput on size 999999

set line 160

select DBMS_SQLTUNE.REPORT_TUNING_TASK(' sql_profile_test_SQLID') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name : sql_profile_test_SQLID

Tuning Task Owner : SCOTT

Workload Type : Single SQL Statement

Execution Count : 2

Current Execution : EXEC_314

Execution Type : TUNE SQL

Scope : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

Started at : 04/12/2020 18:23:49

Completed at : 04/12/2020 18:23:49

-------------------------------------------------------------------------------

Schema Name: SCOTT

SQL ID : 8xwgyq8mkv86x

SQL Text : select count(*) from obj o, ind i where

o.object_name=i.index_name

-------------------------------------------------------------------------------

FINDINGS SECTION (2 findings)

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

1- Statistics Finding

---------------------

Table "SCOTT"."IND" was not analyzed.

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,

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

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- Statistics Finding

---------------------

Table "SCOTT"."OBJ" was not analyzed.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

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

---------

The optimizer requires up-to-date statistics for the table in order to

select a good execution plan.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

EXPLAIN PLANS SECTION

-------------------------------------------------------------------------------

1- Original

-----------

Plan hash value: 380737209

--------------------------------------------------------------------------------

----

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

----

| 0 | SELECT STATEMENT | | 1 | 83 | | 11272 (1)| 00:02:

16 |

| 1 | SORT AGGREGATE | | 1 | 83 | | |

|* 2 | HASH JOIN | | 13M| 1086M| 1416K| 11272 (1)| 00:02:

16 |

| 3 | TABLE ACCESS FULL| IND | 49775 | 826K| | 378 (0)| 00:00:

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

05 |

| 4 | TABLE ACCESS FULL| OBJ | 1456K| 91M| | 5413 (1)| 00:01:

05 |

--------------------------------------------------------------------------------

----

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("O"."OBJECT_NAME"="I"."INDEX_NAME")

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST_SQLID')

--------------------------------------------------------------------------------

-------------------------------------------------------------------------------

执行优化建议

SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'OBJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>'IND', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

5 删除优化任务

通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务，可以释放资源。

SQL>exec dbms_sqltune.drop_tuning_task('sql_profile_test_SQLID');

很赞哦！