
COMPUTER ENGINEERING
1999 25 10 Vol.25 No.10 1999



OracleClient/Serverܵ
Ŷ衡Ҧҫ
ժҪֺͨ̽оOracleClient/ServerصԭߡOracleӦϵͳܵһЩԭͷ
ؼʣOracleͻ/ϵͳȫI/Oع
Oracle Performance Tunign Based on Client/Server
Zhang Dong Wang Hong Yao Yaowen
(1.Dep.of Computer Sciences and Engineering.South China University of Technology,Guangzhou 510641;2.Dept.of Informatics,Guangzhou School of Finauce,Guangzhou 510521)
AbstractIn this paper discuses and studies the characteristic of Oracle service and theory about Oracle service based on Client/Server.It also gives the way to improve and ture the performance of Oracle based on Client/Server
Key wordsOracle;Cclient/Server;SGA;Network I/O;Rollback Segment
Oracle ݿ㷺ӦĸرClient/ServerģʽӦãӦÿϵͳ½⣬Ϊ˽⣬¼棺ݿI/OӦóȶϵͳԵַOracleЧܣϵͳܡ
1  ݿ
OracleݿϵͳĺģܸߵֱӰϵͳܣΪ˵OracleݿܣҪ¼濼ǣ
1.1  ϵͳʺOracleݿ
Oracleݿ̶ܴзĲϵͳϵͳṩܣôεOracleݿҲ޷Ӧеܡ
1.1.1  ΪOracleݿ滮ϵͳԴ
мԴ,  滮OracleԴԭǣʹOracleʹԴ,رClient/Serverо÷ԴOracle
1.1.2   ϵͳеڴ
ϵͳģϸڴ棬ʵӲϵһĴ̿ռ䡣ʵʵڴռ䲻ӦҪʱϵͳͽⲿֵĴ̿ռڴеϢҳ滻⽫ĴI/Oʹ½Ϊ˱ʹ棬ӦӴڴ档
1.1.3  ΪOracleݿòϵͳȼ
ҪڲϵͳеOracle̵ȼΪOracleݿϵͳУеĺ̨ǰ̨ݿִеͬҪĹҪͬȵȼڰװʱеݿ̶ʹȱʡȼС
1.2  ڴ
Oracleݿ3ڴٻ棬ֱӦ3ֲͬ͵ݣٻ棬ֵٻͻٻ档ٻֵٻһ𹹳ɹأټϻٻ㹹ϵͳȫ(SGA)SGAǶݿݽпٷʵһϵͳȫSGAҪƵؽͷš䣬ܴﵽٷݵĿģӦSGAУҪڴСڴĵҪָSGAڴṹĴСϵͳܣOracleݿڴṹӦأڴṹĵӦڴI/O֮ǰС
1.2.1 ⻺ĵ
⻺а˽ú͹SQLPL/SQLͨȽϿ⻺ʾĴСҪ⻺˽ÿ⻺Ļ⻺ĻͳϢڶ̬ܱv$librarycacheֵУͨѯñ˽Ծε
Select sum(pins),sum(reloads) from v$librarycache;
PinsиSQL䣬PL/SQL鼰ʶܴReloadsиSQL PL/SQLʽװʱڿ򻺳зĴsum(pins)/sum(reloads) 0⻺ʺʣsum(pins)/sum(reloads)>1, ʼ shared_pool_sizeµصڴ
1.2.2 ֵ仺ĵ
ֵ仺йݿĽṹûʵϢֵʣϵͳӰ켫ֵ仺ʹ¼ڶ̬ܱv$librarycacheУͨѯñ˽Ծε
Select sum(gets),sum(getmisses) from v$rowcache;
GetsǶӦͳƣGetmisses 𻺳ݵƵʵֵ仺sum(getmisses)/sum(gets)<10%15%ڴ˰ٷӦֵ仺ʼshared_pool_sizeµصڴ
1.2.3 ٻĵ
ûȡݶǾٻȡԸòֵʣҪٻʹ¼ڶ̬ܱv$sysstatУͨѯñ˽Ծε
Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');
dbblock getsconsistent getsֵݻжܴphysical readsֵʱжļĴӻٻжĿԵĸߵͳΪʣ㹫ʽ
Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))
Hit Ratio<60%70%Ӧdb_block_buffersĲֵdb_block_buffersԵٻڴdb_block_buffers÷仺ٻݿĸٻֽ=db_block_buffersֵ*db_block_sizeֵ db_block_size ֵʾݿСֽɲѯ v$parameter 
select name,value from v$parameter where name='db_block_size';
޸ݿĳʼԺ󣬱ȹرݿ⣬ݿʹµá
1.3   I/O
̵I/OٶȶϵͳҪӰ졣ôI/O⣬ܡӰI/OܵҪԭд̾I/Oݿռķ
ΪOracleݿļʱǱռõļ¼õ־ļӦϸݿϵĿôԴж̣ɽļɢ洢ôϣٶݿļ־ļľӶЧظƷܡڲͬӦϵͳиԵݼӦͬıռֱ洢ӦϵͳݣҾܵİѱռӦļڲͬĴϣִϰÿӦϵͳıռɢŵķųӦϵͳ̵Ŀԡļ־ļֱڲͬĴϣִеĴ̷ʲӦ־ǼǵĴ̷ʡж̿ã־ԱڲͬĴϣͿ־ļܲĴ̾ӦһӦõıݺݷɢŲͬռϣҾѲͬ͵ıռڲͬϣ˱ݺݵĴ̾
1.4  ݿĻع
عһ洢ݿʹøô洢һ»ɾеԭʼֵûҪعһĸı䣬ôݿʹӻعжظıǰݲʹӰиıΪǵԭ״̬عοݿ⴦ݿɹŹؼԵãݿƵöãƵòӰϵͳܡ͵عεԭ¡
1.4.1 ع
عָΪعδһϵıռ䣬ʹعֵ䡢ûݡȷ뿪ڻعεдݺдǲнеģ˽ԼI/Oáعݲ룬Ҫĳռѻôڸñռеĸعûȫѻ֮ǰܽռѻһñռ䲻ãñռелعҲʹã⽫˷зĴ̿ռ䡣ԣعοʹݿסعεľʹñռɿγƬعοԼݿռƬ
1.4.2 ͬСĻعȺ
һЩﴦһƵضݽ޸ģСĻعζ֮ÿһֻһعΣعӦóִһﴦ˶һЩϴҪϴ͵ĻعΡѻĻعϢʱҪһشŵĻعЩۣOracleݿ3ﴦ飺С顢ϴ顢شȴСͬĻعȺҽ֮ɢ3ͬıռϣȺڴСͬӦﴦҪ
1.4.3 ʵĻع
һع벢йأ¸ڲӦعεĲο
nع
n<164
16  n<328
n 32n/4
2   Client/Server ģʽµ I/O
Client/ServerеӦôǷֲڿͻӦóݿ֮ġ Client/ServerClientServer֮I/OϵͳߵƿһͻӦóI/OԽ٣ӦüϵͳԽáI/OҪһԭ򣺽Ӧ߼ݿС
2.1  ʹOracleݿԼ
ΪӦýʱӦΪһЩҪݼʵԼʵݿⱾӦóԼݷһݿ˵ԼִвڱSQL伶͵ϵͳŻͻ޹أֻڷУClient ˺Server֮䴫SQL䣬ЧؼI/O
2.2  ʹݿⴥ
ԼֻʵһЩϼ򵥵ԼһЩϸӵﴦΪʱòҪӦóʵʩӵĳƣӦݿⴥʵʩӵݿⴥʵݿⱾӦóԼݷϸӵﴦ򣬲״ڹI/O
磺ǰAгΪʷļ¼AתʷBУʾΪLsbs
Ӧóʵ֣ݿⴥʵ֣
BeignCreate trigger delete1
Update A set lsbs='T';After update of lsbs on A
Insert into BFor each row
Select * from A where lsbs='T';   Insert into B
Delete A where lsbs='T';select * from A where :new.lsbs='T';
End; Delete A where :new.lsbs='T';
End delete1; 
ӦóʵʱеSQL͵ݶҪͨClient˺Server˽нݿⴥһSQLServerˣҪͨ紫ݡв൱ʱҶûͬʱʱͨӦóʵָӵĿƣؽI/Oĸɣʹϵͳܽͣݿⴥȫ
2.3  ʹô洢̡洢Ͱ
OracleĴ洢̺ʹ洢һܲҴ洢Server˵PL/SQLļϡһְйصĹ̺ͺ֯װһݿԪķӦóĹ̡ԣSQL洢Serverˡʹô洢̺ʹ洢Ӧó򲻱ٰSQLȥִݿǼ򵥵ô洢̺ʹ洢ϴֻǵù̵ֺͿɼٴI/O
磺ABĶ壺name char(20)detail char(10)A100¼Ӧó򽫴ӻAмdetailзϸļ¼֮B
Declare
Cursor cursor1 is select*from A;poin cursor1%type
con1 number(2);res1 char(4)='abcd';
BeginInsert into B values(poin.name,poin.detail);end if;
 For poin in cursor1 loopEnd loop;End loop;
  For con1 in 1..7 loopCommit;
If substr(poin.detail ,con1,4)=res1 then End;
Developer/2000 FromаťֱPL/SQLʵֺͰдΪһOracle洢̣ȻFromеô˹ʵֱȽϣߡ
ڿʹ3ַʱȿʹԼԡݿⴥʹ洢̣ҪзݿĳԶʵʩһ飬ôʹݿⴥֻĳʵʩһĹ飬ɴһ̣йس̡
3  Ӧóĵ
3.1 SQLŻ
SQLִٶȣܺܶصӰ仯ҪӰǣִвȺ˳áɺܶ಻ͬķӵظıЩأԴﵽŵִٶȡҪֵ̽ԶӲѯʱӦѭŻԭ
(1)  ӵӾӦWhereӾӦǱܿ
(2)  ӲӦӷؽ
(3)  ӵıABAԶԶBӽϴA
(4)  WhereӾкѡ,Where No=20ѡԲַڱʽ
(5)  ֻһһıͨΪANoԱBNo ûӦBΪAΪ
(6)  ӵкWhereӾѡо򰴸ԲѯЧԺѡԷֱ𶨳𣬽ϱоݹѡŻ·һҪǣӾЩпʹЩΨһԼѯĿȡ
3.2  ʹͼ
ͼԽелнвüһݣܹ漰ĸӲѯͼķʽʹӦó򿪷١߲ѯܣٴ I/OŻݱĲѯSQLִСκʱܣʱӦѭԭ򣺸ñϲѯñ¡롢ɾȲѯĽ¼Ӧԭ2%4% 
3.3  ʹ Oracle ӿ
һͻӦóһлһѯĳʱǷ;ееǲ鴦ҪĶлĶлУȻͨٵļͿϴЩ顣磬һSelect䷵2000ݣÿƽСΪ40ֽڣݰĴСΪ4kBС(arraysize)Ϊ20 ӷ100ݰͻ򵥵ذ(arraysize)Ϊ2000ôͬĲֻҪ 20ݰͼĴӦõܡ
4  ܽ
ڿӦóʱѭķԭ򣬶ϵͳеյЧӦָڿͻ硢3໥ɲֶͬܲѵܣ˻Ӧϵͳľ͵
߼飺Ŷ1972Уозݿ
ߵλŶҦҫģʽѧѧϵ510641
裨ݽڸߵרѧУϢϵ510521
ο
1 Bobrowski S M.Mastering Oracle7&Client/Server Computing.ӹҵ,1996
2 .OracleݿԱ̳.ӹҵ磬1996
3 ͨѧԶϵͳо.Oracle7ʹü.ѧ磬1996
ոڣ1999-01-04
