程序sql不应该滥用Hint

Hint提示是优化SQL的一种手段，但不应该放在首位，记的国外有位大师说过顺序应该是学写SQL，写更好SQL，学写ORACLE SQL，写更好的ORACLE SQL，在了解原理且CBO没有走理想路线的情况下再去指引CBO。所以开始不要就用上Hint，在使用HINT可以放到在调整访问结构如index、full table、partition table，和调整sql后无法达到目的时再行考虑，其它优化方法还有sql profile ,outline,baseline,mv,调整query optimized 参数等等..
但我们的开发人员可以对HINT，在发现了Hint后异常兴奋，在了一个OLTP项目的web app中的SQL大量使用了hint，先不说开始时这种方法是不是最优，刚好昨天的一个案例来分析一下
case code:

我对sql进行了跟踪，下面这个sql是我把hint 去掉后的信息，注意没有+
select *
 from (select /* index(t1 IDX_STUDENT_ORGID) */
 t1.*
 from icme_student t1,
 (select org2.org_id
 from icme_org org1, icme_org org2
 where org1.org_id = 37000041
 and org2.org_code like org1.org_code || '%') t2
 where t1.tran_id > 0
 And t1.Is_Valid < 3
 and t1.IC_CODE = '1101380ZF'
 and t1.org_id = t2.org_id
 and t1.rank_id in (37000014,
 37000015,
 37000016,
 37000017,
 37000018,
 37000019,
 37000020,
 37000021,
 37000022,
 37000023,
 37000024,
 37000025,
 37000026,
 37000027,
 37000028,
 37000029,
 37000030,
 37000031,
 37000032,
 37000033,
 37000034,
 37000035,
 37000036,
 37000037,
 37000038,
 37000039,
 37000040,
 37000041,
 37000042,
 37000043,
 37000044,
 37000045,
 37000046,
 37000047,
 37000048,
 37000049,
 37000050,
 37000051,
 37000052,
 37000053,
 37000054,
 37000055,
 37000056,
 37000057,
 37000058,
 37000059,
 37000060,
 37000061,
 37000062,
 37000063,
 37000064,
 37000065,
 37000066,
 37000067,
 37000068,
 37000069,
 37000070,
 37000071)
 order by t1.name)
 where rownum <= 20
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         10          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0         10          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  
Rows     Row Source Operation
-------  ---------------------------------------------------
 1  COUNT STOPKEY (cr=10 pr=0 pw=0 time=246 us)
 1   NESTED LOOPS  (cr=10 pr=0 pw=0 time=240 us)
 1    NESTED LOOPS  (cr=7 pr=0 pw=0 time=193 us)
 1     TABLE ACCESS BY INDEX ROWID ICME_ORG (cr=3 pr=0 pw=0 time=76 us)
 1      INDEX UNIQUE SCAN PK_ICME_ORG (cr=2 pr=0 pw=0 time=46 us)(object id 51402)
 1     TABLE ACCESS BY INDEX ROWID ICME_STUDENT (cr=4 pr=0 pw=0 time=115 us)
 1      INDEX RANGE SCAN PK_ICME_STUDENTS (cr=3 pr=0 pw=0 time=81 us)(object id 51449)
 1    TABLE ACCESS BY INDEX ROWID ICME_ORG (cr=3 pr=0 pw=0 time=42 us)
 1     INDEX UNIQUE SCAN PK_ICME_ORG (cr=2 pr=0 pw=0 time=16 us)(object id 51402)
 下面是带+，hint提示有效的情况
select *
 from (select /*+ index(t1 IDX_STUDENT_ORGID) */
 t1.*
 from icme_student t1,
 (select org2.org_id
 from icme_org org1, icme_org org2
 where org1.org_id = 37000041
 and org2.org_code like org1.org_code || '%') t2
 where t1.tran_id > 0
 And t1.Is_Valid < 3
 and t1.IC_CODE = '1101380ZF'
 and t1.org_id = t2.org_id
 and t1.rank_id in (37000014,
 37000015,
 37000016,
 37000017,
 37000018,
 37000019,
 37000020,
 37000021,
 37000022,
 37000023,
 37000024,
 37000025,
 37000026,
 37000027,
 37000028,
 37000029,
 37000030,
 37000031,
 37000032,
 37000033,
 37000034,
 37000035,
 37000036,
 37000037,
 37000038,
 37000039,
 37000040,
 37000041,
 37000042,
 37000043,
 37000044,
 37000045,
 37000046,
 37000047,
 37000048,
 37000049,
 37000050,
 37000051,
 37000052,
 37000053,
 37000054,
 37000055,
 37000056,
 37000057,
 37000058,
 37000059,
 37000060,
 37000061,
 37000062,
 37000063,
 37000064,
 37000065,
 37000066,
 37000067,
 37000068,
 37000069,
 37000070,
 37000071)
 order by t1.name)
 where rownum <= 20
 call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.90       1.85          0     474111          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.90       1.86          0     474111          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57  
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY (cr=474111 pr=0 pw=0 time=1859730 us)
      1   NESTED LOOPS  (cr=474111 pr=0 pw=0 time=1859726 us)
      1    NESTED LOOPS  (cr=474108 pr=0 pw=0 time=1859679 us)
      1     TABLE ACCESS BY INDEX ROWID ICME_ORG (cr=3 pr=0 pw=0 time=26 us)
      1      INDEX UNIQUE SCAN PK_ICME_ORG (cr=2 pr=0 pw=0 time=20 us)(object id 51402)
      1     TABLE ACCESS BY INDEX ROWID ICME_STUDENT (cr=474105 pr=0 pw=0 time=1859650 us)
 799823      INDEX FULL SCAN IDX_STUDENT_ORGID (cr=2085 pr=0 pw=0 time=2898 us)(object id 68979)
      1    TABLE ACCESS BY INDEX ROWID ICME_ORG (cr=3 pr=0 pw=0 time=42 us)
      1     INDEX UNIQUE SCAN PK_ICME_ORG (cr=2 pr=0 pw=0 time=23 us)(object id 51402)
比较一下，因为hint中指写了一个错误的索引，指标对比
no hint:elapsed 0.02 s ,10  buffers gotten for consistent read
use hint:elapsed 1.86s （增加了93倍的时间）474111 buffers gotten for consistent read(增加了4.7万+倍)
这是一个本地磁盘存储的PC SERVER，看看大量的多余一致读会带来什么？

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	808	19-Nov-12 15:00:52	22	4.2
End Snap:	809	19-Nov-12 15:44:17	38	7.1
Elapsed:		43.43 (mins)
DB Time:		322.22 (mins)

	Per Second	Per Transaction
Redo size:	8,905.72	38,230.56
Logical reads:	56,921.25	244,351.95
Block changes:	66.59	285.84
Physical reads:	3,867.71	16,603.32

Top 5 Timed Events
 



















































Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
CPU time
14,186
73.4
db file scattered read
1,647,853
206
0
1.1
User I/O
db file sequential read
842,227
143
0
.7
User I/O
latch: cache buffers chains
1,826
137
75
.7
Concurrency
latch: library cache
422
41
96
.2
Concurrency

 [oracle@ggsdb ~]$ vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 9  1 161048 409804 106904 14838472    0    0    12    38    1    2  9  0 91  0  0
21  1 161032 409556 106912 14838508   10    0    10     6 1432  421 100  0  0  0  0
24  0 161024 409432 106916 14838540    2    0     4   104 1464  465 99  0  0  0  0
23  0 161024 409432 106936 14838548    0    0    10    78 1451  533 100  0  0  0  0
25  0 161024 409432 106936 14838576    0    0     0    44 1477  503 100  0  0  0  0
24  1 161000 409432 106944 14838588   10    0    22   104 1442  464 100  0  0  0  0
20  1 160900 408708 106952 14839052    6    0   298    36 1445  479 100  0  0  0  0
19  1 160796 408708 106952 14839360   54    0    54    10 1436  480 100  0  0  0  0
21  1 160760 408524 106956 14839512   14    0    46   110 1462  465 99  0  0  0  0
25  0 160760 408464 106968 14839512    0    0    24    44 1448  523 100  0  0  0  0

哎，在继续..