If you have a question or suggestion for a blog post, please leave a comment below.
If you need help interpreting an AWR report, you can email it to me (nsavvinv “at” gmail “dot” com) — I will look at it and get back to you. When requesting AWR help, please keep in mind the following:
1. By sending an AWR report to me, you are granting me the right to publish it
2. If the report contains any sensitive information that shouldn’t be disclosed to the general public, it is your responsibility to obfuscate and/or remove prior to sending it
3. A text version of the report is preferable, thanks!
Copied the question from Srinivas here:
Sir..Iam newbie to tuning and am doing sample application using scott schema
SQL> exec dbms_stats.gather_table_stats(user, 'emp', method_opt => 'for all columns size 1'); PL/SQL procedure successfully completed. SQL> select /*+ gather_plan_statistics */ e.empno,e.ename,e.sal,e.deptno,d.deptno,d.dname,d.loc 2 from emp e, dept d 3 where e.deptno=d.deptno; EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC ----- --------------- ----- ------ ------ -------------- ------------- 7369 SMITH 800 20 20 RESEARCH DALLAS 7499 ALLEN 1600 30 30 SALES CHICAGO 7521 WARD 1250 30 30 SALES CHICAGO 7566 JONES 2975 20 20 RESEARCH DALLAS 7654 MARTIN 1250 30 30 SALES CHICAGO 7698 BLAKE 2850 30 30 SALES CHICAGO 7782 CLARK 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT 3000 20 20 RESEARCH DALLAS 7839 KING 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER 1500 30 30 SALES CHICAGO 7876 ADAMS 1100 20 20 RESEARCH DALLAS 7900 JAMES 950 30 30 SALES CHICAGO 7902 FORD 3000 20 20 RESEARCH DALLAS 7934 MILLER 1300 10 10 ACCOUNTING NEW YORK 14 rows selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------- SQL_ID 5rtg4hdm9vpan, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ e.empno,e.ename,e.sal,e.deptno,d.deptno,d.dname,d.loc from emp e, dept d where e.deptno=d.deptno Plan hash value: 615168685 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- |* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 15 | | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | | 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 8 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") Note ----- - dynamic sampling used for this statement 24 rows selected. SQL>Here E-rows and A-rows are same..and elapsing time also not even a second.So i can consider this is a fine tuned query..
Sir..i have one more doubt..what are things i need to check when tuning with cardinality feedback..how can i know where is the bottleneck in the query..Kindly reply me
Hi Srinivas,
you are right, if E-rows match A-rows then there’s probably nothing to do from the tuning point of view. In your example, you are returning all rows from two tables joined on DEPTNO column, so HASH JOIN of FULL TABLE SCANs seems like a perfectly reasonable plan.
More generally, identifying bottlenecks in a query plan is a very interesting topic, and I’m preparing a couple of more posts which would cover it. So stay tuned! :)
If you have any further questions or would like to suggest a topic for a blog post feel free to comment here.
Best regards,
Nikolay
An interesting thing to note is although Srinivas collected statistics,oracle ignores them and goes for dynamic sampling.
Hi Srinivas,
This a very small dataset just 15 rows. So E and A rows would most likely be same. You also collected statistics.This lead the CBO to estimate correctly.
Hi,
This is Alok, can I post a question of unix shell scripting ?
Hi Alok,
this blog is devoted to Oracle-related topics, so this page was meant for Oracle-related questions. You have a better chance of getting an accurate answer posting it on a Unix forum.
Best regards,
Nikolay
Hi Nikolay,
Nice blog. Can you share your mail id?
Regards,
Anand
Hi Anand.
Thanks for visiting my blog. My email is nsavvinv@gmail.com.
Best regards,
Nikolay
Hi Nikolay,
Hope you are doing good.
I have experience in Oracle Database, but new to Oracle Patching.I am facing difficulties in finding the correct patch required for Oracle Clusterware and Database.
The Version of Oracle is 11.2.0.2.0 and platform is windows 2008.
The last patch applied is the Interim Patch 12429531.It will be great if you can publish an article which helps how to find out the patches required.
Thanks,
Could you please
Hi Wilson,
thank you for your interest in my blog. Unfortunately, my interests in Oracle in the moment are limited to performance optimization and troubleshooting so you probably have better luck asking patch-related questions on an OTN forum.
Best regards,
Nikolay
Dear Sir,
For answering the following question “If the query is running slowly then what you will check and how you find the bottlenecks in the query” . The below answer is the proper one?
First i will update the statistics of all the tables using dbms_stats.gather_table_stats(user, ‘table name’, method_opt => ‘for all columns size 1′); and then i will use select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’)); from that i can find E-rows and A-rows are matching or not?
Sir is it will be the proper answer? can you please correct/modify/expand my answer
Many Thanks
Srini
Hi Srini,
thank you for your question. Updating optimizer stats can sometimes help get a better plan. However, you should do things in a more systematic way, i.e. first you should see what the problem is and then think about how to fix it.
In your example, you’re doing it in a wrong order: first you are trying to fix it (using a method which may or may not be helpful in this particular situation), and then you’re trying to obtain diagnostic information (which may have changed because of updating optimizer stats in the previous step).
I suggest that you first do cardinality feedback tuning (and do make sure that you set STATISTICS_LEVEL=ALL before running the statement), see where the wrong cardinality estimate originates, and then think about fixing it.
In many cases updating statistics won’t help because the problem is something else (e.g. correlated predicates).
Hope this answers your question.
Best regards,
Nikolay
hi man,very nice…
Thanks for sharing details on Blog and its nice
Just wanted to ask one question.
I have sent the AWR report to you last night..
DB Time is 346,090.59 (346,090.59 / 60 = 5,768.18 (minutes) Perfect.
On which basis we have to calculate this value (DB Time 346,090.59 )
OR Which parameter we have to look in AWR to calculate this value 346,090.59 )
DB version is 10.2.0.4 and total number of CPU is around 64.
Great help.
Thanks
Mohammed.
Hi Mohammed,
thanks for reading my blog!
I’m not quite sure if I understand your question. We already know DB time from the AWR report, so we don’t need to calculate it.
If your question is about the meaning of this value, then it’s the amount of times spent by all database foreground sessions either running on CPU or waiting for some non-idle event. In other word, it’s the amount of time user sessions are in the active state (and that is why if you divide DB time by elapsed time you get average active sessions).
Hope that answers your question. If not, please elaborate and I will try to answer your question again.
Best regards,
Nikolay
Thanks for reply..
if divide (5,768.18 min / 59.87 (mins) ) = 96.3 minute. you means that 96 session was active.. but the unit of divide is in minute.. I have not seen in AWR the average active session some wher around 96.
Please shed some lights..
If you divide 5768.18 minutes by 59.87 minutes you get 96.3. Not 96.3 minutes, just 96.3.
The calculation looks like around 96 average active sessions.
96 Average active sessions * Elaspsed Time = each active session has took this much time
(96* 60 = 5786 minutes)
This is equal to DB time.
Thanks
Mohammed.
Hello Nikolai,
You helped me today on a tuning issue on the Oracle forum (https://forums.oracle.com/forums/thread.jspa?threadID=2505947&tstart=15) and I discovered your blog which is fantastic ! Thanks for sharing with the “newbies” on performance tuning your knowledge.
Your advice with the index worked and my query runs faster now ! Thanks for that !
However, I still have an issue with the wrong cardinality and the Nested Loops being very slow.
Do you have any more advices on how to keep tuning this query ?
Many thanks for your help.
Here is the link of my post on the Oracle Forum : https://forums.oracle.com/forums/thread.jspa?threadID=2505947&tstart=15
Sorry I misspelled your name : Nikolay !