利用Oracle 10g SQL优化器(STA)优化语句
更新时间:2023-10-16 00:09:01 阅读量: 综合文库 文档下载
SQL优化器(SQL Tuning Advisor STA)是Oracle10g中推出的帮助DBA优化工具,
它的特点是简单、智能,DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。下面介绍一下它的使用。
使用STA一定要保证优化器是CBO模式下。
一、利用STA优化语句
对于问题语句的收集,可以通过Oracle10g的另一个自动化工具:数据库自动诊断
监视工具(ADDM)。它的使用可以参照我的另一篇文章《Oracle10g数据库自动诊断监视工具(ADDM)使用指南》。
我们下面简单介绍一下如何优化一条找到的问题语句。正如前面所述说的,STA是
用起来很简单(只要你会调用存储过程,都能使用这个工具),三个步骤就可以完成一次语句调优。
执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限: SYS@ning>grant advisor to ning; Grant succeeded.
使用DBMS_SQLTUNE包进行SQL优化,大致可以分为四个步骤: 创建优化任务 ? 执行优化任务
? 显示优化任务的结果 ? 按照建议执行优化
?
测试环境创建:
SQL> CREATE TABLE bigtab AS SELECT rownum as \
Table created.
SQL> create table smalltab as select rownum as \
Table created.
SQL> ALTER TABLE bigtab MODIFY (empno NUMBER);
Table altered.
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 FOR n IN 1..100
5 LOOP
6 INSERT INTO bigtab SELECT rownum as \
7 COMMIT;
8 END LOOP;
9 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(\ 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 \ 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(\
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(\
-------------------------------------------------------------------------------
看一下这个优化建议报告:
第一部分是关于这次优化任务的基本信息:如任务名称、执行时间、范围、涉及到
的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。前面先给出了
问题描述:可以通过建立更多的所引来提高性能;然后是建议的具体内容:在表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(\ 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(\
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(\
-------------------------------------------------------------------------------
这里可以看到,在优化建议中给出了新的查询计划。现在,我们决定接受这个建议,
并且不重写语句。
第三步:接受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(\ Note -----
- SQL profile \
可以看到,语句采用了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(\
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。我
们将来单独用一篇文章介绍它。
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 10g SQL优化器(STA)优化语句10-16
仙女棒的制作 - 图文09-17
条件求和的N种方式05-21
中国海洋大学 考研 期末 20061计算机系统结构B李海涛 - 图文11-03
【2018最新】医生与病人的对话-范文word版 (4页)05-05
情侣协议书12-13
榆中县纪检监察机关与司法机关、行政执法机关案件移送规定12-03
Scott Mitchell 的ASP.NET 2.0数据教程之二十四.分页和排序报表数据05-25
拌和站信息化考题A卷09-30
- 小学生造句大全
- 增压泵投资项目可行性研究报告(模板)
- 高中语文人教版粤教版必修1-5全部文言文知识点归纳
- 两学一做专题民主生活会组织生活会批评与自我批评环节个人发言提
- 管理处环境保洁工作操作标准作业指导书
- 2012六一儿童节活动议程 - 图文
- 移树申请报告
- 《贵州省市政工程计价定额》2016定额说明及计算规则
- 计算机长期没有向WSUS报告状态
- 汉语拼音教学策略研究
- 发展西部领先的航空货运枢纽
- 司法所上半年工作总结4篇
- 如何提高银行服务水平
- 发电厂各级人员岗位职责
- 丰田汽车的外部环境分析
- 2017—2018年最新冀教版四年级数学下册《混合运算》教案精品优质
- 中建八局样板策划 - 图文
- 戚安邦《项目管理学》电子书
- 2015年高级项目经理笔记
- 弯桥的设计要点
- 优化
- 语句
- 利用
- Oracle
- 10g
- SQL
- STA