Home » RDBMS Server » Performance Tuning » hints required
hints required [message #184683] Thu, 27 July 2006 08:04 Go to next message
b_pooja21
Messages: 4
Registered: July 2005
Junior Member
Hi ,

I have following query which takes 1 hr run :


SELECT DISTINCT SUBSTR(consumer.initials,1,1) init,
consumer.surname surname,
address.original_address_line_1 addr1,
address.postcode postcode,
'N/A' policy,
'N/A' expiry
FROM CONSUMER_DIMENSION CONSUMER,
ADDRESS_DIMENSION ADDRESS
WHERE CONSUMER.ALLOW_PHONE <> 'Y'
AND CONSUMER.CURRENT_ROW = 'Y'
AND ADDRESS.ADDRESS_KEY = CONSUMER.ADDRESS_KEY
ORDER BY postcode;

So i tuned it with the following hint

SELECT/*+ use_hash(CONSUMER,ADDRESS) */ DISTINCT SUBSTR(consumer.initials,1,1) init,
consumer.surname surname,
address.original_address_line_1 addr1,
address.postcode postcode,
'N/A' policy,
'N/A' expiry
FROM CONSUMER_DIMENSION CONSUMER,
ADDRESS_DIMENSION ADDRESS
WHERE CONSUMER.ALLOW_PHONE <> 'Y'
AND CONSUMER.CURRENT_ROW = 'Y'
AND ADDRESS.ADDRESS_KEY = CONSUMER.ADDRESS_KEY
ORDER BY postcode;

cost for above hint is

SELECT STATEMENT Optimizer=CHOOSE (Cost=21487 Card=16470412
Bytes=856461424)

But it didn't help . Query is still taking 1 hr to run . Actually it executes in 3 mins but runs for 1 hr till all the rows are fetched . User requires performance improvement on this.

I hv tried all the possible combinations but they didn't help .

Can someone of you please suggest abt any hint.

Request to reply asap as this is urgent.s

Thanks n regards ,
Pooja
Re: hints required [message #184685 is a reply to message #184683] Thu, 27 July 2006 08:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Umm, you're fetching an estimated 16.5 million rows of data in an hour.
Thats over 4500 rows per second.
What makes you think this is slow?

The distinct and the order by will be slowing things down a bit, but I guess you wouldn't have them there if you didn't need them.

What is the plan of the original query?

Also, how do you know it only takes 3 minutes to execute the query and 1 hour to fetch the data? Can you explain to us how you arrived at these figures?
Re: hints required [message #184695 is a reply to message #184685] Thu, 27 July 2006 08:43 Go to previous messageGo to next message
b_pooja21
Messages: 4
Registered: July 2005
Junior Member
Hi ,

Following is the execution plan for the original query :

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21487 Card=16470412
          Bytes=856461424)

   1    0   SORT* (UNIQUE) (Cost=14940 Card=16470412 Bytes=856461424)  :Q114645
                                                                       3003

   2    1     HASH JOIN* (Cost=8392 Card=16470412 Bytes=856461424)     :Q114645
                                                                       3002

   3    2       PARTITION HASH* (ALL)                                  :Q114645
                                                                       3002

   4    3         TABLE ACCESS* (FULL) OF 'CONSUMER_DIMENSION' (Cost=6 :Q114645
          537 Card=16470413 Bytes=345878673)                           3000

   5    2       PARTITION HASH* (ALL)                                  :Q114645
                                                                       3002

   6    5         TABLE ACCESS* (FULL) OF 'ADDRESS_DIMENSION' (Cost=11 :Q114645
          13 Card=14327825 Bytes=444162575)                            3001



   1 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1,C2 C2,C3 C3,C4 C
                                   4,C5 C5 FROM :Q1146453002 ORDER BY C

   2 PARALLEL_TO_PARALLEL          SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SW
                                   AP_JOIN_INPUTS(A2) */ A1.C2 C0,A2.C1

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ADDRES
                                   S_KEY" C0,A1."SURNAME" C1,A1."INITIA

   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ADDRES
                                   S_KEY" C0,A1."ORIGINAL_ADDRESS_LINE_



I use sql plus commands like set time on and set timing on to know how much time query takes to execute .

Once the display of the rows begin , I do cntrl C in unix to know the time. It shows me 3mins ..But when i let the entire rows to be fetched , it takes 1 hr.

Can u pls help

Thanks n Regards ,
Pooja

[Updated on: Thu, 27 July 2006 09:02] by Moderator

Report message to a moderator

Re: hints required [message #184697 is a reply to message #184695] Thu, 27 July 2006 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Right. So whats taking an hour is getting all the data to display in sql*Plus.
How long does it take to display in the application you are using it in (and what are you doing with these 16 million rows?)
Re: hints required [message #184699 is a reply to message #184697] Thu, 27 July 2006 08:54 Go to previous messageGo to next message
b_pooja21
Messages: 4
Registered: July 2005
Junior Member
Hi ,

Actually , I work in support where in users directly give me the queries which take long for them to run and am suppose to provide hints for them ..

I don't know where and how they use these queries ..

Regards ,
Pooja
Re: hints required [message #184700 is a reply to message #184699] Thu, 27 July 2006 08:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>suppose to provide hints for them
You mean oracle Hints?
Re: hints required [message #184703 is a reply to message #184700] Thu, 27 July 2006 09:03 Go to previous messageGo to next message
b_pooja21
Messages: 4
Registered: July 2005
Junior Member
yes Oracle hints ..
Re: hints required [message #184706 is a reply to message #184703] Thu, 27 July 2006 09:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Methinks, it is not a right tuning approach.
In Oracle,a HINT is just a HINT. CBO may take the HINT or just IGNORE it.
Paraphrasing TomKyte,
use HINTS as the last resort.
Re: hints required [message #184709 is a reply to message #184699] Thu, 27 July 2006 09:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think there's much you can do for this one without knowing what they're doing with it.

It's retrieving 16,5 million rows of data - that is always going to take time.

Try and find out what they're doing with the query, and we may be able to help further.
Re: hints required [message #184916 is a reply to message #184709] Fri, 28 July 2006 11:35 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

Your query performs following:
1. FULL table scab of 'CONSUMER_DIMENSION' ( at least 16,5 M rows )
2. FULL table scan of 'ADDRESS_DIMENSION' ( over 14M rows )
3. Join both tables ( I bet you don't have big enough HASH_AREA_SEIZE ) so IO must be performed
4. SORT to eliminate duplicate rows (DISTINCT).

You can try increasing sort_area_size and hash_area_size values.

How many rows in 'CONSUMER_DIMENSION' table?
How much of them having CURRENT_ROW = 'Y'?
If the relative number of rows having CURRENT_ROW = 'Y' is small (compared to number of rows in table ) -then it may help defining an index on CURRENT_ROW column.

HTH.
Michael
Previous Topic: DBA_FREE_SPACE view in 10g taking more time
Next Topic: Dilemma on Loading and querying.
Goto Forum:
  


Current Time: Thu Dec 08 16:30:29 CST 2016

Total time taken to generate the page: 0.12145 seconds