Ada 1 query yg plg signifikan di statspack Anda (sql
dengan hash_value = 2889914980), dari total cpu 378s,
300s terpakai.
Dari statspack diketahui bahwa sql ini dijalankan
2,997 kali, dengan rata2 mengakses 2,972.7
blocks/exec.
Dari execution plan query tersebut di tkprof,
perhatikan bahwa akses ke table LED_DETAIL menggunakan
FULL TABLE SCANS.
Coba Anda cari tahu apakah perlu ada penambahan index
disini. Perhatikan kolom2 yg dipakai di WHERE clause,
apakah mungkin ada kolom/kombinasi kolom yg cukup
membatasi lingkup pencarian? (kolom:
gl_code,sl_code,curr_code)
Satu hal lagi, query Anda sebaiknya dipisah menjadi 2
query (tanpa union) berdasarkan kondisi dibawah.
Soalnya saya lihat sebenarnya cuma salah satu bagian
dari query yg dijalankan pada satu saat, tetapi bgn yg
lain ikut dijalankan:
- :base_curr = :curr_code jalan
- :base_curr <> :curr_code
regards,
tomi
exec plan:
---------
SELECT STATEMENT GOAL: CHOOSE
SORT (UNIQUE)
UNION-ALL
FILTER
NESTED LOOPS
TABLE ACCESS GOAL: ANALYZED (FULL) OF
'LED_DETAIL'
TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'LED_HEAD'
INDEX (UNIQUE SCAN) OF 'SYS_C0015833'
(UNIQUE)
query:
-----
select b.due_date, b.ref_no1,
b.doc_date,a.seq_no,item_no,sub_item_no,
dc_flag,
decode(:os_type,'R',decode(dc_flag,'C',-1*nvl(curr_amt,0),
nvl(curr_amt,0)),
decode(dc_flag,'D',-1*nvl(curr_amt,0),nvl(curr_amt,0)))
curr_amt,
decode(:os_type,'R',decode(dc_flag,'C',-1*nvl(os_curr_amt,0),
nvl(os_curr_amt,0)),
decode(dc_flag,'D',-1*nvl(os_curr_amt,0),nvl(os_curr_amt,0)))
os_curr_amt,
rtrim(doc_code)||'/'||doc_no doc_no, due_date, 'Y'
curr_flag
from led_detail A, led_head B
where B.doc_date <= :as_of_date
and a.seq_no = b.seq_no
and b.doc_status = 'C'
and a.gl_code = :gl_code
and a.sl_code = :sl_code
and b.comp_code = :comp_code
and a.curr_code = :curr_code
and :base_curr <> :curr_code
union
select b.due_date, b.ref_no1,
b.doc_date,a.seq_no,item_no,sub_item_no,
dc_flag,
decode(:os_type,'R',decode(dc_flag,'C',-1*nvl(ac_amt,0),
nvl(ac_amt,0)),
decode(dc_flag,'D',-1*nvl(ac_amt,0),nvl(ac_amt,0)))
curr_amt,
decode(:os_type,'R',decode(dc_flag,'C',-1*nvl(os_ac_amt,0),
nvl(os_ac_amt,0)),
decode(dc_flag,'D',-1*nvl(os_ac_amt,0),nvl(os_ac_amt,0)))
os_curr_amt,
rtrim(doc_code)||'/'||doc_no doc_no, due_date, 'N'
curr_flag
from led_detail A, led_head B
where B.doc_date <= :as_of_date
and a.seq_no = b.seq_no
and b.doc_status = 'C'
and a.gl_code = :gl_code
and a.sl_code = :sl_code
and b.comp_code = :comp_code
and :base_curr = :curr_code
order by 1
--- null zero <[EMAIL PROTECTED]> wrote:
> pak tomi dan pak bowo,
>
> sorry kemaren salah kirim attachment, ini saya
> running tkprof dan statspack
> untuk report jalan di client sekitar 11 minutes
> (kemarin waktu load server
> peak, nyampe 20 mins).
>
> ?:
> - apakah sudah optimize sql saya?
> - adakah sugesti yang lain?
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.lizt.org (NEW)
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
