RE: Slow running Query.
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 12:50:45 -0400
Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi, =0D
First try to get statistics use the =0D
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('ADM','COMPUTE'); package=0D
and try again=0D
=0D
If not work send me to my mail an export from all the tables and the quer=
y
you have.=0D
I will get it work in 9i changing cbo features to 8i (I don't have 8i)
specify the release you use=0D
=0D
Maybe you could be having other problems withthe =0D
=0D
DB_FILE_MULTIBLOCK_READ_COUNT parameter=0D
inlcude there the ini.ora file=0D
=0D
-------Original Message-------=0D
=0D
From: oracle-l@xxxxxxxxxxxxx=0D
Date: 06/25/04 12:38:27=0D
To: oracle-l@xxxxxxxxxxxxx=0D
Subject: RE: Slow running Query.=0D
=0D
Juan,=0D
I did try the original query with session mode set to 'CHOOSE'.=0D
Here is the results.=0D
=0D
SQL> alter session set optimizer_mode=3D3D'CHOOSE';=0D
=0D
=0D
OPERATION OPTIONS OBJECT_NAME =
=3D=0D
POSITION=0D
------------------------- ------------------------- -------------------- =
--=3D=0D
--------=0D
SELECT STATEMENT =
=3D=0D
6830=0D
SORT AGGREGATE =
=3D=0D
1=0D
HASH JOIN =
=3D=0D
1=0D
VIEW ASSETS_VIEW =
=3D=0D
1=0D
SORT GROUP BY =
=3D=0D
1=0D
TABLE ACCESS FULL ME_CLIENT_ASSETS =
=3D=0D
1=0D
HASH JOIN =
=3D=0D
2=0D
TABLE ACCESS FULL PERFCLIENT =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID CLIENT =
=3D=0D
2=0D
INDEX RANGE SCAN CL_DECMKR_IDX =
=3D=0D
1=0D
=0D
It is even worse as it is doing FULL table scan to two tables now.=0D
=0D
Following is the explain plan for the same query in RULE mode.=0D
=0D
OPERATION OPTIONS OBJECT_NAME =
=3D=0D
POSITION=0D
------------------------- ------------------------- -------------------- =
--=3D=0D
--------=0D
SELECT STATEMENT=0D
SORT AGGREGATE =
=3D=0D
1=0D
NESTED LOOPS =
=3D=0D
1=0D
NESTED LOOPS =
=3D=0D
1=0D
VIEW ASSETS_VIEW =
=3D=0D
1=0D
SORT GROUP BY =
=3D=0D
1=0D
TABLE ACCESS FULL ME_CLIENT_ASSETS =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID CLIENT =
=3D=0D
2=0D
INDEX UNIQUE SCAN CLIENT_IDX =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID PERFCLIENT =
=3D=0D
2=0D
INDEX RANGE SCAN PC_CLCODE_IDX =
=3D=0D
1=0D
=0D
Now in addition to this I tried to run the following query in RULE, CHOOS=
E =3D=0D
mode and here is the results.=0D
=0D
select * from me_client_assets where=0D
ca_clcode in ( select cl_clcode from client where cl_decmkr=3D3D'2929292'=
)=0D
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D3D'20012'=0D
=0D
Explain Plan Result=0D
for CHOOSE mode=0D
=0D
OPERATION OPTIONS OBJECT_NAME =
=3D=0D
POSITION=0D
------------------------- ------------------------- -------------------- =
--=3D=0D
--------=0D
SELECT STATEMENT =
=3D=0D
3836=0D
HASH JOIN =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID CLIENT =
=3D=0D
1=0D
INDEX RANGE SCAN CL_DECMKR_IDX =
=3D=0D
1=0D
TABLE ACCESS FULL ME_CLIENT_ASSETS =
=3D=0D
2=0D
=0D
for RULE mode=0D
OPERATION OPTIONS OBJECT_NAME =
=3D=0D
POSITION=0D
------------------------- ------------------------- -------------------- =
--=3D=0D
--------=0D
SELECT STATEMENT=0D
NESTED LOOPS =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID CLIENT =
=3D=0D
1=0D
INDEX RANGE SCAN CL_DECMKR_IDX =
=3D=0D
1=0D
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =
=3D=0D
2=0D
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =
=3D=0D
1=0D
=0D
And now you can note that RULE based explain plan is using index PK_ME_CL=
IE=3D=0D
NT_ASSETS,=0D
whereas the CHOOSE based query still uses FULL tablescan.=0D
=0D
As mail server does not allow for SQL files to be attached to the email, =
I =3D=0D
have put the scripts for tables, views, queries in question at the follow=
in=3D=0D
g web adress, incase somebody is interestig in doing tests.=0D
http://dharm-renu.tripod.com/sqltuning.htm=0D
=0D
Thanks.=0D
=0D
=0D
-----Original Message-----=0D
From: oracle-l-bounce@xxxxxxxxxxxxx=0D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Juan Carlos Reyes=0D
Pacheco=0D
Sent: June 24, 2004 5:22 PM=0D
To: oracle-l@xxxxxxxxxxxxx=0D
Subject: RE: Slow running Query.=0D
=0D
=0D
Content-Type: Text/Plain;=0D
charset=3D3D"iso-8859-1"=0D
Content-Transfer-Encoding: quoted-printable=0D
Why don't you try to use for your session =3D3D0D=0D
ALTER SESSION SET OPTIMIZER_MODE =3D3D3Dchoose=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-------Original Message-------=3D3D0D=0D
=3D3D0D=0D
From: oracle-l@xxxxxxxxxxxxx=3D3D0D=0D
Date: 06/24/04 11:31:10=3D3D0D=0D
To: oracle-l@xxxxxxxxxxxxx=3D3D0D=0D
Subject: RE: Slow running Query.=3D3D0D=0D
=3D3D0D=0D
First of all it is not type mismatch as clcode column in all the tables =
=3D3D=0D
is=0D
defined as VARCHAR.=3D3D0D=0D
Based on the data the sub query for cl_clcode will return a maximum of 5=0D
rows.=3D3D0D=0D
=3D3D0D=0D
The new addition is that I have found from the DBA that we run the databa=
=3D3D=0D
se=0D
in RULE optimizer MODE. So I tried to run the explain for following query=
=3D3D=0D
=3D3D2E=3D3D0D=0D
select * from me_client_assets -- on the table itself not VIEW assets_v=
=3D3D=0D
iew=3D3D0D=0D
where ca_clcode in=3D3D0D=0D
(select cl_clcode from client where cl_decmkr=3D3D3D'299292')=3D3D0D=0D
and substr(ca_date,1,4)||substr(ca_date,6,2)=3D3D3D'20012'=3D3D0D=0D
and got the following explain plan.=3D3D0D=0D
=3D3D0D=0D
OPERATION OPTIONS OBJECT_NAME =
=3D3D=0D
=3D3D20=0D
POSITION=3D3D0D=0D
------------------------- ------------------------- --------------------=0D
----------=3D3D0D=0D
SELECT STATEMENT=3D3D0D=0D
NESTED LOOPS =
=3D3D=0D
=3D3D20=0D
1=3D3D0D=0D
TABLE ACCESS BY INDEX ROWID CLIENT =
=3D3D=0D
=3D3D20=0D
1=3D3D0D=0D
INDEX RANGE SCAN CL_DECMKR_IDX =
=3D3D=0D
=3D3D20=0D
1=3D3D0D=0D
TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS =
=3D3D=0D
=3D3D20=0D
2=3D3D0D=0D
INDEX RANGE SCAN PK_ME_CLIENT_ASSETS =
=3D3D=0D
=3D3D20=0D
1=3D3D0D=0D
=3D3D0D=0D
Now as you can see that this query uses the index PK_ME_CLIENT_ASSETS=0D
despited the query clause=3D3D0D=0D
substr(ca_date,1,4)||substr(ca_date,6,2)=3D3D3D'20012=3D3D0D=0D
=3D3D0D=0D
So it seems to me as if original query does use the required index becaus=
=3D3D=0D
e=0D
of the definition of View assets_view.=3D3D0D=0D
=3D3D0D=0D
I am attaching with following file, if someone is interested in doing som=
=3D3D=0D
e=0D
test.=3D3D0D=0D
1. create_schema.sql ( will create required tables and view).=3D3D0D=0D
2. drop_schema.sql ( will drop all the objects created by above script).=3D=
3D=3D=0D
0D=0D
3. q1.sql ( My original query based on view, which does not use required=0D
Index)=3D3D0D=0D
4. q2.sql (The above query where I use substr(column) in the where clause=
=3D3D=0D
,=0D
but still used Index).=3D3D0D=0D
=3D3D0D=0D
Anyway thanks for all of you who provided your valuable inputs.=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-----Original Message-----=3D3D0D=0D
From: oracle-l-bounce@xxxxxxxxxxxxx=3D3D0D=0D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling=3D3=
D0D=0D
Sent: June 22, 2004 7:10 PM=3D3D0D=0D
To: oracle-l@xxxxxxxxxxxxx=3D3D0D=0D
Subject: RE: Slow running Query.=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
How many cl_clcodes does the subselect return and how does that number=3D=
3D0D=0D
compare to the cardinality of the VW_NSO_1 view in the explain plan?=3D3D=
0D=0D
=3D3D0D=0D
At 11:03 AM 6/22/2004, you wrote:=3D3D0D=0D
>Mark,=3D3D0D=0D
>I tried both the options suggested by you. Following is the result.=3D3D=
0D=0D
>=3D3D0D=0D
>Thought 1.=3D3D0D=0D
>Query=3D3D0D=0D
>=3D3D0D=0D
>SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),=3D3D0D=0D
> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))=3D3D0D=0D
> FROM assets_view=3D3D0D=0D
> WHERE as_clcode in ( select cl_clcode from perfclient,client=3D=
3D0D=0D
> WHERE cl_decmkr=3D3D3D3D'64501013'=3D3D=
0D=0D
> AND cl_clcode=3D3D3D3Dpc_clcode=3D3D0D=0D
> AND pc_prfcomb in ('B', 'Y')=3D3D0D=0D
> AND pc_grpdte !=3D3D3D '0000-00-00' -- =
dkum=3D=0D
=3D3D=0D
ar=0D
added=3D3D0D=0D
> AND=3D3D0D=0D
> substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D3D '200312')=3D3D0D=0D
> and as_date =3D3D3D'200312'=3D3D0D=0D
=3D3D0D=0D
regards=3D3D0D=0D
=3D3D0D=0D
Wolfgang Breitling=3D3D0D=0D
Centrex Consulting Corporation=3D3D0D=0D
www.centrexcc.com=3D3D0D=0D
=3D3D0D=0D
----------------------------------------------------------------=3D3D0D=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=3D3D0D=0D
----------------------------------------------------------------=3D3D0D=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=3D3D0D=0D
put 'unsubscribe' in the subject line.=3D3D0D=0D
--=3D3D0D=0D
Archives are at //www.freelists.org/archives/oracle-l/=3D3D0D=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=3D3D0D=0D
-----------------------------------------------------------------=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
<FONT SIZE =3D3D3D=0D
1>***********************************************************************=
=3D3D=0D
****=0D
=3D3D0D=0D
This e-mail and any attachments may contain confidential and privileged=0D
information. If you are not the intended recipient, please notify the sen=
=3D3D=0D
der=0D
immediately by return e-mail, delete this e-mail and destroy any copies. =
=3D3D=0D
Any=0D
dissemination or use of this information by a person other than the inten=
=3D3D=0D
ded=0D
recipient is unauthorized and may be illegal. Unless otherwise stated,=0D
opinions expressed in this e-mail are those of the author and are not=0D
endorsed by the author's employer.</FONT>=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-- Binary/unsupported file stripped by Ecartis --=3D3D0D=0D
-- Type: application/octet-stream=3D3D0D=0D
-- File: create_schema.sql=3D3D0D=0D
-- Desc: create_schema.sql=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-- Binary/unsupported file stripped by Ecartis --=3D3D0D=0D
-- Type: application/octet-stream=3D3D0D=0D
-- File: drop_schema.sql=3D3D0D=0D
-- Desc: drop_schema.sql=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-- Binary/unsupported file stripped by Ecartis --=3D3D0D=0D
-- Type: application/octet-stream=3D3D0D=0D
-- File: q1.sql=3D3D0D=0D
-- Desc: q1.sql=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
-- Binary/unsupported file stripped by Ecartis --=3D3D0D=0D
-- Type: application/octet-stream=3D3D0D=0D
-- File: q2.sql=3D3D0D=0D
-- Desc: q2.sql=3D3D0D=0D
=3D3D0D=0D
=3D3D0D=0D
----------------------------------------------------------------=3D3D0D=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=3D3D0D=0D
----------------------------------------------------------------=3D3D0D=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=3D3D0D=0D
put 'unsubscribe' in the subject line.=3D3D0D=0D
--=3D3D0D=0D
Archives are at //www.freelists.org/archives/oracle-l/=3D3D0D=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=3D3D0D=0D
-----------------------------------------------------------------=0D
=0D
=0D
-- Binary/unsupported file stripped by Ecartis --=0D
-- Type: image/gif=0D
-- File: IMSTP.gif=0D
=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------=0D
=0D
=0D
<FONT SIZE =3D3D 1>******************************************************=
****=3D=0D
******************=0D
This e-mail and any attachments may contain confidential and privileged i=
nf=3D=0D
ormation. If you are not the intended recipient, please notify the sender=
i=3D=0D
mmediately by return e-mail, delete this e-mail and destroy any copies. A=
ny=3D=0D
dissemination or use of this information by a person other than the
intend=3D=0D
ed recipient is unauthorized and may be illegal. Unless otherwise stated,=
o=3D=0D
pinions expressed in this e-mail are those of the author and are not endo=
rs=3D=0D
ed by the author's employer.</FONT>=0D
=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=0D
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: image/gif
-- File: IMSTP.gif
----------------------------------------------------------------
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:
