|
Replies:
32
-
Pages:
3
[
1
2
3
| Next
]
-
Last Post:
Jun 11, 2009 4:17 PM
Last Post By: Randolf Geist
|
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Performance tuning Issue
Posted:
Jun 10, 2009 10:41 AM
|
|
|
Hi folks,
I having a problem with performance tuning ... Below is a sample query
SELECT /*+ PARALLEL (K 4) */ DISTINCT ltrim(rtrim(ibc_item)), substr(IBC_BUSINESS_CLASS, 1,1)
FROM AAA K
WHERE ltrim(rtrim(ibc_item)) NOT IN
(
select /*+ PARALLEL (II 4) */ DISTINCT ltrim(rtrim(THIRD_MAINKEY)) FROM BBB II
WHERE SECOND_MAINKEY = 3
UNION
SELECT /*+ PARALLEL (III 4) */ DISTINCT ltrim(rtrim(BLN_BUSINESS_LINE_NAME)) FROM CCC III
WHERE BLN_BUSINESS_LINE = 3
)
The above query is having a cost of 460 Million. I tried creating index but oracle is not using index as a FT scan looks better. (I too feel FT scan is the best as 90% of the rows are used in the table)
After using the parallel hint the cost goes to 100 Million ....
Is there any way to decrease the cost ...
Thanks in advance for ur help ! |
|
|
Posts:
519
Registered:
05/10/09
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 10:53 AM
in response to: Nalla (Nallasiv...
|
|
|
DBMS_STATS.SET_TABLE_STATS (ownname, tabname, numrows => 0, numblks=>0);
Bartek
Edited by: brtk on 2009-06-10 19:58 added numblks |
|
|
Posts:
169
Registered:
01/06/07
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 10:56 AM
in response to: Nalla (Nallasiv...
|
|
|
|
As you are using NOT IN, soritng is not needed.
You may use UNION ALL instead. You can gain sorting cost.
By
Vamsi
|
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 10:58 AM
in response to: brtk
|
|
|
|
Bartek,
Could you be bit more clear .... I see from google DBMS_STATS.SET_TABLE_STATS is going to set table related information ... But how is that gonna reduce the cost ????
|
|
|
Posts:
519
Registered:
05/10/09
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:03 AM
in response to: Nalla (Nallasiv...
|
|
|
|
Simply. Optimizer estimates cost based on statistics. If you provide false statistics than optimizer gives you false cost.
Of course, the time of execution won't change. But you didn't ask for that.
Treat this advise as a joke, please.
Bartek
|
|
|
Posts:
4,483
Registered:
05/07/02
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:05 AM
in response to: Nalla (Nallasiv...
|
|
|
Hi,
Please read When your query takes too long ....
And:
Have you tried using NOT EXISTS instead of NOT IN already?
untested
select distinct ltrim(rtrim(ibc_item))
, substr(ibc_business_class, 1, 1)
from aaa k
where not exists (select null
from bbb ii
where second_mainkey = 3
and ltrim(rtrim(k.ibc_item)) = ltrim(rtrim(ii.third_mainkey))
union
select null
from ccc iii
where bln_business_line = 3
and ltrim(rtrim(k.ibc_item)) = ltrim(rtrim(iii.bln_business_line_name))
)
|
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:05 AM
in response to: Vamsi Kasina
|
|
|
|
With UNION ALL there is no decrease in cost.It remains the same !
|
|
|
Posts:
1,783
Registered:
10/30/06
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:13 AM
in response to: Nalla (Nallasiv...
|
|
|
Hello, there are a few things with your query:
SELECT /*+ PARALLEL (K 4) */ DISTINCT ltrim(rtrim(ibc_item)), substr(IBC_BUSINESS_CLASS, 1,1)}
ltrim(rtrim()) = TRIM()
WHERE ltrim(rtrim(ibc_item)) NOT IN
Unless you have an index on ltrim(rtrim(ibc_item)), this is a full table scan, and as above, you only need TRIM() here, not LTRIM(RTRIM()). It looks to me like you're using the CHAR data type here, a horrible, horrible idea.
UNION
This should be UNION ALL (regardless of whether you see a cost reduction or not)
And as mentioned, NOT EXISTS (SELECT 1... may work better. |
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:17 AM
in response to: hoek
|
|
|
Hoek,
The cost does not change irrespective of ur new query .... It comes to 400 M |
|
|
Posts:
4,483
Registered:
05/07/02
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:22 AM
in response to: Nalla (Nallasiv...
|
|
|
|
Hi Nalla,
Well, so what? Cost doesn't say much about the TIME your queries take on it's own.
There's cardinality as well as bytes en time spent executing.
I'd be more interested in the complete set of them instead of focusing on cost in the first place.
Why are you so eager on the cost, and why don't you provide us some additional info, like explain plans, execution times etc. ?
Are you getting a FT scan now, using NOT EXISTS instead of NOT IN by the way?
Edited by: hoek on Jun 10, 2009 8:23 PM add. question
|
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:23 AM
in response to: SeánMacGC
|
|
|
|
Sean,
Thanks, you gave given me some useful tips. I have tried with what all you have said and achieved a small decrease in cost ....
Are there any alternate ideas/ Performance tuning steps to reduce the cost so that the query execution is faster....
With this cost I see the execution to go for more than a day ...
|
|
|
Posts:
4,483
Registered:
05/07/02
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:25 AM
in response to: Nalla (Nallasiv...
|
|
|
|
deleted double post, sorry
Edited by: hoek on Jun 10, 2009 8:26 PM
|
|
|
Posts:
4,483
Registered:
05/07/02
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:26 AM
in response to: Nalla (Nallasiv...
|
|
|
steps to reduce the cost so that the query execution is faster....
It just doesn't work that way...it's one thing to measure, yes, but there are many other factors playing a role in the tuning process as well. |
|
|
Posts:
177
Registered:
02/14/08
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:33 AM
in response to: hoek
|
|
|
Hoek,
What you say is true. But, for a cost of 100M I seriously feel the query will take a day to execute irrespective of other parameters ... Well I have executed the query also trying to figure out how to get the explain plan for u guys 
let me know if any one knows .. Im using a SQL Developer |
|
|
Posts:
1,783
Registered:
10/30/06
|
|
|
|
Re: Performance tuning Issue
Posted:
Jun 10, 2009 11:35 AM
in response to: Nalla (Nallasiv...
|
|
|
Be aware too Nalla, that the PARALLEL hint will rule out the use of an index if Oracle adheres to it.
This is what I would try:
SELECT /*+ PARALLEL (K 4) */ DISTINCT TRIM(ibc_item), substr(IBC_BUSINESS_CLASS, 1,1)
FROM AAA K
WHERE NOT EXISTS (
SELECT 1
FROM BBB II
WHERE SECOND_MAINKEY = 3
AND TRIM(THIRD_MAINKEY) = TRIM(K.ibc_item))
AND NOT EXISTS (
SELECT 1
FROM CCC III
WHERE BLN_BUSINESS_LINE = 3
AND TRIM(BLN_BUSINESS_LINE_NAME) = TRIM(K.ibc_item))
But I don't like this at all: TRIM(K.ibc_item), and you never need to use DISTINCT with NOT IN or NOT EXISTS.
Try this:
SELECT DISTINCT TRIM(ibc_item), substr(IBC_BUSINESS_CLASS, 1,1)
FROM AAA K
WHERE NOT EXISTS (
SELECT 1
FROM BBB II
WHERE SECOND_MAINKEY = 3
AND TRIM(THIRD_MAINKEY) = K.ibc_item
AND NOT EXISTS (
SELECT 1
FROM CCC III
WHERE BLN_BUSINESS_LINE = 3
AND TRIM(BLN_BUSINESS_LINE_NAME) = K.ibc_item
This may not work though, since you may have whitespaces in K.ibc_item. |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
