This content has been marked as final. Show 5 replies
Venkat ,Thanks for the quick response
the options that you have provided will work but my limitation is the query cannot be modified and it fires using application, as it fires using application so i cannot set anything at session level.
so I decided to to set the DOP to 10 but no luck.
I am still with the same issue...
Edited by: user513478 on Jan 22, 2013 6:59 AM
Using parallel servers depends on the resources availability, what action that CBO thinks relevant to satisfy the query. Even if your table has DOP set, if the query is only going to use an index to do a range scan and then use table access by index rowid, it won't even use parallel processes. If your query demands a full table scan, it may use parallel servers.
For ex, in a million record table, if you have an unique index on ID column and if you are querying SELECT * FROM TABLE where ID=:id; even though this table may have DOP set, it may not use parallel servers at all as it will be a single index scan followed by table access by ROWID
If you can show the query, original explain plan and DOP of table(s), indexe(s) involved, it may help better
user513478 wrote:What version of Oracle?
stats are good
indexes are good
a select query is taking time 20min , the indexes exists on the where clause columns, I tried the query using parallel hint it gave the results iin 20 sec.
the DOP of the table set to 10, but when I run the query it picks old plan
constraints : I cannot modify the query, it needs to be tuned without modifying it
Tuning SQL without modifying the SQL can be very hard but you may have options.
Could you use a materialized view with automatic query rewrite?
Can you use a SQL profile to affect performance?
Have you looked very+ carefully at the indexes to see if other indexes might be more efficient?
20 minutes to 20 seconds sounds too good to be true. Are you sure you aren't reading cached data and that the 2nd, faster run really is running in parallel?
Like you said optimizer is using the old plan it could be your statistics are accurate for the corresponding objects, If you have set object level parallelism there is no guarantee that that the number of parallel processes would be used. If auto DOP isnt there then the query may or may not have used degree of table parallelism depending on the optimizer, parallel you did try and it forced it use it, the third option for you is to alter session force parallel query ; which you said is not possible in your case. Even if the auto DOP was used and the estimated elapse time for the statement was less than PARALLEL_MIN_TIME_THRESHOLD by default 10 seconds then the statement will run serial. hope it helps. The point is you need to understand optimizer and parallelism concepts.