1ã1ãè°æ´æ°æ®ç»æç设计ãè¿ä¸é¨åå¨å¼åä¿¡æ¯ç³»ç»ä¹åå®æï¼ç¨åºåéè¦èèæ¯å¦ä½¿ç¨ORACLEæ°æ®åºçååºåè½ï¼å¯¹äºç»å¸¸è®¿é®çæ°æ®åºè¡¨æ¯å¦éè¦å»ºç«ç´¢å¼çã
2ã2ãè°æ´åºç¨ç¨åºç»æ设计ãè¿ä¸é¨åä¹æ¯å¨å¼åä¿¡æ¯ç³»ç»ä¹åå®æï¼ç¨åºåå¨è¿ä¸æ¥éè¦èèåºç¨ç¨åºä½¿ç¨ä»ä¹æ ·çä½ç³»ç»æï¼æ¯ä½¿ç¨ä¼ ç»çClient/Server两å±ä½ç³»ç»æï¼è¿æ¯ä½¿ç¨Browser/Web/Databaseçä¸å±ä½ç³»ç»æãä¸åçåºç¨ç¨åºä½ç³»ç»æè¦æ±çæ°æ®åºèµæºæ¯ä¸åçã
3ã3ãè°æ´æ°æ®åºSQLè¯å¥ãåºç¨ç¨åºçæ§è¡æç»å°å½ç»ä¸ºæ°æ®åºä¸çSQLè¯å¥æ§è¡ï¼å æ¤SQLè¯å¥çæ§è¡æçæç»å³å®äºORACLEæ°æ®åºçæ§è½ãORACLEå
¬å¸æ¨è使ç¨ORACLEè¯å¥ä¼åå¨ï¼Oracle Optimizerï¼åè¡é管çå¨ï¼row-level managerï¼æ¥è°æ´ä¼åSQLè¯å¥ã
4ã4ãè°æ´æå¡å¨å
ååé
ãå
ååé
æ¯å¨ä¿¡æ¯ç³»ç»è¿è¡è¿ç¨ä¸ä¼åé
ç½®çï¼æ°æ®åºç®¡çåå¯ä»¥æ ¹æ®æ°æ®åºè¿è¡ç¶åµè°æ´æ°æ®åºç³»ç»å
¨å±åºï¼SGAåºï¼çæ°æ®ç¼å²åºãæ¥å¿ç¼å²åºåå
±äº«æ± ç大å°ï¼è¿å¯ä»¥è°æ´ç¨åºå
¨å±åºï¼PGAåºï¼ç大å°ãéè¦æ³¨æçæ¯ï¼SGAåºä¸æ¯è¶å¤§è¶å¥½ï¼SGAåºè¿å¤§ä¼å ç¨æä½ç³»ç»ä½¿ç¨çå
åèå¼èµ·èæå
åç页é¢äº¤æ¢ï¼è¿æ ·åèä¼éä½ç³»ç»ã
5ã5ãè°æ´ç¡¬çI/Oï¼è¿ä¸æ¥æ¯å¨ä¿¡æ¯ç³»ç»å¼åä¹åå®æçãæ°æ®åºç®¡çåå¯ä»¥å°ç»æåä¸ä¸ªè¡¨ç©ºé´çæ°æ®æ件æ¾å¨ä¸åç硬çä¸ï¼åå°ç¡¬çä¹é´I/Oè´è½½åè¡¡ã
6ã6ãè°æ´æä½ç³»ç»åæ°ï¼ä¾å¦ï¼è¿è¡å¨UNIXæä½ç³»ç»ä¸çORACLEæ°æ®åºï¼å¯ä»¥è°æ´UNIXæ°æ®ç¼å²æ± ç大å°ï¼æ¯ä¸ªè¿ç¨æè½ä½¿ç¨çå
å大å°çåæ°ã
å®é
ä¸ï¼ä¸è¿°æ°æ®åºä¼åæªæ½ä¹é´æ¯ç¸äºèç³»çãORACLEæ°æ®åºæ§è½æ¶å表ç°åºæ¬ä¸é½æ¯ç¨æ·ååºæ¶é´æ¯è¾é¿ï¼éè¦ç¨æ·é¿æ¶é´ççå¾
ãä½æ§è½æ¶åçåå å´æ¯å¤ç§å¤æ ·çï¼ææ¶æ¯å¤ä¸ªå ç´ å
±åé æäºæ§è½æ¶åçç»æï¼è¿å°±éè¦æ°æ®åºç®¡çåææ¯è¾å
¨é¢ç计ç®æºç¥è¯ï¼è½å¤ææå°å¯è§å°å½±åæ°æ®åºæ§è½ç主è¦åå æå¨ãå¦å¤ï¼è¯å¥½çæ°æ®åºç®¡çå·¥å
·å¯¹äºä¼åæ°æ®åºæ§è½ä¹æ¯å¾éè¦çã
ORACLEæ°æ®åºæ§è½ä¼åå·¥å
·
常ç¨çæ°æ®åºæ§è½ä¼åå·¥å
·æï¼
1ã1ãORACLEæ°æ®åºå¨çº¿æ°æ®åå
¸ï¼ORACLEå¨çº¿æ°æ®åå
¸è½å¤åæ åºORACLEå¨æè¿è¡æ
åµï¼å¯¹äºè°æ´æ°æ®åºæ§è½æ¯å¾æ帮å©çã
2ã2ãæä½ç³»ç»å·¥å
·ï¼ä¾å¦UNIXæä½ç³»ç»çvmstatï¼iostatçå½ä»¤å¯ä»¥æ¥çå°ç³»ç»ç³»ç»çº§å
åå硬çI/Oç使ç¨æ
åµï¼è¿äºå·¥å
·å¯¹äºç®¡çåå¼æ¸
åºç³»ç»ç¶é¢åºç°å¨ä»ä¹å°æ¹ææ¶åå¾æç¨ã
3ã3ãSQLè¯è¨è·è¸ªå·¥å
·ï¼SQL TRACE FACILITYï¼ï¼SQLè¯è¨è·è¸ªå·¥å
·å¯ä»¥è®°å½SQLè¯å¥çæ§è¡æ
åµï¼ç®¡çåå¯ä»¥ä½¿ç¨èæ表æ¥è°æ´å®ä¾ï¼ä½¿ç¨SQLè¯å¥è·è¸ªæ件è°æ´åºç¨ç¨åºæ§è½ãSQLè¯è¨è·è¸ªå·¥å
·å°ç»æè¾åºæä¸ä¸ªæä½ç³»ç»çæ件ï¼ç®¡çåå¯ä»¥ä½¿ç¨TKPROFå·¥å
·æ¥çè¿äºæ件ã
4ã4ãORACLE Enterprise Managerï¼OEMï¼ï¼è¿æ¯ä¸ä¸ªå¾å½¢çç¨æ·ç®¡ççé¢ï¼ç¨æ·å¯ä»¥ä½¿ç¨å®æ¹ä¾¿å°è¿è¡æ°æ®åºç®¡çèä¸å¿
è®°ä½å¤æçORACLEæ°æ®åºç®¡ççå½ä»¤ã
5ã5ãEXPLAIN PLANââSQLè¯è¨ä¼åå½ä»¤ï¼ä½¿ç¨è¿ä¸ªå½ä»¤å¯ä»¥å¸®å©ç¨åºåååºé«æçSQLè¯è¨ã
ORACLEæ°æ®åºçç³»ç»æ§è½è¯ä¼°
ä¿¡æ¯ç³»ç»çç±»åä¸åï¼éè¦å
³æ³¨çæ°æ®åºåæ°ä¹æ¯ä¸åçãæ°æ®åºç®¡çåéè¦æ ¹æ®èªå·±çä¿¡æ¯ç³»ç»çç±»åçéèèä¸åçæ°æ®åºåæ°ã
1ã1ãå¨çº¿äºå¡å¤çä¿¡æ¯ç³»ç»ï¼OLTPï¼ï¼è¿ç§ç±»åçä¿¡æ¯ç³»ç»ä¸è¬éè¦æ大éçInsertãUpdateæä½ï¼å
¸åçç³»ç»å
æ¬æ°èªæºç¥¨åå®ç³»ç»ãé¶è¡å¨èç³»ç»çãOLTPç³»ç»éè¦ä¿è¯æ°æ®åºç并åæ§ãå¯é æ§åæç»ç¨æ·çé度ï¼è¿ç±»ç³»ç»ä½¿ç¨çORACLEæ°æ®åºéè¦ä¸»è¦èèä¸è¿°åæ°ï¼
l l æ°æ®åºåæ»æ®µæ¯å¦è¶³å¤ï¼
l l æ¯å¦éè¦å»ºç«ORACLEæ°æ®åºç´¢å¼ãèéãæ£åï¼
l l ç³»ç»å
¨å±åºï¼SGAï¼å¤§å°æ¯å¦è¶³å¤ï¼
l l SQLè¯å¥æ¯å¦é«æï¼
2ã2ãæ°æ®ä»åºç³»ç»ï¼Data Warehousingï¼ï¼è¿ç§ä¿¡æ¯ç³»ç»ç主è¦ä»»å¡æ¯ä»ORACLEçæµ·éæ°æ®ä¸è¿è¡æ¥è¯¢ï¼å¾å°æ°æ®ä¹é´çæäºè§å¾ãæ°æ®åºç®¡çåéè¦ä¸ºè¿ç§ç±»åçORACLEæ°æ®åºçéèèä¸è¿°åæ°ï¼
l l æ¯å¦éç¨B*-ç´¢å¼æè
bitmapç´¢å¼ï¼
l l æ¯å¦éç¨å¹¶è¡SQLæ¥è¯¢ä»¥æé«æ¥è¯¢æçï¼
l l æ¯å¦éç¨PL/SQLå½æ°ç¼ååå¨è¿ç¨ï¼
l l æå¿
è¦çè¯ï¼éè¦å»ºç«å¹¶è¡æ°æ®åºæé«æ°æ®åºçæ¥è¯¢æç
SQLè¯å¥çè°æ´åå
SQLè¯è¨æ¯ä¸ç§çµæ´»çè¯è¨ï¼ç¸åçåè½å¯ä»¥ä½¿ç¨ä¸åçè¯å¥æ¥å®ç°ï¼ä½æ¯è¯å¥çæ§è¡æçæ¯å¾ä¸ç¸åçãç¨åºåå¯ä»¥ä½¿ç¨EXPLAIN PLANè¯å¥æ¥æ¯è¾åç§å®ç°æ¹æ¡ï¼å¹¶éåºæä¼çå®ç°æ¹æ¡ãæ»å¾æ¥è®²ï¼ç¨åºååSQLè¯å¥éè¦æ»¡è¶³èèå¦ä¸è§åï¼
1ã1ãå°½é使ç¨ç´¢å¼ãè¯æ¯è¾ä¸é¢ä¸¤æ¡SQLè¯å¥ï¼
è¯å¥Aï¼SELECT dname, deptno FROM dept WHERE deptno NOT IN
(SELECT deptno FROM emp);
è¯å¥Bï¼SELECT dname, deptno FROM dept WHERE NOT EXISTS
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
è¿ä¸¤æ¡æ¥è¯¢è¯å¥å®ç°çç»ææ¯ç¸åçï¼ä½æ¯æ§è¡è¯å¥Açæ¶åï¼ORACLEä¼å¯¹æ´ä¸ªemp表è¿è¡æ«æï¼æ²¡æ使ç¨å»ºç«å¨emp表ä¸çdeptnoç´¢å¼ï¼æ§è¡è¯å¥Bçæ¶åï¼ç±äºå¨åæ¥è¯¢ä¸ä½¿ç¨äºèåæ¥è¯¢ï¼ORACLEåªæ¯å¯¹emp表è¿è¡çé¨åæ°æ®æ«æï¼å¹¶å©ç¨äºdeptnoåçç´¢å¼ï¼æ以è¯å¥Bçæçè¦æ¯è¯å¥Açæçé«ä¸äºã
2ã2ãéæ©èåæ¥è¯¢çèå次åºãèèä¸é¢çä¾åï¼
SELECT stuff FROM taba a, tabb b, tabc c
WHERE a.acol between :alow and :ahigh
AND b.bcol between :blow and :bhigh
AND c.ccol between :clow and :chigh
AND a.key1 = b.key1
AMD a.key2 = c.key2;
è¿ä¸ªSQLä¾åä¸ï¼ç¨åºåé¦å
éè¦éæ©è¦æ¥è¯¢ç主表ï¼å 为主表è¦è¿è¡æ´ä¸ªè¡¨æ°æ®çæ«æï¼æ以主表åºè¯¥æ°æ®éæå°ï¼æ以ä¾åä¸è¡¨Açacolåçèå´åºè¯¥æ¯è¡¨Bå表Cç¸åºåçèå´å°ã
3ã3ãå¨åæ¥è¯¢ä¸æ
é使ç¨INæè
NOT INè¯å¥ï¼ä½¿ç¨where (NOT) existsçææè¦å¥½çå¤ã
4ã4ãæ
é使ç¨è§å¾çèåæ¥è¯¢ï¼å°¤å
¶æ¯æ¯è¾å¤æçè§å¾ä¹é´çèåæ¥è¯¢ãä¸è¬å¯¹è§å¾çæ¥è¯¢æ好é½å解为对æ°æ®è¡¨çç´æ¥æ¥è¯¢ææè¦å¥½ä¸äºã
5ã5ãå¯ä»¥å¨åæ°æ件ä¸è®¾ç½®SHARED_POOL_RESERVED_SIZEåæ°ï¼è¿ä¸ªåæ°å¨SGAå
±äº«æ± ä¸ä¿çä¸ä¸ªè¿ç»çå
å空é´ï¼è¿ç»çå
å空é´æçäºåæ¾å¤§çSQLç¨åºå
ã
6ã6ãORACLEå
¬å¸æä¾çDBMS_SHARED_POOLç¨åºå¯ä»¥å¸®å©ç¨åºåå°æäºç»å¸¸ä½¿ç¨çåå¨è¿ç¨âéâå¨SQLåºä¸èä¸è¢«æ¢åºå
åï¼ç¨åºå对äºç»å¸¸ä½¿ç¨å¹¶ä¸å ç¨å
åå¾å¤çåå¨è¿ç¨âéâå°å
åä¸æå©äºæé«æç»ç¨æ·çååºæ¶é´ã
CPUåæ°çè°æ´
CPUæ¯æå¡å¨çä¸é¡¹éè¦èµæºï¼æå¡å¨è¯å¥½çå·¥ä½ç¶ææ¯å¨å·¥ä½é«å³°æ¶CPUç使ç¨çå¨90ï¼
以ä¸ãå¦æ空é²æ¶é´CPU使ç¨çå°±å¨90ï¼
以ä¸ï¼è¯´ææå¡å¨ç¼ºä¹CPUèµæºï¼å¦æå·¥ä½é«å³°æ¶CPU使ç¨çä»ç¶å¾ä½ï¼è¯´ææå¡å¨CPUèµæºè¿æ¯è¾å¯ä½ã
使ç¨æä½ç¸åå½ä»¤å¯ä»¥çå°CPUç使ç¨æ
åµï¼ä¸è¬UNIXæä½ç³»ç»çæå¡å¨ï¼å¯ä»¥ä½¿ç¨sar âuå½ä»¤æ¥çCPUç使ç¨çï¼NTæä½ç³»ç»çæå¡å¨ï¼å¯ä»¥ä½¿ç¨NTçæ§è½ç®¡çå¨æ¥æ¥çCPUç使ç¨çã
æ°æ®åºç®¡çåå¯ä»¥éè¿æ¥çv$sysstatæ°æ®åå
¸ä¸âCPU used by this sessionâç»è®¡é¡¹å¾ç¥ORACLEæ°æ®åºä½¿ç¨çCPUæ¶é´ï¼æ¥çâOS User level CPU timeâç»è®¡é¡¹å¾ç¥æä½ç³»ç»ç¨æ·æä¸çCPUæ¶é´ï¼æ¥çâOS System call CPU timeâç»è®¡é¡¹å¾ç¥æä½ç³»ç»ç³»ç»æä¸çCPUæ¶é´ï¼æä½ç³»ç»æ»çCPUæ¶é´å°±æ¯ç¨æ·æåç³»ç»ææ¶é´ä¹åï¼å¦æORACLEæ°æ®åºä½¿ç¨çCPUæ¶é´å æä½ç³»ç»æ»çCPUæ¶é´90ï¼
以ä¸ï¼è¯´ææå¡å¨CPUåºæ¬ä¸è¢«ORACLEæ°æ®åºä½¿ç¨çï¼è¿æ¯åçï¼åä¹ï¼è¯´ææå¡å¨CPU被å
¶å®ç¨åºå ç¨è¿å¤ï¼ORACLEæ°æ®åºæ æ³å¾å°æ´å¤çCPUæ¶é´ã
æ°æ®åºç®¡çåè¿å¯ä»¥éè¿æ¥çv$sesstatæ°æ®åå
¸æ¥è·å¾å½åè¿æ¥ORACLEæ°æ®åºå个ä¼è¯å ç¨çCPUæ¶é´ï¼ä»èå¾ç¥ä»ä¹ä¼è¯èç¨æå¡å¨CPUæ¯è¾å¤ã
åºç°CPUèµæºä¸è¶³çæ
åµæ¯å¾å¤çï¼SQLè¯å¥çé解æãä½æççSQLè¯å¥ãéå²çªé½ä¼å¼èµ·CPUèµæºä¸è¶³ã
1ãæ°æ®åºç®¡çåå¯ä»¥æ§è¡ä¸è¿°è¯å¥æ¥æ¥çSQLè¯å¥ç解ææ
åµï¼
SELECT * FROM V$SYSSTAT
WHERE NAME IN
('parse time cpu', 'parse time elapsed', 'parse count (hard)');
è¿éparse time cpuæ¯ç³»ç»æå¡æ¶é´ï¼parse time elapsedæ¯ååºæ¶é´ï¼ç¨æ·çå¾
æ¶é´
waite time = parse time elapsed â parse time cpu
ç±æ¤å¯ä»¥å¾å°ç¨æ·SQLè¯å¥å¹³å解æçå¾
æ¶é´ï¼waite time / parse countãè¿ä¸ªå¹³åçå¾
æ¶é´åºè¯¥æ¥è¿äº0ï¼å¦æå¹³å解æçå¾
æ¶é´è¿é¿ï¼æ°æ®åºç®¡çåå¯ä»¥éè¿ä¸è¿°è¯å¥
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA
ORDER BY PARSE_CALLS;
æ¥åç°æ¯ä»ä¹SQLè¯å¥è§£ææçæ¯è¾ä½ãç¨åºåå¯ä»¥ä¼åè¿äºè¯å¥ï¼æè
å¢å ORACLEåæ°SESSION_CACHED_CURSORSçå¼ã
2ãæ°æ®åºç®¡çåè¿å¯ä»¥éè¿ä¸è¿°è¯å¥ï¼
SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;
æ¥çä½æççSQLè¯å¥ï¼ä¼åè¿äºè¯å¥ä¹æå©äºæé«CPUçå©ç¨çã
3ã3ãæ°æ®åºç®¡çåå¯ä»¥éè¿v$system_eventæ°æ®åå
¸ä¸çâlatch freeâç»è®¡é¡¹æ¥çORACLEæ°æ®åºçå²çªæ
åµï¼å¦æ没æå²çªçè¯ï¼latch freeæ¥è¯¢åºæ¥æ²¡æç»æãå¦æå²çªå¤ªå¤§çè¯ï¼æ°æ®åºç®¡çåå¯ä»¥éä½spin_countåæ°å¼ï¼æ¥æ¶é¤é«çCPU使ç¨çã
å
ååæ°çè°æ´
å
ååæ°çè°æ´ä¸»è¦æ¯æORACLEæ°æ®åºçç³»ç»å
¨å±åºï¼SGAï¼çè°æ´ãSGA主è¦ç±ä¸é¨åææï¼å
±äº«æ± ãæ°æ®ç¼å²åºãæ¥å¿ç¼å²åºã
1ã 1ã å
±äº«æ± ç±ä¸¤é¨åææï¼å
±äº«SQLåºåæ°æ®åå
¸ç¼å²åºï¼å
±äº«SQLåºæ¯åæ¾ç¨æ·SQLå½ä»¤çåºåï¼æ°æ®åå
¸ç¼å²åºåæ¾æ°æ®åºè¿è¡çå¨æä¿¡æ¯ãæ°æ®åºç®¡çåéè¿æ§è¡ä¸è¿°è¯å¥ï¼
select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
æ¥æ¥çå
±äº«SQLåºç使ç¨çãè¿ä¸ªä½¿ç¨çåºè¯¥å¨90ï¼
以ä¸ï¼å¦åéè¦å¢å å
±äº«æ± ç大å°ãæ°æ®åºç®¡çåè¿å¯ä»¥æ§è¡ä¸è¿°è¯å¥ï¼
select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;
æ¥çæ°æ®åå
¸ç¼å²åºç使ç¨çï¼è¿ä¸ªä½¿ç¨çä¹åºè¯¥å¨90ï¼
以ä¸ï¼å¦åéè¦å¢å å
±äº«æ± ç大å°ã
2ã 2ã æ°æ®ç¼å²åºãæ°æ®åºç®¡çåå¯ä»¥éè¿ä¸è¿°è¯å¥ï¼
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');
æ¥æ¥çæ°æ®åºæ°æ®ç¼å²åºç使ç¨æ
åµãæ¥è¯¢åºæ¥çç»æå¯ä»¥è®¡ç®åºæ¥æ°æ®ç¼å²åºç使ç¨å½ä¸çï¼1 - ( physical reads / (db block gets + consistent gets) )ã
è¿ä¸ªå½ä¸çåºè¯¥å¨90ï¼
以ä¸ï¼å¦åéè¦å¢å æ°æ®ç¼å²åºç大å°ã
3ã 3ã æ¥å¿ç¼å²åºãæ°æ®åºç®¡çåå¯ä»¥éè¿æ§è¡ä¸è¿°è¯å¥ï¼
select name,value from v$sysstat where name in ('redo entries','redo log space requests');æ¥çæ¥å¿ç¼å²åºç使ç¨æ
åµãæ¥è¯¢åºçç»æå¯ä»¥è®¡ç®åºæ¥å¿ç¼å²åºçç³è¯·å¤±è´¥çï¼
ç³è¯·å¤±è´¥çï¼requests/entriesï¼ç³è¯·å¤±è´¥çåºè¯¥æ¥è¿äº0ï¼å¦å说ææ¥å¿ç¼å²åºå¼è®¾å¤ªå°ï¼éè¦å¢å ORACLEæ°æ®åºçæ¥å¿ç¼å²åºã
温馨提示:答案为网友推荐,仅供参考