Performance degrade

All posts relating to Oracle database administration.

Moderator: Tim...

Performance degrade

Postby younusdba » Sat Nov 23, 2013 6:11 pm

Hi all,

SQL query which is fetching data from five tables in Oracle 11.2.0.1.0 on Linux 5 , taking double of the time to fetch the data before it used to take, i have gathered statistics of all tables by seeing the view dba_tab_statistics under that column stale_statistics , but no affect or impact of that, so could any one suggest me how to tune the SQL query step by step. i would appreciate a lot.

Thanks & Regards.
younusdba
Member
 
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

Re: Performance degrade

Postby Tim... » Sun Nov 24, 2013 9:35 am

Hi.

There are whole books written on this subject. :)

Quick pointers:

1) If you have the diagnostics and tuning pack, consider doing an SQL Tuning advisor check. It's not always perfect, but sometimes it gives quick wins.

http://www.oracle-base.com/articles/10g ... ng-10g.php

2) Get the execution plan, either directly, or via SQL Trace.

http://www.oracle-base.com/articles/9i/dbms_xplan.php
http://www.oracle-base.com/articles/mis ... tkprof.php

3) Check the cardinality of each step in the execution plan. Does it hit approximately the correct number of rows? If not, then it means your stats are not accurately representing your data.

4) Do the order of the steps in the execution plan look correct? Typically you would expect Oracle to use the smaller sets as the driving sets.

5) Do the join operations look appropriate for the size of the sets being joined. For example, large join operations probably should not use nested loops joins as hash joins will be more efficient and vice versa.

6) In the SQL Trace output, you will be able to see what most of the elapsed time is being spent on. This might help point you in the direction of what to do next.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Performance degrade

Postby younusdba » Thu Nov 28, 2013 6:26 pm

Hi tim
thanks for you reply. i know the whole things which you revealed thanks again
could you please share how to force our CBO to use a specific execution plan through SQL_profile.
younusdba
Member
 
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

Re: Performance degrade

Postby Tim... » Thu Nov 28, 2013 6:42 pm

Hi.

Well, strictly speaking an SQL Profile can't force a specific plan. It just provides extra information information to help the optimizer pick a better plan. If you want to fix a plan, you really need to use an SQL Baseline. The difference is explained really clearly here:

https://blogs.oracle.com/optimizer/entr ... nt_between

I've written something on SQL Plan Management here:

http://www.oracle-base.com/articles/11g ... -11gr1.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Performance degrade

Postby younusdba » Thu Nov 28, 2013 6:54 pm

Hi tim,

Appreciate for your suggestion. thanks a lot
younusdba
Member
 
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

Re: Performance degrade

Postby younusdba » Thu Nov 28, 2013 6:59 pm

Tim... wrote:Hi.

Well, strictly speaking an SQL Profile can't force a specific plan. It just provides extra information information to help the optimizer pick a better plan. If you want to fix a plan, you really need to use an SQL Baseline. The difference is explained really clearly here:

https://blogs.oracle.com/optimizer/entr ... nt_between

I've written something on SQL Plan Management here:

http://www.oracle-base.com/articles/11g ... -11gr1.php

Cheers

Tim...




Well, how can we revert back to statistic(previous statistics) for that tables when the query was giving good response by fetching the data from these tables
younusdba
Member
 
Posts: 21
Joined: Mon Oct 28, 2013 5:24 pm

Re: Performance degrade

Postby Tim... » Thu Nov 28, 2013 7:06 pm

Hi.

You can't retrospectively, unless you have saved the old statistics. See Pending stats in 11g.

http://www.oracle-base.com/articles/11g ... statistics

This all involves you planning for protecting against possible future change...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron