Home » RDBMS Server » Performance Tuning » How to reduce the optimizer cost for the below said query (Oracle 10G /Linux)
How to reduce the optimizer cost for the below said query [message #385742] Tue, 10 February 2009 22:49 Go to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Please look into this query and help me to find how the cost and response time can be reduced.

SELECT cnsmr_id
FROM consumer
WHERE edir_gu_id IS NULL
MINUS -- consumer has no transactions
SELECT snd_cnsmr_id
FROM consumer_tran
MINUS -- consumer has no loyalty cards
SELECT cnsmr_id
FROM loyalty_program_mbshp
MINUS
SELECT cp.prm_cnsmr_id
FROM consumer cp
WHERE cp.prm_cnsmr_id != cp.cnsmr_id;

Re: How to reduce the optimizer cost for the below said query [message #385746 is a reply to message #385742] Tue, 10 February 2009 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

You have table & provided us no DDL for it.
You have data & provided us no DML for test data.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Re: How to reduce the optimizer cost for the below said query [message #385756 is a reply to message #385742] Tue, 10 February 2009 23:26 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

@dhanamukesh

Try the following and post the explain plan for both (query posted by you and mine) the statements.

SELECT cn.cnsmr_id
FROM consumer cn
WHERE cn.edir_gu_id IS NULL
  AND cp.prm_cnsmr_id != cp.cnsmr_id
MINUS -- consumer has no transactions
(
SELECT snd_cnsmr_id
FROM consumer_tran
UNION ALL -- consumer has no loyalty cards
SELECT cnsmr_id
FROM loyalty_program_mbshp
)

[Updated on: Tue, 10 February 2009 23:26]

Report message to a moderator

Re: How to reduce the optimizer cost for the below said query [message #385762 is a reply to message #385746] Tue, 10 February 2009 23:41 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
I have tried to combine the first query and the last query with Or condition in the where clause.
the rewritten query is

SELECT cnsmr_id,prm_cnsmr_id
FROM consumer
WHERE (edir_gu_id IS NULL or prm_cnsmr_id != cnsmr_id)
MINUS -- consumer has no transactions
SELECT snd_cnsmr_id,null
FROM consumer_tran
MINUS -- consumer has no loyalty cards
SELECT cnsmr_id,null
FROM loyalty_program_mbshp.

I have attached DDL with this.

  • Attachment: DDL.txt
    (Size: 5.82KB, Downloaded 163 times)
Re: How to reduce the optimizer cost for the below said query [message #385764 is a reply to message #385756] Tue, 10 February 2009 23:45 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Thanks Srinivas.Actually i have already tried with that option.but still the consumer table going for full scan.Cost reduced a bit.. But not much..I need some more...


regards,
Dhanalakshmi.P
Re: How to reduce the optimizer cost for the below said query [message #385788 is a reply to message #385764] Wed, 11 February 2009 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the appropriate information no one can help.

Regards
Michel
Re: How to reduce the optimizer cost for the below said query [message #385790 is a reply to message #385788] Wed, 11 February 2009 01:06 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
I have already sent you the DDL know...


Regards,
Dhanalakshmi.P
Re: How to reduce the optimizer cost for the below said query [message #385791 is a reply to message #385790] Wed, 11 February 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not enough.
You should read the provided links and post the requested information.

Regards
Michel
Re: How to reduce the optimizer cost for the below said query [message #385811 is a reply to message #385791] Wed, 11 February 2009 03:15 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
i couldn't able to catch anything from the links that you provided.Please tell me the things that you need to evaluate the query.

regards,
dhanalakshmi
Re: How to reduce the optimizer cost for the below said query [message #385839 is a reply to message #385762] Wed, 11 February 2009 06:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm pretty sure that your rewritten query is wrong.
SELECT cnsmr_id
 FROM consumer
 WHERE edir_gu_id IS NULL
 MINUS 
 SELECT cp.prm_cnsmr_id
 FROM consumer cp
 WHERE cp.prm_cnsmr_id != cp.cnsmr_id;
is not the same as
SELECT cnsmr_id,prm_cnsmr_id
 FROM consumer
 WHERE (edir_gu_id IS NULL or prm_cnsmr_id != cnsmr_id)


If there are a relatively small number of rows in Consumer where edir_gu_id is null, you could create a function based index on nvl(edir_gu_id,-999) (assuming that edir_gu_id will never by -999) and then rewrite the first clause of the query as
SELECT cnsmr_id
 FROM consumer
 WHERE nvl(edir_gu_id,-999) = -999
Re: How to reduce the optimizer cost for the below said query [message #385912 is a reply to message #385839] Wed, 11 February 2009 20:00 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It's a bit difficult to tell without any information.
  • How many rows in consumer
  • How many of those rows have edir_gu_id IS NULL
  • How many of those remaining rows will be filtered out by each of the MINUS conditions? Treat them separately - i.e. the first condition might filter out 100 rows and the second condition might filter out 100 rows. They might be the same 100 rows, or separate (200 in total). Just tell us how many are filtered by each MINUS condition one at a time - not combined.
  • Finally, how many rows are returned by the query.

There are a number of ways to go about this, and the BEST way will depend on your accurate responses to ALL of the above questions.

Ross Leishman
Re: How to reduce the optimizer cost for the below said query [message #385948 is a reply to message #385912] Wed, 11 February 2009 23:57 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Thanks for your reply..
I have given all the detais down.

How many rows in consumer
Count -- 16164761


How many of those rows have edir_gu_id IS NULL
Count -- 16163799


How many of those remaining rows will be filtered out by each of the MINUS conditions? Treat them separately - i.e. the first condition might filter out 100 rows and the second condition might filter out 100 rows. They might be the same 100 rows, or separate (200 in total). Just tell us how many are filtered by each MINUS condition one at a time - not combined

In the first Minus the count will be 3523456
After executing the second Minus Condition, the count would be
3261775 AND executing the last Minus condition the count will be 3261772.

Finally, how many rows are returned by the query
count -- 3261772


Help me to tune this query asap.

Regards,
Dhanalakshmi.P

Re: How to reduce the optimizer cost for the below said query [message #386032 is a reply to message #385742] Thu, 12 February 2009 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
What is the current response time for this?
Cause if you're getting 3 million rows out of a 16 million row table it's never going to be fast
Re: How to reduce the optimizer cost for the below said query [message #386152 is a reply to message #386032] Thu, 12 February 2009 21:46 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's great information. 3 million rows from 16 million is LOT. There is no way you want this to be indexed; when you are reading so much data, the best way to go is Full Table Scans.

Your current method is pretty good. MINUS is a quite scaleable way to filter large volumes of data. The only problem is that it requires both sides of the MINUS to be sorted in memory. For large volumes, memory will run out and it will page to disk.

You can rewrite it using NOT IN sub-queries. These can be made to use HASH ANTI JOINS instead of sorting. This can be faster, but you are still dealing with some large volumes, so it may still page to disk. Generally speaking, hash anti joins will out-perform MINUS.

SELECT cnsmr_id
FROM consumer
WHERE edir_gu_id IS NULL
AND sys_op_map_nonnull(cnsmr_id) NOT IN (
  SELECT sys_op_map_nonnull(snd_cnsmr_id)
  FROM consumer_tran
  UNION ALL -- consumer has no loyalty cards
  SELECT sys_op_map_nonnull(cnsmr_id)
  FROM loyalty_program_mbshp
  UNION ALL 
  SELECT sys_op_map_nonnull(cp.prm_cnsmr_id)
  FROM consumer cp
  WHERE cp.prm_cnsmr_id != cp.cnsmr_id
);


For an anti-join to work, you CANNOT have nulls in the comparison. SYS_OP_MAP_NONNULL takes care of nulls. You could do the same with NVL(), mapping NULL to another character/number; or you could use AND col IS NOT NULL clauses to remove NULLS altogether.

Ross Leishman
Previous Topic: Environment problem...
Next Topic: Performance
Goto Forum:
  


Current Time: Wed Dec 07 10:59:26 CST 2016

Total time taken to generate the page: 0.09159 seconds