一 创建测试数据
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
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 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')
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 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');