《基于ORACLE的SQL优化读书》笔记 ORACLE里应用的类型

ORACLE里应用的类型：

硬解析：（open,parse,execute,fetch,close）

软件解析：SESSION_CACHED_CURSORS 设置为0（每次open,parse,bind,execute,fetch,close）

软软解析：SESSION_CACHED_CURSORS 设置>0,执行三次以上。（一次open,每次parse,bind,execute,fetch一次close）

一次解析，多次执行（一次open,prase,bind 每次execute,fetch,一次close）

分使用cursor和使用批量绑定+批量执行。

create table t1 as select * from dba_objects;

set serveroutput on size 1000000

 

@p_gen_multl_sql_with_bind_v.prc

@p_app_type_perf_diff_demo.prc

exec p_app_type_perf_diff_demo('TYPE1');

sys@XE> exec p_app_type_perf_diff_demo('TYPE1');

Total elapsed time is : +00 00:00:23.554000

Library cache pin allocation gets is : 20

Library cache pin allocation immediate gets is : 0

Library cache lock allocation gets is : 10

Library cache lock allocation immediate gets is : 0

Library cache lock gets is : 121145

Library cache lock immediate gets is : 0

Library cache gets is : 421949

Library cache immediate gets is : 11653

Library cache pin gets is : 142876

Library cache pin immediate gets is : 0

Library cache load lock gets is : 103

Library cache load lock immediate gets is : 0

 

PL/SQL procedure successfully completed.

 

exec p_app_type_perf_diff_demo('TYPE2');

 

sys@XE> exec p_app_type_perf_diff_demo('TYPE2');

Total elapsed time is : +00 00:00:05.328000

Library cache pin allocation gets is : 7

Library cache pin allocation immediate gets is : 0

Library cache lock allocation gets is : 18

Library cache lock allocation immediate gets is : 0

Library cache lock gets is : 42353

Library cache lock immediate gets is : 0

Library cache gets is : 85237

Library cache immediate gets is : 0

Library cache pin gets is : 43186

Library cache pin immediate gets is : 0

Library cache load lock gets is : 94

Library cache load lock immediate gets is : 0

 

PL/SQL procedure successfully completed.

 

exec p_app_type_perf_diff_demo('TYPE3');

sys@XE> exec p_app_type_perf_diff_demo('TYPE3');

Total elapsed time is : +00 00:00:05.095000

Library cache pin allocation gets is : 29

Library cache pin allocation immediate gets is : 0

Library cache lock allocation gets is : 58

Library cache lock allocation immediate gets is : 0

Library cache lock gets is : 3087

Library cache lock immediate gets is : 0

Library cache gets is : 46389

Library cache immediate gets is : 0

Library cache pin gets is : 43412

Library cache pin immediate gets is : 0

Library cache load lock gets is : 94

Library cache load lock immediate gets is : 0

 

exec p_app_type_perf_diff_demo('TYPE4');

sys@XE> exec p_app_type_perf_diff_demo('TYPE4');

Total elapsed time is : +00 00:00:05.126000

Library cache pin allocation gets is : 4

Library cache pin allocation immediate gets is : 0

Library cache lock allocation gets is : 8

Library cache lock allocation immediate gets is : 0

Library cache lock gets is : 978

Library cache lock immediate gets is : 0

Library cache gets is : 43835

Library cache immediate gets is : 0

Library cache pin gets is : 42587

Library cache pin immediate gets is : 0

Library cache load lock gets is : 96

Library cache load lock immediate gets is : 0

 

PL/SQL procedure successfully completed.

 

exec p_app_type_perf_diff_demo('TYPE4_ADVANCED');

sys@XE> exec p_app_type_perf_diff_demo('TYPE4_ADVANCED');

Total elapsed time is : +00 00:00:00.208000

Library cache pin allocation gets is : 7

Library cache pin allocation immediate gets is : 0

Library cache lock allocation gets is : 10

Library cache lock allocation immediate gets is : 0

Library cache lock gets is : 993

Library cache lock immediate gets is : 0

Library cache gets is : 23610

Library cache immediate gets is : 0

Library cache pin gets is : 22408

Library cache pin immediate gets is : 0

Library cache load lock gets is : 97

Library cache load lock immediate gets is : 0

 

PL/SQL procedure successfully completed.

 

select sql_text,sql_id,version_count,executions fromv$sqlarea where sql_text like 'select count(*) from t1%';

select parse_calls,executions from v$sql where sql_id='32sqj6wgs87ju';

sys@XE> select parse_calls,executions from v$sql wheresql_id='32sqj6wgs87ju';

 

PARSE_CALLS EXECUTIONS

----------- ----------

          1      10000