PL/SQL :: Wait Event Starts With SQL*Net Message From Client - Time Wait 178577 Units
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.
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.
One of our Job running long than usual time. I checked the wtait event for which its waiting. its PL/SQL Timer wait. But i noticed the total waits is 179 and timed out is also 179 for PL/SQL Timer. I checked that job that particular procedure calls dbms_lock.sleep in it. I want to know why this wait event is getting timed out ?
Yesterday i got wait event when executed simple select from table.This select was like:
SELECT emp_number from employer where subs_id = 111
I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:
1) SELECT emp_number from employer where subs_id = 111 then 2) Package with this function
Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?
BANNER 1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi 2PL/SQL Release 10.2.0.5.0 - Production 3CORE 10.2.0.5.0 Production 4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production 5NLSRTL Version 10.2.0.5.0 - Production [code]...
Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.
We are experiencing tx row lock wait time over hours. There is no blocking session and it seems that the application hangs. What is funny is that when we gather_stats on the tables, those tx row lock wait are being released.
In our environment we have db link to fetch data from other database. Whenever we try to fetch data using the dblink we receive the wait event "cursor: pin S wait on X" and we do not get any result. The db link works fine. what could be the issue.
i am using the Oracle 10 g 10.2 with the windows 2003 3 sessions from a single User shows the currenncy on the top activity graph of the enterprice manager they are not getting so much resources of system....when i go in the detail of the session i get
cursor: pin S wait on X
i am unable to kill these sessions how could i get rid of these sessions
I am using Forms 6i in Oracle Applications, In the button click event i used set_canvas_proeprty() twice. First time it executes these two statements, second time it does not. If i use any message() statement before these two statements, it executes.
Do we have wait() statement in Forms ?in apps we have app_item_property.set_property(), can we use same command to set canvas property also.
I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?
Is it possible to make the SELECT (PL/SQL is OK ) wait/block in case there are no rows found until another session inserts rows that match the condition?
Something like this
cursor c is select * from TABLE_1 where a=b order by column_a for update skip locked; row c%rowtype; begin open c;
[Code].....
Or the INSERTing session should send some kind of notification (semaphore?) to the reading session?
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.
ERROR at line 1: ORA-20001: ORA-04022: nowait requested, but had to wait to lock dictionary object ORA-06512: at "MDWDBA.MDW_PKG", line 917 ORA-04022: nowait requested, but had to wait to lock dictionary object ORA-06512: at line 1
Ours is Oracle 11.2.0.2.0 Db 4 instances RAC on Unix AIX OS.Since long we are facing problem that CPU utilization reached 100% and reboot is required alteast once or twice a month.On seeing the Events Logs we find that the Event "CURSOR PIN S WAIT FOR X" is consuming a lot of waits.
On analyzing i came to know that we are firing same query from Application 15 to 20 lack times for which a lot of Mutex keeps spining for getting Shared Mode and consumes a large amount of CPU.
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 ).
I have a RAC 48 cores on solaris. I check dbconsole when application performance is very slow and everyone complains, and I see that the main wait is cpu - also on the awr report. however when I check server cpu I see about 80% idle! so how can I make oracle use more cpu power instead of waiting for it? I don't think that parallel is an option here because I can't change the application code.
on our 10.2.0.5 database, when we run full backup, my system performance comes to an halt. we run full backup and then do a validate backup to validate the structure of the database etc. Database performance takes a hit and all of the application connections goes in wait mode: On ASH or AWR - this is the top wait i see:
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
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.
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
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]....
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?
In statspack I find a lot of wait events "ARCH wait on SENDREQ". The database is configured with the LGWR ASYNC attribute. In the documentation I can read that the "ARCH wait"-events may happen in case the database is configured with the ARCH attribute.How is it possible I get these "ARCH wait"-envents and do these wait events have any effect on the "on line" performance of the database? Is it possible that a user that saves data in the database has to wait for thest "ARCH wait"-events?
Does cache buffer chain latch and buffer busy wait event are related to one any another.
Latch definition from Google says : Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA).
what does it mean my protect. Does this mean protects from aging as per LRU algorithm and getting removed from SGA or protect from other processes ,say from example from simultaneously DML operations. or both
Does buffer busy wait event occurs , because of the cache buffer chain latch ?
Most of my day to day work involves the support of DB2 on AIX and z/OS. I support the database infrastructure for one business system that runs on the Oracle RDBMS. The application is Oracle Transportation Management. The non-production environments don't get much traffic. If I sign into the TEST or DEV Enterprise Manager DB Control screen, the following warning is almost always listed on the main page:
Metrics "Database Time Spent Waiting (%)" is at 44.00384 for event class "Concurrency"
Enterprise Manager reports that this metric is continuously fluctuating above and below the warning threshold of 30% of db time when the environment is idle.To investigate(and I am no oracle expert, far from it), I ran the following:
SELECT * FROM v$session_event WHERE WAIT_CLASS='Concurrency' ORDER BY TOTAL_WAITS DESC;
By far, the top two entries in the result are the following: SIDEVENTTOTAL_WAITSTIME_WAITEDAVERAGE_WAITMAX_WAIT 124os thread startup12041423733.5218 359os thread startup150653433.5521 [code]...
Does this indicate a possible problem with the operating system?
we are running Oracle R12.1.3 on DB version 11.2.0.3. I just migrated a created a custom subscription for the oracle.apps.ap.supplier.event Oracle Event. I execute a custom package when this event fires. My package is working fine and I'm getting the expected results. My problem is I keep getting a Workflow notification saying:
Sent: Monday, March 04, 2013 9:14 PM To: SYSADMIN Subject: Action Required: Local Event UNEXPECTED : oracle.apps.ap.supplier.event / 36422
To SYSADMIN Sent 04-MAR-2013 21:13:40 ID 1659929
An Error occurred in the following Event Subscription: Event Subscription
Event Error Name: Event Error Message: No Event Subscriptions exist for this Event Event Error Stack: Event Data: Event Data URL Event Details Event Field Value [code]...
I saw an earlier post about this and have tried changing the Source Type to "External" but that didn't change anything. why Workflow is telling me a subscription doesn't exist when my subscription is executing with no problems.
I am getting "sqlnet message from client" in top 5 wait events and sql*net trace .We are getting waiting for SQL*Net message from client for a huge number of sessions with huge total waits. From session level sql tracing
we figured that the whole query took 7 to 8 Hours to complete. But in trace we see query executed within 10 to 12 minutes and after that it was showing waiting for SQL*Net message from client till next 7 hours.
