Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 高version count，请帮忙解惑

5 积分	0 好友	6 主题

发消息

[SQL调优] 高version count，请帮忙解惑

1^#

发表于 2013-8-27 15:11:53 | 查看: 4110| 回复: 11

数据库版本： oracle 10.2.0.4
主机版本： AIX 6.1

在awr报告里发现多个sql存在大量的version count，参照这个帖子的内容，感觉很相似
http://www.cnblogs.com/springside-example/archive/2011/06/24/2529708.html

SQL> select sql_id,version_count from v$sqlarea where version_count> 500 order by 2 desc;

SQL_ID       VERSION_COUNT
------------- -------------
9mahnd9an0uku          975
d1ty0p1y5m32g          958
1ugfq6nuwn7ja          798
bkqnw9uqmnu0n          771
8n535k8rrdpyp          727
5jkpfwpkvc5mu          720
90cfkb6m0kz0p          702
85b4mp7ma7rgh          608

8 rows selected.

SQL> select sql_id,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id in ('9mahnd9an0uku','d1ty0p1y5m32g','1ugfq6nuwn7ja','bkqnw9uqmnu0n','8n535k8rrdpyp','5jkpfwpkvc5mu','90cfkb6m0kz0p','85b4mp7ma7rgh') and BIND_MISMATCH='Y' and rownum<30;

SQL_ID       CHILD_NUMBER B
------------- ------------ -
5jkpfwpkvc5mu       703 Y
9mahnd9an0uku       898 Y
8n535k8rrdpyp       697 Y
90cfkb6m0kz0p       504 Y
90cfkb6m0kz0p       617 Y
90cfkb6m0kz0p       645 Y
90cfkb6m0kz0p       661 Y
90cfkb6m0kz0p       666 Y
90cfkb6m0kz0p       682 Y
90cfkb6m0kz0p       695 Y
90cfkb6m0kz0p       699 Y
d1ty0p1y5m32g       947 Y

12 rows selected.

SQL> select position,LAST_CAPTURED,datatype_string,value_string from v$sql_bind_capture where sql_id='9mahnd9an0uku' and rownum<50;

  POSITION LAST_CAPT DATATYPE_STRING             VALUE_STRING
---------- --------- ------------------------------ ----------------------------------------
      1          VARCHAR2(32)
      2          VARCHAR2(32)
      3          VARCHAR2(32)
      4          VARCHAR2(32)
      5          VARCHAR2(32)
      6          NUMBER
      7          VARCHAR2(128)
      8          VARCHAR2(32)
      9          VARCHAR2(128)
      10          TIMESTAMP
      11          NUMBER
      12          VARCHAR2(32)
      13 27-AUG-13 VARCHAR2(32)                1072334287
      14 27-AUG-13 VARCHAR2(32)                023164767
      1          VARCHAR2(32)
      2          VARCHAR2(32)
      3          VARCHAR2(32)
      4          VARCHAR2(32)
      5          VARCHAR2(32)
      6          NUMBER
      7          VARCHAR2(128)
      8          VARCHAR2(32)
      9          VARCHAR2(128)
      10          TIMESTAMP
      11          NUMBER
      12          VARCHAR2(32)
      13 27-AUG-13 VARCHAR2(32)                1072334277
      14 27-AUG-13 VARCHAR2(32)                886382301
      1          VARCHAR2(32)
      2          VARCHAR2(32)
      3          VARCHAR2(32)
      4          VARCHAR2(32)
      5          VARCHAR2(32)
      6          NUMBER
      7          VARCHAR2(128)
      8          VARCHAR2(32)
      9          VARCHAR2(128)
      10          TIMESTAMP
      11          NUMBER
      12          VARCHAR2(32)
      13 27-AUG-13 VARCHAR2(32)                1072334197
      14 27-AUG-13 VARCHAR2(32)                013864818

请各位帮忙分析，指点下：
1. 帖子里有介绍可设置ALTER SESSION SET EVENTS '10503 trace name context level <buffer length>, forever';。因为这个是生产库，想了解这个语句的具体作用，来评估设置后有多大的风险。
2. 高version count的原因是？
3. awr报告中Parse CPU to Parse Elapsd %: 12.39 很低，是否也跟这个有关？

awrrpt_1_62075_62076.html

715.51 KB, 下载次数: 798

分享0

收藏0 回复只看该作者道具举报

suredandan

2^#

发表于 2013-8-27 15:28:27

刘大估计要让你先做一个格式化的V$SQL_SHARED_CURSOR报告再说：）
http://www.oracledatabase12g.com/archives/formated-vsql_shared_cursor-report-by-sqlid-or-hash-value.html

回复只看该作者道具举报

s_chrison

3^#

发表于 2013-8-27 15:31:31

哦，好

但这个帖子里的附件version_rpt3_12.sql，下不下来，貌似失效了
Not found, error 404

回复只看该作者道具举报

suredandan

4^#

发表于 2013-8-27 15:34:34

s_chrison 发表于 2013-8-27 15:31
哦，好

但这个帖子里的附件version_rpt3_12.sql，下不下来，貌似失效了

呃。。那就得问问刘大了。

回复只看该作者道具举报

harryzhang

5^#

发表于 2013-8-27 15:50:19

version_rpt3_22.zip

如附件~

version_rpt3_22.zip

5.37 KB, 下载次数: 1032

version_rpt3_22.zip

回复只看该作者道具举报

harryzhang

6^#

发表于 2013-8-27 15:53:34

            Per Second Per Transaction
Parses:    1,248.11    16.61
Hard parses:    140.51    1.87

硬解析每秒有140个哦

回复只看该作者道具举报

s_chrison

7^#

发表于 2013-8-27 16:28:35

谢谢提醒，version_rpt3_22脚本采集内容如下。不知道跑对了没

SQL> select b.* from v$sqlarea a ,table(version_rpt(a.sql_id,null,'Y')) b where loaded_versions >=100;

COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.2.2 -- Today's Date 27-aug-13 16:22
RDBMS Version :10.2.0.4.0 Host: p780a Instance 1 : cdld1
==================================================================
Addr: 0700001743F0E4F8  Hash_Value: 55932549  SQL_ID 5mw0ksn1paxn5
Sharable_Mem: 7468277 bytes Parses: 143050
Stmt:
0 INSERT INTO tmp_ic04i5 (          YAB281,       AAE001,
1             AAE041,       AAE042,       AIC020,       AAA
2 042,       AAA041,       BJ,       AIC072,       LX,
3    AIC075,       HJ,       AIC041,       YAD110,
4  AIC079,       AAA043,       AAE013) VALUES (       :1,

COLUMN_VALUE
--------------------------------------------------------------------------------
5          :2,          :3,          :4,          :5,
6 :6,          :7,          :8,          :9,          :10,
7          :11,          :12,       :13,       :14,
8 :15,       :16,       :17)
9

Versions Summary
----------------
BIND_MISMATCH :256
OPTIMIZER_MODE_MISMATCH :257

COLUMN_VALUE
--------------------------------------------------------------------------------
Total Versions:256

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
            0 257

COLUMN_VALUE
--------------------------------------------------------------------------------

~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for BIND_MISMATCH :

Consolidated details for :
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)
======== ======== =============== =============== ======== ================

COLUMN_VALUE
--------------------------------------------------------------------------------
   14       1             32             32       1 (,)
   243       1             22             22       2 (,)
   9       2             32             32       1 (,)
   248       2             22             22       2 (,)
   36       3             32             32       1 (,)
   221       3             22             22       2 (,)
   41       4             32             32       1 (,)
   216       4             22             22       2 (,)
   73       5             32             32       1 (,)
   184       5             22             22       2 (,)
   107       6             32             32       1 (,)

COLUMN_VALUE
--------------------------------------------------------------------------------
   150       6             22             22       2 (,)
   82       7             32             32       1 (,)
   175       7             22             22       2 (,)
   115       8             32             32       1 (,)
   142       8             22             22       2 (,)
   140       9             32             32       1 (,)
   117       9             22             22       2 (,)
   154    10             32             32       1 (,)
   103    10             22             22       2 (,)
   163    11             32             32       1 (,)
   94    11             22             22       2 (,)

COLUMN_VALUE
--------------------------------------------------------------------------------
   89    12             32             32       1 (,)
   168    12             22             22       2 (,)
   126    13             32             32       1 (,)
   131    13             22             22       2 (,)
   8    14             32             32       1 (,)
   249    14             22             22       2 (,)
   257    15             32             32       1 (,)
   180    16             32             32       1 (,)
   77    16             22             22       2 (,)
   257    17             32             32       1 (,)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN_VALUE
--------------------------------------------------------------------------------
Details for OPTIMIZER_MODE_MISMATCH :

153 versions with CHOOSE
104 versions with ALL_ROWS
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 55932549, level LLL';
To turn it off do use address 1, level 2147483648
================================================================

87 rows selected.

回复只看该作者道具举报

Maclean Liu(刘相兵

8^#

发表于 2013-8-27 16:37:34

就你分析的这个SQL
Details for OPTIMIZER_MODE_MISMATCH :

153 versions with CHOOSE
104 versions with ALL_ROWS
####

200多个 version 我觉得还处于可以接受的情况

SQL_ID       VERSION_COUNT
------------- -------------
9mahnd9an0uku          975

分析 9mahnd9an0uku          这个SQL_ID呢？

回复只看该作者道具举报

s_chrison

9^#

发表于 2013-8-27 19:00:58

刚离开客户环境，只有明天看能不能收集了

回复只看该作者道具举报

s_chrison

10^#

发表于 2013-8-28 14:00:23

SQL> select * from table(version_rpt('9mahnd9an0uku'));

COLUMN_VALUE
--------------------------------------------------------------------------------
Version Count Report Version 3.2.2 -- Today's Date 28-aug-13 13:55
RDBMS Version :10.2.0.4.0 Host: p780a Instance 1 : cdld1
==================================================================
Addr: 0700001493FFA3E0  Hash_Value: 1430284890  SQL_ID 9mahnd9an0uku
Sharable_Mem: 110531 bytes Parses: 804
Stmt:
0 UPDATE ac29 SET       yac137 = :1       ,    yac112 = :2
1       ,    yab003 = :3       ,    yac136 = :4       ,
2 yac150 = :5       ,    yac135 = :6       ,    aac002 = :7
3       ,    aac003 = :8       ,    yac138 = :9       ,
4  yac140 = :10       ,    yac141 = :11                   ,

COLUMN_VALUE
--------------------------------------------------------------------------------
5  aab001 = :12       WHERE 1 = 1    AND yac005 = :13
6    AND aac001 = :14
7

Versions Summary
----------------
BIND_MISMATCH :1
OPTIMIZER_MODE_MISMATCH :10

Total Versions:11

COLUMN_VALUE
--------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cursor_sharing = EXACT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan Hash Value Summary
-----------------------
Plan Hash Value Count
=============== =====
   3566276944 4

~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN_VALUE
--------------------------------------------------------------------------------
Details for BIND_MISMATCH :

Consolidated details for :
BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and
BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)

from v$sql_bind_capture
COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)
======== ======== =============== =============== ======== ================
   12       1             32             32       1 (,)
   12       2             32             32       1 (,)

COLUMN_VALUE
--------------------------------------------------------------------------------
   12       3             32             32       1 (,)
   12       4             32             32       1 (,)
   12       5             32             32       1 (,)
   12       6             22             22       2 (,)
   12       7          128          128       1 (,)
   12       8             32          128       1 (,)
   12       9          128          128       1 (,)
   12    10             11             11    180 (,)
   12    11             22             22       2 (,)
   12    12             32             32       1 (,)
   12    13             32             32       1 (,)

COLUMN_VALUE
--------------------------------------------------------------------------------
   12    14             32             32       1 (,)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details for OPTIMIZER_MODE_MISMATCH :

3 versions with CHOOSE
1 versions with ALL_ROWS
####
To further debug Ask Oracle Support for the appropiate level LLL.
alter session set events
'immediate trace name cursortrace address 1430284890, level LLL';
To turn it off do use address 1, level 2147483648

COLUMN_VALUE
--------------------------------------------------------------------------------
================================================================

67 rows selected.

回复只看该作者道具举报

s_chrison

11^#

发表于 2013-8-28 14:04:17

这么看起来好像没什么问题，可是今天查的这个sql_id还是排第一个

SQL> select sql_id,version_count from v$sqlarea where version_count>500 order by 2 desc;

SQL_ID       VERSION_COUNT
------------- -------------
9mahnd9an0uku       1003
d1ty0p1y5m32g          989
1ugfq6nuwn7ja          817
bkqnw9uqmnu0n          805
8n535k8rrdpyp          741
5jkpfwpkvc5mu          739
90cfkb6m0kz0p          713
85b4mp7ma7rgh          620

8 rows selected.

回复只看该作者道具举报

Liu Maclean(刘相兵

12^#

发表于 2013-8-28 14:07:56

重新浏览了 AWR:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read    6,788,865    27,353    4    49.5 User I/O
CPU time         16,906         30.6
SQL*Net break/reset to client    655,993    1,101    2    2.0 Application
read by other session    194,670    629    3    1.1 User I/O
gc buffer busy    348,835    542    2    1.0 Cluster

table fetch continued row 28,307,193 7,808.25 103.92

可能有较为严重的链式行 table fetch continued row 每秒 7800次  对应占50%db time 的db file sequential read

建议你先解决链式行的问题

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册