- 浏览: 158630 次
- 性别:
- 来自: 南京
文章分类
最新评论
SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具,它的特点是简单、智能,DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。
使用STA一定要保证优化器是CBO模式下。
一、利用STA优化语句
对于问题语句的收集,可以通过Oracle10g的另一个自动化工具:数据库自动诊断监视工具(ADDM)。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。
我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的,STA是用起来很简单(只要你会调用存储过程,都能使用这个工具),三个步骤就可以完成一次语句调优。
测试环境创建:
使用Sql Tuning Adviser需要以下权限
Grant succeeded.
SQL> GRANT SELECT_CATALOG_ROLE TO u1;
Grant succeeded.
SQL> GRANT EXECUTE ON DBMS_SQLTUNE TO u1;
Grant succeeded.
SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
Table created.
SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;
Table created.
SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);
Table altered.
SQL> DECLARE
n NUMBER;
BEGIN
FOR n IN 1..100
LOOP
INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;
COMMIT;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:
SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
135000
Elapsed: 00:00:05.59
Execution Plan
----------------------------------------------------------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16013 consistent gets
14491 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出这个语句执行性能很差:16013 consistent gets。
第一步:创建优化任务并执行
通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:
SQL> set autot off
SQL> set timing off
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select count(*) from bigtab a, smalltab b where a.object_name=b.table_name';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'DEMO',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'tuning_sql_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
15 END;
16 /
PL/SQL procedure successfully completed.
在函数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='tuning_sql_
test';
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test COMPLETED
第二步:查看优化结果
通过函数可以查看优化结果。
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_SQL_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test
Tuning Task Owner : DEMO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11/30/2005 13:16:43
Completed at : 11/30/2005 13:16:44
Number of Index Findings : 1
Schema Name: DEMO
SQL ID : 6p64dnnsqf9pm
SQL Text : select count(*) from bigtab a, smalltab b where
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50001 on DEMO.SMALLTAB('TABLE_NAME');
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index DEMO.IDX$$_06C50002 on DEMO.BIGTAB('OBJECT_NAME');
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3089226980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 3550 (2)| 00:00:43 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 3550 (2)| 00:00:43 |
| 3 | TABLE ACCESS FULL| SMALLTAB | 1223 | 22014 | 11 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| BIGTAB | 1205K| 20M| 3526 (1)| 00:00:43 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2- Using New Indices
--------------------
Plan hash value: 494801882
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1108 (3)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5462K| 1108 (3)| 00:00:14 |
| 3 | INDEX FAST FULL SCAN| IDX$$_06C50001 | 1223 | 22014 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX$$_06C50002 | 1205K| 20M| 1093 (2)| 00:00:14 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
-------------------------------------------------------------------------------
看一下这个优化建议报告:
第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了问题描述:可以通过建立更多的所引来提高性能;然后是建议的具体内容:在表smalltab的字段table_name上创建索引,在表bigtab的字段object_name上创建索引;最后是相关注意事项:此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQL Access Advisor SAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。可以看出COST值大大下降。
第三步:按照优化建议进行优化
首先要说明一点的是,最好不要直接按照优化器给出的建议直接优化。因为像建索引这种操作影响可不是这一条语句。二是可以利用sql profile这对某条语句优化或者针对某些会话进行优化(下一章会给出sql profile如何使用)。我们这里只是验证一下优化建议的效果。
按照建议,创建两个索引:
SQL> create index smalltab_idx1 on smalltab(table_name);
Index created.
SQL> create index bigtab_idx1 on bigtab(object_name);
Index created.
SQL> analyze table smalltab compute statistics;
Table analyzed.
SQL> analyze table bigtab compute statistics;
Table analyzed.
SQL> set timing on
SQL> set autot on
SQL> select count(*) from bigtab a, smalltab b where a.object_name=b.table_name;
COUNT(*)
----------
135000
Elapsed: 00:00:01.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2594317117
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 1119 (3)| 00:00:14 |
| 1 | SORT AGGREGATE | | 1 | 36 | | |
|* 2 | HASH JOIN | | 155K| 5463K| 1119 (3)| 00:00:14 |
| 3 | INDEX FAST FULL SCAN| SMALLTAB_IDX1 | 1223 | 22014 | 3 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| BIGTAB_IDX1 | 1205K| 20M| 1104 (2)| 00:00:14 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")
Statistics
----------------------------------------------------------
332 recursive calls
0 db block gets
4999 consistent gets
1 physical reads
0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出,consistent gets比优化前大大下降了,优化建议确实提高了性能。
Oracle10g让优化变得如此简单。
二、利用sql profile存储优化策略
利用STA对语句进行优化后,STA会对语句进行分析,采用最优的优化策略,并给出优化后的查询计划。你可以按照STA给出的建议重写语句。但是,有些情况下,你可能无法重写语句(比如在生产环境中,你的语句又在一个包中)。这个时候就可以利用sql profile,将优化策略存储在profile中,Oracle在构建这条语句的查询计划时,就不会使用已有相关统计数据,而使用profile的策略,生成新的查询计划。
第一部分:profile的使用
SQL Profile对于一下类型语句有效:
SELECT语句;
UPDATE语句;
INSERT语句(仅当使用SELECT子句时有效);
DELETE语句;
CREATE语句(仅当使用SELECT子句时有效);
MERGE语句(仅当作UPDATE和INSERT操作时有效)。
另外,使用SQL Profile还必须有CREATE ANY SQL PROFILE、DROP ANY SQL PROFILE和ALTER ANY SQL PROFILE等系统权限。
还是举例说明吧:
第一步:给用户赋权限
SQL> conn sys/sys as sysdba
Connected.
SQL> GRANT CREATE ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> GRANT DROP ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> GRANT ALTER ANY SQL PROFILE TO DEMO;
Grant succeeded.
SQL> conn demo/demo
Connected.
SQL> create index smalltab_idx1 on smalltab(table_name);
Index created.
SQL> analyze table smalltab compute statistics;
Table analyzed.
SQL> set autot on
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2298554444
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| SMALLTAB | 1 | 18 | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------
第二步,创建、执行优化任务
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from
smalltab where table_name = ''TAB$''';
6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text => my_sqltext,
8 user_name => 'DEMO',
9 scope => 'COMPREHENSIVE',
10 time_limit => 60,
11 task_name => 'sql_profile_test',
12 description => 'Task to tune a query on a specified table');
13
14 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
15 END;
16 /
PL/SQL procedure successfully completed.
第三步:查看优化建议
SQL> set autot off
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_profile_test
Tuning Task Owner : DEMO
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 11/29/2005 14:52:09
Completed at : 11/29/2005 14:52:09
Number of SQL Profile Findings : 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
-------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Schema Name: DEMO
SQL ID : 3kta54ycuqccb
SQL Text : select /*+no_index(smalltab smalltab_idx1)*/count(*) from
smalltab where table_name = 'TAB$'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.94%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2298554444
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| SMALLTAB | 1 | 18 | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TABLE_NAME"='TAB$')
2- Using SQL Profile
--------------------
Plan hash value: 2664476518
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | INDEX RANGE SCAN| SMALLTAB_IDX1 | 1 | 18 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
----------------------------------------------------------------------------------------------------
2 - access("TABLE_NAME"='TAB$')
-------------------------------------------------------------------------------
这里可以看到,在优化建议中给出了新的查询计划。现在,我们决定接受这个建议,并且不重写语句。
第三步:接受profile
SQL> DECLARE
2 my_sqlprofile_name VARCHAR2(30);
3 BEGIN
4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
5 task_name => 'sql_profile_test',
6 name => 'my_sql_profile');
7 END;
8 /
PL/SQL procedure successfully completed.
在这里用了包DBMS_SQLTUNE的另一个函数:ACCEPT_SQL_PROFILE。其中,参数task_name即我们创建的优化建议任务的名称,name是profile的名字,可以是任意合法名称。此外这个函数还有其他一些函数,下面是这个函数的原型:
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL;
task_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE)
RETURN VARCHAR2;
Description是profile的描述信息;task_owner是优化建议任务的所有者;replace为TRUE时,如果这个profile已经存在,就代替它;force_match为TURE时,表示与语句强制匹配,即强制使用绑定变量,和系统参数cursor_sharing设置为FORCE时类似,为FALSE时,与cursor_sharing设置为EXACT时类似,即完全匹配。
这里要特别提到的是category这个参数,你可以通过设置这个参数,制定特定会话使用这个profile。在10g中,每个会话都有一个新参数SQLTUNE_CATEGORY,他的默认值是DEFAULT。而我们在调用这个函数时,如果没有指定这个参数,那它的值也是DEFAULT,而如果我们给这个profile指定了一个其它的CATEGORY值,如FOR_TUNING,那么只有会话参数SQLTUNE_CATEGORY也为FOR_TUNING时,才会使用这个porfile。为什么说这个参数很有用呢?试想一个这样的环境:你在一个生产系统上利用STA调优一条语句,STA已经给出了优化建议,但是你又不敢贸然实施它给出的建议(毕竟它只是机器嘛,不能完全信任),你就可以创建一个有特殊CATEGORY的profile,然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY,那就既可以看优化建议的实际效果又不影响生产环境。
此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。
第四步:查看profile的效果
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2664476518
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | INDEX RANGE SCAN| SMALLTAB_IDX1 | 1 | 18 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TAB$')
Note
-----
- SQL profile "my_sql_profile" used for this statement
可以看到,语句采用了profile中的数据,创建了新的查询计划。并且在查询计划中还有一些附加信息,表明这个语句是采用了’my_sql_profile’这个profile,而不是根据对象上面的统计数据来生成的查询计划。
第二部分:profile的控制
对profile的控制比较简单:修改和删除。包DBMS_SQLTUNE提供了两个存储过程来完成这两个操作:ALTER_SQL_PROFILE和DROP_SQL_PROFILE。
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
END;
/
1.Profile的修改
ALTER_SQL_PROFILE的原型是:
DBMS_SQLTUNE.ALTER_SQL_PROFILE (
name IN VARCHAR2,
attribute_name IN VARCHAR2,
value IN VARCHAR2);
其中,name就是profile的名字;attribute_name是需要修改的属性的名字;value是修改后的值。例如,需要使’my_sql_profile’失效,可以修改STATUS属性为DISABLED:
SQL> BEGIN
2 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
3 name => 'my_sql_profile',
4 attribute_name => 'STATUS',
5 value => 'DISABLED');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> set autot on exp
SQL> select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where ta
ble_name = 'TAB$';
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 2298554444
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| SMALLTAB | 1 | 18 | 11 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TABLE_NAME"='TAB$')
2.删除profile
DROP_SQL_PROFILE的原型是:
DBMS_SQLTUNE.DROP_SQL_PROFILE (
name IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
其中,name是profile的名字,ignore为TRUE时,当指定的profile不存在时不报错。
3.确认某条语句是否已经有相应profile
当然我们也希望能确认某条语句是否已经形成了profile,看是否有必要在对它进行tuning。这时就可以利用SQLTEXT_TO_SIGNATURE函数:
SQL> set serveroutput on
SQL> declare
2 v_signature number;
3 begin
4 v_signature:=DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (
5 sql_text => 'select /*+no_index(smalltab smalltab_idx1)*/count(*) from smalltab where table_name = ''TAB$''',
6 force_match => FALSE);
7 if v_signature is null then
8 DBMS_OUTPUT.put_line('no such sql text in profile');
9 else
10 DBMS_OUTPUT.put_line('The sql text is in profile');
11 end if;
12 end;
13 /
The sql text is in profile
PL/SQL procedure successfully completed.
其中,sql_text就是需要检测的内容;force_match的解释与ACCEPT_SQL_PROFILE中相应参数是一样的。
第三部分:profile的转储与移植
在某些环境下,比如生产环境的安全要求非常高,无法直接对生产环境进行优化,只能在一个与生产环境保持一致的镜像环境中作优化,那么,希望将优化结果实施到生产环境中去,该怎么办呢?DBMS_SQLTUNE包提供了另外几个函数用于将profile的数据导出到表中,然后可以再将表中的数据移植到其它环境中,下面介绍一下如何使用它们。
第一步:创建存储表
先利用存储过程创建一张存储profile的表:
SQL> begin
2 DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
3 table_name => 'PROFILE_STGTAB',
4 schema_name => 'DEMO',
5 tablespace_name => 'EDGARDEMO');
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> desc PROFILE_STGTAB
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
PROFILE_NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
SIGNATURE NUMBER
SQL_TEXT CLOB
DESCRIPTION VARCHAR2(500)
TYPE VARCHAR2(9)
STATUS VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES SQLPROF_ATTR
VERSION NUMBER
SPARE1 CLOB
SPARE2 BLOB
Table_name是需要创建的存储表的名字,schema_name是它所属schema的名字,tablespace_name是所属表空间。
此外,需要注意的是,这个存储过程实际上做的是create table操作,也就是DDL操作,所以不要在一个事务中调用它。使用这个存储过程需要有CREATE ANY TABLE的权限。
第二步:将profile中数据存入存储表中
利用存储过程可以将profile中数据存储刚才建的那张存储表中:
SQL> BEGIN
2 DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
3 profile_name => '%',
4 profile_category => 'DEFAULT',
5 staging_table_name => 'PROFILE_STGTAB',
6 staging_schema_owner => 'DEMO');
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> select profile_name, category, sql_text from PROFILE_STGTAB;
PROFILE_NAME CATEGORY SQL_TEXT
------------------------------ ---------------------------------------------------------------------------------------
my_sql_profile DEFAULT select /*+no_index(smalltab smalltab_idx1)*/count(*)
frommalltab where table_name = 'TAB$'
profile_name是需要存储的profile的名字(大小写敏感),默认为’%’,即这个CATEGORY下的所有profile;profile_category即需要存储的profile所在category名字(大小写敏感),默认是DEFAULT;staging_table_name就是用于存储profile数据的表名(大小写敏感);staging_schema_owner是该表所属的schema。调用该函数需要有的CREATE ANY SQL PROFILE系统权限,并且对存储表要有SELECT权限。
要注意一点:调用了这个存储过程,会执行COMMIT,所以要注意对前面事务的影响。
另外,可以通过存储过程来修改存储表中的信息:
SQL> begin
2 DBMS_SQLTUNE.REMAP_STGTAB_SQLPROF (
3 old_profile_name => 'my_sql_profile',
4 new_profile_name => 'new_sql_profile',
5 new_profile_category => 'DEV',
6 staging_table_name => 'PROFILE_STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /
PL/SQL procedure successfully completed.
Old_profile_name是需要修改的存储在存储表中的profile的名字(大小写敏感);new_profile_name是需要修改为的新名字,默认是NULL,既不修改名字;new_profile_category是需要修改为的新目录名字,默认是NULL,既不修改;staging_table_name是需要修改的存储表的名字(大小写敏感);staging_schema_owner是存储表所属的schema。
当然,调用这个函数需要有对存储表的UPDATE权限。
第三步:从存储表中导入profile数据
将profile中的数据导入到存储表中后,我们就可以将存储表中的数据导到其他数据库中去了:
SQL> create table STGTAB as select * from PROFILE_STGTAB@EDGAR;
Table created.
然后将数据导出为profile:
SQL> begin
2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
3 profile_name => 'new_sql_profile',
4 profile_category => 'DEV',
5 replace => FALSE,
6 staging_table_name => 'STGTAB',
7 staging_schema_owner => 'DEMO');
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select name, category, sql_text from DBA_SQL_PROFILES
2 ;
NAME CATEGORY SQL_TEXT
------------------------------ ------------------------------ ----------------------------------------------------
new_sql_profile DEV select /*+no_index(smalltab smalltab_idx1)*/count(*)
from smalltab where table_name = 'TAB$'
可以看到,profile已经被成功导入。调用该存储过程需要有的CREATE ANY SQL PROFILE系统权限。参数的含义与前面的函数是一样的。
补充:存储表中的数据的删除和存储表的删除。
实际上,存储表数据的删除和存储表的删除和普通表的操作是一样的,使用DELETE和DROP就可以了:
SQL> delete from STGTAB;
1 row deleted.
SQL> drop table STGTAB;
Table dropped.
三、总结
正如文章开始提到的,这个工具让语句调优工作变得非常简单,DBA可以用最短的时间、最好的方式给出优化建议,并有最安全的方式来调试优化结果。
此外,STA还有一套对于数据仓库环境下调忧十分有用的工具:SQL Tuning Set。
相关推荐
文档为oracle调优的STA无脑命令,直接安装命令贴到sqlplus里面就好,sql_id号从awr报告里面获得,想调优的语句找到sql_id,替换到文档里面的sql_id位置,最后无脑贴进去就可以使用STA调优助手了。
Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短...
Step7语句表监控时RLO,STA,STANDARD的含义
oracle数据库sql turning advise(sta)的使用简单案例
Oracle 1z0-051
这是一种改进的状态转换算法 ...近年来,状态转换算法(STA)已成为一种新颖的全局优化元启发式方法。在以前的版本中,连续 STA 中变换算子的参数保持不变或周期性地减小。在这个改进版本中,考虑了STA的最优参数选择。
STA1052 CD Servo Controller
openwrt总结(设置sta)
STA1052ROM3.1版本CD\MP3\WMA全功能
STA客户跨线程调用STA对象,使用了marshal,消息循环。
STA1050 CD Servo Controller
STA508 is a monolithic quad half bridge stage in Multipower BCD technology. The device can be used as dual bridge or reconfigured, by connecting CONFIG pin to Vdd pin, as single bridge with double ...
实现了一个STA组件, 然后里面有STA客户调用和MTA客户调用。
sta-1040 sta sta
Android8.0平台实现双wifi模块STA+AP模式
openwrt配置WiFi的AP+STA中继模式的关键步骤,内包含相关配置文件,以及uci2dat源码
很有用的音频放大集成块 STA328 很难找到的
RT2870STA.dat
RT3070 STA linux driver
北京STA软件测试技术研讨会 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软 主要包括C# ASP.NET SQLDBA 源码 毕业设计 开题报告 答辩PPT等等好多知识