Home » RDBMS Server » Performance Tuning » Please help for Tuning sql Query. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Please help for Tuning sql Query. [message #643653] Thu, 15 October 2015 02:26 Go to next message
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 #643654 is a reply to message #643653] Thu, 15 October 2015 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Please give me is there having any generic solution for resolving this type of issue with query.


1/ Remove records in the tables
2/ Buy faster hardware
3/ Remove the query from the application

Re: Please help for Tuning sql Query. [message #643655 is a reply to message #643654] Thu, 15 October 2015 02:44 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Quote:


1/ Remove records in the tables
2/ Buy faster hardware
3/ Remove the query from the application



Not a Valid Solution !!!
Re: Please help for Tuning sql Query. [message #643656 is a reply to message #643655] Thu, 15 October 2015 02:52 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
And you're not going to get a valid solution without posting the query since there is no generic approach for improving performance other than parallel and faster hardware.
Re: Please help for Tuning sql Query. [message #643659 is a reply to message #643653] Thu, 15 October 2015 03:28 Go to previous messageGo to next message
John Watson
Messages: 7622
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 #643661 is a reply to message #643656] Thu, 15 October 2015 03:29 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member

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.
Re: Please help for Tuning sql Query. [message #643664 is a reply to message #643659] Thu, 15 October 2015 03:33 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks 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.
Re: Please help for Tuning sql Query. [message #643665 is a reply to message #643664] Thu, 15 October 2015 03:33 Go to previous messageGo to next message
John Watson
Messages: 7622
Registered: January 2010
Location: Global Village
Senior Member
msol25 wrote on Thu, 15 October 2015 09:33
Thanks 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 #643667 is a reply to message #643665] Thu, 15 October 2015 03:40 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Quote:

You "think" incorrectly.


This means we should avoid parallel hint explicitly and parallel_degree_policy=auto should be set at DB level.No other Option?
Re: Please help for Tuning sql Query. [message #643668 is a reply to message #643661] Thu, 15 October 2015 03:43 Go to previous messageGo to next message
cookiemonster
Messages: 13292
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 #643672 is a reply to message #643668] Thu, 15 October 2015 03:51 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Please find Details:


PLAN_TABLE_OUTPUT

Plan hash value: 646199851
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     2 |   190 |  2686  (13)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |                            |     2 |   190 |  2686  (13)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| X_TABLE                    |     2 |   106 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | x_table_IX1                |     2 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS STORAGE FULL  | Y_TABLE                    |    11M|   469M|  2621  (11)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("X_TABLE"."SWITCH_CODE"="Y_TABLE"."SWITCH_BLOCK"(+) AND "X_TABLE"."EN"="Y_TABLE"."EN"(+))
   2 - filter("X_TABLE"."DISCONNECT_DATE">SYSDATE@!)
   3 - access("X_TABLE"."MEMBER_ACCESS_NUMBER"='00000000000920020213')



Please consider facing problem with Y_TABLE.
Re: Please help for Tuning sql Query. [message #643673 is a reply to message #643672] Thu, 15 October 2015 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
A plan without a query is pretty much useless.
Re: Please help for Tuning sql Query. [message #643681 is a reply to message #643673] Thu, 15 October 2015 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And knowing you are on Exadata too.
I suggest you rewrite the query in a most efficient way.
I suggest you appropriately execute it.

Note that what you showed us and what you told us in the original question are 2 different things.

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
Kevin Meade
Messages: 2102
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
Previous Topic: Facing performance issues in simple insert statement from Oracle 10g
Next Topic: Memory issue with PGA and AMM (merged)
Goto Forum:
  


Current Time: Mon Oct 22 12:20:32 CDT 2018