Home » RDBMS Server » Performance Tuning » sql-tuning for specific query? (oracle 9i)
sql-tuning for specific query? [message #377630] Wed, 24 December 2008 00:37 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi gurus,
Here i wrote query which takes time for a monthno
it is


select row_number() over (order by a.documentno) slno,
       a.documentno,a.documentdate,a.glcode,
       a.slcode,b.vendname,
       a.suppbillno,suppbilldate,
       a.link_doc_no,
       c.remarks,
       a.creditamount,debitamount,(f.BasicAmt + Nvl(c.ExciseDutyAmt,0) + Nvl(c.cessAmt,0)) Cndn_Taxable,d.grinno,d.grdref_no,
       e.vatserialno,e.taxcode,e.taxperc,e.taxableamt,e.taxamount
      
from 
         ledger  a,
         vendmast b,
         cndnmain c,
         grinmain d,
         grddata e,
        (SELECT   DocumentNo,
                   SUM(Nvl(BasicAmt,0)) BasicAmt
          FROM     cndNitem
          WHERE    UnitId = 1
                   AND PeriodId = 13
          GROUP BY DocumentNo) f
where 
a.documentdate BETWEEN '01-apr-2008' AND '30-apr-2008'
   And a.unitid =c.unitid 
   And d.unitid =e.unitid 
   And a.unitid =e.unitid 
   And a.unitid = c.unitid 
   And a.unitid =1   
   And a.periodid=13
   And a.periodid =e.periodid 
   And d.periodid =e.periodid 
   And a.periodid =c.periodid 
   And a.documentno =c.documentno 
   And a.slcode=b.vendcode
   And d.vendcode =e.vendcode 
   And a.slcode =e.vendcode 
   And d.grdref_no = e.grdnumber
   And b.vendcode =c.vendcode 
   And a.slcode =c.vendcode 
   And d.cn_refno =  c.ap_documentno
   And a.link_doc_no =c.ap_documentno
   And a.documentno = f.documentno
   And c.documentno = f.documentno
   And a.glcode  in ((SELECT glCode
                           FROM   glMast
                           WHERE  gldesc LIKE 'VAT%'
                                  AND glCode > '90000')
                          UNION 
                          (SELECT DISTINCT Vat_glCode
                           FROM   PartGroup))
and substr(a.documentno,2,3) in ('207')
ORDER BY DOCUMENTNO


[CODE] Elapsed: 00:00:24.13 [CODE]


Execution Plan 
---------------------------------------------------------- 
0     SELECT STATEMENT Optimizer=CHOOSE (Cost=323 Card=1 Bytes=312 )     
1  0      WINDOW (NOSORT) (Cost=323 Card=1 Bytes=312)     
2  1          SORT (GROUP BY) (Cost=323 Card=1 Bytes=312)     
3  2              HASH JOIN (Cost=31 1 Card=1 Bytes=312)     
4  3                  TABL E ACCESS (BY INDEX ROWID) OF 'GRINMAIN' (Cost=31 Card=1 Byte s=45)     
5  4                  &nbs p;   NESTED LOOPS (Cost=264 Card=1 Bytes=305)     
6  5                  &nbs p;       HASH JOIN (Cost=233 Card=1 Bytes=260 )     
7  6                  &nbs p;           TABLE ACCESS (BY INDEX ROWID) OF 'CNDNITEM' (Cost=2 Card=1 Bytes=19)     
8  7                  &nbs p;               NESTED L OOPS (Cost=8 Card=1 Bytes=225)     
9  8                  &nbs p;                 & nbsp; NESTED LOOPS (Cost=6 Card=1 Bytes=206)     
10  9                  &nbs p;                 & nbsp;     NESTED LOOPS (Cost=5 Card=1 Bytes=170)     
11  10                  &nbs p;                 & nbsp;         TABLE ACCESS (BY INDEX ROW ID) OF 'LEDGER' (Cost=4 Card=1 Bytes=64)     
12  11                  &nbs p;                 & nbsp;             INDEX (RANGE SCAN) OF 'PK_LEDA' (UNIQUE) (Cost=3 Card=1)     
13  10                  &nbs p;                 & nbsp;         TABLE ACCESS (BY INDEX ROW ID) OF 'CNDNMAIN' (Cost=1 Card=1 Bytes=106)     
14  13                  &nbs p;                 & nbsp;             INDEX (UNIQU E SCAN) OF 'PK_CNDNA' (UNIQUE)     
15  9                  &nbs p;                 & nbsp;     TABLE ACCESS (BY INDEX ROWID) OF 'VENDMA ST' (Cost=1 Card=1 Bytes=36)     
16  15                  &nbs p;                 & nbsp;         INDEX (UNIQUE SCAN) OF 'PK _VEND' (UNIQUE)     
17  8                  &nbs p;                 & nbsp; INDEX (RANGE SCAN) OF 'PK_CNDNITEMA' (UNIQUE) (Cost=1 Card=1)     
18  6                  &nbs p;           TABLE ACCESS (FULL) OF 'GRDDATA' (Cost=224 Card=1887 Bytes=66045)     
19  5                  &nbs p;       INDEX (RANGE SCAN) OF 'VEND_IDX' (NO N-UNIQUE) (Cost=1 Card=50)     
20  3                  VIEW OF 'VW_NSO_1' (Cost=46 Card=12 Bytes=84)     
21  20                  &nbs p;   SORT (UNIQUE) (Cost=46 Card=12 Bytes=100)     
22  21                  &nbs p;       UNION-ALL     
23  22                  &nbs p;           TABLE ACCESS (FULL) OF 'GLMAST' (Cost=11 Card=1 Bytes=34)     
24  22                  &nbs p;           TABLE ACCESS (FULL) OF 'PARTGROUP' (Cost=11 Card=43 Bytes=258)  



Statistics 
---------------------------------------------------------- 

0  recursive calls  
0  db block gets  
4922896  consistent gets  
1196  physical reads  
0  redo size  
4413  bytes sent via SQL*Net to client  
514  bytes received via SQL*Net from client  
3  SQL*Net roundtrips to/from client  
2  sorts (memory)  
0  sorts (disk)  


The same which run for 6 months

if i run via report 6i it would take 22 minutes.

if i run via isql*plus it exit from isql after a 15 minustes

please help to tune this query as well as why isql exits.

kanish
Re: sql-tuning for specific query? [message #377640 is a reply to message #377630] Wed, 24 December 2008 01:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. How the indexes 'PK_LEDA', 'PK_CNDNITEMA' and 'VEND_IDX' are defined (columns and their order)?
2. How many rows in GRDDATA table?
3. Post TKPROF.

Re: sql-tuning for specific query? [message #377670 is a reply to message #377640] Wed, 24 December 2008 02:48 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Pk_leda,pk_cndnitem is a index created when primary key created.

OWNER  CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME  POSITION  
ACCL  PK_LEDA  LEDGER  UNITID  1  
ACCL  PK_LEDA  LEDGER  PERIODID  2  
ACCL  PK_LEDA  LEDGER  GLCODE  5  
ACCL  PK_LEDA  LEDGER  DOCUMENTDATE  3  
ACCL  PK_LEDA  LEDGER  DOCUMENTNO  4  
ACCL  PK_LEDA  LEDGER  ITEMNO  6  



OWNER  CONSTRAINT_NAME  TABLE_NAME  COLUMN_NAME  POSITION  
ACCL  PK_CNDNITEMA  CNDNITEM  UNITID  1  
ACCL  PK_CNDNITEMA  CNDNITEM  PERIODID  2  
ACCL  PK_CNDNITEMA  CNDNITEM  DOCUMENTNO  3  
ACCL  PK_CNDNITEMA  CNDNITEM  SERIALNO  4  


VEND_IDX that i dont know how to view the index properties.

the grddata table total rows is 124215;

please help me findout the vend_idx / like any user_tab_index.

kanish





Re: sql-tuning for specific query? [message #377671 is a reply to message #377630] Wed, 24 December 2008 02:54 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
all_ind_columns will give you the column list for any index.
Re: sql-tuning for specific query? [message #377676 is a reply to message #377671] Wed, 24 December 2008 03:16 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

THANKS FOR ALL_IND_COLUMNS,

FOR VEND_IDX

INDEX_OWNER  INDEX_NAME  TABLE_OWNER  TABLE_NAME  COLUMN_NAME  COLUMN_POSITION  COLUMN_LENGTH  CHAR_LENGTH  DESC  
SYS  VEND_IDX  ACCL  GRINMAIN  VENDCODE  1  5  5  ASC  


KANISH
Previous Topic: SQL Tuning
Next Topic: Bitmap indexes "drop/create" or "disable/rebuild"
Goto Forum:
  


Current Time: Wed Dec 07 09:08:42 CST 2016

Total time taken to generate the page: 0.05576 seconds