軟解析、硬解析的一個小測試

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%'
3 ;

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 32127
parse count (hard) 30365
parse count (failures) 1

SQL>
SQL> begin
2 for i in 1..10000 loop
3 execute immediate 'insert into test values('||i||','||i||')';
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed

SQL>
SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42201
parse count (hard) 40379
parse count (failures) 1

沒有使用繫結變數(這種寫法往往有人誤認為使用了繫結,這裡就不多解釋了)
總解析次數：10074
硬解析次數：10014
上邊2個值除了我們的迴圈10000次外，還包括其他一些系統表的讀寫，所以解析次數大於10000，但是我們要注意下面的結果，對於每個SQL
都有一個版本，也就是ORACLE對於每個不同的SQL分別執行了硬解析，下面是共享池最後快取的資料(部分已經被覆蓋)

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into test values(9630,9630) 1 1 1
insert into test values(9950,9950) 1 1 1
insert into test values(9625,9625) 1 1 1
insert into test values(9592,9592) 1 1 1
insert into test values(9940,9940) 1 1 1
insert into test values(9897,9897) 1 1 1
insert into test values(9679,9679) 1 1 1
insert into test values(9850,9850) 1 1 1
insert into test values(9744,9744) 1 1 1
insert into test values(9938,9938) 1 1 1
insert into test values(9977,9977) 1 1 1
insert into test values(9907,9907) 1 1 1
insert into test values(9809,9809) 1 1 1
insert into test values(9800,9800) 1 1 1
insert into test values(9645,9645) 1 1 1
insert into test values(9724,9724) 1 1 1
insert into test values(9799,9799) 1 1 1
insert into test values(9818,9818) 1 1 1
insert into test values(9642,9642) 1 1 1
insert into test values(9624,9624) 1 1 1

中間內容略

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
insert into test values(9838,9838) 1 1 1
insert into test values(9716,9716) 1 1 1
insert into test values(9691,9691) 1 1 1
insert into test values(9831,9831) 1 1 1
insert into test values(9992,9992) 1 1 1
insert into test values(9883,9883) 1 1 1
insert into test values(9865,9865) 1 1 1
insert into test values(9901,9901) 1 1 1
insert into test values(9657,9657) 1 1 1
insert into test values(10000,10000) 1 1 1
insert into test values(9659,9659) 1 1 1
insert into test values(9746,9746) 1 1 1
insert into test values(9695,9695) 1 1 1
insert into test values(9869,9869) 1 1 1
insert into test values(9804,9804) 1 1 1
insert into test values(9843,9843) 1 1 1

435 rows selected

SQL> truncate talbe sess_event;

truncate talbe sess_event

ORA-03290: 無效的截斷命令 - 缺失 CLUSTER 或 TABLE 關鍵字

SQL> truncate table sess_event;

Table truncated

SQL> alter system flush shared_pool;

System altered

SQL>
SQL> insert into sess_event
2 select * from v$session_event
3 where sid=(select sid from v$mystat where rownum=1);

10 rows inserted

SQL>
SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42643
parse count (hard) 40456
parse count (failures) 2

SQL>
SQL> begin
2 for i in 1..10000 loop
3 execute immediate 'insert into test values(:v1,:v2)' using i,i;
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed

SQL>
SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42688
parse count (hard) 40466
parse count (failures) 2

下面看下使用繫結變數的情況(真正使用了繫結)
總解析次數：45
硬解析次數：10
我們可以看出差異是多麼大了，呵呵，對於SQL本身只有一次軟解析，執行次數為10000

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin for i in 1..10000 loop execute immediate 'insert into test values(:v1,:v2 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values(:v1,:v2) 1 1 10000
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL> alter system flush shared_pool;

System altered

但是我們要注意一個情況，即時同樣的SQL如果沒有使用繫結變數，ORACLE也會對其執行一次軟解析的，但是沒有硬解析，如下：
每執行一次SQL，也會同時產生其他寫系統表等很多相關的軟解析包括查詢SQL本身。硬解析不變。
SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values('1','1111111111') 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 1 1
insert into test values('1','1111111111') 1 2 2
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 2 2

SQL>

SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42906
parse count (hard) 40520
parse count (failures) 2

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1
/* OracleOEM */ BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
/* OracleOEM */ DECLARE instance_number NUMBER; latest_task_id NUMBER; 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 4 4
insert into test values('1','1111111111') 1 3 3
DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 3 3

8 rows selected

SQL>
SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42922
parse count (hard) 40520
parse count (failures) 2

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into test values('1','1111111111');

1 row inserted

SQL> commit;

Commit complete

SQL> select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper(sql_text) like '%TEST%';

SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
-------------------------------------------------------------------------------- ------------- ----------- ----------
begin dbsnmp.mgmt_response.get_latest_curs(:a); end; 1 1 1
/* OracleOEM */ BEGIN IF (:1 = 'READ WRITE') THEN ---- -- For a read- 1 1 1
/* OracleOEM */ DECLARE instance_number NUMBER; latest_task_id NUMBER; 1 1 1
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_dat 1 5 5
insert into test values('1','1111111111') 1 5 5
DELETE FROM MGMT_DB_LATEST_HDM_FINDINGS WHERE TARGET_GUID = :B2 AND COLLECTION_T 2 3 3
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 1 1
select SQL_TEXT,VERSION_COUNT,PARSE_CALLS,EXECUTIONS from v$sqlarea where upper( 1 4 4

8 rows selected

SQL>
SQL> SELECT NAME,VALUE FROM V$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
2 AND NAME LIKE 'parse count%';

NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 42946
parse count (hard) 40520
parse count (failures) 2

SQL>


通過這個例子我們也就知道V$SQLAREA中的解析包含什麼內容了。

/*******************METHOD OF GET SESSION INFO***********************/

以上例子我們直接從V$MYSTAT 獲得的SESSION相關資訊，類似測試中我們也可以利用一下方法來獲得SESSION相關的資訊。

SQL> CREATE GLOBAL TEMPORARY TABLE SESS_EVENT ON COMMIT PRESERVE ROWS AS SELECT * FROM V$SESSION_EVENT WHERE 1=0;

TABLE CREATED

SQL> INSERT INTO SESS_EVENT
SELECT * FROM V$SESSION_EVENT
WHERE SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);