[20140327]toad 12的缺陷.txt-阿里云开发者社区

[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)

SQL_ID 44sbw94bmwg48, child number 4
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 1353890219

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

1 - (NUMBER): 1729

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

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

SQL_ID 44sbw94bmwg48, child number 5
-------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 3936865862

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

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

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

2 - access("YGDM"=:1)

SQL_ID 44sbw94bmwg48, child number 10
--------------------------------------
SELECT ygxm FROM gy_ygdm Where ygdm =:1

Plan hash value: 1353890219

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

1 - (NUMBER): 2073

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

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

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

如果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的占了大部分。
--解决方式是找到语句修改代码，实际上我自己对这样的开发团队实在太失望了。

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

[20140327]toad 12的缺陷.txt

热门文章

最新文章

相关电子书