RE: Slow running Query.
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Jun 2004 14:08:17 -0400
grumble. we hand it a list of its first two columns of its pk and it doesn't
find that plan?
hmm. I wonder if a type mismatch is happening.
okay, if
where (as_clcode, as_date) in (select cl_code, '200312' ..... doesn't get
it,
then it's time to type check and trace.
Speaking of type checking, your original query 2 used string literals for
both args, so I didn't
worry about it much. If cl_clcode is a number, you might need to convert it
on the query (if the ca_clcode underlying as_clcode is numeric).
ditto for the virtual table thingy.
doh! I just noticed that your as_date is a substr/concat of the underlying
table's column. You might
add an unmodified as_full_date as ca_date to the view, and toss in a
as_full_date like '2003__12%' (is that underbar or a ?
for a single char wildcard in like? anyway use the right one). Then the
optimizer might know it can use the first two columns to some good effect
and that might tip the score.
We're probably doing something that confuses the optimizer about being able
to use as_clcode being the leading edge of an index.
Since it works in your orginal query 2, it seems unlikely it is missing
stats, so I guess I'd wager a donut on a type mismatch at this point.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Kumar, Dharminder
Sent: Tuesday, June 22, 2004 1:04 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow running Query.
Mark,
I tried both the options suggested by you. Following is the result.
Thought 1.
Query
=3D=3D=3D=3D=3D
SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
FROM assets_view
WHERE as_clcode in ( select cl_clcode from perfclient,client
WHERE cl_decmkr=3D'64501013'
AND cl_clcode=3Dpc_clcode
AND pc_prfcomb in ('B', 'Y')
AND pc_grpdte !=3D '0000-00-00' -- dkumar a=
dded
AND substr(pc_grpdte,1,4)||substr(pc_grpdte=
,6,2) <=3D '200312')
and as_date =3D'200312'
Explain Plan
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT
SORT AGGREGATE =
1
MERGE JOIN =
1
SORT JOIN =
1
VIEW ASSETS_VIEW =
1
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
SORT JOIN =
2
VIEW VW_NSO_1 =
1
SORT UNIQUE =
1
NESTED LOOPS =
1
TABLE ACCES BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCES BY INDEX ROWID PERFCLIENT =
2
INDEX RANGE SCAN PC_CLCODE_IDX =
1
Thought 2 ( Hope I got it right)
=3D=3D=3D=3D=3D=3D=3D=3D=3D
Query 2
=3D=3D=3D=3D=3D=3D=3DSELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
FROM assets_view ,(select cl_clcode,rownum from client,perfclient
WHERE cl_decmkr=3D'64501013'
AND cl_clcode=3Dpc_clcode
AND pc_prfcomb in ('B', 'Y')
AND pc_grpdte !=3D '0000-00-00' -- dkumar added
AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312')
where as_clcode =3D cl_clcode
and as_date=3D'200312'
Explain Plan
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT
SORT AGGREGATE =
1
MERGE JOIN =
1
SORT JOIN =
1
VIEW =
1
COUNT =
1
NESTED LOOPS =
1
TABLE ACCES BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCES BY INDEX ROWID PERFCLIENT =
2
INDEX RANGE SCAN PC_CLCODE_IDX =
1
SORT JOIN =
2
VIEW ASSETS_VIEW =
1
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
So you can see that in both the cases, queries are not using the INDEX in q=
uestion.
Let me know if you have any other suggestion.
Thanks.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: June 21, 2004 6:18 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow running Query.
Two thoughts:
Take your first fast query and modify the where clause using the second fast
query as the IN list.
where as_clcode in (select cl_clcode from .....your second fast query)
If you're on a release that supports order by, ordering the IN list query by
cl_clcode *might* make the view's group by operation faster and probably
costs little.
Second thought:
make the entire part of the query that delivers you the cl_clcode list you
need a virtual table in the from clause. If this doesn't "fix" the plan
selected, consider adding a dummy rownum column in the query part of the
virtual table so it forces projection.
Now if your list of cl_clcodes actually delivered is very long, the
sub-seconds for each part may add up to the few minutes you mentioned.
The existence of the referenced features varies on release, and your mileage
may vary.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Kumar, Dharminder
Sent: Monday, June 21, 2004 5:06 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Slow running Query.
> Hi all,
> I have the following query which is running very slow as it takes few min=
=3D
utes to complete.
>=3D20
> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
> FROM perfclient, assets_view, client
> WHERE cl_decmkr=3D3D'64501013'
> AND cl_clcode=3D3Das_clcode
> AND as_date=3D3D '200312'
> AND cl_clcode=3D3Dpc_clcode
> AND pc_prfcomb in ('B', 'Y')
> AND pc_grpdte !=3D3D '0000-00-00'
> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'
>=3D20
> But if I run the following queries the response time is less than a seco=
=3D
nd.
>=3D20
> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
> FROM assets_view
> WHERE as_clcode in '64501013'=3D20
> and as_date=3D3D'200312'
>=3D20
> select cl_clcode from=3D20
> perfclient,client
> WHERE cl_decmkr=3D3D'64501013'
> AND cl_clcode=3D3Dpc_clcode
> AND pc_prfcomb in ('B', 'Y')
> AND pc_grpdte !=3D3D '0000-00-00'
> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'
>=3D20
> Following is the definiton of VIEW assets_view
>=3D20
> CREATE OR REPLACE VIEW retail.assets_view
> ( as_clcode
> , as_date
> , as_ofcsh
> , as_offxd
> , as_ofeqty
> , as_delcsh
> , as_delfxd
> , as_deleqty
> , as_uncsh
> , as_unfxd
> , as_uneqty
> , as_ofai
> , as_delai
> , as_unai
> )
> AS
> select ca_clcode as_clcode,
> substr(ca_date,1,4)||substr(ca_date,6,2) as_date,
> sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_ofcsh,
> sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_offxd,
> sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_ofeqty,
> sum(decode(ca_status, 'D', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_delcsh,
> sum(decode(ca_status, 'D', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_delfxd,
> sum(decode(ca_status, 'D', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_deleqty,
> sum(decode(ca_status, 'U', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_uncsh,
> sum(decode(ca_status, 'U', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_unfxd,
> sum(decode(ca_status, 'U', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=
=3D
)) as_uneqty,
> sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0)) as_ofai,
> sum(decode(ca_status, 'D', nvl(ca_mktval_ai,0),0)) as_delai,
> sum(decode(ca_status, 'U', nvl(ca_mktval_ai,0),0)) as_unai
> from me_client_assets
> group by ca_clcode, ca_date
>=3D20
> (ME_CLIENT_ASSETS table has UNIQUE INDEX pk_me_client_assets
> ON me_client_assets
> ( ca_clcode,
> ca_date,
> ca_status,
> ca_class,
> ca_risk,
> ca_rspelig,
> ca_short,
> ca_foreign )
> CLIENT table has index on column "cl_clcode" and another index on "cl_dec=
=3D
mkr".
> PERFCLIENT table has index on column "pc_clcode"
>=3D20
> So can you suggest some ways to improve this query.
> Thanks in advance.=3D20
>=3D20
>=3D20
>=3D20
>=3D20
> Dharminder Kumar
>=3D20
<FONT SIZE =3D3D 1>********************************************************=
**=3D
******************
This e-mail and any attachments may contain confidential and privileged inf=
=3D
ormation. If you are not the intended recipient, please notify the sender i=
=3D
mmediately by return e-mail, delete this e-mail and destroy any copies. Any=
=3D
dissemination or use of this information by a person other than the intend=
=3D
ed recipient is unauthorized and may be illegal. Unless otherwise stated, o=
=3D
pinions expressed in this e-mail are those of the author and are not endors=
=3D
ed by the author's employer.</FONT>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
<FONT SIZE =3D 1>**********************************************************=
******************
This e-mail and any attachments may contain confidential and privileged inf=
ormation. If you are not the intended recipient, please notify the sender i=
mmediately by return e-mail, delete this e-mail and destroy any copies. Any=
dissemination or use of this information by a person other than the intend=
ed recipient is unauthorized and may be illegal. Unless otherwise stated, o=
pinions expressed in this e-mail are those of the author and are not endors=
ed by the author's employer.</FONT>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
