Home » RDBMS Server » Performance Tuning » Any Better Way to Tune this Query
Any Better Way to Tune this Query [message #224649] Wed, 14 March 2007 23:04 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We have this issue of slow query in Production it's taking more than 2mins,
Is there some thing we can do here,I have attached PLAN and Number of rows, There are couple of Views in that, one of the View is calling a function IF there is any room we can modify this or tune this that will be really great,
SQL> select count(*) from txn; 

  COUNT(*)
----------
  70491573

SQL>  select count(*) from pymt_view; 

  COUNT(*)
----------
  70043726

SQL>  select count(*) from txn_Type;

  COUNT(*)
----------
         4

SQL>  select count(*) from site;

  COUNT(*)
----------
        51

SQL>  select count(*) from note;

  COUNT(*)
----------
   3287078

SQL>  select count(*) from note_Txt;

  COUNT(*)
----------
   1986000

SQL> select count(*) from  txn_inv_cnt_view;

  COUNT(*)
----------
  70491573
 

SELECT *
  FROM (SELECT a.*, ROWNUM rnum
          FROM (SELECT          /*+ FIRST_ROWS */
                       DISTINCT txn.txn_id, txn.txn_seq_nb, txn.bat_id,
                                txn.txn_type_id, txn.gp_nb, pymt.pymt_id,
                                pymt.pymt_nb_tx, NVL (pymt.dol_am, 0) dol_am,
                                pymt.rmit_nm, bat.cr_dt, bat.proc_dt,
                                bat.bat_nb, bat.lockbox_id,
                                lockbox.lockbox_nb, txn_type.txn_type_desc_tx,
                                note.note_id, note.asgn_to_nb, note.excp_cd,
                                pymt.rte_nb_tx, pymt.acct_nb_tx,
                                note_txt.note_id note_txt_note_id,
                                note.note_sts_in, site.site_nm, site.site_cd,
                                txn.txn_arc_in, txn.txn_shrt_term_in,
                                pymt.rtn_id, dda.dda_nb, pymt.crcy_id,
                                txn_inv_cnt_view.excp_count, asscn_sts_cd
                           FROM txn,
                                pymt_view pymt,
                                lockbox,
                                txn_type,
                                note,
                                note_txt,
                                site,
                                dda,
                                txn_inv_cnt_view,
                                ext_bat_view bat
                          WHERE pymt.pymt_nb_tx = 5169 
                            AND dol_am = 14.34 
                            AND bat.proc_dt >= '30-NOV-2006' 
                            AND bat.usr_id = 22437
                            AND txn.bat_id = bat.bat_id
                            AND bat.lockbox_id = lockbox.lockbox_id
                            AND txn.txn_id = pymt.txn_id(+)
                            AND txn.txn_type_id = txn_type.txn_type_id
                            AND txn.txn_id = note.txn_id(+)
                            AND note.note_id = note_txt.note_id(+)
                            AND lockbox.site_id = site.site_id
                            AND txn.dda_id = dda.dda_id(+)
                            AND txn.txn_id = txn_inv_cnt_view.txn_id
                       ORDER BY bat.cr_dt DESC,
                                lockbox.lockbox_nb,
                                site.site_cd,
                                bat.bat_nb,
                                txn.txn_seq_nb) a
         WHERE ROWNUM <= 1000)
 WHERE rnum >= 1
 

Elapsed: 00:01:10.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=42 Card=1
          Bytes=849)

   1    0   VIEW (Cost=42 Card=1 Bytes=849)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=42 Card=1 Bytes=836)
   4    3         SORT (UNIQUE STOPKEY) (Cost=39 Card=1 Bytes=346)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'LOCKBOX_ACL' (Co
          st=2 Card=1 Bytes=11)

   6    5             NESTED LOOPS (Cost=36 Card=1 Bytes=346)
   7    6               NESTED LOOPS (Cost=34 Card=1 Bytes=335)
   8    7                 NESTED LOOPS (Cost=33 Card=1 Bytes=317)
   9    8                   NESTED LOOPS (Cost=32 Card=1 Bytes=303)
  10    9                     NESTED LOOPS (Cost=32 Card=1 Bytes=298)
  11   10                       NESTED LOOPS (OUTER) (Cost=31 Card=1 B
          ytes=291)

  12   11                         NESTED LOOPS (OUTER) (Cost=29 Card=1
           Bytes=285)

  13   12                           NESTED LOOPS (OUTER) (Cost=27 Card
          =1 Bytes=264)

  14   13                             NESTED LOOPS (OUTER) (Cost=26 Ca
          rd=1 Bytes=252)

  15   14                               NESTED LOOPS (Cost=25 Card=1 B
          ytes=213)

  16   15                                 NESTED LOOPS (Cost=24 Card=1
           Bytes=205)

  17   16                                   NESTED LOOPS (Cost=21 Card
          =1 Bytes=147)

  18   17                                     NESTED LOOPS (Cost=16 Ca
          rd=1 Bytes=114)

  19   18                                       NESTED LOOPS (Cost=15
          Card=1 Bytes=99)

  20   19                                         NESTED LOOPS (Cost=9
           Card=3 Bytes=180)

  21   20                                           NESTED LOOPS (OUTE
          R) (Cost=7 Card=1 Bytes=50)

  22   21                                             NESTED LOOPS (Co
          st=4 Card=1 Bytes=42)

  23   22                                               NESTED LOOPS (
          Cost=3 Card=1 Bytes=20)

  24   23                                                 TABLE ACCESS
           (BY INDEX ROWID) OF 'USR' (Cost=2 Card=1 Bytes=10)

  25   24                                                   INDEX (UNI
          QUE SCAN) OF 'XPK_USR' (UNIQUE) (Cost=1 Card=1)

  26   23                                                 TABLE ACCESS
           (BY INDEX ROWID) OF 'USR' (Cost=1 Card=1 Bytes=10)

  27   26                                                   INDEX (UNI
          QUE SCAN) OF 'XPK_USR' (UNIQUE)

  28   22                                               TABLE ACCESS (
          BY INDEX ROWID) OF 'CUST' (Cost=1 Card=1 Bytes=22)

  29   28                                                 INDEX (UNIQU
          E SCAN) OF 'XPK_CUS' (UNIQUE)

  30   21                                             TABLE ACCESS (BY
           INDEX ROWID) OF 'USR_ROLE' (Cost=3 Card=1 Bytes=8)

  31   30                                               INDEX (RANGE S
          CAN) OF 'XIF_USRROL_USRID' (NON-UNIQUE) (Cost=1 Card=3)

  32   20                                           TABLE ACCESS (BY I
          NDEX ROWID) OF 'LOCKBOX_CUST' (Cost=2 Card=3 Bytes=30)

  33   32                                             INDEX (RANGE SCA
          N) OF 'XIF_LOCCUS_CUSID' (NON-UNIQUE) (Cost=1 Card=3)

  34   19                                         TABLE ACCESS (BY GLO
          BAL INDEX ROWID) OF 'BAT' (Cost=2 Card=1 Bytes=39)

  35   34                                           INDEX (RANGE SCAN)
           OF 'XAK_BAT_LBIDPRDTBTNB' (UNIQUE) (Cost=1 Card=17)

  36   18                                       TABLE ACCESS (BY INDEX
           ROWID) OF 'LOCKBOX_CUST' (Cost=1 Card=1 Bytes=15)

  37   36                                         INDEX (UNIQUE SCAN)
          OF 'XAK_LOCCUS_LOCID_CUSID' (UNIQUE)

  38   17                                     TABLE ACCESS (BY GLOBAL
          INDEX ROWID) OF 'TXN' (Cost=5 Card=14 Bytes=462)

  39   38                                       INDEX (RANGE SCAN) OF
          'XAK_TXN_BATID_TXNID' (UNIQUE) (Cost=3 Card=14)

  40   16                                   TABLE ACCESS (BY GLOBAL IN
          DEX ROWID) OF 'PYMT' (Cost=3 Card=1 Bytes=58)

  41   40                                     INDEX (RANGE SCAN) OF 'X
          IE_PYM_TXNID_DOLAMPYMNBTX' (NON-UNIQUE) (Cost=2 Card=1)

  42   15                                 TABLE ACCESS (BY INDEX ROWID
          ) OF 'TXN_TYPE' (Cost=1 Card=1 Bytes=8)

  43   42                                   INDEX (UNIQUE SCAN) OF 'XP
          K_TXNTYP' (UNIQUE)

  44   14                               VIEW PUSHED PREDICATE OF 'RMIT
          _ACTV_VIEW' (Cost=1 Card=1 Bytes=39)

  45   44                                 NESTED LOOPS (OUTER) (Cost=2
           Card=1 Bytes=69)

  46   45                                   TABLE ACCESS (BY INDEX ROW
          ID) OF 'RMIT' (Cost=2 Card=1 Bytes=56)

  47   46                                     INDEX (UNIQUE SCAN) OF '
          XPK_RMT' (UNIQUE) (Cost=1 Card=1)

  48   45                                   INDEX (UNIQUE SCAN) OF 'XP
          K_CUSCLS' (UNIQUE)

  49   13                             TABLE ACCESS (BY INDEX ROWID) OF
           'DDA' (Cost=1 Card=1 Bytes=12)

  50   49                               INDEX (UNIQUE SCAN) OF 'XPK_DD
          ' (UNIQUE)

  51   12                           TABLE ACCESS (BY GLOBAL INDEX ROWI
          D) OF 'NOTE' (Cost=2 Card=1 Bytes=21)

  52   51                             INDEX (UNIQUE SCAN) OF 'XAK_NOT_
          TXNID' (UNIQUE) (Cost=1 Card=1)

  53   11                         INDEX (RANGE SCAN) OF 'XAK_NOTTXT_NO
          TID_NOTETXTID' (UNIQUE) (Cost=2 Card=1 Bytes=6)

  54   10                       INDEX (UNIQUE SCAN) OF 'XPK_TXN' (UNIQ
          UE) (Cost=1 Card=1 Bytes=7)

  55    9                     INDEX (UNIQUE SCAN) OF 'XPK_LOC' (UNIQUE
          )

  56    8                   TABLE ACCESS (BY INDEX ROWID) OF 'LOCKBOX'
           (Cost=1 Card=1 Bytes=14)

  57   56                     INDEX (UNIQUE SCAN) OF 'XPK_LOC' (UNIQUE
          )

  58    7                 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cos
          t=1 Card=1 Bytes=18)

  59   58                   INDEX (UNIQUE SCAN) OF 'XPK_SIT' (UNIQUE)
  60    6               INDEX (RANGE SCAN) OF 'XAK_LOCACL_USRID_LOCACL
          ID' (UNIQUE) (Cost=1 Card=1)





Statistics
----------------------------------------------------------
        950  recursive calls
          0  db block gets
      27429  consistent gets
        657  physical reads
          0  redo size
       1050  bytes sent via SQL*Net to client
        859  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Another questions is what does DEGREE column in USER_INDEXES mean for . searching documentaion
it gave this but not clear " Number of threads per instance for scanning the index "
And Logging option when to use and not, does it has any thing to do with Archivle log shipping and
to Standby Server, DOC says
 Specify whether the creation of the index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged. LOGGING is the default.

If index is nonpartitioned, this clause specifies the logging attribute of the index.

If index is partitioned, this clause determines:

The default value of all partitions specified in the CREATE statement (unless you specify the logging_clause in the PARTITION description clause)  The default value for the segments associated with the index partitions 
The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE ... ADD PARTITION operations The logging attribute of the index is independent of that of its base table.
.. Not Clear any link or Other place where i can find more on this 2 topics.

Thanks
Re: Any Better Way to Tune this Query [message #226316 is a reply to message #224649] Fri, 23 March 2007 04:33 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
Anytime you do 'order by' it costs some performance.
I do not understand the ROWNUM <= 1000 line. How can you get the count into the millions and such like you did. I am not sure 'first_rows' helps you, especially with order by. The costs do not look too far out of line.

I would never turn off logging for any reason.
I am not positve but in past versions ddl (such as your index logging) is not captured in the archicelogs. Every time you change the structure of the database, such as creating a new tablespace, you must take a full backup.
Re: Any Better Way to Tune this Query [message #226470 is a reply to message #226316] Fri, 23 March 2007 22:06 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Remove the FIRST_ROWS hint. You may just want the fastest performance to get the first 1000 rows, but the DISTINCT and ORDER BY forces Oracle to return the entire result set (millions of rows) before it picks off the first 1000.

FIRST_ROWS is forcing it to use Nested Loops joins and Indexed Access. But with the quantities of data you are accessing, you want ful scans and hash joins.

Ross Leishman
Previous Topic: 10gR2 impossible de lancer DBCONSOLE
Next Topic: Query execution time is fluctuating lot
Goto Forum:
  


Current Time: Wed Dec 07 14:38:57 CST 2016

Total time taken to generate the page: 0.14575 seconds