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: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 24 Sep 2003 03:39:40 -0800
Message-ID: <F001.005D0EC2.20030924033940@fatcity.com>


Hi "New DBA",

Further to what Stephane has said below, the following two stats in your initial post are interesting ...

consistent gets                                       559985
table fetch continued row                             212027

That suggests that there is a fair amount of row chaining or migration in one of the tables.
If it's migration, rather than chaining, you'll get a ~37% reduction in logical reads if you fix it. The following script can often be used to distinguish between chaining and migration. It counts the number of rows for which the first column is not able to be returned from the first row piece. Although it is possible for that to be the case with row chaining, it's more likely a symptom of migration.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all 


accept OwnerName prompt "Owner Name: "
accept TableName prompt "Table Name: "
prompt

set termout off
delete from
  chained_rows
where
  owner_name = '&OwnerName' and
  table_name = '&TableName'
/
@utlchain
column column_name new_value ColumnName
select
  column_name
from
  dba_tab_columns
where
  owner = '&OwnerName' and
  table_name = '&TableName' and
  rownum = 1
/
set termout on
prompt Analyzing table. Please wait ...
analyze table &OwnerName . &TableName list chained rows into chained_rows /
select
  count(*) continued_rows
from
  chained_rows
where
  owner_name = '&OwnerName' and
  table_name = '&TableName'
/
prompt Checking continued rows for migration ... set termout off
column start_value new_value StartValue
select
  m.value start_value
from
  sys.v_$mystat m,
  sys.v_$statname n
where
  n.name = 'table fetch continued row' and   n.statistic# = m.statistic#
/
select /*+ ordered */
  sum(vsize(t.&ColumnName))
from
  chained_rows c,
  &TableName t
where

  c.owner_name = '&OwnerName' and
  c.table_name = '&TableName' and
  t.rowid = c.head_rowid

/
set termout on
select
  m.value - &StartValue migrated_rows
from
  sys.v_$mystat m,
  sys.v_$statname n
where
  n.name = 'table fetch continued row' and   n.statistic# = m.statistic#
/

-----Original Message-----
Stephane Faroult
Sent: Wednesday, 24 September 2003 6:50 PM To: Multiple recipients of list ORACLE-L

Before checking stats, execution plans and the like take a look at your query. I presume that it is generated, otherwise you would probably say that a date belongs to a month by using a BETWEEN the first and the thirty first rather than listing all the 31 days, would you ? Now perhaps the generator could generate a BETWEEN if you are always interested by consecutive days? Note that the GEO table is totally useless in the FROM clause. You return no data from it, and it is not needed to join together two tables you return data from. If you need it to check some data consistency, it should be in a subquery (IN or EXISTS, depending on the volumes of data to process), but best of all the problem should have been tackled at the root with referential integrity constraints. Even if you may have (always those b***y generators) it should be better located in a subquery - by the way, it might help you dispose of the UNIQUE (calling DISTINCT UNIQUE doesn't make it better :-)).

The condition of CUR_SYS_NO should be applied to the column from MEPAI, which you will encounter first, rather than the column from CUR, since they are equal.
 Now you have to decide which of MEPAI or PRODUCTS should be the table you search first. It depends on the selectivity of your data. Be certain that your table and index statistics are up-to-date. If you still feel that Oracle processes it badly, try playing on the optimizer goal (FIRST_ROWS/ALL_ROWS) and if you get a result which satisfies you add it as a hint.

SF

>----- ------- Original Message ------- -----
>From: New DBA <new_dba_on_the_block_at_yahoo.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Tue, 23 Sep 2003 23:39:44
>
>
>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
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

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 - 06:39:40 CDT

Original text of this message

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