RE: Tuning help required
This probably doesn't apply to the original question, but I would like to point out
that
MEPAI.MPAI_AS_OF_DATE between to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
is not the same as
MEPAI.MPAI_AS_OF_DATE in (to_date('03/01/2003','MM/DD/'), ...,
to_date('03/31/2003','MM/DD/'))
unless all the dates have 0:0:0 for the time portion, for example because of a before
insert or update trigger that sets :new.mpai_as_of_date := trunc (:new.mpai_as_of_date)
Try this and see the difference.
create table orders (order_id number (4), order_date date) ;
begin
for i in 1..24
loop
for j in 1..30
loop
insert into orders (order_id, order_date)
values (j + 30 * (i - 1),
to_date ('200309' || to_char (j, 'FM09') || to_char (i - 1, 'FM09'),
'MMDDHH24')
) ;
end loop ;
end loop ;
commit ;
end ;
/
select count (*) from orders
where order_date between to_date ('20030901', 'MMDD')
and to_date ('20030930', 'MMDD') ;
select count (*) from orders
where order_date in
(to_date ('20030901', 'MMDD'),
to_date ('20030902', 'MMDD'),
to_date ('20030903', 'MMDD'),
to_date ('20030904', 'MMDD'),
to_date ('20030905', 'MMDD'),
to_date ('20030906', 'MMDD'),
to_date ('20030907', 'MMDD'),
to_date ('20030908', 'MMDD'),
to_date ('20030909', 'MMDD'),
to_date ('20030910', 'MMDD'),
to_date ('20030911', 'MMDD'),
to_date ('20030912', 'MMDD'),
to_date ('20030913', 'MMDD'),
to_date ('20030914', 'MMDD'),
to_date ('20030915', 'MMDD'),
to_date ('20030916', 'MMDD'),
to_date ('20030917', 'MMDD'),
to_date ('20030918', 'MMDD'),
to_date ('20030919', 'MMDD'),
to_date ('20030920', 'MMDD'),
to_date ('20030921', 'MMDD'),
to_date ('20030922', 'MMDD'),
to_date ('20030923', 'MMDD'),
to_date ('20030924', 'MMDD'),
to_date ('20030925', 'MMDD'),
to_date ('20030926', 'MMDD'),
to_date ('20030927', 'MMDD'),
to_date ('20030928', 'MMDD'),
to_date ('20030929', 'MMDD'),
to_date ('20030930', 'MMDD')
) ;
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
> Sent: mercredi, 24. septembre 2003 02:20
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Tuning help required
>
>
> Well, I'd start by writing the date part as :
>
> MEPAI.MPAI_AS_OF_DATE between
> to_date('03/01/2003','MM/DD/') and
> to_date('03/31/2003','MM/DD/')
>
>
> It will at the very least make the query easier to read and
> understand
> (also for the optimizer : it will know it's filtering on a
> range instead
> of distinct values).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Tuning help required
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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/24/2003 12:39 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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/'), to_date('03/02/2003','MM/DD/')
, to_date('03/03/2003','MM/DD/'), to_date('03/04/2003','MM/DD/')
, to_date('03/05/2003','MM/DD/'), to_date('03/06/2003','MM/DD/')
, to_date('03/07/2003','MM/DD/'), to_date('03/08/2003','MM/DD/')
, to_date('03/09/2003','MM/DD/'), to_date('03/10/2003','MM/DD/')
, to_date('03/11/2003','MM/DD/'), to_date('03/12/2003','MM/DD/')
, to_date('03/13/2003','MM/DD/'), to_date('03/14/2003','MM/DD/')
, to_date('03/15/2003','MM/DD/'), to_date('03/16/2003','MM/DD/')
, to_date('03/17/2003','MM/DD/'), to_date('03/18/2003','MM/DD/')
! ;, to_date('03/19/2003','MM/DD/'), to_date('03/20/2003','MM/DD/')
, to_date('03/21/2003','MM/DD/'), to_date('03/22/2003','MM/DD/')
, to_date('03/23/2003','MM/DD/'), to_date('03/24/2003','MM/DD/')
, to_date('03/25/2003','MM/DD/'), to_date('03/26/2003','MM/DD/')
, to_date('03/27/2003','MM/DD/'), to_date('03/28/2003','MM/DD/')
, to_date('03/29/2003','MM/DD/'), to_date('03/30/2003','MM/DD/')
, to_date('03/31/2003','MM/DD/')
)
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.
RE: Tuning help required
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, &TableNamet 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 <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >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_CURRENCIESCUR, > EPR_GEOGRAPHIES GEO, > EPR_PRODUCTS PRODUCTS, > MOD_EPR_PRICING_ASSET_INFOMEPAI >WHERE MEPAI.MPAI_ISS_SYS_NO = >PRODUCTS.ISS_SYS_NO >AND MEPAI.MPAI_GEO_SYS_NO = GEO
Re: Tuning help required
Well, I'd start by writing the date part as :
MEPAI.MPAI_AS_OF_DATE between to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
It will at the very least make the query easier to read and understand
(also for the optimizer : it will know it's filtering on a range instead
of distinct values).
regards
Jo
New DBA <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/24/2003 09:39
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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_CURRENCIESCUR,
EPR_GEOGRAPHIES GEO,
EPR_PRODUCTS PRODUCTS,
MOD_EPR_PRICING_ASSET_INFOMEPAI
WHERE &nb! sp; 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/'), to_date('03/02/2003','MM/DD/')
, to_date('03/03/2003','MM/DD/'), to_date('03/04/2003','MM/DD/')
, to_date('03/05/2003','MM/DD/'), to_date('03/06/2003','MM/DD/')
, to_date('03/07/2003','MM/DD/'), to_date('03/08/2003','MM/DD/')
, to_date('03/09/2003','MM/DD/'), to_date('03/10/2003','MM/DD/')
, to_date('03/11/2003','MM/DD/'), to_date('03/12/2003','MM/DD/')
, to_date('03/13/2003','MM/DD/'), to_date('03/14/2003','MM/DD/')
, to_date('03/15/2003','MM/DD/'), to_date('03/16/2003','MM/DD/')
, to_date('03/17/2003','MM/DD/'), to_date('03/18/2003','MM/DD/')
! ;, to_date('03/19/2003','MM/DD/'),
to_date('03/20/2003','MM/DD/')
, to_date('03/21/2003','MM/DD/'), to_date('03/22/2003','MM/DD/')
, to_date('03/23/2003','MM/DD/'), to_date('03/24/2003','MM/DD/')
, to_date('03/25/2003','MM/DD/'), to_date('03/26/2003','MM/DD/')
, to_date('03/27/2003','MM/DD/'), to_date('03/28/2003','MM/DD/')
, to_date('03/29/2003','MM/DD/'), to_date('03/30/2003','MM/DD/')
, to_date('03/31/2003','MM/DD/')
)
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)
10 SORT (UNIQUE) (Cost=503 Card=122 Bytes=8296)
21 CONCATENATION
32 NESTED LOOPS (Cost=223 Card=61 Bytes=4148)
43 HASH JOIN (Cost=223 Card=61 Bytes=3965)
54 INLIST ITERATOR
65 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_PRODUCTS'
(Cost=3 Card=16 Bytes=128)
76 INDEX (RANGE SCAN) OF 'ISS_ISS_INSTR_ID'
(NON-UNIQUE) (Cost=2 Card=16)
84 NESTED LOOPS (Cost=219 Card=4415 Bytes=251655)
98 TABLE ACCESS (BY INDEX ROWID) OF 'EPR_CURRENCIES'
(Cost=1 Card=1 Bytes=21)
109 INDEX (UNIQUE SCAN) OF 'CUR_PK' (UNIQUE)
118 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)
133 INDEX (UNIQUE SCAN) OF 'GEO_PK' (UNIQUE)
142 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
RE: Tuning help required
In addition, since you're using the cost-based optimizer (CBO), make sure
that you've used dbms_stats to gather statistics on all the objects
involved in the query recently. If you have any columns where histograms
would be useful, make sure you've gathered histograms for those columns.
Justin Cave
At 02:49 AM 9/24/2003, you wrote:
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 <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>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_CURRENCIESCUR,
> EPR_GEOGRAPHIES GEO,
> EPR_PRODUCTS PRODUCTS,
> MOD_EPR_PRICING_ASSET_INFOMEPAI
>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/'),
>to_date('03/02/2003','MM/DD/')
> , to_date('03/03/2003','MM/DD/'),
>to_date('03/04/2003','MM/DD/')
> , to_date('03/05/2003','MM/DD/'),
>to_date('03/06/2003','MM/DD/')
> , to_date('03/07/2003','MM/DD/'),
>to_date('03/08/2003','MM/DD/')
> , to_date('03/09/2003','MM/DD/'),
>to_date('03/10/2003','MM/DD/')
> , to_date('03/11/2003','MM/DD/'),
>to_date('03/12/2003','MM/DD/')
> , to_date('03/13/2003','MM/DD/'),
>to_date('03/14/2003','MM/DD/')
> , to_date('03/15/2003','MM/DD/'),
>to_date('03/16/2003','MM/DD/')
> , to_date('03/17/2003','MM/DD/'),
>to_date('03/18/2003','MM/DD/')
> , to_date('03/19/2003','MM/DD/'),
>to_date('03/20/2003','MM/DD/')
> , to_date('03/21/2003','MM/DD/'),
>to_date('03/22/2003','MM/DD/')
> , to_date('03/23/2003','MM/DD/'),
>to_date('03/24/2003','MM/DD/')
> , to_date('03/25/2003','MM/DD/'),
>to_date('03/26/2003','MM/DD/')
> , to_date('03/27/2003','MM/DD/'),
>to_date('03/28/2003','MM/DD/')
> , to_date('03/29/2003','MM/DD/'),
>to_date('03/30/2003','MM/DD/')
> , to_date('03/31/2003','MM/DD/')
> )
>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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UN
RE: Tuning help required
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 <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>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_CURRENCIESCUR,
> EPR_GEOGRAPHIES GEO,
> EPR_PRODUCTS PRODUCTS,
> MOD_EPR_PRICING_ASSET_INFOMEPAI
>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/'),
>to_date('03/02/2003','MM/DD/')
> , to_date('03/03/2003','MM/DD/'),
>to_date('03/04/2003','MM/DD/')
> , to_date('03/05/2003','MM/DD/'),
>to_date('03/06/2003','MM/DD/')
> , to_date('03/07/2003','MM/DD/'),
>to_date('03/08/2003','MM/DD/')
> , to_date('03/09/2003','MM/DD/'),
>to_date('03/10/2003','MM/DD/')
> , to_date('03/11/2003','MM/DD/'),
>to_date('03/12/2003','MM/DD/')
> , to_date('03/13/2003','MM/DD/'),
>to_date('03/14/2003','MM/DD/')
> , to_date('03/15/2003','MM/DD/'),
>to_date('03/16/2003','MM/DD/')
> , to_date('03/17/2003','MM/DD/'),
>to_date('03/18/2003','MM/DD/')
> , to_date('03/19/2003','MM/DD/'),
>to_date('03/20/2003','MM/DD/')
> , to_date('03/21/2003','MM/DD/'),
>to_date('03/22/2003','MM/DD/')
> , to_date('03/23/2003','MM/DD/'),
>to_date('03/24/2003','MM/DD/')
> , to_date('03/25/2003','MM/DD/'),
>to_date('03/26/2003','MM/DD/')
> , to_date('03/27/2003','MM/DD/'),
>to_date('03/28/2003','MM/DD/')
> , to_date('03/29/2003','MM/DD/'),
>to_date('03/30/2003','MM/DD/')
> , to_date('03/31/2003','MM/DD/')
> )
>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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
