Performance Tuning :: AWR Report Network Foreground Wait Events?
Jun 29, 2011
I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU 224 80.70
Other 2,668 0 28 10 9.99
System I/O 4,753 0 9 2 3.23
Administrative 1 0 6 5543 2.00
Commit 357 0 4 11 1.46
[code]....
The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 224 80.70
unspecified wait event 2,666 28 10 9.99 Other
control file sequential read 4,753 9 2 3.23 System I/O
switch logfile command 1 6 5543 2.00 Administrative
log file sync 357 4 11 1.46 Commit
View 8 Replies
ADVERTISEMENT
Jun 24, 2013
We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events
**********************
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
direct path read 308,729 0 21,191 69 58.0 39.5
db file sequential read 208,754 0 3,742 18 39.2 7.0
cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8
[code]....
View 2 Replies
View Related
Oct 15, 2013
I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.
select time,
round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client,
round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock ,
round(max(case when event = 'enq: KO - fast object checkpoint' then
[code]....
Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_ active_ session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.
Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ).
SELECT user_id,
PROGRAM,
machine,
session_id,
SESSION_SERIAL#,
sample_time-7/24,
session_state,
[code]....
View 14 Replies
View Related
Oct 19, 2010
I have a partitioned table with degree for parallelism defined as 10.
I am getting maximum wait events on PX Deq: Execute Reply.For 2 hours trace the wait event is almost 1.5 hour.
I have done some seraching and i found this.
Quote:
In principle:A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition. Unfortunately, this is NOT strictly true.
It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins. You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.
So i have following questions
1) What is the meaning of PX Deq: Execute Reply.
2) Is this not recommended to use DEGREE clause in Partitioning.
3) Defining DEGREE clause in partioning of table will automatically executes DML on table PARALLELY same a PARALLEL hint clause
View 6 Replies
View Related
Aug 31, 2010
In my awr reports I have CPU time(in seconds) as one of top 5 timed events.Does that mean oracle is waiting for my cpu to execute the statements.But my cpu usage is 0-10% always
View 4 Replies
View Related
Mar 15, 2012
How to use wait/Sleep command in SQL. I have to write a script that takes about 20 min to execute.
View 1 Replies
View Related
Dec 20, 2011
I noticed oracle background process ora_fbda_padwsdpr is suffering from buffer busy wait. When i further finding the object, it was on SYS_FBA_FA tables.
what is this is causing BUFFER BUSY WAIT. Also to add we have disabled flashback database.
View 4 Replies
View Related
Oct 2, 2013
I've have a system where the most buffer waits are spent on system tables like
SYS.DBMS_ALERT_INFO
and SYS.DBMS_LOCK_ALLOCATED
Would it convert the system tablespace from dictionary managed to locally managed?
View 7 Replies
View Related
Mar 7, 2012
we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.
View 8 Replies
View Related
Aug 7, 2013
I'm an application developer of an automotive company and developing a lot of database-based applications with either oracle forms or c#.Since we've moved from a 10g rac to 11g using a shared server configuration, the prevailing and overwhelming topic of addm performance analysis is "unusual network wait event" caused by virtual circuit waits. Therefore I cannot use grid control to detect bad sql as I could in 10g anymore, because all "tunable" sql is wiped out by virtual circuit wait.In top activity, I see virtual circuit wait on every type of statement (select, insert...) and pl/sql execution.
What do I have to do as an application developer to avoid virtual circuit waits? Especially in C#: we normally use auto committed dml statements and selects to fill either a datatable or generic list with a data reader. Usually we close a connection after each statement, but/and we are using connection pooling. How can such a activity cause virtual circuit waits? In Oracle Forms: Seems that we have a virtual circuit wait if we show sorted data in a block where not all records are fetched from database. It doesn't make sense to us to rewrite all blocks to always get all records due to performance reasons.
How do I have to write and execute my statements in C#, oracle forms and/or pl/sql to avoid virtual circuit wait?
View 4 Replies
View Related
May 29, 2013
We are experiencing Network waits on one of our 2-node clustered databases...In every 1 hour of clock time we are finding 700-900 seconds of Network waits
From the AWR data I find that "ARCH wait on SENDREQ" is one of the main constituent for these Network waits and as such I suspect Network between Production database and its corresponding database might be slow
Question 1) Does this understanding look correct?
Question 2) Apart from the above what could be the other causes of the Network waits. Can we point out any particular area from following AWR extract...Seeing some gc* waits initially I thought it might be due to slow interconnect between the cluster nodes but some google search denotes it is not the case...So what could be other causes? I mean which network link I would check?
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 22631 22-May-13 10:00:11 976 7.9
End Snap: 22632 22-May-13 11:00:28 978 8.1
Elapsed: 60.29 (mins)
DB Time: 795.66 (mins)
[code].....
View 3 Replies
View Related
May 20, 2010
My DB is oracle10g.
I have AWR report comparison for two different days. I want to find the below things.
1. Which day has better performance?
2. What are the top two findings on the report.
I attached the report.
Here are my answer. Please correct me if i am wrong.
1. Which day has better performance? Second day has higher load. Since redosize is showing very high.
2. What are the top two findings on the report.
a) Compared to two days, first day, little bit more I/0 wait for single block read.
b) Compared to two days, 2nd day, it takes higher CPU.
However, which day is best compared to two days?
View 5 Replies
View Related
Feb 1, 2011
I am trying to generate AWR report for database observation. But I am not getting any snapshot listed there. below is the output of my awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1140984076 AFCCV 1 afccv
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1140984076 1 AFCCV afccv SERVICEDB1
Using 1140984076 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Enter value for num_days: 3
Listing the last 3 days of Completed Snapshots
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
View 8 Replies
View Related
Aug 8, 2012
I am having only select_catalog_role in database. Can I take complete AWR report data from awr views without using DBMS_WORKLOAD_REPOSITORY package?
View 6 Replies
View Related
Dec 12, 2011
Till statspack we had
elapsed time = time spent on waits + time CPU was used
Total time during snaps = Elapsed time + (may be) time waited for CPU...In AWR is it possible to draw such equation? I can see that the AWR report has following elements
1) End Snap time - Begin Snap time
2) DB time - as mentioned at the top of AWR report
3) DB CPU - in "Top 5 Timed Foreground Events" (I assume this is 'CPU used by sesson timing' in statspack)
4) Total of time for all Statistics in "Time Model Statistics"
5) BUSY_TIME + IDLE_TIME - "Operating System Statistics"
Time between 2 snapshots? or what else? Also for which seconds to multiply to 'DB Time(s)' per second and 'DB CPU(s)' per second in Load Profile to get the db time and CPU time?
View 2 Replies
View Related
Jan 10, 2012
I would like to generate HTML awr report and save it to my local machine.
After running $ORACLE_HOME/rdbms/admin/awrrpt.sql in sqlplus and specifying HTML in (Enter value for report_type: HTML) i hvae get numerous html code in my sqlplus prompt.. I want to save the html report in local machine and open it by double clicking on it.. it will be opened in a browser..
View 9 Replies
View Related
Jan 15, 2011
I was trying to generate AWR report, but the report which got generated consist most of the sections without data. Later i came to know that AWR report is not fully supported in 11g? Is that true?
View 6 Replies
View Related
Jun 4, 2010
I want to take AWR report at specific date ex(25-05-2010).
View 2 Replies
View Related
Jul 9, 2012
I understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?
DB Time = sum of database CPU time + waits
Is I/O considered as a part of CPU time?
Does this equation changes with SAN, OS caching?
View 3 Replies
View Related
Sep 9, 2011
One of my procedure recently taking very long time to execute. I'm attaching AWR report. But I don't know how to read and understand this.
View 4 Replies
View Related
Mar 11, 2012
In AWR report, in order to find the disk i/o, Should I see the avg read(ms) under Tablespace I/O and Filesystem I/O columns?
View 1 Replies
View Related
Jun 4, 2012
A coworker of mine asked if there was any documentation from Oracle that listed all of the parts of the AWR report and what each meant. I was taken back because I don't think there is. There are third party books that talk about AWR reports and their predecessor Statspack reports.
Oracle has some notes on their support site about reading an AWR or Statspack report. All I found in the official documentation was some basic information about how to run an AWR report and an overview of what it was. It would be nice to have some sort of documentation that lists out each section and explains the units and purpose.
View 3 Replies
View Related
Nov 5, 2012
Is it possible to generate AWR report for the duration of 5 min? As we know that snapshots are generated for every 1 hour, which we specified in parameter.
By changing the parameter to 2 min, what could be the impact on database?
View 7 Replies
View Related
Jul 28, 2010
Now, I'm tired to capture one-by-part image, and realize.
I've not seen any application or utility to make this work easier to me.
View 4 Replies
View Related
Sep 12, 2012
In ASH report
there is a section that goes like this
SQL ID Planhashed Sampled # of Executions % ActivityEvent% Event Top Row Source
fdy93qpr1227 1567 7.58direct path read 3.65TABLE ACCESS - FULL
does it suggest that this SQL has been executed for 1567 times is this correct .
View 7 Replies
View Related
Oct 2, 2012
We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36%
Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
View 17 Replies
View Related
Jun 11, 2010
How can i differentiate between system issued sql's and user issued sql's in the tkprof report ?
View 5 Replies
View Related
Feb 8, 2012
I have to create the matrix report which shows calender. Looks Like this.
View 14 Replies
View Related
Dec 11, 2012
I am trying to look at wait events for a long running query in TOAD.I start the query on one instance of TOAD and open the Session Browser on another instance.But I am surprised to find that in "TOtal Waits" on the RHS-> SQL*Net message from client is the longest time taking and is already -> 178577 units whereas I have just started the query.
Whereas in the Current Waits it shows DB File Scattered Read currectly as some seconds.
View 5 Replies
View Related
Jul 12, 2010
Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
View 1 Replies
View Related
