| hints required [message #184683] |
Thu, 27 July 2006 08:04  |
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  |
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 |
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 |
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 |
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 #184709 is a reply to message #184699] |
Thu, 27 July 2006 09:07 |
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 |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
