151. 如何監控 SGA 中字典緩衝區的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100
"miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
152. 如何監控 SGA 中共用緩存區的命中率，應該小於1% ？
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit
radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
153. 如何顯示所有資料庫物件的類別和大小？
select count(name) num_instances ,type ,sum(source_size)
source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size
,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type order by 2;
154. 監控 SGA 中重做日誌緩存區的命中率，應該小於1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
155. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)',
'sorts (disk)');
156. 如何監控當前資料庫誰在運行什麽SQL語句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
157. 如何監控字典緩衝區？
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM
V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW
CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE
EXECUTING" FROM V$LIBRARYCACHE;
後者除以前者,此比率小於1%,接近0%爲好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY
CACHE GET MISSES"
FROM V$ROWCACHE
158. 監控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大於0.5時，參數需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where
type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時，參數需加大
159. 如何知道當前用戶的ID號
SQL>SHOW USER;
OR
SQL>select user from dual;
160. 如何查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY
segment_name
HAVING COUNT() = (SELECT MAX( COUNT() ) FROM dba_segments GROUP
BY segment_name);
162. 如何知道表在表空間中的存儲情況
select segment_name,sum(bytes),count(*) ext_quan from dba_extents
where
tablespace_name='&tablespace_name' and segment_type='TABLE' group
by tablespace_name,segment_name;
163. 如何知道索引在表空間中的存儲情況
select segment_name,count(*) from dba_extents where
segment_type='INDEX' and owner='&owner'
group by segment_name;
164、如何知道使用CPU多的用戶session
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40)
prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by
value desc;
165. 如何知道監聽器日誌文件
以8I爲例
$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
166. 如何知道監聽器參數文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
167. 如何知道TNS 連接文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
168. 如何知道Sql*Net 環境文件
以8I爲例
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
169. 如何知道警告日誌文件
以8I爲例
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
170. 如何知道基本結構
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
171. 如何知道建立資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
172. 如何知道建立審計用資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
173. 如何知道建立快照用資料字典視圖
以8I爲例
$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
本講主要講的是SQL語句的優化方法! 主要基於ORACLE9I的.
174. /+ALL_ROWS/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /+ALL+_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
175. /+FIRST_ROWS/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳回應時間,使資源消耗最小化.
例如:
SELECT /+FIRST_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
176. /+CHOOSE/
表明如果資料字典中有訪問表的統計資訊,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的優化方法;
例如:
SELECT /+CHOOSE/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
177. /+RULE/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE
EMP_NO='CCBZZP';
178. /+FULL(TABLE)/
表明對表選擇全局掃描的方法.
例如:
SELECT /+FULL(A)/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE
EMP_NO='CCBZZP';
179. /+ROWID(TABLE)/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /+ROWID(BSEMPMS)/ * FROM BSEMPMS WHERE
ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
180. /+CLUSTER(TABLE)/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
181. /+INDEX(TABLE INDEX_NAME)/
表明對表選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE
FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
182. /+INDEX_ASC(TABLE INDEX_NAME)/
表明對表選擇索引昇冪的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
DPT_NO='CCBZZP';
183. /+INDEX_COMBINE/
爲指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作爲參數的索引,將選擇出點陣圖索引的
布林組合方式.
例如:
SELECT /+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)/ * FROM
BSEMPMS
WHERE SAL<5000000 AND HIREDATE
1<sysdate;< span="">
2
3
4
5184\. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
6
7提示明確命令優化器使用索引作爲訪問路徑.
8
9例如:
10
11SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
12
13FROM BSEMPMS WHERE SAL<60000;
14
15
16
17185\. /*+INDEX_DESC(TABLE INDEX_NAME)*/
18
19表明對表選擇索引降冪的掃描方法.
20
21例如:
22
23SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE
24
25DPT_NO='CCBZZP';
26
27
28
29186\. /*+INDEX_FFS(TABLE INDEX_NAME)*/
30
31對指定的表執行快速全索引掃描,而不是全表掃描的辦法.
32
33例如:
34
35SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE
36
37DPT_NO='TEC305';
38
39
40
41187\. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
42
43提示明確進行執行規劃的選擇,將幾個單列索引的掃描合起來.
44
45例如:
46
47SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM
48
49BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
50
51
52
53188\. /*+USE_CONCAT*/
54
55對查詢中的WHERE後面的OR條件進行轉換爲UNION ALL的組合查詢.
56
57例如:
58
59SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
60
61SEX='M';
62
63189\. /*+NO_EXPAND*/
64
65對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴展.
66
67例如:
68
69SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND
70
71SEX='M';
72
73
74
75190\. /*+NOWRITE*/
76
77禁止對查詢塊的查詢重寫操作.
78
79
80
81191\. /*+REWRITE*/
82
83可以將視圖作爲參數.
84
85
86
87192\. /*+MERGE(TABLE)*/
88
89能夠對視圖的各個查詢進行相應的合併.
90
91例如:
92
93SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A
94
95(SELET DPT_NO
96
97,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
98
99A.DPT_NO=V.DPT_NO
100
101AND A.SAL>V.AVG_SAL;
102
103
104
105193\. /*+NO_MERGE(TABLE)*/
106
107對於有可合併的視圖不再合併.
108
109例如:
110
111SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS
112
113A (SELET DPT_NO
114
115,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE
116
117A.DPT_NO=V.DPT_NO
118
119AND A.SAL>V.AVG_SAL;
120
121
122
123194\. /*+ORDERED*/
124
125根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
126
127例如:
128
129SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2
130
131B,TABLE3 C
132
133WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
134
135
136
137195\. /*+USE_NL(TABLE)*/
138
139將指定表與嵌套的連接的行源進行連接,並把指定表作爲內部表.
140
141例如:
142
143SELECT /*+ORDERED USE_NL(BSEMPMS)*/
144
145BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS
146
147WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
148
149
150
151196\. /*+USE_MERGE(TABLE)*/
152
153將指定的表與其他行源通過合併排序連接方式連接起來.
154
155例如:
156
157SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS
158
159WHERE
160
161BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
162
163
164
165197\. /*+USE_HASH(TABLE)*/
166
167將指定的表與其他行源通過哈希連接方式連接起來.
168
169例如:
170
171SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
172
173BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
174
175
176
177198\. /*+DRIVING_SITE(TABLE)*/
178
179強制與ORACLE所選擇的位置不同的表進行查詢執行.
180
181例如:
182
183SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE
184
185BSEMPMS.DPT_NO=DEPT.DPT_NO;
186
187
188
189199\. /*+LEADING(TABLE)*/
190
191將指定的表作爲連接次序中的首表.
192
193
194
195200\. /*+CACHE(TABLE)*/
196
197當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
198
199例如:
200
201SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
202
203
204
205201\. /*+NOCACHE(TABLE)*/
206
207當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
208
209例如:
210
211SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
212
213
214
215202\. /*+APPEND*/
216
217直接插入到表的最後,可以提高速度.
218
219insert /*+append*/ into test1 select * from test4 ;
220
221
222
223203\. /*+NOAPPEND*/
224
225通過在插入語句生存期內停止並行模式來啓動常規插入.
226
227insert /*+noappend*/ into test1 select * from test4 ;
228
229
230
231ORACLE優化器
232
233. 選用適合的ORACLE優化器
234
235
236
237ORACLE的優化器共有3種:
238
239a. RULE (基於規則) b. COST (基於成本) c. CHOOSE (選擇性)
240
241
242
243設置缺省的優化器,可以通過對init.ora文件中OPTIMIZER_MODE參數的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS
244
245. 你當然也在SQL句級或是會話(session)級對其進行覆蓋.
246
247爲了使用基於成本的優化器(CBO, Cost-Based Optimizer) , 你必須經常運行analyze
248
249命令,以增加資料庫中的物件統計資訊(object statistics)的準確性.
250
251如果資料庫的優化器模式設置爲選擇性(CHOOSE),那麽實際的優化器模式將和是否運行過analyze命令有關.
252
253如果table已經被analyze過, 優化器模式將自動成爲CBO , 反之,資料庫將採用RULE形式的優化器.
254
255
256
257在缺省情況下,ORACLE採用CHOOSE優化器, 爲了避免那些不必要的全表掃描(full table scan) ,
258
259你必須儘量避免使用CHOOSE優化器,而直接採用基於規則或者基於成本的優化器.
260
261
262
2632\. 訪問Table的方式
264
265
266
267ORACLE 採用兩種訪問表中記錄的方式:
268
269
270
271a. 全表掃描
272
273全表掃描就是順序地訪問表中每條記錄. ORACLE採用一次讀入多個資料塊(database block)的方式優化全表掃描.
274
275
276
277b. 通過ROWID訪問表
278
279你可以採用基於ROWID的訪問方式情況,提高訪問表的效率, ,
280
281ROWID包含了表中記錄的物理位置資訊..ORACLE採用索引(INDEX)實現了資料和存放資料的物理位置(ROWID)之間的聯繫.
282
283通常索引提供了快速訪問ROWID的方法,因此那些基於索引列的查詢就可以得到性能上的提高.
284
285
286
2873\. 共用SQL語句
288
289
290
291爲了不重復解析相同的SQL語句,在第一次解析之後, ORACLE將SQL語句存放在記憶體中.這塊位於系統全局區域SGA(system
292
293global area)的共用池(shared buffer pool)中的記憶體可以被所有的資料庫用戶共用.
294
295因此,當你執行一個SQL語句(有時被稱爲一個游標)時,如果它
296
297和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好的
298
299執行路徑. ORACLE的這個功能大大地提高了SQL的執行性能並節省了記憶體的使用.
300
301可惜的是ORACLE只對簡單的表提供高速緩衝(cache buffering) ,這個功能並不適用於多表連接查詢.
302
303資料庫管理員必須在init.ora中爲這個區域設置合適的參數,當這個記憶體區域越大,就可以保留更多的語句,當然被共用的可能性也就越大了.
304
305當你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊記憶體中查找相同的語句.
306
307這裏需要注明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共用,SQL語句必須
308
309完全相同(包括空格,換行等).
310
311共用的語句必須滿足三個條件:
312
313
314
315A. 字元級的比較:
316
317當前被執行的語句和共用池中的語句必須完全相同.
318
319例如:
320
321SELECT * FROM EMP;
322
323和下列每一個都不同
324
325SELECT * from EMP;
326
327Select * From Emp;
328
329SELECT * FROM EMP;
330
331B. 兩個語句所指的物件必須完全相同:
332
333例如:
334
335用戶 物件名 如何訪問
336
337Jack sal_limit private synonym
338
339Work_city public synonym
340
341Plant_detail public synonym
342
343
344
345Jill sal_limit private synonym
346
347Work_city public synonym
348
349Plant_detail table owner
350
351
352
353考慮一下下列SQL語句能否在這兩個用戶之間共用.
354
355SQL
356
357能否共用
358
359原因
360
361select max(sal_cap) from sal_limit;
362
363不能
364
365每個用戶都有一個private synonym - sal_limit , 它們是不同的物件
366
367select count(*0 from work_city where sdesc like 'NEW%';
368
369能
370
371兩個用戶訪問相同的物件public synonym - work_city
372
373select a.sdesc,b.location from work_city a , plant_detail b where
374
375a.city_id = b.city_id
376
377不能
378
379用戶jack 通過private synonym訪問plant_detail 而jill 是表的所有者,物件不同.
380
381C. 兩個SQL語句中必須使用相同的名字的綁定變數(bind variables)
382
383
384
385例如：
386
387
388
389第一組的兩個SQL語句是相同的(可以共用),而第二組中的兩個語句是不同的(即使在運行時,賦於不同的綁定變數相同的值)
390
391a.
392
393select pin , name from people where pin = :blk1.pin;
394
395select pin , name from people where pin = :blk1.pin;
396
397
398
399b.
400
401select pin , name from people where pin = :blk1.ot_ind;
402
403select pin , name from people where pin = :blk1.ov_ind;
404
4054\. 選擇最有效率的表名順序(只在基於規則的優化器中有效)
406
407ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最後的表(基礎表 driving
408
409table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作爲基礎表.當ORACLE處理多個表時,
410
411會運用排序及合併的方式連接它們.首先,掃描第一個表(FROM子句中最後的那個表)並對記錄進行派序,然後掃描第二個表(FROM子句中最後第二個表),最後將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合併.
412
413
414
415例如:
416
417表 TAB1 16,384 條記錄
418
419表 TAB2 1 條記錄
420
421
422
423選擇TAB2作爲基礎表 (最好的方法)
424
425select count(*) from tab1,tab2 執行時間0.96秒
426
427
428
429選擇TAB2作爲基礎表 (不佳的方法)
430
431select count(*) from tab2,tab1 執行時間26.09秒
432
433
434
435如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作爲基礎表,
436
437交叉表是指那個被其他表所引用的表.
438
439
440
441例如:
442
443
444
445EMP表描述了LOCATION表和CATEGORY表的交集.
446
447
448
449SELECT *
450
451FROM LOCATION L ,
452
453CATEGORY C,
454
455EMP E
456
457WHERE E.EMP_NO BETWEEN 1000 AND 2000
458
459AND E.CAT_NO = C.CAT_NO
460
461AND E.LOCN = L.LOCN
462
463
464
465將比下列SQL更有效率
466
467
468
469SELECT *
470
471FROM EMP E ,
472
473LOCATION L ,
474
475CATEGORY C
476
477WHERE E.CAT_NO = C.CAT_NO
478
479AND E.LOCN = L.LOCN
480
481AND E.EMP_NO BETWEEN 1000 AND 2000
482
4835\. WHERE子句中的連接順序．
484
485
486
487ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前,
488
489那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
490
491
492
493例如:
494
495
496
497(低效,執行時間156.3秒)
498
499SELECT …
500
501FROM EMP E
502
503WHERE SAL > 50000
504
505AND JOB = ‘MANAGER’
506
507AND 25 < (SELECT COUNT(*) FROM EMP
508
509WHERE MGR=E.EMPNO);
510
511
512
513(高效,執行時間10.6秒)
514
515SELECT …
516
517FROM EMP E
518
519WHERE 25 < (SELECT COUNT(*) FROM EMP
520
521WHERE MGR=E.EMPNO)
522
523AND SAL > 50000
524
525AND JOB = ‘MANAGER’;
526
5276\. SELECT子句中避免使用 ‘ * ‘
528
529當你想在SELECT子句中列出所有的COLUMN時,使用動態SQL列引用 ‘*’
530
531是一個方便的方法.不幸的是,這是一個非常低效的方法. 實際上,ORACLE在解析的過程中, 會將’*’ 依次轉換成所有的列名,
532
533這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間.
534
5357\. 減少訪問資料庫的次數
536
537當執行每條SQL語句時, ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀資料塊等等.
538
539由此可見, 減少訪問資料庫的次數 , 就能實際上減少ORACLE的工作量.
540
541
542
543例如,
544
545以下有三種方法可以檢索出雇員號等於0342或0291的職員.
546
547
548
549方法1 (最低效)
550
551SELECT EMP_NAME , SALARY , GRADE
552
553FROM EMP
554
555WHERE EMP_NO = 342;
556
557
558
559SELECT EMP_NAME , SALARY , GRADE
560
561FROM EMP
562
563WHERE EMP_NO = 291;
564
565
566
567方法2 (次低效)
568
569
570
571DECLARE
572
573CURSOR C1 (E_NO NUMBER) IS
574
575SELECT EMP_NAME,SALARY,GRADE
576
577FROM EMP
578
579WHERE EMP_NO = E_NO;
580
581BEGIN
582
583OPEN C1(342);
584
585FETCH C1 INTO …,..,.. ;
586
587…..
588
589OPEN C1(291);
590
591FETCH C1 INTO …,..,.. ;
592
593CLOSE C1;
594
595END;
596
597
598
599方法3 (高效)
600
601
602
603SELECT A.EMP_NAME , A.SALARY , A.GRADE,
604
605B.EMP_NAME , B.SALARY , B.GRADE
606
607FROM EMP A,EMP B
608
609WHERE A.EMP_NO = 342
610
611AND B.EMP_NO = 291;
612
613注意:
614
615在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索資料量
616
617,建議值爲200
618
619
620
6218\. 使用DECODE函數來減少處理時間
622
623使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表.
624
625
626
627例如:
628
629SELECT COUNT(*)，SUM(SAL)
630
631FROM EMP
632
633WHERE DEPT_NO = 0020
634
635AND ENAME LIKE ‘SMITH%’;
636
637
638
639SELECT COUNT(*)，SUM(SAL)
640
641FROM EMP
642
643WHERE DEPT_NO = 0030
644
645AND ENAME LIKE ‘SMITH%’;
646
647
648
649你可以用DECODE函數高效地得到相同結果
650
651
652
653SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
654
655COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
656
657SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
658
659SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
660
661FROM EMP WHERE ENAME LIKE ‘SMITH%’;
662
663
664
665類似的,DECODE函數也可以運用於GROUP BY 和ORDER BY子句中.
666
6679\. 整合簡單,無關聯的資料庫訪問
668
669
670
671如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關係)
672
673例如:
674
675
676
677SELECT NAME
678
679FROM EMP
680
681WHERE EMP_NO = 1234;
682
683
684
685SELECT NAME
686
687FROM DPT
688
689WHERE DPT_NO = 10 ;
690
691
692
693SELECT NAME
694
695FROM CAT
696
697WHERE CAT_TYPE = ‘RD’;
698
699
700
701上面的3個查詢可以被合併成一個:
702
703
704
705SELECT E.NAME , D.NAME , C.NAME
706
707FROM CAT C , DPT D , EMP E,DUAL X
708
709WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
710
711AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
712
713AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
714
715AND E.EMP_NO(+) = 1234
716
717AND D.DEPT_NO(+) = 10
718
719AND C.CAT_TYPE(+) = ‘RD’;
720
721
722
723(譯者按: 雖然採取這種方法,效率得到提高,但是程式的可讀性大大降低,所以讀者 還是要權衡之間的利弊)
724
725
726
72710\. 刪除重復記錄
728
729最高效的刪除重復記錄方法 ( 因爲使用了ROWID)
730
731
732
733DELETE FROM EMP E
734
735WHERE E.ROWID > (SELECT MIN(X.ROWID)
736
737FROM EMP X
738
739WHERE X.EMP_NO = E.EMP_NO);
740
741
742
74311\. 用TRUNCATE替代DELETE
744
745當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的資訊.
746
747如果你沒有COMMIT事務,ORACLE會將資料恢復到刪除之前的狀態(準確地說是
748
749恢復到執行刪除命令之前的狀況)
750
751
752
753而當運用TRUNCATE時,
754
755回滾段不再存放任何可被恢復的資訊.當命令運行後,資料不能被恢復.因此很少的資源被調用,執行時間也會很短.
756
757
758
759(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)
760
76112\. 儘量多使用COMMIT
762
763
764
765只要有可能,在程式中儘量多使用COMMIT, 這樣程式的性能得到提高,需求也會因爲COMMIT所釋放的資源而減少:
766
767COMMIT所釋放的資源:
768
769a. 回滾段上用於恢復資料的資訊.
770
771b. 被程式語句獲得的鎖
772
773c. redo log buffer 中的空間
774
775d. ORACLE爲管理上述3種資源中的內部花費
776
777
778
779(譯者按: 在使用COMMIT時必須要注意到事務的完整性,現實中效率和事務完整性往往是魚和熊掌不可得兼)
780
781
782
78313\. 計算記錄條數
784
785和一般的觀點相反, count(*) 比count(1)稍快 , 當然如果可以通過索引檢索,對索引列的計數仍舊是最快的. 例如
786
787COUNT(EMPNO)
788
789
790
791(譯者按: 在CSDN論壇中,曾經對此有過相當熱烈的討論,
792
793作者的觀點並不十分準確,通過實際的測試,上述三種方法並沒有顯著的性能差別)
794
795
796
79714\. 用Where子句替換HAVING子句
798
799
800
801避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作.
802
803如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷.
804
805
806
807例如:
808
809
810
811低效:
812
813SELECT REGION，AVG(LOG_SIZE)
814
815FROM LOCATION
816
817GROUP BY REGION
818
819HAVING REGION REGION != ‘SYDNEY’
820
821AND REGION != ‘PERTH’
822
823
824
825高效
826
827SELECT REGION，AVG(LOG_SIZE)
828
829FROM LOCATION
830
831WHERE REGION REGION != ‘SYDNEY’
832
833AND REGION != ‘PERTH’
834
835GROUP BY REGION
836
837(譯者按: HAVING 中的條件一般用於對一些集合函數的比較,如COUNT() 等等.
838
839除此而外,一般的條件應該寫在WHERE子句中)
840
841
842
84315\. 減少對表的查詢
844
845在含有子查詢的SQL語句中,要特別注意減少對表的查詢.
846
847
848
849例如:
850
851低效
852
853SELECT TAB_NAME
854
855FROM TABLES
856
857WHERE TAB_NAME = ( SELECT TAB_NAME
858
859FROM TAB_COLUMNS
860
861WHERE VERSION = 604)
862
863AND DB_VER= ( SELECT DB_VER
864
865FROM TAB_COLUMNS
866
867WHERE VERSION = 604)
868
869
870
871高效
872
873SELECT TAB_NAME
874
875FROM TABLES
876
877WHERE (TAB_NAME,DB_VER)
878
879= ( SELECT TAB_NAME,DB_VER)
880
881FROM TAB_COLUMNS
882
883WHERE VERSION = 604)
884
885
886
887Update 多個Column 例子:
888
889低效:
890
891UPDATE EMP
892
893SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
894
895SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
896
897WHERE EMP_DEPT = 0020;
898
899
900
901高效:
902
903UPDATE EMP
904
905SET (EMP_CAT, SAL_RANGE)
906
907= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
908
909FROM EMP_CATEGORIES)
910
911WHERE EMP_DEPT = 0020;
912
91316\. 通過內部函數提高SQL效率.
914
915
916
917SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
918
919FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
920
921WHERE H.EMPNO = E.EMPNO
922
923AND H.HIST_TYPE = T.HIST_TYPE
924
925GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
926
927
928
929通過調用下面的函數可以提高效率.
930
931FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
932
933AS
934
935TDESC VARCHAR2(30);
936
937CURSOR C1 IS
938
939SELECT TYPE_DESC
940
941FROM HISTORY_TYPE
942
943WHERE HIST_TYPE = TYP;
944
945BEGIN
946
947OPEN C1;
948
949FETCH C1 INTO TDESC;
950
951CLOSE C1;
952
953RETURN (NVL(TDESC,’ ’));
954
955END;
956
957
958
959FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
960
961AS
962
963ENAME VARCHAR2(30);
964
965CURSOR C1 IS
966
967SELECT ENAME
968
969FROM EMP
970
971WHERE EMPNO=EMP;
972
973BEGIN
974
975OPEN C1;
976
977FETCH C1 INTO ENAME;
978
979CLOSE C1;
980
981RETURN (NVL(ENAME,’ ’));
982
983END;
984
985
986
987SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
988
989H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
990
991FROM EMP_HISTORY H
992
993GROUP BY H.EMPNO , H.HIST_TYPE;
994
995
996
997(譯者按: 經常在論壇中看到如 ’能不能用一個SQL寫出….’ 的貼子, 殊不知複雜的SQL往往犧牲了執行效率.
998
999能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的)
1000
1001
1002
100317\. 使用表的別名(Alias)
1004
1005當在SQL語句中連接多個表時,
1006
1007請使用表的別名並把別名字首於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.
1008
1009
1010
1011(譯者注:
1012
1013Column歧義指的是由於SQL中不同的表具有相同的Column名,當SQL語句中出現這個Column時,SQL解析器無法判斷這個Column的歸屬)
1014
1015
1016
101718\. 用EXISTS替代IN
1018
1019在許多基於基礎表的查詢中,爲了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT
1020
1021EXISTS)通常將提高查詢的效率.
1022
1023
1024
1025低效:
1026
1027SELECT *
1028
1029FROM EMP (基礎表)
1030
1031WHERE EMPNO > 0
1032
1033AND DEPTNO IN (SELECT DEPTNO
1034
1035FROM DEPT
1036
1037WHERE LOC = ‘MELB’)
1038
1039
1040
1041高效:
1042
1043SELECT *
1044
1045FROM EMP (基礎表)
1046
1047WHERE EMPNO > 0
1048
1049AND EXISTS (SELECT ‘X’
1050
1051FROM DEPT
1052
1053WHERE DEPT.DEPTNO = EMP.DEPTNO
1054
1055AND LOC = ‘MELB’)
1056
1057(譯者按: 相對來說,用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節中將指出)
1058
105919\. 用NOT EXISTS替代NOT IN
1060
1061在子查詢中,NOT IN子句將執行一個內部的排序和合併. 無論在哪種情況下,NOT IN都是最低效的
1062
1063(因爲它對子查詢中的表執行了一個全表遍曆). 爲了避免使用NOT IN ,我們可以把它改寫成外連接(Outer Joins)或NOT
1064
1065EXISTS.
1066
1067
1068
1069例如:
1070
1071SELECT …
1072
1073FROM EMP
1074
1075WHERE DEPT_NO NOT IN (SELECT DEPT_NO
1076
1077FROM DEPT
1078
1079WHERE DEPT_CAT=’A’);
1080
1081
1082
1083爲了提高效率.改寫爲:
1084
1085
1086
1087(方法一: 高效)
1088
1089SELECT ….
1090
1091FROM EMP A,DEPT B
1092
1093WHERE A.DEPT_NO = B.DEPT(+)
1094
1095AND B.DEPT_NO IS NULL
1096
1097AND B.DEPT_CAT(+) = ‘A’
1098
1099(方法二: 最高效)
1100
1101SELECT ….
1102
1103FROM EMP E
1104
1105WHERE NOT EXISTS (SELECT ‘X’
1106
1107FROM DEPT D
1108
1109WHERE D.DEPT_NO = E.DEPT_NO
1110
1111AND DEPT_CAT = ‘A’);
1112
111320\. 用表連接替換EXISTS
1114
1115
1116
1117通常來說 , 採用表連接的方式比EXISTS更有效率
1118
1119SELECT ENAME
1120
1121FROM EMP E
1122
1123WHERE EXISTS (SELECT ‘X’
1124
1125FROM DEPT
1126
1127WHERE DEPT_NO = E.DEPT_NO
1128
1129AND DEPT_CAT = ‘A’);
1130
1131
1132
1133(更高效)
1134
1135SELECT ENAME
1136
1137FROM DEPT D,EMP E
1138
1139WHERE E.DEPT_NO = D.DEPT_NO
1140
1141AND DEPT_CAT = ‘A’ ;
1142
1143
1144
1145(譯者按: 在RBO的情況下,前者的執行路徑包括FILTER,後者使用NESTED LOOP)</sysdate;<>