oracle實驗記錄 (分析oracle硬解析&軟解析&fast soft parse(2))
關於pl/sql ,動態SQL 的parse
SQL> conn tr/a123
已連線。
SQL> select distinct sid from v$mystat;
SID
----------
140
SQL> select serial# from v$session where sid=140;
SERIAL#
----------
21
SQL> show user
USER 為 "TR"
SQL> create table t1 (a int, b int);
表已建立。
SQL> ed
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..1000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> execute sys.dbms_stats.gather_table_stats('tr','t1');
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 6
session cursor cache count 29
parse count (total) 58
parse count (hard) 9
SQL> execute dbms_system.set_sql_trace_in_session(140,21,true);
PL/SQL 過程已成功完成。
1 declare
2 begin
3 for i in 1..100 loop
4 execute immediate 'select * from t1 where a=1';
5 end loop;
6* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 6
session cursor cache count 29
parse count (total) 64
parse count (hard) 13
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
2867764059 2867764059
SQL> col sql_text format a30
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2867764059;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 1 1
loop execute immediate 'select
* from t1 where a=1'; end loo
p; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text='select * from t1 where a=1';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=1 1 100 1 1489680637
TR
SQL> /
PL/SQL 過程已成功完成。
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text='select * from t1 where a=1';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=1 1 200 2 1489680637
TR
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2867764059;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 2 2
loop execute immediate 'select
* from t1 where a=1'; end loo
p; end;
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 6
session cursor cache count 29
parse count (total) 66~~~
parse count (hard) 13
SQL> /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 6
session cursor cache count 30~~cache cursor了
parse count (total) 68~~~~
parse count (hard) 13
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2867764059;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 3 3
loop execute immediate 'select
* from t1 where a=1'; end loo
p; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value from v$sqlare
a where sql_text='select * from t1 where a=1';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
select * from t1 where a=1 1 300 3 1489680637
SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_34720.trc d:\t1.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期三 9月 30 14:23:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
declare
begin
for i in 1..100 loop
execute immediate 'select * from t1 where a=1';
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.00 0 0 0 0
Execute 3 0.43 0.51 0 0 0 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.45 0.52 0 0 0 3
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67
********************************************************************************
select *
from
t1 where a=1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 300 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 303 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)~~~~~~
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=400 us)
SQL> ed
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..100 loop
4 execute immediate 'select * from t1 where a='||i||'';
5 end loop;
6* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 8
session cursor cache count 30
parse count (total) 175
parse count (hard) 118~~~~~多了N 多HARD PARSE 基本是多了 101次
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
3622731519 3622731519
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3622731519;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 1 1
loop execute immediate 'select
* from t1 where a='||i||''; e
nd loop; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text like 'select * from t1 where a=%' and rownum<1
0
2 ;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=36 1 1 1 1421345405
TR
select * from t1 where a=55 1 1 1 667289837
TR
select * from t1 where a=49 1 1 1 3372485161
TR
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=34 1 1 1 4024442497
TR
select * from t1 where a=33 1 1 1 2085625811
TR
select * from t1 where a=92 1 1 1 3843959062
TR
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=8 1 1 1 4204407230
TR
select * from t1 where a=60 1 1 1 3229626287
TR
select * from t1 where a=88 1 1 1 639251647
TR
已選擇9行。
SQL> /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 9
session cursor cache count 30
parse count (total) 276~~~多了101次
parse count (hard) 118
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3622731519;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 2 2
loop execute immediate 'select
* from t1 where a='||i||''; e
nd loop; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text like 'select * from t1 where a=%' and rownum<1
0;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=36 1 2 2 1421345405
TR
select * from t1 where a=55 1 2 2 667289837
TR
select * from t1 where a=49 1 2 2 3372485161
TR
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=34 1 2 2 4024442497
TR
select * from t1 where a=33 1 2 2 2085625811
TR
select * from t1 where a=92 1 2 2 3843959062
TR
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=8 1 2 2 4204407230
TR
select * from t1 where a=60 1 2 2 3229626287
TR
select * from t1 where a=88 1 2 2 639251647
TR
已選擇9行。
SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_34720.trc d:\t1.tx
TKPROF: Release 10.2.0.1.0 - Production on 星期三 9月 30 14:43:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
declare
begin
for i in 1..100 loop
execute immediate 'select * from t1 where a='||i||'';
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.12 0.11 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.12 0.12 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67
select *
from
t1 where a=2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=4 us)
.............................
.............................
select *
from
t1 where a=100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=3 us)
1 declare
2 begin
3 for i in 1..100 loop
4 execute immediate 'select * from t1 where a=:x' using i;
5 end loop;
6* end;
7 /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 9
session cursor cache count 30
parse count (total) 278
parse count (hard) 120~~~~~2 次hard parse
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1335041921 1335041921
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1335041921;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 1 1
loop execute immediate 'select
* from t1 where a=:x' using i
; end loop; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text='select * from t1 where a=:x';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=:x 1 100 1 3289402228
TR
SQL> /
PL/SQL 過程已成功完成。
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text='select * from t1 where a=:x';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
select * from t1 where a=:x 1 200 2 3289402228~~~~~~~解析2次
TR
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1335041921;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 2 2~~~~~~~~~解析2次
loop execute immediate 'select
* from t1 where a=:x' using i
; end loop; end;
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 9~~沒變
session cursor cache count 30
parse count (total) 280~~~2次soft parse
parse count (hard) 120~~沒變
********************************************************************************
declare
begin
for i in 1..100 loop
execute immediate 'select * from t1 where a=:x' using i;
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.02 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67
********************************************************************************
select *
from
t1 where a=:x
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 200 0.01 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 202 0.01 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=268 us)
********************************************************************************
1 declare
2 begin
3 for i in 1..100 loop
4 insert into t1 values(1,1);
5 end loop;
6 commit;
7* end;
8 /
PL/SQL 過程已成功完成。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 9
session cursor cache count 30
parse count (total) 283
parse count (hard) 122
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
2403475982 2403475982
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2403475982;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 1 1
loop insert into t1 values(1,1
); end loop; commit; end;
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text like'INSERT INTO T1 VALUES%';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
INSERT INTO T1 VALUES(1,1) 1 100 1 2479797333
TR
INSERT INTO T1 VALUES(:B1 ,:B1 1 1000 1 7809332 ~~~~~~~~~~~~~建表時的
+1)
TR
SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_34720.trc d:\t1.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期三 9月 30 15:16:52 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
declare
begin
for i in 1..100 loop
insert into t1 values(1,1);
end loop;
commit;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67
********************************************************************************
INSERT INTO T1
VALUES
(1,1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 100 0.00 0.00 0 1 107 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.01 0.00 0 1 107 100
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
SQL> ed
已寫入 file afiedt.buf
1 declare
2 begin
3 for i in 1..100 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7* end;
8 /
PL/SQL 過程已成功完成。
SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=140;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
2278035423 2278035423
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2278035423;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 1 1
loop insert into t1 values(i,i
+1); end loop; commit; end;
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 10
session cursor cache count 30
parse count (total) 286
parse count (hard) 124~~~~2次hard parse
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text like'INSERT INTO T1 VALUES%';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
INSERT INTO T1 VALUES(1,1) 1 100 1 2479797333
TR
INSERT INTO T1 VALUES(:B1 ,:B1 2 1100 2 78093321
+1)
TR
SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_34720.trc d:\t1.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期三 9月 30 15:22:54 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
declare
begin
for i in 1..100 loop
insert into t1 values(i,i+1);
end loop;
commit;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.02 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 67
********************************************************************************
INSERT INTO T1
VALUES
(:B1 ,:B1 +1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 1 107 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.00 0 1 107 100
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
SQL> /
PL/SQL 過程已成功完成。
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS,hash_value,PARSING_SCHEM
A_NAME from v$sqlarea where sql_text like'INSERT INTO T1 VALUES%';
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS HASH_VALUE
------------------------------ ------------- ---------- ----------- ----------
PARSING_SCHEMA_NAME
------------------------------
INSERT INTO T1 VALUES(1,1) 1 100 1 2479797333
TR
INSERT INTO T1 VALUES(:B1 ,:B1 2 1200 3 78093321
+1)
TR
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=140 and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 15
session cursor cache hits 11~~~~~
session cursor cache count 30
parse count (total) 289~~~~~~~3次解析
parse count (hard) 124
SQL> select SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=2278035423;
SQL_TEXT VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
declare begin for i in 1..100 1 2 2
loop insert into t1 values(i,i
+1); end loop; commit; end;
declare
begin
for i in 1..100 loop
insert into t1 values(i,i+1);
end loop;
commit;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.01 0.00 0 0 0 0
Execute 3 0.01 0.02 0 0 1 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.03 0.03 0 0 1 2
Misses in library cache during parse: 1
Parsing user id: 67 (recursive depth: 1)
********************************************************************************
INSERT INTO T1
VALUES
(:B1 ,:B1 +1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 200 0.01 0.01 0 2 213 200
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 202 0.01 0.01 0 2 213 200
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 67 (recursive depth: 1)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-616812/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle實驗記錄 （分析oracle硬解析&軟解析&fast soft parse）OracleAST
- soft parse(軟解析),hard parse(硬解析)
- Oracle的軟解析(soft prase)和硬解析(hard prase)Oracle
- 關於軟解析(soft parse)與硬解析(hard parse),以及session cached cursors (asktom)Session
- ORACLE 硬解析和軟解析 軟軟解析Oracle
- Oracle 硬解析與軟解析Oracle
- fast parse,soft parse,hard parse的區別!AST
- Oracle的硬解析和軟解析Oracle
- 徹底弄懂oracle硬解析、軟解析、軟軟解析Oracle
- ORACLE SQL解析之硬解析和軟解析OracleSQL
- Oracle SQL的硬解析和軟解析OracleSQL
- oracle實驗記錄 （子游標與解析）Oracle
- oracle實驗記錄 （histogram是否影響解析）OracleHistogram
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（2）)Oracle
- Oracle中的遊標、硬解析、軟解析、軟軟解析、解析失敗Oracle
- 在oracle 10.2.0.5分析硬解析及軟解析及軟軟解析獲取shared pool latch機制系列五Oracle
- oracle實驗記錄 (buffer_cache分析（2）cbc latch)Oracle
- oracle實驗記錄（INDEX fast full scan 的成本計算）OracleIndexAST
- oracle實驗記錄 (oracle 分析shared pool(1))Oracle
- oracle實驗記錄 (oracle 詳細分析redo（1）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（3）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（4）)Oracle
- oracle實驗記錄 (oracle 詳細分析redo（5）)Oracle
- oracle breakable parse lock 易碎解析鎖Oracle
- Oracle - 共享遊標、父子游標、硬軟解析Oracle
- 【體系結構】sql語句解析過程小實驗 軟解析、硬解析SQL
- 軟解析和硬解析
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄 (buffer_cache分析（1）)Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- oracle實驗記錄 (oracle 10G 詳細分析undo)Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- ORACLE的軟 軟 軟 解析!Oracle