Re: [indo-oracle] server lemot

Fri, 21 Apr 2006 23:56:31 -0700

pak tomi, untuk query tsb di table led_detail sepertinya tidak bisa dibuat
inde pada field seq_no, karena bukan unik.
index sudah dibuatin dgn composite index (seq_no,item_no,item_sub_no)

saya mencatatnya karena ada wait event dalam pembacaan file di hardisk?

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ---------------------------------------- 
Waited  ----------  ------------
  SQL*Net message to client                     680        0.00
0.00
  SQL*Net more data to client                   410        0.00
0.01
  SQL*Net message from client                   680        0.00
0.00
  db file scattered read                      28235        0.00
1.30
  db file sequential read                      7591        0.00
0.06
  virtual circuit status                          4        0.00
0.00
********************************************************************************

saya coba tracing lagi dgn system event 10046 level 12, hasilnya begini :

call     count       cpu    elapsed       disk      query    current
rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0
0
Execute    561      0.28       0.20          0          0          0
0
Fetch      679     15.24      14.95     228103     665440          0
14768
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1242     15.52      15.15     228103     665440          0
14768


Rows     Row Source Operation
-------  ---------------------------------------------------
    561  SORT ORDER BY
    561   FILTER
    561    NESTED LOOPS
    830     NESTED LOOPS
    830      INDEX RANGE SCAN SL_GL_IDX1 (object id 95931)
    830      TABLE ACCESS BY INDEX ROWID GL
    830       INDEX UNIQUE SCAN GL_GC_PK (object id 95463)
    561     TABLE ACCESS BY INDEX ROWID SL
    561      INDEX UNIQUE SCAN SL_SC_PK (object id 95923)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    561   SORT (UNIQUE)
    561    UNION-ALL
    561     FILTER
    830      NESTED LOOPS
    830       TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'LED_DETAIL'
    830       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'LED_HEAD'
    830        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0032091'
                   (UNIQUE)
    561     FILTER
    561      NESTED LOOPS
      0       TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'LED_DETAIL'
      0       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'LED_HEAD'
      0        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'SYS_C0032091'
                   (UNIQUE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ---------------------------------------- 
Waited  ----------  ------------
  SQL*Net message to client                     680        0.00
0.00
  SQL*Net more data to client                   410        0.00
0.01
  SQL*Net message from client                   680        0.00
0.00
  db file scattered read                      28235        0.00
1.30
  db file sequential read                      7591        0.00
0.06
  virtual circuit status                          4        0.00
0.00
********************************************************************************


--null
----- Original Message -----
From: "Tomi Wijanto" <[EMAIL PROTECTED]>
To: <[email protected]>
Cc: "null zero" <[EMAIL PROTECTED]>
Sent: Wednesday, April 19, 2006 11:04 AM
Subject: Re: [indo-oracle] server lemot


> 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
>

Send instant messages to your online friends http://asia.messenger.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



SPONSORED LINKS
Membership database software Database mortgage software Pda database software
Database management software Oracle database administration Oracle database management

YAHOO! GROUPS LINKS



Kirim email ke