[20131122]跟踪sql profile的操作.txt

[20131122]跟踪sql profile的操作.txt

sql profile是11G的新特性,前几天我在给别人做优化时,偷懒直接使用toad,step by step分析使用sql profile,导致系统执行缓慢,不得不中断分析.今天有空跟踪sql profile的操作看看,了解一些过程.

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

1.建立测试环境:
create table t pctfree 99 as select rownum id,lpad('x',1000,'x')  name from dual connect by level
create index i_t_id on t(id) ;
alter table t modify(id NOT NULL);
exec dbms_stats.gather_table_stats(user, 'T',  no_invalidate => false);

2.建立测试例子:

SCOTT@test> select /*+ full(t) */ count(*) from t;
  COUNT(*)
----------
      1000

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cdwnbcfy42f8b, child number 0
-------------------------------------
select /*+ full(t) */ count(*) from t
Plan hash value: 2966233522
---------------------------------------------------------
| Id  | Operation          | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |   279 (100)|
|   1 |  SORT AGGREGATE    |      |      1 |            |
|   2 |   TABLE ACCESS FULL| T    |   1000 |   279   (0)|
---------------------------------------------------------

--由于使用了提示执行计划选择全表扫描.现在使用sql profile来优化这条语句.

3.使用与跟踪sql profile:

@10046on 12

DECLARE
  ret_val VARCHAR2(4000);
BEGIN
  ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                sql_id          => 'cdwnbcfy42f8b',
                plan_hash_value => NULL,
                scope       => 'COMPREHENSIVE',
                time_limit  => 1800,
                task_name   => 'TASK1',
                description => 'test');
  --:rv := ret_val;
END;
/

Begin
  Dbms_Sqltune.EXECUTE_TUNING_TASK('TASK1');
End;
/
@10046off

--select Dbms_Sqltune.REPORT_TUNING_TASK('TASK1', 'TEXT', 'TYPICAL') report from dual;
--execute dbms_sqltune.accept_sql_profile(task_name => 'TASK1', replace => TRUE);

4.分析跟踪文件:
--$ ./trimsql.sh  /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_26538_127_0_0_1.trc > /tmp/task1.txt
$ tkprof  /u01/app/oracle11g/diag/rdbms/test/test/trace/test_ora_27568_127_0_0_1.trc
....
********************************************************************************

SQL ID: 4jh8pskb3zgu0 Plan Hash: 2966233522

select /*+ full(t) */ count(*)
from
 t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.02       0.02          0      10040          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.02       0.02          0      10040          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
********************************************************************************

SQL ID: 4jh8pskb3zgu0 Plan Hash: 3548397654

select /*+ full(t) */ count(*)
from
 t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         60          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0         60          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
********************************************************************************

SQL ID: 4jh8pskb3zgu0 Plan Hash: 4021579484

select /*+ full(t) */ count(*)
from
 t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         40          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.00          0         40          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
********************************************************************************

--从这里分析可以发现每种不同的执行计划,执行10次,如果存在很慢的执行方式,在生产系统执行,简直就是灾难!
--查询sql_id='4jh8pskb3zgu0' 看看执行计划:

SCOTT@test> @dpc 4jh8pskb3zgu0 ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  4jh8pskb3zgu0, child number 0
-------------------------------------
/* SQL Analyze(68,0) */ select /*+ full(t) */ count(*) from t

Plan hash value: 2966233522

---------------------------------------------------------
| Id  | Operation          | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |   279 (100)|
|   1 |  SORT AGGREGATE    |      |      1 |            |
|   2 |   TABLE ACCESS FULL| T    |   1000 |   279   (0)|
---------------------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID  4jh8pskb3zgu0, child number 1
-------------------------------------
/* SQL Analyze(68,0) */ select /*+ full(t) */ count(*) from t

Plan hash value: 3548397654

--------------------------------------------------------------
| Id  | Operation             | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |        |     3 (100)|
|   1 |  SORT AGGREGATE       |        |      1 |            |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |   1000 |     3   (0)|
--------------------------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID  4jh8pskb3zgu0, child number 2
-------------------------------------
/* SQL Analyze(68,0) */ select /*+ full(t) */ count(*) from t

Plan hash value: 4021579484

----------------------------------------------------
| Id  | Operation        | Name   | E-Rows | Cost  |
----------------------------------------------------
|   0 | SELECT STATEMENT |        |        |     4 |
|   1 |  SORT AGGREGATE  |        |      1 |       |
|   2 |   INDEX FULL SCAN| I_T_ID |   1000 |     4 |
----------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

61 rows selected.

--可以发现oracle选择3中方式:1.全表扫描 2.INDEX FAST FULL SCAN 3. INDEX FULL SCAN.
--实际上还要检查统计信息是否stale,还有stale还有分析表等情况,如果这些表很大,运行更慢,以后要在具体优化过程中注意.

5.查看分析报告:
SCOTT@test> select Dbms_Sqltune.REPORT_TUNING_TASK('TASK1', 'TEXT', 'TYPICAL') report from dual;
REPORT
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK1
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/22/2013 10:01:22
Completed at       : 11/22/2013 10:01:23

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : cdwnbcfy42f8b
SQL Text   : select /*+ full(t) */ count(*) from t

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.4%)
  -----------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK1', task_owner
            => 'SCOTT', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):              .00276           .000155      94.38 %
  CPU Time (s):                 .002799             .0001      96.42 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1004                 6       99.4 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   279   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000 |   279   (0)| 00:00:04 |
-------------------------------------------------------------------

2- Using SQL Profile
--------------------
Plan hash value: 3548397654

------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |  1000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------

--建议执行如下:
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK1', task_owner=> 'SCOTT', replace => TRUE);

SCOTT@test> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK1', task_owner=> 'SCOTT', replace => TRUE);
PL/SQL procedure successfully completed.

SCOTT@test> select /*+ full(t) */ count(*) from t;
  COUNT(*)
----------
      1000

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cdwnbcfy42f8b, child number 0
-------------------------------------
select /*+ full(t) */ count(*) from t

Plan hash value: 3548397654

--------------------------------------------------------------
| Id  | Operation             | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |        |     3 (100)|
|   1 |  SORT AGGREGATE       |        |      1 |            |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |   1000 |     3   (0)|
--------------------------------------------------------------

Note
-----
   - SQL profile SYS_SQLPROF_01427dac70410001 used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

21 rows selected.

--可以发现下载执行计划使用INDEX FAST FULL SCAN.
--即使我改动第1个字母大写,依旧使用INDEX FAST FULL SCAN.
SCOTT@test> Select /*+ full(t) */ count(*) from t;
  COUNT(*)
----------
      1000

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  dxvbtb8hzfdr9, child number 0
-------------------------------------
Select /*+ full(t) */ count(*) from t

Plan hash value: 3548397654

--------------------------------------------------------------
| Id  | Operation             | Name   | E-Rows | Cost (%CPU)|
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |        |     3 (100)|
|   1 |  SORT AGGREGATE       |        |      1 |            |
|   2 |   INDEX FAST FULL SCAN| I_T_ID |   1000 |     3   (0)|
--------------------------------------------------------------

Note
-----
   - SQL profile SYS_SQLPROF_01427dac70410001 used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
21 rows selected.

--补充1点,如果优化语句带文字变量,想优化大量相似语句,可以在执行dbms_sqltune.accept_sql_profile
--加入:force_match=>true
--         force_match  - If TRUE this causes SQL Profiles
--                        to target all SQL statements which have the same
--                        text after normalizing all literal values into
--                        bind variables. (Note that if a combination of
--                        literal values and bind values is used in a
--                        SQL statement, no bind transformation occurs.)
--                        This is analogous to the matching algorithm
--                        used by the "FORCE" option of the
--                        CURSOR_SHARING parameter.  If FALSE, literals are
--                        not transformed.  This is analogous to the
--                        matching algorithm used by the "EXACT" option of
--                        the CURSOR_SHARING parameter.

总结:
sql profile是好东西,在生产系统使用,特别在优化很慢的语句时,要注意这些相关问题.

时间： 2024-07-30 10:47:28

[20131122]跟踪sql profile的操作.txt的相关文章

[20160816]使用oradebug跟踪sql net.txt

[20160816]使用oradebug跟踪sql net.txt --可以oradebug跟踪包,可以不用修改sqlnet.ora等文件参数,做一个记录. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ------------------------------------------

[20130123]spm与sql profile的主要区别在那里.txt

[20130123]spm与sql profile的主要区别在那里.txt     SPM是11G的新特性,而10g下sql profile一定程度代替store ountlined来稳定执行计划,oracle为什么11G下还要推出SPM?两者区别主要在那里呢? 我举一个例子来说明: 1.建立测试环境: select * from v$version where rownum BANNER -----------------------------------------------------

oracle sql profile实战

第一部分:profile概念 Oracle数据库10g使用了一个叫做SQL配置文件的新方法弥补了存储概要的缺点, DBA可以使用SQL调整顾问(STA)或SQL访问顾问(SAA)来识别可以得到更好性能的SQL语句, 这些语句可以保存在SQL调整集.一个AWR快照或保存在当前的库缓存中,一旦识别出调整候 选者, 这些顾问程序就开始分析捕获到的语句以期获得更好的性能,然后生成专用的语句扩展(就叫 做SQL配置文件)并重写SQL语句以在执行期间获取更佳的性能. 与存储概要类似,一个SQL配置文件提供了

使用SQL Profile进行SQL优化案例

一个社保系统的自助查询系统查询个人医疗费用明细的查询语句要用一分多钟还没查询出来,语句如下: select * from  v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017' 从上面的语句可知是从视图 v_zzzd_ylbx_ylfymxcx中查询数据.v_zzzd_ylbx_ylfymxcx视图的创建语句如下: create or replace view v_zzzd_ylbx_ylfymxcx as select a.indi_id

1223 result cache,sql profile,sql patch

[20141223]result cache 与sql profile,sql patch.txt --前面blog已经提到result cache的好处与缺点,对于第三方优化,sql profile可以改变稳定执行计划,是否可以通过改变提示来稳定 --执行计划,这样对频繁执行的语句较少逻辑读,提高服务器响应有积极意义. --sql patch 也具有相似的作用,看看这种方式是否可行. SCOTT@test> @ver1 PORT_STRING                    VERSIO

一个执行计划异常变更的案例 - 外传之SQL Profile(下)

之前的几篇文章: <一个执行计划异常变更的案例 - 前传> <一个执行计划异常变更的案例 - 外传之绑定变量窥探> <一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法> <一个执行计划异常变更的案例 - 外传之rolling invalidation> <一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)> <一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法> <一个执

SQL PROFILE的用法详解

一般只需要步骤三.四就可以完成执行计划的修改和固定,而outline和baseline则需要N多个步骤. SQL PROFILE使用简单,不区分大小写,回车,空格,但是对DBA写HINT的能力要求比较高,因为SQL PROFILE要求HINT必须写明查询块名, SQL PROFILE还有其他一些牛逼的特性. 我的示例里教了大家偷懒的做法,但是有时间我们还是最好认真把query block的东西学下. 步骤一-------------------------创建测试表,根据DBA_OBJECTS创

sql server 数据库操作报错“日志不可用”

问题描述 sql server 数据库操作报错"日志不可用" 在操作数据库时查询信息操作没有问题,但增删改操作时就会报下面的错误请问是什么原因: 消息 9001,级别 21,状态 1,第 2 行 数据库 'hrtest1' 的日志不可用.有关相应错误消息,请查看事件日志.修复所有错误后重新启动数据库. 解决方案 SQL Server 数据库操作关于SQL Server数据库日志已满的解决方法SQL Server 数据库操作总结(sql语法的使用) 解决方案二: recovery下数据库

操作txt 获取指定值-操作txt文件获取指定值

问题描述 操作txt文件获取指定值 我要操作第二行开始的后四列数据,但是想不通用什么方式获取,求思路 最好具体一点 解决方案 假设明确从第二行始便是你需要的数据,那么可以用java里面BufferedReader的readLine方法,一行一行去读.如果你并不知道哪一行或哪个位置才是想要的数据,不妨在里面设定一些间隔字符(信号,标识),再去定位和读取.C/C++实现思路大同小异. 解决方案二: string[] data = File.ReadAllLines("1.txt")[1].