å 个ç®åçæ¥éª¤å¤§å¹
æé«Oracleæ§è½--æä¼åæ°æ®åºçä¸æ¿æ§ã
æ°æ®åºä¼åç讨论å¯ä»¥è¯´æ¯ä¸ä¸ªæ°¸æç主é¢ãèµæ·±çOracleä¼å人åé常ä¼è¦æ±æåºæ§è½é®é¢ç人对æ°æ®åºåä¸ä¸ªstatspackï¼è´´åºæ°æ®åºé
ç½®ççãè¿æç人认为è¦æåºæ§è¡ææ
¢çè¯å¥æ¥è¿è¡ä¼åãä½å®é
æ
åµæ¯ï¼æåºçé®ç人å¾å¯è½æ ¹æ¬ä¸ææ§è¡è®¡åï¼æ´ä¸è¦è¯´statspackäºãèæ认为ï¼æ°æ®åºä¼åï¼åºè¯¥é¦å
ä»å¤§çæ¹é¢èèï¼ç½ç»ãæå¡å¨ç¡¬ä»¶é
ç½®ãæä½ç³»ç»é
ç½®ãOracleæå¡å¨é
ç½®ãæ°æ®ç»æç»ç»ãç¶åææ¯å
·ä½çè°æ´ãå®é
ä¸ç½ç»ã硬件çå¾å¾æ æ³å³å®æ´æ¢ï¼åºç¨ç¨åºä¸è¬ä¹æ æ³ä¿®æ¹ï¼å æ¤åºè¯¥çéä»æ°æ®åºé
ç½®ãæ°æ®ç»æä¸æ¥ä¸æï¼é¦å
让æ°æ®åºæä¸ä¸ªè¯å¥½çé
ç½®ï¼ç¶ååèèå
·ä½ä¼åæäºè¿æ
¢çè¯å¥ãæå¨ç»æçç¨æ·ç³»ç»è¿è¡ä¼åçè¿ç¨ä¸ï¼æ»ç»äºä¸äºåºæ¬çï¼ç®åæè¡çåæ³æ¥ä¼åæ°æ®åºï¼ç®æ¯æçä¸æ¿æ§ï¼åµåµãä¸è¿è¯·æ³¨æï¼è¿äºä¸ä¸å®æ®é使ç¨ï¼çè³æçä¼æå¯ä½ç¨ï¼ä½æ¯å¯¹OLTPç³»ç»ãåºäºææ¬çæ°æ®åºå¾å¾è¡ä¹ææï¼ä¸å¦¨è¯è¯ãï¼æ³¨ï¼é件æ¯Burlesonåçç¨æ¥æ¥åæ°æ®åºæ§è½çä¿¡æ¯çèæ¬ï¼æ¬æç¨å°ï¼
ä¸ï¼è®¾ç½®åéçSGA
常常æ人æ±æ¨æå¡å¨ç¡¬ä»¶å¾å¥½ï¼ä½æ¯Oracleå°±æ¯å¾æ
¢ãå¾å¯è½æ¯å
ååé
ä¸åçé æçã(1)å设å
åæ512Mï¼è¿é常æ¯å°ååºç¨ã建议OracleçSGA大约240Mï¼å
¶ä¸ï¼å
±äº«æ± ï¼SHARED_POOL_SIZEï¼å¯ä»¥è®¾ç½®60Må°80Mï¼æ ¹æ®å®é
çç¨æ·æ°ãæ¥è¯¢çæ¥å®ãæ°æ®åç¼å²åºå¯ä»¥å¤§è´åé
120M-150Mï¼8iä¸éè¦è®¾ç½®DB_BLOCK_BUFFERSï¼DB_BLOCK_BUFFER*DB_BLOCK_SIZEçäºæ°æ®åç¼å²åºå¤§å°ã9i ä¸çæ°æ®ç¼å²åºå¯ä»¥ç¨db_cache_sizeæ¥ç´æ¥åé
ã
(2)å设å
åæ1Gï¼Oracle çSGAå¯ä»¥èèåé
500Mï¼å
±äº«æ± åé
100Må°150Mï¼æ°æ®ç¼å²åºåé
300Må°400Mã
(3)å
å2Gï¼SGAå¯ä»¥èèåé
1.2Gï¼å
±äº«æ± 300Må°500Mï¼å©ä¸çç»æ°æ®åç¼å²åºã
(4)å
å2G以ä¸ï¼å
±äº«æ± 300Må°500M就足å¤å¦ï¼åå¤ä¹æ²¡æ太大帮å©ï¼(Biti_rainyæä¸è¿°)æ°æ®ç¼å²åºæ¯å°½å¯è½ç大ï¼ä½æ¯ä¸å®è¦æ³¨æ两个é®é¢ï¼ä¸æ¯è¦ç»æä½ç³»ç»åå
¶ä»åºç¨çå¤å
åï¼äºæ¯å¯¹äº32ä½çæä½ç³»ç»ï¼OracleçSGAæ1.75Gçéå¶ãæç32ä½æä½ç³»ç»ä¸å¯ä»¥çªç ´è¿ä¸ªéå¶ï¼æ¹æ³è¿è¯·çBitiç大ä½å§ã
äºï¼åæ表åç´¢å¼ï¼æ´æ¹ä¼å模å¼
Oracleé»è®¤ä¼å模å¼æ¯CHOOSEï¼å¨è¿ç§æ
åµä¸ï¼å¦æ表没æç»è¿åæï¼ç»å¸¸å¯¼è´æ¥è¯¢ä½¿ç¨å
¨è¡¨æ«æï¼èä¸ä½¿ç¨ç´¢å¼ãè¿é常导è´ç£çI/O太å¤ï¼è导è´æ¥è¯¢å¾æ
¢ãå¦æ没æ使ç¨æ§è¡è®¡å稳å®æ§ï¼ååºè¯¥æ表åç´¢å¼é½åæä¸ä¸ï¼è¿æ ·å¯è½ç´æ¥ä¼ä½¿æ¥è¯¢é度大å¹
æåãåæ表å½ä»¤å¯ä»¥ç¨ANALYZE TABLE åæç´¢å¼å¯ä»¥ç¨ANALYZE INDEXå½ä»¤ã对äºå°äº100ä¸ç表ï¼å¯ä»¥èèåææ´ä¸ªè¡¨ï¼å¯¹äºå¾å¤§ç表ï¼å¯ä»¥æç¾åæ¯æ¥åæï¼ä½æ¯ç¾åæ¯ä¸è½è¿ä½ï¼å¦åçæçç»è®¡ä¿¡æ¯å¯è½ä¸åç¡®ãå¯ä»¥éè¿DBA_TABLESçLAST_ANALYZEDåæ¥æ¥ç表æ¯å¦ç»è¿åææåææ¶é´ï¼ç´¢å¼å¯ä»¥éè¿DBA_INDEXESçLAST_ANALYZEDåã
ä¸é¢éè¿ä¾åæ¥è¯´æåæååçé度对æ¯ãï¼è¡¨CASE_GA_AJZLZ大约æ35ä¸æ°æ®ï¼æ主é®ï¼é¦å
å¨SQLPLUSä¸æå¼èªå¨æ¥è¯¢æ§è¡è®¡ååè½ã(第ä¸æ¬¡è¦æ§è¡\RDBMS\ADMIN\utlxplan.sqlæ¥å建PLAN_TABLEè¿ä¸ªè¡¨)
ããSQL> SET AUTOTRACE ON
ããSQL>SET TIMING ON
éè¿SET AUTOTRACE ON æ¥æ¥çè¯å¥çæ§è¡è®¡åï¼éè¿SET TIMING ON æ¥æ¥çè¯å¥è¿è¡æ¶é´ã
ããSQL> seleCT count(*) from CASE_GA_AJZLZ;
ããCOUNT(*)
ãã----------
ãã346639
ããå·²ç¨æ¶é´: 00: 00: 21.38
ããExecution Plan
ãããã0 SELECT STATEMENT Optimizer=CHOOSE
ãã1 0 SORT (AGGREGATE)
ãã2 1 TABLE ACCESS (FULL) OF 'CASE_GA_AJZLZ'
ããâ¦â¦â¦â¦â¦â¦â¦â¦
请注æä¸é¢åæä¸çTABLE ACCESS(FULL)ï¼è¿è¯´æ该è¯å¥æ§è¡äºå
¨è¡¨æ«æãèä¸æ¥è¯¢ä½¿ç¨äº21.38ç§ãè¿æ¶è¡¨è¿æ²¡æç»è¿åæãä¸é¢æ们æ¥å¯¹è¯¥è¡¨è¿è¡åæï¼
ããSQL> analyze table CASE_GA_AJZLZ compute statistics;
表已åæãå·²ç¨æ¶é´: 00: 05: 357.63ãç¶ååæ¥æ¥è¯¢ï¼
ããSQL> select count(*) from CASE_GA_AJZLZ;
ããCOUNT(*)
ãã----------
ãã346639
ããå·²ç¨æ¶é´: 00: 00: 00.71
ããExecution Plan
ãã0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=351 Card=1)
ãã1 0 SORT (AGGREGATE)
ãã2 1 INDEX (FAST FULL SCAN) OF 'PK_AJZLZ' (UNIQUE) (Cost=351
ããCard=346351)
ããâ¦â¦â¦â¦â¦â¦â¦â¦â¦â¦
请注æï¼è¿æ¬¡æ¶é´ä»
ä»
ç¨äº0.71ç§ï¼è¿è¦å½åäºINDEX(FAST FULL SCAN)ãéè¿åæ表ï¼æ¥è¯¢ä½¿ç¨äºPK_AJZLZç´¢å¼ï¼ç£çI/O大å¹
åå°ï¼é度ä¹å¤§å¹
æåï¼ä¸é¢çå®ç¨è¯å¥å¯ä»¥ç¨æ¥çæåææ个ç¨æ·çææ表åç´¢å¼ï¼å设ç¨æ·æ¯GAXZUSRï¼
ããSQL> set pagesize 0
ããSQL> spool d:\analyze_tables.sql;
ããSQL> select 'analyze table '||owner||'.'||table_name||'
compute statistics;' from dba_tables where owner='GAXZUSR';
ããSQL> spool off
ããSQL> spool spool d:\analyze_indexes.sql;
ããSQL> select 'analyze index '||owner||'.'||index_name||'
compute statistics;' from dba_indexes where owner='GAXZUSR';
ããSQL> spool off
ããSQL> @d:\analyze_tables.sql
ããSQL> @d:\analyze_indexes.sql
解éï¼ä¸é¢çè¯å¥çæäºä¸¤ä¸ªsqlæ件ï¼åå«åæå
¨é¨çGAXZUSRç表åç´¢å¼ãå¦æéè¦æç
§ç¾åæ¯æ¥åæ表ï¼å¯ä»¥ä¿®æ¹ä¸ä¸èæ¬ãéè¿ä¸é¢çæ¥éª¤ï¼æ们就å®æäºå¯¹è¡¨åç´¢å¼çåæï¼å¯ä»¥æµè¯ä¸ä¸é度çæ¹è¿å¦ã建议å®æè¿è¡ä¸é¢çè¯å¥ï¼å°¤å
¶æ¯æ°æ®ç»è¿å¤§éæ´æ°ã
å½ç¶ï¼ä¹å¯ä»¥éè¿dbms_statsæ¥åæ表åç´¢å¼ï¼æ´æ¹ä¾¿ä¸äºãä½æ¯æä»ç¶ä¹ æ¯ä¸é¢çæ¹æ³ï¼å 为æåä¸å¦ä¼ç´æ¥æ示åºæ¥ã
å¦å¤ï¼æ们å¯ä»¥å°ä¼å模å¼è¿è¡ä¿®æ¹ãoptimizer_modeå¼å¯ä»¥æ¯RULEãCHOOSEãFIRST_ROWSåALL_ROWSã对äºOLTPç³»ç»ï¼å¯ä»¥æ¹æFIRST_ROWSï¼æ¥è¦æ±æ¥è¯¢å°½å¿«è¿åç»æãè¿æ ·å³ä½¿ä¸ç¨åæï¼å¨ä¸è¬æ
åµä¸ä¹å¯ä»¥æé«æ¥è¯¢æ§è½ãä½æ¯è¡¨åç´¢å¼ç»è¿åæåæå©äºæ¾å°æåéçæ§è¡è®¡åã
ä¸ï¼è®¾ç½®cursor_sharing=FORCE æSIMILAR
è¿ç§æ¹æ³æ¯8iæå¼å§æçï¼oracle805ä¸æ¯æãéè¿è®¾ç½®è¯¥åæ°ï¼å¯ä»¥å¼ºå¶å
±äº«åªææåä¸åçè¯å¥è§£é计åãä¾å¦ä¸é¢ä¸¤æ¡è¯å¥å¯ä»¥å
±äº«ï¼
ããSQL> SELECT * FROM MYTABLE WHERE NAME='tom'
ããSQL> SELECT * FROM MYTABLE WHERE NAME='turner'
è¿ä¸ªæ¹æ³å¯ä»¥å¤§å¹
éä½ç¼å²åºå©ç¨çä½çé®é¢ï¼é¿å
è¯å¥éæ°è§£éãéè¿è¿ä¸ªåè½ï¼å¯ä»¥å¾å¤§ç¨åº¦ä¸è§£å³ç¡¬è§£æ带æ¥çæ§è½ä¸éçé®é¢ã个人æè§å¯æ ¹æ®ç³»ç»çå®é
æ
åµï¼å³å®æ¯å¦å°è¯¥åæ°æ¹æFORCEã该åæ°é»è®¤æ¯exactãä¸è¿ä¸å®è¦æ³¨æï¼ä¿®æ¹ä¹åï¼å¿
é¡»å
ç»ORACLEæè¡¥ä¸ï¼å¦åæ¹ä¹åoracleä¼å ç¨100%çCPU,æ æ³ä½¿ç¨ã对äºORACLE9iï¼å¯ä»¥è®¾ç½®æSIMILARï¼è¿ä¸ªè®¾ç½®ç»¼åäºFORCEåEXACTçä¼ç¹ãä¸è¿è¯·æ
ç¨è¿ä¸ªåè½ï¼è¿ä¸ªåæ°ä¹å¯è½å¸¦æ¥å¾å¤§çè´é¢å½±åï¼
åï¼å°å¸¸ç¨çå°è¡¨ãç´¢å¼éå¨æ°æ®ç¼åKEEPæ± ä¸
å
åä¸æ°æ®è¯»åé度è¿è¿æ¯ç¡¬çä¸è¯»åè¦å¿«ï¼æ®ç§°ï¼å
åä¸æ°æ®è¯»çé度æ¯ç¡¬çç14000åï¼å¦æèµæºæ¯è¾ä¸°å¯ï¼æ常ç¨çå°çãèä¸ç»å¸¸è¿è¡å
¨è¡¨æ«æç表ç»éå
åä¸ï¼å½ç¶æ¯å¨å¥½ä¸è¿äºãå¯ä»¥ç®åçéè¿ALTER TABLE tablename CACHEæ¥å®ç°ï¼å¨ORACLE8iä¹åå¯ä»¥ä½¿ç¨ALTER TABLE table STORAGE(BUFFER_POOL KEEP)ãä¸è¬æ¥è¯´ï¼å¯ä»¥èèæ200æ°æ®åä¹å
ç表æ¾å¨keepæ± ä¸ï¼å½ç¶è¦æ ¹æ®å
å大å°çå ç´ æ¥å®ãå
³äºå¦ä½æ¥åºé£äºè¡¨æç´¢å¼ç¬¦åæ¡ä»¶ï¼å¯ä»¥ä½¿ç¨æ¬ææä¾çaccess.sqlåaccess_report.sqlãè¿ä¸¤ä¸ªèæ¬æ¯èåçOracleä¸å®¶ Burlesonåçï¼ä½ ä¹å¯ä»¥å¨è¯»æäºæ
åµä¸æ ¹æ®å®é
æ
åµè°æ´ä¸ä¸èæ¬ã对äºç´¢å¼ï¼å¯ä»¥éè¿ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)æ¥éå¨KEEPæ± ä¸ã
å°è¡¨å®å¨KEEPæ± ä¸éè¦åä¸äºåå¤å·¥ä½ã对äºORACLE9i éè¦è®¾ç½®DB_KEEP_CACHE_SIZEï¼å¯¹äº8iï¼éè¦è®¾ç½®buffer_pool_keepãå¨8iä¸ï¼è¿è¦ä¿®æ¹db_block_lru_latchesï¼è¯¥åæ°é»è®¤æ¯1ï¼æ æ³ä½¿ç¨buffer_pool_keepã该åæ°åºè¯¥æ¯2*3*CPUæ°éå°ï¼ä½æ¯è¦å¤§äº1ï¼æè½è®¾ç½®DB_KEEP_CACHE_BUFFERãbuffer_pool_keepä»db_block_buffersä¸åé
ï¼å æ¤ä¹è¦å°äºdb_block_buffersã设置好è¿äºåæ°åï¼å°±å¯ä»¥æ常ç¨å¯¹è±¡æ°¸ä¹
éå¨å
åéã
äºï¼è®¾ç½®optimizer_max_permutations
对äºå¤è¡¨è¿æ¥æ¥è¯¢ï¼å¦æéç¨åºäºææ¬ä¼å(CBO)ï¼ORACLEä¼è®¡ç®åºå¾å¤ç§è¿è¡æ¹æ¡ï¼ä»ä¸éæ©åºæä¼æ¹æ¡ãè¿ä¸ªåæ°å°±æ¯è®¾ç½®oracle究ç«ä»å¤å°ç§æ¹æ¡æ¥éæ©æä¼ãå¦æ设置太大ï¼é£ä¹è®¡ç®æä¼æ¹æ¡è¿ç¨ä¹æ¯æ¶é´æ¯è¾é¿çãOracle805å8ié»è®¤æ¯80000ï¼8建议æ¹æ2000ã对äº9iï¼å·²ç»é»è®¤æ¯2000äºã
å
ï¼è°æ´æåºåæ°
(1) SORT_AREA_SIZE:é»è®¤çç¨æ¥æåºçSORT_AREA_SIZE大å°æ¯32Kï¼é常æ¾å¾æç¹å°ï¼ä¸è¬å¯ä»¥èè设置æ1Mï¼1048576ï¼ãè¿ä¸ªåæ°ä¸è½è®¾ç½®è¿å¤§ï¼å 为æ¯ä¸ªè¿æ¥é½è¦åé
åæ ·çæåºå
åã
(2) SORT_MULTIBLOCK_READ_COUNT:å¢å¤§è¿ä¸ªåæ°å¯ä»¥æé«ä¸´æ¶è¡¨ç©ºé´æåºæ§è½ï¼è¯¥åæ°é»è®¤æ¯2ï¼å¯ä»¥æ¹æ32æ¥å¯¹æ¯ä¸ä¸æåºæ¥è¯¢æ¶é´ååã注æï¼è¿ä¸ªåæ°çæ大å¼ä¸å¹³å°æå
³ç³»ã
温馨提示:答案为网友推荐,仅供参考