Oracle中的體系結構：
oracle體系結構中的程序：
共享池相關的優化：
drop table t purge;
create table t as select * from dba_objects;
set linesize 1000
set autotrace on
set timing on
--第1次執行
select count(*) from t;
--第2次執行
--該指令隻是為了先不考慮解析的優化,單純考慮第2次執行實體讀減少帶來的優化效應
alter system flush shared_pool;
select count(*) from t;
綁定變量帶來的性能飛躍：
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values (:x)' using i;
end loop;
commit;
end;
/
select t.sql_text, t.sql_id, t.executions, t.parse_calls
from v$sql t
where sql_text like 'insert into t values%';
select t.sql_text, t.sql_id, t.executions, t.parse_calls
from v$sql t
where sql_text like 'insert into t values (:x)%';
SQL>--未使用綁定變量
SQL> begin
2 for i in 1 .. 100000
3 loop
4 execute immediate
5 'insert into t values ( '||i||')';
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 過程已成功完成。
已用時間: 00: 00: 43.50
SQL>--使用綁定變量
SQL> begin
2 for i in 1 .. 100000
3 loop
4 execute immediate
5 'insert into t values ( :x )' using i;
6 end loop;
7 commit;
8 end;
9 /
PL/SQL 過程已成功完成。
已用時間: 00: 00: 04.77 硬解析次數和執行次數：
實驗1
drop table t purge;
create table t ( x int );
alter system flush shared_pool;
exec dbms_workload_repository.create_snapshot();
set timing on
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( '||i||')';
end loop;
commit;
end;
/
exec dbms_workload_repository.create_snapshot();
@?/rdbms/admin/awrrpt.sql
實驗2
drop table t purge;
create table t ( x int );
alter system flush shared_pool;
exec dbms_workload_repository.create_snapshot();
set timing on
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;
commit;
end;
/
exec dbms_workload_repository.create_snapshot();
@?/rdbms/admin/awrrpt.sql
執行語句：
drop table t purge;
create table t ( x int );
set linesize 266
set pagesize 5000
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 12';
begin
for i in 1 .. 10000
loop
execute immediate
'insert into t values ( '||i||')';
end loop;
commit;
end;
alter session set events '10046 trace name context off'; --通過如下指令可以查出生成的trc檔案
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit 生成報表：
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_4516.trc d:\10046_no_bind.txt sys=no sort=prsela,exeela,fchela
--查詢10046.txt,發現總共是花費4.38秒，其中解析時間占用了3.3秒
---用10046來跟蹤另外一個使用綁定變量的寫法，如下：
drop table t purge;
create table t ( x int );
set linesize 266
set pagesize 5000
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 12';
begin
for i in 1 .. 10000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;
commit;
end;
/
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2908.trc d:\10046_bind.txt sys=no sort=prsela,exeela,fchela
--查詢10046.txt,發現總共是花費2.75秒，其中解析時間占用了0.03秒 靜态sql自動綁定變量：
drop table t purge;
create table t(x int);
alter system flush shared_pool;
select * from v$mystat where rownum=1;
set timing on
begin
for i in 1 .. 100000
loop
insert into t values (i);
end loop;
commit;
end;
/
select t.sql_text, t.sql_id, t.executions, t.parse_calls
from v$sql t
where lower(sql_text) like 'insert into t values%'; 參數對Sql性能的影響：
drop table t purge;
create table t ( x int );
set timing on
alter session set session_cached_cursors=0;
--使用綁定變量
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;
commit;
end;
/
drop table t purge;
create table t ( x int );
set timing on
alter session set session_cached_cursors=50;
--使用綁定變量
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;
commit;
end;
/
說明：
oracle有一個概念，那就是session cursor cache,中文描述就是有一塊記憶體區域，用來存儲關閉了的cursor。
當一個cursor關閉之後，oracle會檢查這個cursor的request次數是否超過3次，如果超過了三次，就會放入session cursor cache。
這樣在下次parse的時候，就可以從session cursor cache中找到這個statement, session cursor cache的管理也是使用LRU。
session_cached_cursors這個參數是控制session cursor cache的大小的。
session_cached_cursors定義了session cursor cache中存儲的cursor的個數。這個值越大，則會消耗的記憶體越多。
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
--測試普通插入
drop table test;
create table test as select * from dba_objects where 1=2;
set timing on
insert into test select * from t;
commit;
--注意這個普通方式插入試驗輸出的實體讀（首次讀）
set autotrace traceonly
select count(*) from test;
--測試直接路徑讀方式
drop table test;
create table test as select * from dba_objects where 1=2;
set timing on
insert /*+ append */ into test select * from t;
commit;
--注意這個直接路徑方式插入試驗輸出的實體讀（首次讀）
set autotrace traceonly
select count(*) from test; 批量送出與否性能差異：
set serveroutput on size 100000
drop table t purge;
create table t(x int);
set timing on
begin
for i in 1 .. 100000 loop
insert into t1 values (i);
commit;
end loop;
end;
/
drop table t purge;
create table t(x int);
begin
for i in 1 .. 100000 loop
insert into t values (i);
end loop;
commit;
end;
/ 日志關閉與否對性能的影響：
--環境準備(構造一個記錄有400萬左右的表）
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
--多插幾次，讓資料大一點
insert into t select * from t;
insert into t select * from t;
commit;
--測試直接路徑讀方式
drop table test;
create table test as select * from dba_objects where 1=2;
set timing on
insert /*+ append */ into test select * from t;
commit;
--測試nolgging關閉日志+直接路徑讀方式
drop table test;
create table test as select * from dba_objects where 1=2;
alter table test nologging;
set timing on
insert /*+ append */ into test select * from t;
commit;