[20120209] SET AUTOTRACE TRACEONLY EXPLAIN的问题.txt

1.测试环境
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY');
Enter value for day: 30
old   1: select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY')
new   1: select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("HIREDATE"=TO_DATE('30-02-2011','DD-MM-YYYY'))

SQL> set autotrace traceonly ;
SQL> select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY');
Enter value for day: 30
old   1: select count(*) from emp where hiredate=to_date('&day-02-2011','DD-MM-YYYY')
new   1: select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
select count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
                                                *
ERROR at line 1:
ORA-01839: date not valid for month specified

区别在那里呢?很明显,在SET AUTOTRACE TRACEONLY EXPLAIN下，select并没有执行，仅仅执行explain plan for。而在set autotrace traceonly ;情况下是先执行在调用explain plan for.

2.接着测试:
我加入注解，为了进行硬分析，我仅仅执行一次：

SQL> set autotrace traceonly explain;
SQL> Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY');

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     8 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("HIREDATE"=TO_DATE('30-02-2011','DD-MM-YYYY'))

SQL> set autotrace off ;

SQL> column aa format a140
SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%testme%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         0 2hj45mw2bn6dq Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')
         1 gwrj6jf4wd44r EXPLAIN PLAN SET STATEMENT_ID='PLUS127058' FOR Select /*+ testme */ count(*) from emp where hiredate=to_date('30-02-2011','DD-MM-YYYY')

--可以发现我并没有执行，而在v$sql中已经存在这样的光标，而EXECUTIONS=0。也就是讲这条语句已经完成了分析步骤。
--这样会带来一个问题，因为光标已经存在，这样再次执行的时候的时候，就不再分析了，这样可能导致执行计划选择不好的执行语句。

3.建立一个测试例子：
create table t as select rownum id ,'test' name  from dual connect by levelinsert into t select 1001,'aaaa' from dual connect by levelcommit ;
create index i_t_id on t(id);
exec dbms_stats.gather_table_stats(OWNNAME =>user,TABNAME =>'T',Method_Opt=>'FOR COLUMNS id SIZE 254');

SQL> column data_type format a20
SQL> SELECT table_name, column_name, data_type, histogram FROM dba_tab_cols WHERE table_name ='T' ;
TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            HISTOGRAM
------------------------------ ------------------------------ -------------------- ---------------
T                              NAME                           CHAR                 NONE
T                              ID                             NUMBER               FREQUENCY

我建立的表id分布很不均匀。

SQL> set autotrace traceonly explain;
SQL> variable a number;
SQL> exec :a := 1001;
PL/SQL procedure successfully completed.
SQL> select /*+ this_is_a_test */ * from t where id = :a ; 

Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    47 |   423 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |    47 |   423 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |    47 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=TO_NUMBER(:A))

SQL> set autotrace off;

SQL> column aa format a140
SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%this_is_a_test%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         1 6cgfnfpaq0uyg EXPLAIN PLAN SET STATEMENT_ID='PLUS127070' FOR select /*+ this_is_a_test */ * from t where id = :a
         0 1qy45kg5422mr select /*+ this_is_a_test */ * from t where id = :a

SQL> select * from table(dbms_xplan.display_cursor('1qy45kg5422mr',NULL,'allstats last peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1qy45kg5422mr, child number 0
-------------------------------------
select /*+ this_is_a_test */ * from t where id = :a
Plan hash value: 4153437776
-------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     47 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     47 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:A)
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
25 rows selected.

--我们可以发现我定义是:a :=1001,而如果按照正常，第1次peeked_binds,选择的最佳的执行计划是全表扫描。而不是走索引。
SQL> print :a
         A
----------
      1001

SQL> select /*+ this_is_a_test */ * from t where id = :a ;
...

SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'allstats last peeked_binds'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  1qy45kg5422mr, child number 0
-------------------------------------
select /*+ this_is_a_test */ * from t where id = :a
Plan hash value: 4153437776
-------------------------------------------------------
| Id  | Operation                   | Name   | E-Rows |
-------------------------------------------------------
|   0 | SELECT STATEMENT            |        |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     47 |
|*  2 |   INDEX RANGE SCAN          | I_T_ID |     47 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:A)
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
25 rows selected.

SQL> select executions,sql_id,substr(sql_text,1,150) aa from v$sql where lower(sql_text) like '%this_is_a_test%' and lower(sql_text) not like '%sql_text%';
EXECUTIONS SQL_ID        AA
---------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------
         1 6cgfnfpaq0uyg EXPLAIN PLAN SET STATEMENT_ID='PLUS127070' FOR select /*+ this_is_a_test */ * from t where id = :a
         1 1qy45kg5422mr select /*+ this_is_a_test */ * from t where id = :a

--可以发现执行计划选择了索引，一个不好的执行计划。

4.再做一个测试：
SQL> variable b number;
SQL> exec :b := 1001;
PL/SQL procedure successfully completed.

SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  3mn6m7yf29wht, child number 0
-------------------------------------
select /*+ this_is_a_test */ * from t where id = :b
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |     4 (100)|
|*  1 |  TABLE ACCESS FULL| T    |   1000 |     4   (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 1001
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=:B)
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
29 rows selected.

--看来以后在测试与显示执行计划最好不要使用set autotrace traceonly explain;
--做多仅仅使用set autotrace traceonly查看执行计划的统计信息。

时间： 2024-07-30 10:55:33

[20120209] SET AUTOTRACE TRACEONLY EXPLAIN的问题.txt的相关文章

[20120112]谨慎使用set autotrace traceonly查看执行计划.txt

自己经常在优化sql语句是经常使用set autotrace traceonly来优化sql语句.这样的好处是如果输出记录很多,可以不受干扰,直接查看执行计划以及执行计划的统计信息.但是在一些特殊情况要注意也许执行计划是不真实的. SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g

[20121212]谨慎使用set autotrace traceonly查看执行计划[补充].txt

使用toad自带sqlmonitor,toad10以上版本现在叫sqltrace. 12:00:24 SQL> set autotrace traceonly ;12:01:23 SQL> select * from t2 where id=45; 10000 rows selected. Execution Plan----------------------------------------------------------Plan hash value: 1513984157 ---

[20140621]Cluster Nulls.txt

[20140621]Cluster Nulls.txt https://jonathanlewis.wordpress.com/2014/06/17/cluster-nulls/ 重复测试: SCOTT@test01p> @ver BANNER                                                                               CON_ID ------------------------------------------

[20140109]显示执行计划的问题.txt

昨天看别人调优,我跟对方讲使用SET Autotrace看执行计划可能不真实的. 自己做一个简单的例子来说明: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production cr

关于Oracle 9i 跳跃式索引扫描(Index Skip Scan)的小测试

oracle|索引 在Oracle9i中我们知道能够使用跳跃式索引扫描(Index Skip Scan).然而,能利用跳跃式索引扫描的情况其实是有些限制的. 从Oracle的文档中我们可以找到这样的话: Index Skip ScansIndex skip scans improve index scans by nonprefix columns. Often, scanning index blocks is faster than scanning table data blocks.Sk

对参数 optimizer

参数optimizer_index_cost_adj设定了通过索引扫描和全表扫描之间的代价消耗关系,其默认值为100,表示ORACLE在计算查询成本时,通过索引查找和全表扫描成本相等.该参数值越小,表明通过索引查找的代价越小,反之则越大.     通过以下试验,演示该参数的用法. 1) 查看参数optimizer_index_cost_adj的当前值 SQL> conn / as sysdba         Connected.           SQL> show parameter o

Oracle Compile 编译无效对象(ORA-04063: package body

使用AUTOTRACE 前要做好准备工作 创建角色Plustrace SQL>        @?/sqlplus/admin/plustrce.sql 将Plustrace角色授于要执行AUTOTRACE的用户 SQL>        grant plustrace to sst; ----------------------------------------------------以上用sys身份执行 设置启用AUTOTRACE SQL>        set autotrace

CBO学习笔记（转）

Query Transformation 在继续研究SQL的其他操作(比如Join)对CBO的影响之前,我们来讨论一下Oracle优化器的Query Transformation特性.我们都习惯于根据我们的SQL结构来判断SQL的效率,但是我们必须要知道,对于我们写好的SQL,Oracle的优化器会进行改写,或者说是进行转化.转化的目的是是要把我们的SQL转化成oracle认为更简单,更有效率的结构,需要注意的是,有些时候(主要取决的版本),CBO的的某些转化似乎不是基于cost的,而是基于ru

oracle_执行计划_谓词信息和数据获取(access and filter区别) (转)

These two terms in the Predicate Information section indicate when the data source is reduced. Simply, access means only retrieve those records meeting the condition and ignore others. Filter means *after* you already got the data, go through them al