Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tuning help required

Re: Tuning help required

From: <Jared.Still_at_radisys.com>
Date: Wed, 24 Sep 2003 10:44:51 -0800
Message-ID: <F001.005D0F10.20030924104451@fatcity.com> 





In addition to the excellent advice you have already received,
let me congratulate you on taking the time to format your 
code and trace data so that it is readable.



There are folks that are known to skip long posts such as
this when poorly formatted and difficult to read.  ;)



Jared








New DBA <new_dba_on_the_block_at_yahoo.com>
Sent by: ml-errors_at_fatcity.com


 09/24/2003 12:39 AM


 Please respond to ORACLE-L

 

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Tuning help required






Hi All,


I need help in tuning the following query. It takes around 6-7 minutes to 
run. I hope that someone will be able to go through the details and give 
me a few pointers.


I have gathered a few statistics, but don't know where to go from here. 
Please view the mail in a fixed size font e.g. courier to preserve the 
formatting. If the lines wrap over copying and pasting in a text editor 
might help, though I'm not sure.


I apologize for the long message in advance.
Following is the query:


SELECT  UNIQUE 


 MEPAI.MPAI_NAV_MOD ,
 MEPAI.MPAI_NAV_MODS,
 MEPAI.MPAI_SYS_NO,
 MEPAI.MPAI_PAI_SYS_NO,
 MEPAI.MPAI_AS_OF_DATE,




 PRODUCTS.ISS_INSTR_ID PRODUCT_INSTR_ID,


 CUR.CUR_CURRENCY_NAME,
 CUR.CUR_CURRENCY_CODE,
 CUR.CUR_SYS_NO
FROM 
 EPR_CURRENCIES                        CUR,
 EPR_GEOGRAPHIES                       GEO,
 EPR_PRODUCTS                          PRODUCTS,
 MOD_EPR_PRICING_ASSET_INFO            MEPAI


WHERE MEPAI.MPAI_ISS_SYS_NO   = PRODUCTS.ISS_SYS_NO


AND MEPAI.MPAI_GEO_SYS_NO   = GEO.GEO_SYS_NO
AND MEPAI.MPAI_CUR_SYS_NO   = CUR.CUR_SYS_NO
AND MEPAI.MPAI_AS_OF_DATE IN 




 (

   to_date('03/01/2003','MM/DD/YYYY'), to_date('03/02/2003','MM/DD/YYYY')


 , to_date('03/03/2003','MM/DD/YYYY'), to_date('03/04/2003','MM/DD/YYYY')

 , to_date('03/05/2003','MM/DD/YYYY'), to_date('03/06/2003','MM/DD/YYYY')

 , to_date('03/07/2003','MM/DD/YYYY'), to_date('03/08/2003','MM/DD/YYYY')

 , to_date('03/09/2003','MM/DD/YYYY'), to_date('03/10/2003','MM/DD/YYYY')

 , to_date('03/11/2003','MM/DD/YYYY'), to_date('03/12/2003','MM/DD/YYYY')

 , to_date('03/13/2003','MM/DD/YYYY'), to_date('03/14/2003','MM/DD/YYYY')

 , to_date('03/15/2003','MM/DD/YYYY'), to_date('03/16/2003','MM/DD/YYYY')



 , to_date('03/17/2003','MM/DD/YYYY'), to_date('03/18/2003','MM/DD/YYYY')

 ! ;, to_date('03/19/2003','MM/DD/YYYY'), 

to_date('03/20/2003','MM/DD/YYYY')


 , to_date('03/21/2003','MM/DD/YYYY'), to_date('03/22/2003','MM/DD/YYYY')

 , to_date('03/23/2003','MM/DD/YYYY'), to_date('03/24/2003','MM/DD/YYYY')

 , to_date('03/25/2003','MM/DD/YYYY'), to_date('03/26/2003','MM/DD/YYYY')

 , to_date('03/27/2003','MM/DD/YYYY'), to_date('03/28/2003','MM/DD/YYYY')



 , to_date('03/29/2003','MM/DD/YYYY'), to_date('03/30/2003','MM/DD/YYYY')

 , to_date('03/31/2003','MM/DD/YYYY')



 ) 


AND PRODUCTS.ISS_INSTR_ID in (1321,1339,1344,1342,1343,1341,1340) 
AND CUR.CUR_SYS_NO in (200,226)


Order By MEPAI.MPAI_SYS_NO


Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=515 Card=122 Bytes=8296)
   1    0   SORT (UNIQUE) (Cost=503 Card=122 Bytes=8296)

   2    1     CONCATENATION
   3    2       NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
   4    3         HASH JOIN (Cost=223 Card=61 Bytes=3965)
   5    4           INLIST ITERATOR
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' 
(Cost=3 Card=16 Bytes=128)
   7    6               INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' 
(NON-UNIQUE) (Cost=2 Card=16)
   8    4           NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
   9    8             TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' 
(Cost=1 Card=1 Bytes=21)
  10    9               INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
  11    8             TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 
'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
  12   11               INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) 
(Cost=217 Card=92720)
  13    3         INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
  14    2       NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
  15   14         HASH JOIN (Cost=223 Card=61 Bytes=3965)
  16   15           INLIST ITERATOR
  17   16             TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS' 
(Cost=3 Card=16 Bytes=128)
  18   17               INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID' 
(NON-UNIQUE) (Cost=2 Card=16)
  19   15           NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
  20   19             TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES' 
(Cost=1 Card=1 Bytes=21)
  21   20               INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
  22   19             TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 
'MOD_EPR_PRICING_ASSET_INFO' (Cost=218 Card=92720 Bytes=3337920)
  23   22               INDEX (RANGE SCAN) OF 'MPAI_CUR_FK_I' (NON-UNIQUE) 
(Cost=217 Card=92720)
  24   14         INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)


The output of the following query before running the SQL and after running 
the SQL are as follows:


SQL> select names.name, stats.value


  2  from v$session sessions, v$sesstat stats, v$statname names
  3  where stats.statistic# = names.statistic#
  4  and stats.sid = sessions.sid


  5  and sessions.audsid = userenv('SESSIONID')
  6  and value != 0


  7  order by value desc;


Before running the problem query:


NAME                                                   VALUE
------------------------------------------------- ----------
bytes sent via SQL*Net to client                        3640
bytes received via SQL*Net from client                  2587
SQL*Net roundtrips to/from client                         40
user calls                                                38
recursive calls                                           37
session logical reads                                     29
opened cursors cumulative                                 17
buffer is not pinned count                                17
db block gets                                             16
execute count                                             15
parse count (total)                                       15
consistent gets                                           13
no work - consistent read gets                            10
calls to get snapshot scn: kcmgss                          9
table fetch by rowid                                       7
table scans (short tables)                                 4
opened cursors current                                     3
table scan rows gotten                                     2
free buffer requested                                      2
parse time cpu                                             2
parse time elapsed                                         2
physical reads                                             2
table scan blocks gotten                                   2
logons cumulative                                          1
parse count (hard)                                         1
enqueue requests                                           1
hot buffers moved to head of LRU                           1
CPU used when call started                                 1
CPU used by this session                                   1
total file opens                                           1
enqueue releases                                           1
logons current                                             1





After running the problem query:


NAME                                                   VALUE
------------------------------------------------- ----------
buffer is pinned count                               5366822
table fetch by rowid                                 2850954
session logical reads                                 560183
consistent gets                                       559985
no work - consistent read gets                        559871
buffer is not pinned count                            547204
table fetch continued row                             212027
free buffer requested                                 161921
physical reads                                        161920
hot buffers moved to head of LRU                       41068
bytes sent via SQL*Net to client                       20455
redo size                                              17844
sorts (rows)                                           14977
bytes received via SQL*Net from client                 13124
CPU used by this session                                7368
CPU used when call started                              7368
recursive calls                                          929
table scan rows gotten                                   704
db block gets                                            198
sorts (memory)                                           103
db block changes                                         103
user calls                                                93
execute count                                             87
calls to get snapshot scn: kcmgss                         82
SQL*Net roundtrips to/from client                         76
table scan blocks gotten                                  57
opened cursors cumulative                                 55
parse count (total)                                       54
redo entries                                              51
total file opens                                          44
table scans (short tables)                                36
parse time elapsed                                        18
parse time cpu                                            17
rows fetched via callback                                 14
cluster key scans                                         14
cluster key scan block gets                               14
parse count (hard)                                         6
opened cursors current                                     3
enqueue requests                                           3
recursive cpu usage                                        2
logons cumulative                                          1
messages sent                                              1
cleanouts only - consistent read gets                      1
switch current t! o new buffer                               1
free buffer inspected                                      1
enqueue releases                                           1
dirty buffers inspected                                    1
immediate (CR) block cleanout applications                 1
logons current                                             1


The output of the following query after running the problem query:
SQL> select event, total_waits, time_waited, average_wait
  2  from v$session_event events, v$session sessions
  3  where sessions.sid = events.sid


  4  and sessions.audsid = USERENV('SESSIONID')
  5  and time_waited !=0


  6  and event not like 'SQL*Net%'


  7  order by time_waited desc; 


EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Wed Sep 24 2003 - 13:44:51 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US