ITPub博客

首页 > 数据库 > Oracle > [20140327]toad 12的缺陷.txt

[20140327]toad 12的缺陷.txt

原创 Oracle 作者:lfree 时间:2014-03-27 15:31:18 0 删除 编辑

[20140327]toad 12的缺陷.txt

我现在使用的toad版本是12.0.0.61,64位版本。我发现使用SGA trace存在一些问题。使用自带SQL TRACKER的跟踪发现:

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:47:39

Select *
from v$sql_plan
Where hash_value = '389954696'
and child_number =0
order by id

sqlhv=['389954696']
cn=[0]

Elapsed time: 0.005

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:47:39

alter session set current_schema = XXXX

Elapsed time: 0.001

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:47:39

explain plan set statement_id='Administrator:032714114739' into TOAD.TOAD_PLAN_TABLE For /* Formatted on 2014/3/27 11:44:14 (QP5 v5.252.13127.32867) */
SELECT ygxm
  FROM gy_ygdm
WHERE ygdm = :1

Elapsed time: 0.002

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:47:39

alter session set current_schema = YYY


Elapsed time: 0.038

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:47:39

Select *
From TOAD.TOAD_PLAN_TABLE
Where statement_id = 'Administrator:032714114739'
order by id


STATEMENT_ID=['Administrator:032714114739']


Elapsed time: 0.001

--------------------------------------------------------------------------------
Timestamp: 2014/3/27 11:48:00

 

如果查询以下语句,无法获得结果,
Select * from v$sql_plan Where hash_value = '389954696' and child_number =0 order by id;

生成显示的执行计划实际上是使用explain plan生成的。而执行如下:

SQL> Select count(*) from v$sql_plan Where hash_value = '389954696' and child_number =1 order by id;
  COUNT(*)
----------
         3

--实际上是没有child_number=0的记录,存在child_number=1的信息,并且child_number=0已经被换出共享池。

SQL> select sql_text ,sql_id ,buffer_gets,executions,buffer_gets/executions,rows_processed from v$sqlarea where sql_id='44sbw94bmwg48';
SQL_TEXT                                 SQL_ID        BUFFER_GETS EXECUTIONS BUFFER_GETS/EXECUTIONS ROWS_PROCESSED
---------------------------------------- ------------- ----------- ---------- ---------------------- --------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1  44sbw94bmwg48   129552243    2534464             51.1162293        2534459

--而ygdm字段是这个表的主键,不可能存在这么高的逻辑读,而且看ROWS_PROCESSED数量,基本上与执行次数是1:1.

--查看执行计划

SQL> @dpc 44sbw94bmwg48 ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  44sbw94bmwg48, child number 1
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 3936865862

------------------------------------------------------------------------
| Id  | Operation                   | Name       | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| GY_YGDM    |      1 |     2   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_GY_YGDM |      1 |     1   (0)|
------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (CHAR(30), CSID=852): '2890'

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

   2 - access("YGDM"=:1)

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  44sbw94bmwg48, child number 3
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 1353890219

-----------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| GY_YGDM |      1 |    13   (0)|
-----------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 2016

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

   1 - filter(TO_NUMBER("YGDM")=:1)

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  44sbw94bmwg48, child number 4
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 1353890219

-----------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| GY_YGDM |      1 |    13   (0)|
-----------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 1729

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

   1 - filter(TO_NUMBER("YGDM")=:1)

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  44sbw94bmwg48, child number 5
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 3936865862

------------------------------------------------------------------------
| Id  | Operation                   | Name       | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| GY_YGDM    |      1 |     2   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_GY_YGDM |      1 |     1   (0)|
------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (CHAR(30), CSID=852): '2875'

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

   2 - access("YGDM"=:1)

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  44sbw94bmwg48, child number 10
--------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 1353890219

-----------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | Cost (%CPU)|
-----------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| GY_YGDM |      1 |    13   (0)|
-----------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 2073

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

   1 - filter(TO_NUMBER("YGDM")=:1)

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

--昏,又是隐式转换的问题。我仅仅想说的是开发团队太差劲了。两种执行计划都有。

如果toad能够选择查看其他child_number的执行计划,就不存在这个问题了。

另外toad还提供替换绑定变量使用文字变量的功能,勾上:substitute values for bind variables if possible。

我发现许多情况下无法替换,必须先选上,在点击相应的sql语句才可以替换。

SQL> host cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a12
break on sql_id on child_number  skip 1
SELECT sql_id,
       child_number,
       was_captured,
       name,
       position,
       max_length,
       last_captured,
       datatype_string,
       DECODE (
          datatype_string,
          'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),
                           'yyyy/mm/dd hh24:mi:ss'),
          value_string)
          value_string
  FROM v$sql_bind_capture
WHERE sql_id = '&1' and was_captured='YES'
order by child_number,was_captured,position;


SQL> @bind_cap 44sbw94bmwg48
SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STR VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- ------------ -------------
44sbw94bmwg48            1 YES :1                            1         32 2014-03-27 11:57:12 CHAR(32)     2313
                         3 YES :1                            1         22 2014-03-26 15:50:25 NUMBER       385
                         4 YES :1                            1         22 2014-03-27 12:03:56 NUMBER       2156
                         5 YES :1                            1         32 2014-03-27 12:05:45 CHAR(32)     2875
                        10 YES :1                            1         22 2014-03-27 11:26:01 NUMBER       494

--看视图v$sql_bind_capture 也可以发现这个问题。可以发现抓取两种类型的变量。

SQL> select child_number,executions from v$sql where sql_id='44sbw94bmwg48';
CHILD_NUMBER EXECUTIONS
------------ ----------
           1     137319
           3       2165
           4    2348072
           5       5848
          10      42399

--可以发现执行次数child_number=3,4,10的占了大部分。
--解决方式是找到语句修改代码,实际上我自己对这样的开发团队实在太失望了。

最快捷的方式建立函数索引解决问题。

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2627
  • 访问量
    6391611