RE: Slow running Query.
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Jul 2004 11:26:02 -0400
Hi,
you didn't sent me your init.ora neither a export from that tables as I ask
you
If you do I'll test your query in my computer.
I want to see if you set the QUERY_REWRITE_ENABLED and
QUERY_REWRITE_INTEGRITY parameters, you need them to get cbo uses function
indexes.
-------Original Message-------
From: oracle-l@xxxxxxxxxxxxx
Date: 07/07/04 11:23:51
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow running Query.
Wolfgang and Juan,
First of all sorry for reply after so many days as I was too busy at workp=
lace.
Here is my findings.
After doing compute statistics on the tables. The following query=20
select * from me_client_assets=20
where ca_clcode in=20
( select cl_clcode from client where cl_decmkr=3D'2929292')
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012'
used the required INDEX in both the cases ( 'CHOOSE', 'RULE).
But still on my original query, I was not able to=20
get INDEX being used (in both RULE, CHOSSE sessions).=20
SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),
sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
FROM assets_view,perfclient, client
WHERE
as_clcode=3Dpc_clcode
and as_date=3D '200312'
AND pc_clcode=3Dcl_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'
and cl_decmkr=3D'645010103';
EXPLAIN PLAN (for CHOOSE session)=20
------------
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT =
2923
SORT AGGREGATE =
1
HASH JOIN =
1
NESTED LOOPS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
2
INDEX UNIQUE SCAN PC_CLCODE_IDX =
1
VIEW ASSETS_VIEW =
2
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
So I decided to use table name instead of view in the query as follows
and now finally it uses the required INDEX on table ME_CLIENT_ASSETS
table.
select
sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0))=
,
sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0))
from me_client_assets,client,perfclient
where
ca_clcode=3Dpc_clcode
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D '200312'
AND pc_clcode=3Dcl_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'
and cl_decmkr=3D'32002590'
group by ca_clcode,ca_date
and following is the EXPLAIN PLAN ( same for both CHOOSE, RULE).
SQL> @expsql view
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT
SORT GROUP BY =
1
NESTED LOOPS =
1
NESTED LOOPS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
2
INDEX UNIQUE SCAN PC_CLCODE_IDX =
1
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =
2
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =
1
Once again thanks for all your inputs that helped me make this query work e=
fficiently.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: June 25, 2004 1:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Slow running Query.
In a case like this, the statistics on the tables, columns and indexes are=
vitally important and nobody could accurately reproduce those in a test=20
environment.
Can you export your statistics to a stattab table and post the export of th=
at=20
table to the same website?
Quoting "Kumar, Dharminder" <Dharminder.Kumar@xxxxxxxxx>:
>=20
> As mail server does not allow for SQL files to be attached to the email, I
> =3D
> have put the scripts for tables, views, queries in question at the
> followin=3D
> g web adress, incase somebody is interestig in doing tests.
> http://dharm-renu.tripod.com/sqltuning.htm
>=20
--=20
regards
Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com
----------------------------------------------------------------
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:
