Home » RDBMS Server » Performance Tuning » SQL tuning (Oracle, 10gR2, Linux 5.2)
SQL tuning [message #414513] Wed, 22 July 2009 07:22 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Parse And Executing Statements
Next Topic: Viewing Pinned Objects in KEEP cache
Goto Forum:
  


Current Time: Sun Dec 04 06:40:05 CST 2016

Total time taken to generate the page: 0.07296 seconds