Performance Tuning :: Top Wait Events - Specific Document To Analyze AWR Report

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


ADVERTISEMENT

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 View Related

Performance Tuning :: Digging Out Wait Events

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

Performance Tuning :: Maximum Wait Events On PX Deq - Execute Reply?

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

Performance Tuning :: AWR Report For Specific Date

Jun 4, 2010

I want to take AWR report at specific date ex(25-05-2010).

View 2 Replies View Related

Performance Tuning :: Analyze Stats Running Long

Jul 31, 2013

I have a Datamart DB which has a continuously increasing volume. I run a daily optimize job, to have the data analysed using the below:-

EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );

analyze table xxx.abc compute statistics; But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.

Is there a better way of running this optimization?

View 9 Replies View Related

Performance Tuning :: Audit Analyze Table On 9i Database?

Apr 19, 2011

I support to get a handle on statistics collectionn in their data warehouses. It seems developers have created several ANALYZE TABLE jobs but the code for these is not stored as PLSQL in the database and thus it is problematic for statistics collection. Even if we collect stats that way we want, these jobs kick in and overlay the statistics we collect every day.

Is there a way to AUDIT ANALYZE TABLE? I can't find it anywhere.

Is there a way to globally turn of ANALYZE TABLE in a 9i database?

View 2 Replies View Related

Performance Tuning :: Top 5 Timed Events?

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

Performance Tuning :: Analyze Table Compute Statics To Gather Stats

Sep 11, 2012

I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.

View 1 Replies View Related

Performance Tuning :: Oracle Server - Analyze Table In Main Database

Jul 13, 2010

I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.

I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;

I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.

View 8 Replies View Related

Performance Tuning :: Wait / Sleep Command In SQL?

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

Performance Tuning :: Buffer Busy Wait?

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

Performance Tuning :: Buffer Busy Wait DBMS_ALERT_INFO

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

Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT

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

Performance Tuning :: How To Avoid Virtual Circuit Wait As Application Developer

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

Turning Off Trace On Specific Deadlocks Events?

Jun 17, 2013

Can I disable tracing just on deadlocks events on 11gR2?One of our applications provoke several deadlocks. I've warned the developers team several times, but it's taking time to solve it. However, these deadlocks generate trace files of several sizes (from 300M to 3G ..)The Deadlock Graph on them are very useful but the PROCESS STATE section are too long, and until the developers team fix the problem they are just filling up my user dump dest.  Can I disable them or disable the PROCESS STATE section from them?

View 2 Replies View Related

How To Analyze Locks And Performance Of Database

Sep 6, 2010

I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.

How to do proper performance checkup in ORACLE database as well analyze it.

View 4 Replies View Related

Performance Tuning :: AWR Report Evaluation

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

Performance Tuning :: No Snapshot Available For AWR Report

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

Performance Tuning :: Get AWR Report Data?

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

Performance Tuning :: Time Calculation In AWR Report

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

Performance Tuning :: Generate HTML AWR Report?

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

Performance Tuning :: AWR Report In 11g Standard Not Supported?

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

Performance Tuning :: Database Time In AWR Report

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

Performance Tuning :: How To Read And Understand AWR Report

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

Performance Tuning :: AWR Report - Find Disk I/O

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

Performance Tuning :: Oracle Documentation On Details Of AWR Report

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

Performance Tuning :: AWR Report Generation For Short Duration

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

Performance Tuning :: Application (software / Utility) For Report?

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

Performance Tuning :: ASH Report - SQL Has Been Executed For 1567 Times?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved