不同库上同一个SQL，执行计划相同，性能相差很多，为何？
SQL语句如下：SELECT dtl.corp_org_id org_id,
sub1.org_name_c branch_name,
SUM (
CASE
WHEN dtl.account_book_id = 0 THEN dtl.total_charge
ELSE 0
END)
/ 100
一次性退费
FROM cs.cs_app app
INNER JOIN cs.cs_app appr ON app.relat_app_id = appr.app_id
INNER JOIN rep.kd_app_bill_month dtl ON dtl.app_id = app.app_id
LEFT JOIN rep.kd_subscriber_connect_month sub1
ON sub1.subscriber_id = dtl.subscriber_id
WHERE app.app_action IN ('CS_CANCEL', 'CS_REVERSE')
AND dtl.busy_type = 2
AND appr.app_time < DATE '2013-12-01'
AND app.app_time BETWEEN DATE '2013-12-01' AND DATE '2013-12-31' + 1
GROUP BY dtl.corp_org_id, sub1.org_name_c;
库A的执行计划（慢，几分钟）：
Plan
SELECT STATEMENT ALL_ROWSCost: 63,717 Bytes: 8,463 Cardinality: 91
11 HASH GROUP BY Cost: 63,717 Bytes: 8,463 Cardinality: 91
10 HASH JOIN OUTER Cost: 63,716 Bytes: 101,463 Cardinality: 1,091
8 TABLE ACCESS BY INDEX ROWID TABLE REP.KD_APP_BILL_MONTH Cost: 4 Bytes: 56 Cardinality: 2
7 NESTED LOOPS Cost: 62,093 Bytes: 86,189 Cardinality: 1,091
5 NESTED LOOPS Cost: 60,922 Bytes: 29,376 Cardinality: 576
2 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 59,769 Bytes: 20,736 Cardinality: 576
1 INDEX RANGE SCAN INDEX CS.IDX_CS_APP_APP_TIME Cost: 206 Cardinality: 76,566
4 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 2 Bytes: 15 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) CS.IDX_PK_CS_APP Cost: 1 Cardinality: 1
6 INDEX RANGE SCAN INDEX REP.IDX_KD_APP_BILL_MONTH_APPID Cost: 2 Cardinality: 2
9 TABLE ACCESS FULL TABLE REP.KD_SUBSCRIBER_CONNECT_MONTH Cost: 1,603 Bytes: 21,743,960 Cardinality: 1,553,140
库B的执行计划（快，十几秒）：
Plan
SELECT STATEMENT ALL_ROWSCost: 58,408 Bytes: 7,440 Cardinality: 80
11 HASH GROUP BY Cost: 58,408 Bytes: 7,440 Cardinality: 80
10 HASH JOIN OUTER Cost: 58,407 Bytes: 74,493 Cardinality: 801
8 TABLE ACCESS BY INDEX ROWID TABLE REP.KD_APP_BILL_MONTH Cost: 3 Bytes: 28 Cardinality: 1
7 NESTED LOOPS Cost: 56,799 Bytes: 63,279 Cardinality: 801
5 NESTED LOOPS Cost: 55,626 Bytes: 29,580 Cardinality: 580
2 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 54,464 Bytes: 20,880 Cardinality: 580
1 INDEX RANGE SCAN INDEX CS.IDX_CS_APP_APP_TIME Cost: 216 Cardinality: 76,569
4 TABLE ACCESS BY INDEX ROWID TABLE CS.CS_APP Cost: 2 Bytes: 15 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) CS.IDX_PK_CS_APP Cost: 1 Cardinality: 1
6 INDEX RANGE SCAN INDEX REP.IDX_KD_APP_BILL_MONTH_APPID Cost: 2 Cardinality: 1
9 TABLE ACCESS FULL TABLE REP.KD_SUBSCRIBER_CONNECT_MONTH Cost: 1,588 Bytes: 21,765,408 Cardinality: 1,554,672
两台机器硬件配置完全相同，
请刘大帮忙分析下，是否还需要提供其它信息？ 这个格式 难以阅读
一个好的问题 的基本要素：一个好的标题、 完整的版本信息、 完整的日志信息、 报错信息
请使用 sql health check 脚本 分析该SQL 然后上传HTML
http://www.oracledatabase12g.com/archives/sql-tuning-health-check-script.html
PS: 你标题这种问题 实在是在国内很普遍的问题，究其原因无非是 大家喜欢忽略细节，以及唯有忽略细节才能活下去
http://t.askmaclean.com/thread-3245-1-1.html
http://t.askmaclean.com/thread-3821-1-1.html 参考链接已经看过，情况不同，意义不大 Current SQL Statistics (GV$SQL)
Performance metrics of child cursors of 5zn5rwavxhdcu while still in memory.
# Inst
ID Child
Num Plan HV Execs Fetch Loads Inval Parse
Calls Buffer
Gets Disk
Reads Direct
Writes Rows
Proc Elapsed
Time
(secs) CPU
Time
(secs) IO
Time
(secs) Conc
Time
(secs) Appl
Time
(secs) Clus
Time
(secs) PLSQL
Time
(secs) Java
Time
(secs) Optimizer
Mode Cost Opt Env HV Parsing
Schema
Name Module Action Outline Profile First Load Last Load Last Active
1 1 0 1974753989 1 1 1 0 1 2782643 61188 0 12 165.992 18.540 155.031 0.000 0.000 0.000 0.000 0.000 ALL_ROWS 63717 2513728184 SYSTEM TOAD background query session 2014-01-17/15:11:32 2014-01-17/15:11:32 2014-01-17/15:14:22
VS
Current SQL Statistics (GV$SQL)
Performance metrics of child cursors of 5zn5rwavxhdcu while still in memory.
# Inst
ID Child
Num Plan HV Execs Fetch Loads Inval Parse
Calls Buffer
Gets Disk
Reads Direct
Writes Rows
Proc Elapsed
Time
(secs) CPU
Time
(secs) IO
Time
(secs) Conc
Time
(secs) Appl
Time
(secs) Clus
Time
(secs) PLSQL
Time
(secs) Java
Time
(secs) Optimizer
Mode Cost Opt Env HV Parsing
Schema
Name Module Action Outline Profile First Load Last Load Last Active
1 1 0 1974753989 1 1 1 0 1 101977 61380 0 12 46.019 10.430 42.085 0.000 0.000 0.000 0.000 0.000 ALL_ROWS 58408 1134700906 SYSTEM TOAD background query session 2014-01-17/15:06:15 2014-01-17/15:06:15 2014-01-17/15:07:02
Plan HV 一样说明确实执行计划一样
Buffer Gets 2782643 Disk Reads 61188 CPU Time 18s IO TIME 155s
vs
Buffer Gets 101977 Disk Reads 61380 CPU Time 10s IO TIME 42.085
给一下这2个 SQL 的 raw 的10046 TRACE 和这2个库的AWR AWR目前无法提供 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 25.13 221.76 80888 2814070 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 25.13 221.77 80888 2814070 0 12
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
12 HASH GROUP BY (cr=2814070 pr=80888 pw=0 time=221769108 us)
101 HASH JOIN OUTER (cr=2814070 pr=80888 pw=0 time=222580556 us)
101 HASH JOIN (cr=2807064 pr=73891 pw=0 time=217940971 us)
63 NESTED LOOPS (cr=2775323 pr=49001 pw=0 time=6731973 us)
1476 TABLE ACCESS BY INDEX ROWID CS_APP (cr=2770893 pr=47981 pw=0 time=160940455 us)
3445081 INDEX RANGE SCAN IDX_CS_APP_APP_TIME (cr=9142 pr=9140 pw=0 time=6919360 us)(object id 937218)
63 TABLE ACCESS BY INDEX ROWID CS_APP (cr=4430 pr=1020 pw=0 time=8771442 us)
1476 INDEX UNIQUE SCAN IDX_PK_CS_APP (cr=2954 pr=967 pw=0 time=8351212 us)(object id 881020)
3000793 TABLE ACCESS FULL KD_APP_BILL_MONTH (cr=31741 pr=24890 pw=0 time=7199 us)
1558326 TABLE ACCESS FULL KD_SUBSCRIBER_CONNECT_MONTH (cr=7006 pr=6997 pw=0 time=75193 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 17.25 27.99
db file sequential read 49145 0.53 203.02
latch: cache buffers chains 2 0.00 0.00
db file scattered read 2046 0.07 2.25
vs
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 9.46 10.92 38857 133453 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 9.46 10.93 38857 133453 0 12
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
12 HASH GROUP BY (cr=133453 pr=38857 pw=0 time=10926602 us)
101 HASH JOIN OUTER (cr=133453 pr=38857 pw=0 time=11842583 us)
101 HASH JOIN (cr=126454 pr=31866 pw=0 time=6241637 us)
63 NESTED LOOPS (cr=94662 pr=92 pw=0 time=124835 us)
1476 TABLE ACCESS BY INDEX ROWID CS_APP (cr=90232 pr=4 pw=0 time=4968855 us)
3445081 INDEX RANGE SCAN IDX_CS_APP_APP_TIME (cr=9142 pr=0 pw=0 time=3962 us)(object id 331843)
63 TABLE ACCESS BY INDEX ROWID CS_APP (cr=4430 pr=88 pw=0 time=731313 us)
1476 INDEX UNIQUE SCAN IDX_PK_CS_APP (cr=2954 pr=86 pw=0 time=720815 us)(object id 319263)
3000793 TABLE ACCESS FULL KD_APP_BILL_MONTH (cr=31792 pr=31774 pw=0 time=3006926 us)
1558326 TABLE ACCESS FULL KD_SUBSCRIBER_CONNECT_MONTH (cr=6999 pr=6991 pw=0 time=9406 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 6.47 9.30
db file sequential read 96 0.07 0.70
db file scattered read 2461 0.08 2.82 db file sequential read 49145 0.53 203.02
主要区别在于 db file sequential read
TABLE ACCESS BY INDEX ROWID CS_APP (cr=2770893 pr=47981 pw=0 time=160940455 us)
怀疑2边的配置 有较大的区别， 需要AWR 证实 因包含敏感信息，AWR不方便提供，sorry
你说的配置具体指哪些？ kurz 发表于 2014-1-20 10:14 static/image/common/back.gif
因包含敏感信息，AWR不方便提供，sorry
你说的配置具体指哪些？
everything Thanks! anyway
页:
[1]