| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running Query.
Juan,
I did try the original query with session mode set to 'CHOOSE'.
Here is the results.
SQL> alter session set optimizer_mode=3D'CHOOSE';
OPERATION OPTIONS OBJECT_NAME =POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT =
6830
SORT AGGREGATE =
1
HASH JOIN =
1
VIEW ASSETS_VIEW =
1
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
HASH JOIN =
2
TABLE ACCESS FULL PERFCLIENT =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
2
INDEX RANGE SCAN CL_DECMKR_IDX =
1
It is even worse as it is doing FULL table scan to two tables now.
Following is the explain plan for the same query in RULE mode.
OPERATION OPTIONS OBJECT_NAME =POSITION
------------------------- ------------------------- -------------------- --=
SORT AGGREGATE =
1
NESTED LOOPS =
1
NESTED LOOPS =
1
VIEW ASSETS_VIEW =
1
SORT GROUP BY =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
2
INDEX UNIQUE SCAN CLIENT_IDX =
1
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
2
INDEX RANGE SCAN PC_CLCODE_IDX =
1
Now in addition to this I tried to run the following query in RULE, CHOOSE = mode and here is the results.
select * from me_client_assets where
ca_clcode in ( select cl_clcode from client where cl_decmkr=3D'2929292')
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012'
Explain Plan Result
for CHOOSE mode
OPERATION OPTIONS OBJECT_NAME =POSITION
------------------------- ------------------------- -------------------- --=
--------
SELECT STATEMENT =
3836
HASH JOIN =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS FULL ME_CLIENT_ASSETS =
2
for RULE mode
OPERATION OPTIONS OBJECT_NAME =
POSITION
------------------------- ------------------------- -------------------- --=
NESTED LOOPS =
1
TABLE ACCESS BY INDEX ROWID CLIENT =
1
INDEX RANGE SCAN CL_DECMKR_IDX =
1
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =
2
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =
1
And now you can note that RULE based explain plan is using index PK_ME_CLIE=
NT_ASSETS,
whereas the CHOOSE based query still uses FULL tablescan.
As mail server does not allow for SQL files to be attached to the email, I = have put the scripts for tables, views, queries in question at the followin= g web adress, incase somebody is interestig in doing tests. http://dharm-renu.tripod.com/sqltuning.htm
Thanks.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes
Pacheco
Sent: June 24, 2004 5:22 PM
To: oracle-l_at_freelists.org
Subject: RE: Slow running Query.
Content-Type: Text/Plain;
charset=3D"iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Why don't you try to use for your session =3D0D
ALTER SESSION SET OPTIMIZER_MODE =3D3Dchoose=3D0D
=3D0D
=3D0D
-------Original Message-------=3D0D
=3D0D
From: oracle-l_at_freelists.org=3D0D
Date: 06/24/04 11:31:10=3D0D
To: oracle-l_at_freelists.org=3D0D
Subject: RE: Slow running Query.=3D0D
=3D0D
First of all it is not type mismatch as clcode column in all the tables =3D
is
defined as VARCHAR.=3D0D
Based on the data the sub query for cl_clcode will return a maximum of 5
rows.=3D0D
=3D0D
The new addition is that I have found from the DBA that we run the databa=3D
se
in RULE optimizer MODE. So I tried to run the explain for following query=3D
=3D2E=3D0D
select * from me_client_assets -- on the table itself not VIEW assets_v=3D
iew=3D0D
where ca_clcode in=3D0D
(select cl_clcode from client where cl_decmkr=3D3D'299292')=3D0D
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D3D'20012'=3D0D
and got the following explain plan.=3D0D
=3D0D
OPERATION OPTIONS OBJECT_NAME =3D=3D20
------------------------- ------------------------- ------------------------------=3D0D
NESTED LOOPS =3D
=3D20
1=3D0D
TABLE ACCESS BY INDEX ROWID CLIENT =3D
=3D20
1=3D0D
INDEX RANGE SCAN CL_DECMKR_IDX =3D
=3D20
1=3D0D
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =3D
=3D20
2=3D0D
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =3D
=3D20
1=3D0D
>Mark,=3D0D
>I tried both the options suggested by you. Following is the result.=3D0D
>=3D0D
>Thought 1.=3D0D
>Query=3D0D
>=3D0D
>SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),=3D0D
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))=3D0D
> FROM assets_view=3D0D
> WHERE as_clcode in ( select cl_clcode from perfclient,client=3D0D
> WHERE cl_decmkr=3D3D3D'64501013'=3D0D
> AND cl_clcode=3D3D3Dpc_clcode=3D0D
> AND pc_prfcomb in ('B', 'Y')=3D0D
> AND pc_grpdte !=3D3D '0000-00-00' -- dkum=
=3D
> AND=3D0D > substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312')=3D0D > and as_date =3D3D'200312'=3D0D
----------------------------------------------------------------=3D0DPlease see the official ORACLE-L FAQ: http://www.orafaq.com=3D0D
----------------------------------------------------------------=3D0DTo unsubscribe send email to: oracle-l-request_at_freelists.org=3D0D put 'unsubscribe' in the subject line.=3D0D --=3D0D
-----------------------------------------------------------------=3D0D
1>***********************************************************************=3D
-- Binary/unsupported file stripped by Ecartis --=3D0D -- Type: application/octet-stream=3D0D -- File: create_schema.sql=3D0D -- Desc: create_schema.sql=3D0D
-- Binary/unsupported file stripped by Ecartis --=3D0D -- Type: application/octet-stream=3D0D -- File: drop_schema.sql=3D0D -- Desc: drop_schema.sql=3D0D
-- Binary/unsupported file stripped by Ecartis --=3D0D -- Type: application/octet-stream=3D0D -- File: q1.sql=3D0D -- Desc: q1.sql=3D0D
-- Binary/unsupported file stripped by Ecartis --=3D0D -- Type: application/octet-stream=3D0D -- File: q2.sql=3D0D -- Desc: q2.sql=3D0D
----------------------------------------------------------------=3D0DPlease see the official ORACLE-L FAQ: http://www.orafaq.com=3D0D
----------------------------------------------------------------=3D0DTo unsubscribe send email to: oracle-l-request_at_freelists.org=3D0D put 'unsubscribe' in the subject line.=3D0D --=3D0D
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 25 2004 - 11:37:36 CDT
 |
 |