Please help for Tuning sql Query. [message #643653] |
Thu, 15 October 2015 02:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/b69a15eb1f3eaa0ea6558f32108cf3af?s=64&d=mm&r=g) |
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Dear Experts,
I need help regarding sql query.When executing the query in production that time query is taking 3 seconds for execution in production.But,I need to execute the query in milliseconds.In this having 2 tables join.1 table is having cost=1(excellent performing) and another table is having cost=565 with /*+ paralel(4) */.When Increasing the parallel degree then cost is getting reduced and query is getting executed in 1 seconds.But,I cannot give parallel hint more than 4 degree.Is there having any other solution/hint to execute the query faster without giving parallel hint.
Please consider,I cannot paste query in orafaq.
Please give me is there having any generic solution for resolving this type of issue with query.
|
|
|
|
|
|
Re: Please help for Tuning sql Query. [message #643659 is a reply to message #643653] |
Thu, 15 October 2015 03:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If there were a "generic solution" Oracle would implement it by default.
Are you running the same query repeatedly? If so, first, you could result cache it. That will give you spectacular improvements, if the result set does not change between executions. Try it - you have nothing to lose. For example, if you run your query 10 times a second and the data changes only once a second, your average exec time will drop from 3 seconds to 0.3 seconds. Second, stop trying to tune parallel query manually. You need to calibrate your IO, and set parallel_degree_policy=auto. That will allow Oracle to cache the table you are scanning, which would convert all the physical IO to logical IO.
|
|
|
|
|
Re: Please help for Tuning sql Query. [message #643665 is a reply to message #643664] |
Thu, 15 October 2015 03:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
msol25 wrote on Thu, 15 October 2015 09:33Thanks John,
For giving me the solution.But,this query need to be executed through view and view will be accessible by client.So,I think "Auto" clause for parallel query will not possible in my case.
Please suggest me,If its possible. You "think" incorrectly.
|
|
|
|
Re: Please help for Tuning sql Query. [message #643668 is a reply to message #643661] |
Thu, 15 October 2015 03:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13921 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
msol25 wrote on Thu, 15 October 2015 09:29
Thanks Cookie,
In this using parallel hint with degree 4,having cardinality = 11,725,270,cost=725 and bytes=492,461,340.In this expecting to reduce cardinality to improve performance.Please give me some suggestions to reduce cardinality for getting faster results.
There's precisely one suggestion we can give for reducing the cardinality without seeing the query and explain plan - delete some data.
I get that there's a problem with you sharing the query with us but it doesn't change the fact that there aren't generic solutions and we can't possibly know what to suggest without seeing it.
|
|
|
|
|
|
Re: Please help for Tuning sql Query. [message #643757 is a reply to message #643681] |
Sat, 17 October 2015 01:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
1) In order for your query to finish in "milliseconds" it needs to be fetching a very small number of rows. So the first question is, how many rows does the query result contain? Run the query and give us a rowcount.
2) Additionally one needs to know if the cardinality shown in the plan is in fact correct. Please use the GATHER_PLAN_STATISTICS hint, run the query, and then generate a new plan.
select /*+ gather_plan_statistics */ <rest of your query>;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
The resulting plan will show both the estimated and actual cardinalities of each plan step. Once we have this we can post additional information to help you.
3) Also, it appears that you are missing an index on X_TABLE.
CREATE INDEX Y_TABLE_IX99 ON Y_TABLE(SWITCH_BLOCK,EN);
This index will allow you to fetch only those rows in Y_TABLE that match the 2 you are getting from X_TABLE, rather than scanning the entire 11 million rows from Y_TABLE table. Your query should be a precision style query that uses NESTED LOOPS JOIN and INDEX LOOKUPS. But instead it is acting like a warehouse style query by doing a HASH JOIN supported with a FULL TABLE SCAN.
OK so there are some starting ideas.
Kevin
|
|
|