Oracle sqlprofile 偷梁换柱

最新推荐文章于 2023-06-25 19:38:20 发布

cjggw28026

最新推荐文章于 2023-06-25 19:38:20 发布

阅读量472

点赞数

文章标签：数据库

注：测试环境 win7+oracle10.2.0.4

基础知识：

STA—sql tuning advisor；

使用sqlprofile 时需要创建 sql tuning task，也就是说要先搞清楚 sta 的使用情况，下面来看看sta 的使用

Sta 的使用可以通过em完成，也可以通过oracle db中的 dbms_sqltue完成；我比较习惯后者；dbms_sqltune的使用主要分为两步：

—创建sql tuning task

—执行 sql tuning task

来看一个简单的实例：

SQL> desc emp 名称是否为空? 类型 —————————————————– ——– EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)SQL> show userUSER 为 “SCOTT”SQL> DECLARE 2 huosi_task1 VARCHAR2(30); 3 huosi_txt1 CLOB; 4 BEGIN 5 huosi_txt1 := ‘SELECT * ‘ || 6 ‘FROM EMP ‘ || ‘WHERE EMPNO=7369′ ; 7 huosi_task1 := DBMS_SQLTUNE.CREATE_TUNING_TASK( 8 sql_text => huosi_txt1, 9 user_name => ‘SCOTT’, 10 scope => ‘COMPREHENSIVE’, 11 time_limit => 60, 12 task_name => ‘test_task1′, 13 description => ‘first test task’); 14 END; 15 /PL/SQL 过程已成功完成。SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => ‘test_task1′);PL/SQL 过程已成功完成。SQL> select status from dba_advisor_log where task_name=’test_task1′;STATUS———————-COMPLETEDSQL> set long 65536SQL> set longchunksize 65536SQL> set linesize 100SQL> select dbms_sqltune.report_tuning_task(‘test_task1′) from dual 2 ;SQL>

注意此时没有返回数据的原因是，sqlplus 不显示lob数据，可以plsql查看，如图：

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name : test_task1Tuning Task Owner : SCOTTScope : COMPREHENSIVETime Limit(seconds) : 60Completion Status : COMPLETEDStarted at : 05/29/2012 10:35:39Completed at : 05/29/2012 10:35:40Number of Statistic Findings : 1——————————————————————————-Schema Name: SCOTTSQL ID : 31qp81kj64a38SQL Text : SELECT * FROM EMP WHERE EMPNO=7369——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- Statistics Finding——————— 表 “SCOTT”.”EMP” 及其索引的优化程序统计信息已失效。 Recommendation ————– – 考虑收集此表的优化程序统计信息。 execute dbms_stats.gather_table_stats(ownname => ‘SCOTT’, tabname => ‘EMP’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE AUTO’); Rationale ——— 为了选择好的执行计划, 优化程序需要此表的最新统计信息。——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original———–Plan hash value: 2949544139————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————–| 0 | SELECT STATEMENT | | 1 | 32 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |————————————————————————————–Predicate Information (identified by operation id):————————————————— 2 – access(“EMPNO”=7369)——————————————————————————-

到这里 STA 的基本使用就完成了；

Sql profile :

Sql profile 是10g 的一个新特性，可以通过em 也可以通过dbms_sqltune进行管理；

Sql profile 本身是存储在数据字典中的信息的一个集合，这些信息可以被cob使用，以使优化器能够生成一个更优化的执行计划；profile中的信息能够改善优化器中的cardinality和selectivity，从而使优化器能够选择更好的执行计划；

当时sql profile 并不包括单个的执行计划的信息，当优化器选择执行计划时它本身会包含以下的信息：

The environment, which contains the database configuration, bind variable values, optimizer statistics, data set, and so on
The supplemental statistics in the SQL profile

Sql profile 本身并不会像 outline一样固定一个sql 的执行计划，比如当数据量发生变化时执行计划可能就会变化；能够应用sql profile 的语句包括：

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

在10g当中，oracle CBO的功能进一步加强，也就是说oracle 的优化器会根据系统的统计信息、sqlprofile等来自动选择执行计划，比如在某些情况下优化器会跳过hint；

下面来看看 sqlprofile是如何偷梁换柱，避开hint 的；

SQL> show userUSER 为 “SCOTT”SQL> create table test_sp(n number);表已创建。SQL> declare 2 begin 3 for i in 1 .. 10000 loop 4 insert into test_sp values(i); 5 commit; 6 end loop; 7 end; 8 /PL/SQL 过程已成功完成。

SQL> create index test_idx on test_sp(n);索引已创建。SQL> exec dbms_stats.gather_table_stats(”,’TEST_SP’);PL/SQL 过程已成功完成。SQL> set autotrace onSQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1 2 ; N———- 1执行计划———————————————————-Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=1)统计信息———————————————————- 1 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此时可以看出 sql是进行的全表扫描；

重新开启一个会话：

SQL> declare 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 begin 5 my_sqltext := ‘select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1′; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text => my_sqltext, 8 user_name => ‘SCOTT’, 9 scope => ‘COMPREHENSIVE’, 10 time_limit => 60, 11 task_name => ‘my_sql_tuning_task_3′, 12 description => ‘Task to tune a query on a specified table’); 13 end; 14 /PL/SQL 过程已成功完成。SQL> begin 2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_3′); 3 end; 4 /PL/SQL 过程已成功完成。SQL>

看看 sqlprofile的信息；

GENERAL INFORMATION SECTION——————————————————————————-Tuning Task Name : my_sql_tuning_task_3Tuning Task Owner : SYSScope : COMPREHENSIVETime Limit(seconds) : 60Completion Status : COMPLETEDStarted at : 05/29/2012 11:32:13Completed at : 05/29/2012 11:32:13Number of SQL Profile Findings : 1——————————————————————————-Schema Name: SCOTTSQL ID : 1ks8q8x9ttbbySQL Text : select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1

——————————————————————————-FINDINGS SECTION (1 finding)——————————————————————————-1- SQL Profile Finding (see explain plans section below)——————————————————– 为此语句找到了性能更好的执行计划。 Recommendation (estimated benefit: 86.4%) —————————————– – 考虑接受推荐的 SQL 概要文件。 execute dbms_sqltune.accept_sql_profile(task_name => ‘my_sql_tuning_task_3′, replace => TRUE);——————————————————————————-EXPLAIN PLANS SECTION——————————————————————————-1- Original With Adjusted Cost——————————Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–

Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=1)2- Using SQL Profile——————–Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=1)——————————————————————————-

SQL> DECLARE2 my_sqlprofile_name VARCHAR2(30);

3 begin

4 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

5 task_name => ‘my_sql_tuning_task_3′,

6 name => ‘my_sql_profile’);

7 end;

8 /

PL/SQL 过程已成功完成。

SQL>

此时再次执行查询语句：

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=1;

N———- 1执行计划———————————————————-Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=1)Note—– – SQL profile “my_sql_profile” used for this statement统计信息———————————————————- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此时sql的执行已经跳开了hint，开始使用索引，也就是说现在优化器选择一个比hint更优的执行计划

此时再次修改下 sql：

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2;

N———- 2执行计划———————————————————-Plan hash value: 3988747878—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 7 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_SP | 1 | 3 | 7 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – filter(“N”=2)统计信息———————————————————- 1 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

可以发现，此时sql执行的仍旧为全表扫描，仅仅是将1变成了2，sqlprofile 就失去作用；

那么对于此种literal sql 是否可以使用相同的sql profile呢，答案是肯定的，下面演示下：

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task_3′, replace => TRUE, force_match=>true);PL/SQL 过程已成功完成。

此时sql profile将启用force match，其作用类型crsor_sharing 的force设置；

此时再次执行sql（scott）：

SQL> select /*+ no_index(test_sp test_idx) */ * from test_sp where n=2; N———- 2执行计划———————————————————-Plan hash value: 2882402178—————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————–| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 |—————————————————————————–Predicate Information (identified by operation id):————————————————— 1 – access(“N”=2)Note—– – SQL profile “SYS_SQLPROF_014de5abcc24c000″ used for this statement统计信息———————————————————- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL>

此时可以看出sql 已经开始使用索引；

通过以上的简单例子可以发现对sql profile可以帮助用来更好的优化sql，所以掌握sql profile 是非常有必要的；

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25656398/viewspace-731367/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25656398/viewspace-731367/