一 创建测试数据
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');
