OraFAQ Forum: Performance Tuning » Please help for Tuning sql Query.

Home » RDBMS Server » Performance Tuning » Please help for Tuning sql Query. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help for Tuning sql Query. [message #643653]

Thu, 15 October 2015 02:26

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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643654 is a reply to message #643653]

Thu, 15 October 2015 02:38

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643655 is a reply to message #643654]

Thu, 15 October 2015 02:44

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 !!!

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643656 is a reply to message #643655]

Thu, 15 October 2015 02:52

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643659 is a reply to message #643653]

Thu, 15 October 2015 03:28

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643661 is a reply to message #643656]

Thu, 15 October 2015 03:29

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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643664 is a reply to message #643659]

Thu, 15 October 2015 03:33

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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643665 is a reply to message #643664]

Thu, 15 October 2015 03:33

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643667 is a reply to message #643665]

Thu, 15 October 2015 03:40

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?

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643668 is a reply to message #643661]

Thu, 15 October 2015 03:43

cookiemonster
Messages: 13963
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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643672 is a reply to message #643668]

Thu, 15 October 2015 03:51

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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643673 is a reply to message #643672]

Thu, 15 October 2015 03:55

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

A plan without a query is pretty much useless.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643681 is a reply to message #643673]

Thu, 15 October 2015 05:01

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Please help for Tuning sql Query. [message #643757 is a reply to message #643681]

Sat, 17 October 2015 01:20

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

Report message to a moderator

Previous Topic:	Facing performance issues in simple insert statement from Oracle 10g
Next Topic:	Memory issue with PGA and AMM (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 16:15:26 CST 2025