Home » RDBMS Server » Performance Tuning » AWR report (oracle 12c - Windows server 2012)
AWR report [message #627910] Tue, 18 November 2014 02:06 Go to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi,

Need your help on AWR report.

We have generated a AWR report on a production DB, here is the details.

With this report I have concluded that "The Memory Area is not being used Much (Shared Pool Area)". If I am right. then how we can utilized it efficiently.

Or else any observation/suggestion from your end.

Cache Sizes 
===================
			Begin	End		
			------	------
Buffer Cache:		87,888M	87,888M		Std Block Size:	8K
Shared Pool Size:	5,008M	5,008M		Log Buffer:	30,540K


Load Profile
===================	Per Second	Per Transaction
			-----------	---------------
Redo size:		15,793,164.54	232,404.16
Logical reads:		555,077.29	8,168.23
Block changes:		137,447.27	2,022.60
Physical reads:		9,393.32	138.23
Physical writes:	2,836.27	41.74
User calls:		1,387.39	20.42
Parses:			337.28		4.96
Hard parses:		0.38		0.01
Sorts:			682.88		10.05
Logons:			0.15		0.00
Executes:		2,485.05	36.57
Transactions:		67.96	 

% Blocks changed per Read:	24.76	Recursive Call %:	79.99
Rollback per transaction %:	0.01	Rows per Sort:	        278.57

Instance Efficiency Percentages (Target 100%) 
=============================================
Buffer Nowait %:		99.97	Redo NoWait %:	        100.00
Buffer Hit %:			98.59	In-memory Sort %:	100.00
Library Hit %:			99.97	Soft Parse %:	        99.89
Execute to Parse %:		86.43	Latch Hit %:	        98.84
Parse CPU to Parse Elapsd %:	10.31	% Non-Parse CPU:	99.95

Shared Pool Statistics 
===================
				Begin	End
				------	-----
Memory Usage %:			57.49	57.81
% SQL with executions>1:	93.13	90.90
% Memory for SQL w/exec>1:	88.26	89.86


Top 5 Timed Events 
===================
Event			  Waits		Time(s)	   Avg Wait(ms)	  % Total    Wait Class
                                                                  Call Time
-----------------------	  -----------	--------   ------------	  --------   ----------
db file sequential read	  68,407,830	685,952	   10		  72.0	     User I/O
CPU time	 	  		107,053	                   11.2	 
db file parallel write	  8,276,249	24,506	   3		   2.6	     System I/O
db file scattered read	  1,578,310	23,072	   15		   2.4	     User I/O
log file sync		  1,257,898	10,698	   9		   1.1	     Commit





Thanks and regards
muktha
Re: AWR report [message #627913 is a reply to message #627910] Tue, 18 November 2014 02:19 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
You can conclude nothing from that snippet of a report. You need to upload the whole thing.
Re: AWR report [message #627914 is a reply to message #627910] Tue, 18 November 2014 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Wow! I'd love to have such databases. Everything's fine.
Yes, you could lower the SGA size but it you have no memory problem on your server, don't touch it.
Have a look at "Shared Pool Advisory" section to get the optimal value.

Re: AWR report [message #627916 is a reply to message #627910] Tue, 18 November 2014 02:44 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
What "problem" are you trying to resolve?
Re: AWR report [message #627932 is a reply to message #627916] Tue, 18 November 2014 03:29 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
My guess is that the problem is a "the database is slow" problem, resulting in unfocused tuning where one picks a figure in a report that one does not like and attempts to "fix" it. If I were going to take this approach (which I strongly disagree with) I would look at the awful ratio of CPU time to waiting time. Muktha's database is losing 74.4% of db time hanging on user I/O. But without the whole report, one can do nothing.
Re: AWR report [message #627948 is a reply to message #627916] Tue, 18 November 2014 03:58 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi,

Here is the data of "Shared Pool Advisory" advisory.

I am facing the problem of Slowness. As you all mentioned, there should not be any issue based on this awr report.
But I am facing slowness, kindly suggest me.

Shared 	 SP 	 Est LC	  Est LC     Est LC 	 Est LC       Est LC 	Est LC 	    Est LC
Pool	 Size		 	     Time	 Time	      Load	Load	    Mem Obj
Size(M)	 Factr	 Size (M) Mem Obj    Saved (s)	 Saved Factr  Time (s)	Time Factr  Hits (K)
=======  ======= ======== =======    =========	 ==========   ========  ==========  ========
2,464	0.49	 574	  73,397     197,998,632	1     255,805	1.03	    662,198
2,976	0.59	 1,060	  139,229    198,005,209	1     249,228	1.01	    662,561
3,488	0.69	 1,570	  262,715    198,006,412	1     248,025	1	    662,622
4,000	0.8	 2,081	  382,156    198,006,805	1     247,632	1	    662,644
4,512	0.9	 2,592	  492,495    198,007,081	1     247,356	1	    662,657
5,024	1	 3,103	  590,012    198,007,268	1     247,169	1	    662,666
5,536	1.1	 3,614	  688,225    198,007,413	1     247,024	1	    662,672
6,048	1.2	 4,125	  770,369    198,007,520	1     246,917	1	    662,676
6,560	1.31	 4,636	  871,230    198,007,571	1     246,866	1	    662,679
7,072	1.41	 5,147	  968,483    198,007,630	1     246,807	1	    662,682
7,584	1.51	 5,658	  1,078,513  198,007,669	1     246,768	1	    662,684
8,096	1.61	 6,169	  1,199,038  198,007,717	1     246,720	1	    662,687
8,608	1.71	 6,680	  1,314,07   198,007,761	1     246,676	1	    662,690
9,120	1.82	 7,191	  1,430,267  198,007,786	1     246,651	1	    662,692
9,632	1.92	 7,702	  1,542,533  198,007,811	1     246,626	1	    662,694
10,144	2.02	 8,213	  1,655,355  198,007,836	1     246,601	1	    662,695



Regards
Muktha
Re: AWR report [message #627949 is a reply to message #627948] Tue, 18 November 2014 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Find the bits of the application that are running slow, trace the sessions involved and look at the tkprof to see where the time is being spent.
Re: AWR report [message #627954 is a reply to message #627948] Tue, 18 November 2014 04:18 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
You can do what you like with the shared pool, and it will have zero effect. Zero.
Post the whole report, not just bits if it.
THere is a term for the tuning method based on trying to "fix" certain metrics whose value you don't like: "compulsive tuning disorder". It is supposed to be funny, but it does have some value. Look it up.
Re: AWR report [message #627956 is a reply to message #627948] Tue, 18 November 2014 04:27 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi John,

The file too big, not able to attach, sorry.

Re: AWR report [message #627957 is a reply to message #627956] Tue, 18 November 2014 04:28 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
Your problem, not mine.
Re: AWR report [message #627958 is a reply to message #627956] Tue, 18 November 2014 04:32 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Can you not just Copy & Paste it onto this forum rather than attach? Don't forget to use the [CODE] tags to ensure readability.
Re: AWR report [message #627963 is a reply to message #627948] Tue, 18 November 2014 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The problem is not in the database, it is in the application.

Re: AWR report [message #627967 is a reply to message #627963] Tue, 18 November 2014 05:53 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi,

The report generated in HTML format, hence I am not able to copy past in the forum in correct order.
If any specific topic you want, then I could do, sorry again.

Hi Michel,

I understood that, there is problem in application side. But could you please suggest the solutions?


Also, why the shared pool memory is not being used much.

Thanks and Regards
Muktha
Re: AWR report [message #627968 is a reply to message #627967] Tue, 18 November 2014 05:57 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
Cookiemonster gave you some advice: find a problematic session and enable SQL trace on it. This will tell you where the time is being spent.
Re: AWR report [message #627969 is a reply to message #627967] Tue, 18 November 2014 06:01 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
You can upload a 2MB file. So zip up the html file, give it a .txt suffix (you can't upload .zip files), and load it up. Do the same with the text version.
Re: AWR report [message #627971 is a reply to message #627969] Tue, 18 November 2014 06:09 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi John,

Thanks,

Here is the full file in zipped format.

Thanks and Regards
Muktha
  • Attachment: DB Name.txt
    (Size: 318.19KB, Downloaded 322 times)
Re: AWR report [message #627972 is a reply to message #627968] Tue, 18 November 2014 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

gazzag wrote on Tue, 18 November 2014 12:57
Cookiemonster gave you some advice: find a problematic session and enable SQL trace on it. This will tell you where the time is being spent.


Another point is to find SQL with high execution numbers and check if they are executed in a loop.

Re: AWR report [message #627973 is a reply to message #627971] Tue, 18 November 2014 06:13 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
That isn't an html file.
Re: AWR report [message #627974 is a reply to message #627973] Tue, 18 November 2014 06:16 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
It is also for a 10.2.0.5 database, and you said in your title that this was 12c.
And you have truncated a lot of the necessary information by converting it to Word.

You really do make it hard for anyone to assist Smile

Re: AWR report [message #627976 is a reply to message #627971] Tue, 18 November 2014 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"HVBE_BillDet_Aggregate" (highest in Elapse time, cpu time, gets) is, with no doubt, the highest consummer, review its code, check it does not execute "SELECT REFERENCEID, STDOFFSET..." (I think it is sql_id 8bzsha6wx7saj) in a loop when it can avoid it...

Hopefully those that have written the code has tagged it with module name, so you can easily get all statements it executes just searching for "HVBE_BILLDET_AGGREGATE" in the report.

Re: AWR report [message #628030 is a reply to message #627976] Wed, 19 November 2014 04:53 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi John,

Thanks for the reply,

Sorry for wrongly mentioned the Version Name.

The output was in HTML format only, but I wanted to hide important info, hence I converted that into word format.

Hi Michel,

Thanks for the reply,

You are right, that the module "HVBE_BILLDET_AGGREGATE" is high in Elapse time, cpu time, gets. But this module shows different sql operation ever where in the report eg- Delete, Insert, Select... so how we can consider this is a repeated statement, if I am not wrong.

With the below mentioned query we can come to know the number of executions. Am I right.

select a.sql_id, a.ratio, a.executions
from  
(
  select sql_id, buffer_gets / executions ratio, executions
  from v$sql
  where executions > 10
  order by 2 desc
) a
where rownum <= 10


Thanks and Regards
Muktha
Re: AWR report [message #628032 is a reply to message #628030] Wed, 19 November 2014 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But this module shows different sql operation ever where in the report eg- Delete, Insert, Select... so how we can consider this is a repeated statement,


See "SQL ordered by Executions" section in your report.

Re: AWR report [message #628185 is a reply to message #628032] Fri, 21 November 2014 00:42 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Michel,

Yes I checked in the section of "SQL ordered by Executions", but there is no looping.

Thanks and Regards
Muktha
Re: AWR report [message #628189 is a reply to message #628185] Fri, 21 November 2014 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check again, there is.

Re: AWR report [message #628296 is a reply to message #627972] Sun, 23 November 2014 08:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Tue, 18 November 2014 17:40

Another point is to find SQL with high execution numbers and check if they are executed in a loop.


I don't see any information about hard parsing. Without bind variables, the same SQL with different values would consume more time to hard parse than compared to soft parse. Or, am I missing something?
Re: AWR report [message #628297 is a reply to message #628296] Sun, 23 November 2014 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Example:
For cur in (select object_name from user_objects) loop
  insert into my table values (cur.object_name);
end loop;

No parse, not even soft parse, just many useless executions.

Re: AWR report [message #628303 is a reply to message #628297] Sun, 23 November 2014 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
Neither RAM nor CPU is a bottleneck, so what is left as source of the slowness?

post FULL results from SQL below

SELECT * FROM V$VERSION;
Re: AWR report [message #628305 is a reply to message #628303] Sun, 23 November 2014 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Neither RAM nor CPU is a bottleneck, so what is left as source of the slowness?


The application. Smile

Re: AWR report [message #628307 is a reply to message #628305] Sun, 23 November 2014 12:17 Go to previous messageGo to next message
BlackSwan
Messages: 26201
Registered: January 2009
Location: SoCal
Senior Member
Michel Cadot wrote on Sun, 23 November 2014 10:11

Quote:
Neither RAM nor CPU is a bottleneck, so what is left as source of the slowness?


The application. Smile




AGREED, but OP needs to admit this reality before any real progress can be made.
Re: AWR report [message #628436 is a reply to message #628307] Tue, 25 November 2014 04:40 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi All,

Yes, I Agree there could be problem in Application only.

But where it is? I am trying to find out the reason from that AWR report.

I am not having access to that particular DB, need to check with that AWR report only.

Thanks and Regards
Muktha
Re: AWR report [message #628438 is a reply to message #628436] Tue, 25 November 2014 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem appears to be that a particular query is run more than necessary. So you need to find that query in the application code and see if that code is really running the code as much as needed, or if it's inefficiently running the query too much.
Re: AWR report [message #628446 is a reply to message #628436] Tue, 25 November 2014 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I gave you all the elements you have in the AWR report, you just have to read it and the procedure code.

Re: AWR report [message #628460 is a reply to message #628436] Tue, 25 November 2014 08:26 Go to previous messageGo to next message
Andrey_R
Messages: 246
Registered: January 2012
Location: Euro-Asia
Senior Member

muktha_22 wrote on Tue, 25 November 2014 12:40
Hi All,

Yes, I Agree there could be problem in Application only.

But where it is? I am trying to find out the reason from that AWR report.

I am not having access to that particular DB, need to check with that AWR report only.

Thanks and Regards
Muktha


I think it is legitimate to ask the requester of the investigation to trace the slow process of the application (with binds and waits), using

1. Something like this

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';


2. or this:

EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);



Or any other way you like.

By the way, I downloaded your file, changed the extension to HTML or to DOC - it never showed anything which is not Gibberish.

Regards,
Andrey R.
Re: AWR report [message #628461 is a reply to message #628460] Tue, 25 November 2014 08:31 Go to previous messageGo to next message
gazzag
Messages: 993
Registered: November 2010
Location: Bristol, UK
Senior Member
... binds=>TRUE);


Wink
Re: AWR report [message #628486 is a reply to message #628460] Tue, 25 November 2014 11:40 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Andrey_R wrote on Tue, 25 November 2014 14:26
By the way, I downloaded your file, changed the extension to HTML or to DOC - it never showed anything which is not Gibberish.


It's a zip

[Updated on: Tue, 25 November 2014 11:40]

Report message to a moderator

Re: AWR report [message #629001 is a reply to message #627910] Tue, 02 December 2014 01:11 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Hi.

1. Parse CPU to Parse Elapsd %: 10.31 is quite low, so your application probably generates a lot of SQL statements without bind variables - you will have to rewrite it.

2. You have to generate/post TKPROF reports for all "heavy" processes:
BEGIN HVBE_BillDet_Aggregate(:...
BEGIN DAILYREADINGSUMMARYAGGR
BEGIN DAILYREADINGSUMMARYTODO_...
BEGIN FlatConfigAttribute_Etl(...

ETC.

HTH
Re: AWR report [message #629002 is a reply to message #629001] Tue, 02 December 2014 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1. No, if it was true there would be more hard parses and so more CPU consumed. See %Non-Parse CPU statistic 99.95%, and Execute to Parse 86.43%, so few parses. Also, look at the SQL statements in the report and try to find those with constants.


Re: AWR report [message #629005 is a reply to message #629002] Tue, 02 December 2014 02:15 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
IMHO - Parse CPU to Parse Elapsd = 10% means that parsed statements are WAITING for a parse (latches?) and probably - some serialization occurrs.
2. AWR does NOT catch "short" statements, so no reason to look there at all.
Re: AWR report [message #629006 is a reply to message #629005] Tue, 02 December 2014 02:50 Go to previous messageGo to next message
John Watson
Messages: 7619
Registered: January 2010
Location: Global Village
Senior Member
The total elapsed time on parsing was only 622 seconds, 0.07% of DBRime. So even reducing it to zero will make no difference.
The problem (in the absence of any knowledge of what issues users are facing: an important bit of information that has not been revealed) would seem to be the 72% of DB Time spent on single block reads.
Looking at the SQL ordered by reads, there re three SELECT statements that together were responsible for about one third of this disc I/O. One of them executed three times, the other two did not even finish. If it were my database, I would look at those statements. Perhaps they are using indexed loop joins when scanned hash joins would be better, for example.
Re: AWR report [message #629056 is a reply to message #629006] Tue, 02 December 2014 14:44 Go to previous messageGo to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
9 out of 10 performance problems on an Oracle database eventually work their way back to one or more SQL statements that are performing badly. Your system (no system for that matter) is immune to this generality, so the likelihood is that it too has SQL somewhere that is performing poorly and is in dire need of tuning.

This is why the excellent advice has been given to find that SQL and tune it. Using AWR, locate the expensive SQL, generate query plans for them, then analyze them to try and see where they are going bad. If you can identify these SQL statements and provide us with the plans and other necessary information (which we can tell you what is needed), this team can help you tune those.

It is certainly possible that there is something other than SQL that is causing your poor performance but it is not as likely as a SQL problem.

Quote:
Additionally, if you have been given the task of fixing this "problem" then it is utterly unrealistic to think it can be fixed without access to the actual database that exhibits the problem. You need to go back to whoever gave you the assignment and tell them you want access to the database or else you can't spend any time on it. You won't get anywhere with it otherwise. The access you need must include SELECT ANY DICTIONARY and SELECT on any tables that are part of the application, and the ability to generate query plans with at the least, using EXPLAIN PLAN, and RESOUCE privilege in a schema where you can create your own tables (in order to check results after you change things). You have to have an environment to manipulate that exhibits the problem or you are wasting your time if your goal is to actually fix something.


Good luck. Kevin
Previous Topic: SQL tuning
Next Topic: INTERNAL_FUNCTION appears at explain plan
Goto Forum:
  


Current Time: Sat Oct 20 04:39:52 CDT 2018