anbob

87 积分	0 好友	5 主题

发消息

[SQL调优] Difference parse call between 11203 and 11204 issue

1^#

发表于 2013-11-18 16:40:57 | 查看: 6417| 回复: 8

本帖最后由 anbob 于 2013-11-22 09:22 编辑

有个库版本11204，发现很严重的parse问题

Load Profile
Per Second Per Transaction Per Exec Per Call
DB Time(s): 0.7 0.0 0.00 0.00
DB CPU(s): 0.4 0.0 0.00 0.00
Redo size (bytes): 469,220.8 1,272.4
Logical read (blocks): 5,820.7 15.8
Block changes: 3,166.9 8.6
Physical read (blocks): 0.0 0.0
Physical write (blocks): 27.1 0.1
Read IO requests: 0.0 0.0
Write IO requests: 5.3 0.0
Read IO (MB): 0.0 0.0
Write IO (MB): 0.2 0.0
User calls: 2,131.7 5.8
Parses (SQL): 841.7 2.3
Hard parses (SQL): 0.0 0.0
SQL Work Area (MB): 0.2 0.0
Logons: 0.6 0.0
Executes (SQL): 1,207.4 3.3
Rollbacks: 0.0 0.0
Transactions: 368.8
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.25 Soft Parse %: 99.99
Execute to Parse %: 30.28 Latch Hit %: 99.85
Parse CPU to Parse Elapsd %: 14.60 % Non-Parse CPU: 98.95
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
log file sync 1,180,970 1630.1 1 67.8 Commit
DB CPU 1239.6 51.5
library cache: mutex X 8,437 125.1 15 5.2 Concurrency
latch: shared pool 713 45.9 64 1.9 Concurrency
SQL*Net message to client 4,597,267 13.9 0 .6 Network
latch: enqueue hash chains 188 3.1 17 .1 Other
cursor: pin S 693 2.9 4 .1 Concurrency
latch free 144 1.6 11 .1 Other
cursor: pin S wait on X 20 .2 12 .0 Concurrency
cursor: mutex S 2 .2 114 .0 Concurrency

复制代码

后来做了个测试，怀疑11204 没用 softer soft parse ，session_cached_cursor不为0

--####################### 11203
sys@ANBOB>alter system flush shared_pool;
System altered.
sys@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*weejar11203*/%';
no rows selected
sys@ANBOB>conn weejar/weejar
Connected.
weejar@ANBOB>select /*anbob11203*/ count(*) from test;
COUNT(*)
--------------------
1
-- 执行同样sql 5次
weejar@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11203*/%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
1 1 5 1 3
weejar@ANBOB>show parameter session_cached
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
weejar@ANBOB>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
############ 11204
sys@ORA11204>conn weejar;
Enter password:
Connected.
weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11204*/%';
no rows selected
weejar@ORA11204>select /*anbob11204*/ count(*) from test1;
COUNT(*)
--------------------
0
-- 同样执行5次
weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*anbob11204*/%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
1 1 5 1 5
sys@ORA11204>show parameter session_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
sys@ORA11204>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

复制代码

问题：
11204 解析有问题还是v$sql.parse_calls显示有问题，有人知道么？

thanks.

分享0

追求卓越

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

Maclean Liu(刘相兵

2^#

发表于 2013-11-18 23:14:12

这个问题延迟到明天白天回答

回复只看该作者道具举报

anbob

3^#

发表于 2013-11-19 12:07:33

本帖最后由 anbob 于 2013-11-19 12:19 编辑

同样的方法在12C 一样

sys@ORA12C>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*weejar12101*/%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
1 1 5 1 5
sys@ORA12C>select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- --------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0

复制代码

10204，10205 也是5:5

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-11-19 15:09:38

11.2.0.4 确实有该问题

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn maclean/oracle
Connected.
SQL>
SQL>
SQL>
SQL> create table testparse(t1 int);
Table created.
SQL>
SQL> select /* macleanparse */ 1 from testeparse;
select /* macleanparse */ 1 from testeparse
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select /* macleanparse */ 1 from testparse;
no rows selected
SQL>
SQL> select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /*macleanparse*/%';
no rows selected
SQL> select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /* macleanparse */%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
--------------- ------------- ---------- ---------- -----------
1 0 1 1 1
SQL> select /* macleanparse */ 1 from testparse;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /* macleanparse */%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
--------------- ------------- ---------- ---------- -----------
1 1 6 1 6
SQL> select /* macleanparse */ 1 from testparse;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'select /* macleanparse */%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
--------------- ------------- ---------- ---------- -----------
1 1 12 1 12
SQL> select vs.name,vm.value from v$mystat vm,v$statname vs where vm.statistic#=vs.statistic# and name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 167
parse count (hard) 34
parse count (failures) 1
parse count (describe) 0
SQL> select /* macleanparse */ 1 from testparse;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> select vs.name,vm.value from v$mystat vm,v$statname vs where vm.statistic#=vs.statistic# and name like '%parse count%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 173
parse count (hard) 34
parse count (failures) 1
parse count (describe) 0
SQL> alter session set events 'immediate trace name errorstack level 3';
Session altered.

复制代码

回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2013-11-19 15:09:56

怀疑与SQLPLUS这个客户端有关，需要尝试jdbc是否会导致该问题

回复只看该作者道具举报

anbob

6^#

发表于 2013-11-20 13:46:15

Maclean Liu(刘相兵发表于 2013-11-19 15:09
怀疑与SQLPLUS这个客户端有关，需要尝试jdbc是否会导致该问题

ML, thanks for your reply. take a sample

JAVA CODE

import java.sql.*;
public class parsetest{
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.168.231:1521:ora11204";
String username = "weejar";
String password = "weejar";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static int countRows(Connection conn, String tableName) throws SQLException {
// select the number of rows in the table
Statement stmt = null;
ResultSet rs = null;
int rowCount = -1;
try {
stmt = conn.createStatement();
for (int i =0; i<10; i++){
rs = stmt.executeQuery("SELECT /*ojdbc_parse*/ COUNT(*) FROM " + tableName);
}
// get the number of rows from the result set
rs.next();
rowCount = rs.getInt(1);
} finally {
rs.close();
stmt.close();
}
return rowCount;
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
String tableName = "test1";
System.out.println("tableName=" + tableName);
System.out.println("conn=" + conn);
System.out.println("rowCount=" + countRows(conn, tableName));
} catch (Exception e) {
e.printStackTrace();
System.exit(1);
} finally {
// release database resources
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

复制代码

D:\>set CLASSPATH=E:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;%CLASSPATH%

D:\>set CLASSPATH
CLASSPATH=E:\oracle\product\11.2.0\client_1\jdbc\lib\ojdbc6.jar;.;D:\jdk1_7\jre\lib\rt.jar;D:\jdk1_7\lib;D:\jdk1_7\lib\tools.jar

D:\>javac parsetest.java

D:\>java parsetest
tableName=test1
conn=oracle.jdbc.driver.T4CConnection@2f0e07
rowCount=0

weejar@ORA11204>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'SELECT /*ojdbc_parse*/%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
1 0 10 1 10

复制代码

修改JAVA CODE 在11203 上执行

sys@ANBOB>select LOADED_VERSIONS ,OPEN_VERSIONS ,EXECUTIONS,LOADS,PARSE_CALLS from v$sql where sql_text like 'SELECT /*ojdbc_parse*/%';
LOADED_VERSIONS OPEN_VERSIONS EXECUTIONS LOADS PARSE_CALLS
-------------------- -------------------- -------------------- -------------------- --------------------
1 0 10 1 3

复制代码

这算bug? ML 可有参数可以降11204 的parse方法为11203么？

回复只看该作者道具举报

anbob

7^#

发表于 2013-11-20 14:18:27

Steve Adams said

If an SQL statement is parsed in a cursor and then executed repeatedly without closing the cursor or parsing another statement in it, then the V$SQLAREA statistics will show many more EXECUTIONS than PARSE_CALLS for that statement. If statements are never reused, then EXECUTIONS and PARSE_CALLS are normally identical. Note that the count of parse calls includes hits in the session cursor cache, which don't even touch the shared cursor. This is done by updating the statistics when the shared cursor is unpinned after execution.

回复只看该作者道具举报

Maclean Liu(刘相兵

8^#

发表于 2013-11-20 16:21:52

11.2.0.4 +session_cached_cursors             integer    50

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0    Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> conn maclean/oracle
Connected.
SQL> select /* macleanparse */ 1 from testparse;

no rows selected

SQL> select distinct sid from v$mystat;

   SID
----------
   145

SQL> SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "%found in cache"
  2  FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
  3  WHERE cach.statistic# = nm1.statistic#
  4  AND nm1.name = 'session cursor cache hits'
  5  AND prs.statistic#=nm2.statistic#
  6  AND nm2.name= 'parse count (total)'
  7  AND cach.sid= 145 and prs.sid= cach.sid;

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      20       45          44.44

SQL>
SQL> select /* macleanparse */ 1 from testparse;

no rows selected

SQL>
SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

在session B 中对应每一步做查询

SQL> SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "%found in cache"
  2  FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
  3  WHERE cach.statistic# = nm1.statistic#
  4  AND nm1.name = 'session cursor cache hits'
  5  AND prs.statistic#=nm2.statistic#
  6  AND nm2.name= 'parse count (total)'
  7  AND cach.sid= 145 and prs.sid= cach.sid;

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      21       46          45.65

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      22       47          46.81

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      23       48          47.92

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      24       49          48.98

对应可以看到session cursor cache hits的增长

回复只看该作者道具举报

Maclean Liu(刘相兵

9^#

发表于 2013-11-20 16:24:17

11.2.0.4 + session_cached_cursors=0
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

session A sid 143

SQL> conn maclean/oracle
Connected.
SQL>
SQL> select /* macleanparse */ 1 from testparse;

no rows selected

SQL> select distinct sid from v$mystat;

   SID
----------
   143

SQL> select /* macleanparse */ 1 from testparse;

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /

no rows selected

session B 对应session A每一个步骤查询

SQL> SELECT cach.value cache_hits, prs.value all_parses,round((cach.value/prs.value)*100,2) as "%found in cache"
  2  FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
  3  WHERE cach.statistic# = nm1.statistic#
  4  AND nm1.name = 'session cursor cache hits'
  5  AND prs.statistic#=nm2.statistic#
  6  AND nm2.name= 'parse count (total)'
  7  AND cach.sid= 143 and prs.sid= cach.sid;

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      0       261             0

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      0       262             0

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      0       268             0

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      0       268             0

SQL> /

CACHE_HITS ALL_PARSES %found in cache
---------- ---------- ---------------
      0       268             0

看来仅仅是 PARSE_CALLS这个指标发生了变化

回复只看该作者道具举报

返回列表

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

[SQL调优] Difference parse call between 11203 and 11204 issue

浏览过的版块