| SQL tuning [message #414513] |
Wed, 22 July 2009 07:22  |
ivpraveen
Messages: 5
Registered: March 2009
|
Junior Member |
|
|
Hi All,
The below mentioned query takes hell of Temp tablespace more than 30Gbs and it retrieves only 5404 records I want to use cursor to avoid temp prob or other way. How to do that? Rows in the tables are. Explain plan is attached herewith this post.
price:3210870
underlying:162377
domains:315
QUERY : -
SELECT
*
FROM
NXMO.PRICE P,
NXMO.UNDERLYING U,
NXMO.DOMAINS D
WHERE
P.UNDERLYING_ID = U.UNDERLYING_ID AND
U.OCC_ELIGIBLE = 'Y' AND
P.PRICE_ID = (SELECT
MAX(PRICE_ID)
FROM
NXMO.PRICE
WHERE
UNDERLYING_ID = P.UNDERLYING_ID
AND CURRENCY_CODE = 'USD'
AND IS_DELETED = 'N'
) AND U.SECURITY_TYPE = D.DOMAIN_CODE
AND D.DOMAIN_VALUE = 'STOCK'
AND D.DOMAIN_TYPE = 'SECURITY_TYPES'
AND U.IS_DELETED = 'N'
ORDER BY
P.UNDERLYING_ID
|
|
|
|
| Re: SQL tuning [message #414532 is a reply to message #414513] |
Wed, 22 July 2009 08:28  |
saptarshibasu
Messages: 15
Registered: July 2009
|
Junior Member |
|
|
I assume
1. You have updated statistics on the tables and indexes.
2. You use Automatic PGA Allocation.
3. You run the SQL at night when the database load is very low.
The most time consuming part seems to be the correlated subquery on the PRICE table which is producing most data to find out the maximum price_id.
So,
1. first of all try to get rid of the correlated subquery, if you can.
2. If we keep the query unchanged you may try increasing the hash_area_size and sort_area size and check the performance. But, please note that this may leave less memory for the other sessions.
alter session set workarea_size_policy=manual';
alter session set hash_area_size=value_in_bytes;
alter session set sort_area_size=value_in_bytes;
|
|
|
|
| Re: SQL tuning [message #414653 is a reply to message #414532] |
Thu, 23 July 2009 01:59 |
ivpraveen
Messages: 5
Registered: March 2009
|
Junior Member |
|
|
Hi
I agree with you, it's all because of the correlated subquery and I tried my level best but couldn't get rid of this correlated subquery. Could you please help me out.
Thanks
|
|
|
|
| Re: SQL tuning [message #414664 is a reply to message #414513] |
Thu, 23 July 2009 02:28 |
saptarshibasu
Messages: 15
Registered: July 2009
|
Junior Member |
|
|
You may try two options:
1. Try using the WITH clause. You may also want to learn about the hints materialize, no_merge.
For e.g. you can try something like:
WITH max_price_id as
(select max(price_id),UNDERLYING_ID
from ...
where <conditions>
group by underlying_id)
select * from
NXMO.PRICE P,
NXMO.UNDERLYING U,
NXMO.DOMAINS D,
max_price_id M
where <conditions>
and P.underlying_id=max_price_id.underlying_id
OR
2. You may try checking the performance with some analytics in the main query. For e.g. MAX(price_id) OVER (partition by undelying_id, .........).
Let us know how these work...
|
|
|
|
| Re: SQL tuning [message #414682 is a reply to message #414664] |
Thu, 23 July 2009 03:47 |
ivpraveen
Messages: 5
Registered: March 2009
|
Junior Member |
|
|
Hi
I tried With clause as you said but it didn't work i'm attaching explain plan of the query herewith post. please check the query, did i make any mistake coz i got different number of records.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
