Home » RDBMS Server » Performance Tuning » Query tuning
Query tuning [message #185966] Fri, 04 August 2006 09:33 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi,

The following query gives a very high cost.

SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L'
AND AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE) ;

The explain plan for the query is

--------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                      | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |    12 |   684 | 14209 |
|   1 |  HASH JOIN                    |                            |    12 |   684 | 14209 |
|   2 |   SORT UNIQUE                 |                            |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| GENERALCONDITIONMASTER     |     1 |    31 |     3 |
|   4 |     INDEX RANGE SCAN          | PK_GENERALCONDITIONMASTER  |     1 |       |     2 |
|   5 |   TABLE ACCESS FULL           | AGREEMENTMASTER            |   242K|  6154K| 14199 |
--------------------------------------------------------------------------------------------


In the table AGREEMENTMASTER,i've created three bitmap indexes on
columns(CUSERDEFFLAG1,CACCOUNTSTATUS,CPORTFOLIOCODE) whose cardinality is just 5.

After that also,the cost & plan are same.

Please give me some tips,so that i can reduce the cost of the query.
Re: Query tuning [message #186152 is a reply to message #185966] Sun, 06 August 2006 09:53 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You cannot compare cost across queries. The fact that the cost looks high is irrelevant. It just shows that the most expensive operation is that last full table scan.

Whenever you see "full table scan" you must think: "do I need to add an index?", or "can I hint the query to use an existing index".
icon2.gif  Re: Query tuning [message #186682 is a reply to message #185966] Wed, 09 August 2006 00:57 Go to previous messageGo to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Try using EXISTS instead of IN for the sub query check and verify whether TABLE FULL ACCESS is elminated or not.
Re: Query tuning [message #186859 is a reply to message #186682] Wed, 09 August 2006 22:29 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Maybe your system could not work like below:

change sqltext '
SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L'

AND AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE)'

to
'
SELECT agr.szagreementno ,
AGR.SZORGBRANCHCODE,
AGR.CPORTFOLIOCODE,
AGR.SZPRODUCTOFFEREDCODE
FROM AGREEMENTMASTER agr
WHERE AGR.CPORTFOLIOCODE IN(SELECT SZVALUE
FROM GENERALCONDITIONMASTER
WHERE SZSYSTEMNAME='ICAPS'
AND SZCONDITION='COMPANY'
AND SZFROMVALUE=:p_COMPCODE) and
AGR.CUSERDEFFLAG1='N'
AND AGR.CACCOUNTSTATUS='L'
'

pay attention to your system's Optimizer
Re: Query tuning [message #186922 is a reply to message #186859] Thu, 10 August 2006 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That change is unlikely to make any difference unless you are using the RBO, and I think you'd need an older version of the RBO.

For queries where the CBO doesn't run out of permutations, the order of clauses in the Where clause doesn't make a difference to the CBO.
icon6.gif  Re: Query tuning [message #187143 is a reply to message #186922] Fri, 11 August 2006 02:05 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Yes ,what you said souds good!
So,maybe the question's owner could run query explain to find out what had happend.
Re: Query tuning [message #187251 is a reply to message #185966] Fri, 11 August 2006 08:59 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you post the results of following queries:

SELECT COUNT(*), COUNT(DISTINCT SZVALUE),
COUNT(DISTINCT SZSYSTEMNAME || '-' || SZCONDITION || '-' || SZFROMVALUE
FROM GENERALCONDITIONMASTER;


SELECT COUNT(*), COUNT(DISTINCT CUSERDEFFLAG1 ) ,
COUNT(DISTINCT CACCOUNTSTATUS ) ,
COUNT(DISTINCT CPORTFOLIOCODE ),
COUNT(DISTINCT CUSERDEFFLAG1 || '-' || CACCOUNTSTATUS || '-' || CPORTFOLIOCODE )
FROM AGREEMENTMASTER agr

Previous Topic: How to find a SQL query which is taking entire resources of an oracle database
Next Topic: Increasing the SGA size
Goto Forum:
  


Current Time: Wed Dec 07 08:48:52 CST 2016

Total time taken to generate the page: 0.12297 seconds