这篇文章将为大家详细讲解有关STA的使用分析,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。
利用Oracle 10g提供的SQL Tuning Advisor,DBA可以很容易的来根据STA给出的意见和建议来进行SQL的优化,一切终于变的如此的简单了。
SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a,smalltab b
2 where a.object_name=b.table_name;
COUNT(*)
———-
191597
已用时间: 00: 00: 27.53
执行计划
———————————————————-
Plan hash value: 3089226980
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 83 | 16418 (3)| 00:03:18 |
| 1 | SORT AGGREGATE | | 1 | 83 | | |
|* 2 | HASH JOIN | | 1928K| 152M| 16418 (3)| 00:03:18 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1594 | 27098 | 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 5465K| 343M| 16311 (2)| 00:03:16 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access("A"."OBJECT_NAME"="B"."TABLE_NAME")
Note
—–
– dynamic sampling used for this statement
统计信息
———————————————————-
446 recursive calls
0 db block gets
73581 consistent gets
73386 physical reads
0 redo size
414 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
现在我们可以创建并执行我们的SQL tuning task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SYS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'oracle_ace_task',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'oracle_ace_task');
END;
或者我们可以利用一个procedure来生成
create or replace procedure sql_tuning(my_sqltext in clob,schema_name in varchar2)
is
my_task_name varchar2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => schema_name,
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'oracle_ace_task',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'oracle_ace_task');
end;
exec sql_tuning('select count(*) from bigtab a, smalltab b where a.object_name=b.table_name','SYS');
在函数CREATE_TUNING_TASK,sql_text是需要优化的语句,user_name是该语句通过哪个用户执行,scope是优化范围(limited或comprehensive),time_limit优化过程的时间限制,task_name优化任务名称,description优化任务描述。
可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。
SQL> select task_name, status from USER_ADVISOR_LOG where task_name='oracle_ace_task';
TASK_NAME STATUS
—————————— ———–
oracle_ace_task COMPLETED
最后我们来生成tuning的报告.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
/
PL/SQL procedure successfully completed.
关于STA的使用分析就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/199856.html