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

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

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

时间： 2024-09-22 11:45:36

[20140327]toad 12的缺陷.txt的相关文章

[20131030]ORA-29275与toad 12.txt

[20131030]ORA-29275与toad 12.txt $ oerr ora 2927529275, 00000, "partial multibyte character"// *Cause:  The requested read operation could not complete because a partial//          multibyte character was found at the end of the input.// *Action:

[20140311]toad 12 alert log viewer.txt

[20140311]toad 12 alert log viewer.txt 今天使用toad 12版本,无意中发现有一个新功能alert log viewer. database => diagnose => alert log viewer. 支持几种查看方式: entire file,Since instance starup ,today,yesterday+today,last 7days,last 1000 lines. 有了这个无需在登录服务器期查看alert*.log文件. 而

[20140217]在toad使用跟踪文件.txt

[20140217]在toad使用跟踪文件.txt 我使用toad版本是12.0.0.61,今天使用生成跟踪文件,发现在界面上可以访问跟踪文件的内容做一个测试看看. SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64

[20140311]等待事件enq HW - contention

[20140311]等待事件enq HW - contention.txt 生产系统业务高峰时出现enq: HW - contention,一般这个主要是插入记录非常密集的情况下出现,自己对系统分析看看主要是那些对象 引起的问题. SQL> @ver BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.

随时给自己贴的图片加文字

随时给自己贴的图片加文字 <? Header( "Content-type: image/jpeg"); function makethumb($srcFile,$text,$size=12,$R=0,$G=0,$B=0) { if(!$text){ $text='welcome xs.net.ru xayle'; $size=20; $R=255; } $data = GetImageSize($srcFile,&$info); switch ($data[2]) {

在SQL Server里把SQL语句结果生成文本文件

在SQL Server里可以调用DOS下的命令行工具bcp来实现把表里的数据或者SQL语句结果生成文本文件. BCP命令的参数格式: BCP {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type

Linux下的sed命令使用详解

  sed是stream editor的简称,也就是流编辑器.它一次处理一行内容,处理时,把当前处理的行存储在临时缓冲区中,称为"模式空间"pattern space,接着用sed命令处理缓冲区中的内容,处理完成后,把缓冲区的内容送往屏幕.接着处理下一行,这样不断重复,直到文件末尾.文件内容并没有 改变,除非你使用重定向存储输出. 使用语法 sed命令的使用规则是这样的: 复制代码 代码如下: sed [option] 'command' input_file 其中option是可选的

Install MATLAB 2013a on CentOS 6.4 x64 with mode silent

首先要下载安装光盘. Matlab801_MacUnix.iso [root@db-172-16-3-150 mnt]# md5sum /ssd1/Matlab801_MacUnix.iso 0d31a7ff79eaf48c0905f9845fa3e825 /ssd1/Matlab801_MacUnix.iso 挂载 :  mount -o loop,ro Matlab801_MacUnix.iso /mnt 光盘内容 :  [root@db-172-16-3-150 ~]# cd /mnt [

【Python之旅】第六篇（一）：Paramiko模块使用演示

  在第五篇的最后本来还应该有个ftp软件开发的作业的,但对比自己写的和师兄写的,感觉自己写的太烂了,所以就不上传了,等以后技术学好些再开发些小工具吧,现在还差好远,还得非常非常努力啊!     先说Paramiko模块是为了以后做监控软件的开发做准备,所以这里先说用Paramiko模块连接远程主机,再介绍用Paramiko模块作远程文件传送.     主要内容是下面几项: 1 2 3 4 1.Paramiko SSH连接远程主机 (1)使用用户名密码连接远程主机 (2)使用key连接远程主机