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.
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 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.
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?
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.
how to know the trigger event name in trigger calling...like-
CREATE OR REPLACE TRIGGER ****** BEFORE DELETE OR INSERT OR UPDATE OF TOTAL_QUANTITY,CANCELLED ON ********* REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN
-----------I want to know given trigger event at this level-----------
IF INSERTING THEN null; ELSIF UPDATING THEN null; ELSIF DELETING THEN null; END IF; END;
I've heard about diferent ways of generating email in response to a database event e.g
1 - Using UTL_MAIL package. OR 2 - Event Driven Reporting feature of 10g Database and using SRW.Run_Report package to generate and mail a report.
Now I'm confused that how should I accomplish the following tasks:
(i) - How to generate an email after Insert/Update/Delete on a table and acknowledge the concerned users about that event? (ii)- How to attach a text file with that email?
I have a problem with a statement that normally execute in few second but after 50h the session is still active and is still waiting for null event.
V$session_wait
EVENT WAIT_TIME SECONDS_IN_WAIT STATE ---------------------------------------------------------------- ---------- --------------- ------------------- null event -1 178737 WAITED KNOWN TIME V$session
SID STATUS PROGRAM LAST_CALL_ET ---------- -------- ------------------------------------------------ ------------ 364 ACTIVE f90runm@CENTRAL1 (TNS V1-V3) 178737
My Oracle version : Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production System version : HP-UX V1
I tried tracing the session but no trace file is generated it same there is no activity.
how to query a list to see if a person had events in consectutive months within the past year. We call a person a LongTermResident if they had a review in any two consectutive months within a reporting period. I wrote a function isResidentLongTerm, passing in FacilityID, ResidentID, ReportPeriodStart, and ReportPeriodStop and returning a 'Y' or 'N'. It works, but the performance is slow.
So if I have a list of reviewers, facilities, reviewees I want to select only those SNF/NF residents who have had routine reviews in any two consectutive months at the same facility.
This is my query:
select ConsultantID, ResidentID from ( select distinct ConsultantID, FacilityID, ResidentID from Reviews where BedType = 17820 -- SNF/NF bed and ReviewType = 17474 -- routine review ) where isResidentLongTerm( FacilityID, ResidentID, :startDate, :stopDate ) = 'Y'
I have employees under a supervisor defines as below:
select LOGIN_USER_ID from APPWMS.VIEW_EMP_LATEST_INFO where SPVSR_LOGIN_USER_ID='erbrand' and EMP_STAT_CODE='ACTIVE'
Now I need to determine if all above employees are clocked in , clocked out or not clocked between yesterday and today using following:
select to_char(CLOCK_IN_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockIn,to_char(CLOCK_OUT_DATE,'dd-mon-yyyy hh24:mi:ss' ) ClockOut ,LOGIN_USER_ID,--CLOCK_IN_DATE,CLOCK_OUT_DATE, CLOCK_OUT_DATE-CLOCK_IN_DATE,trunc(sysdate) , trunc(sysdate-1), case when CLOCK_OUT_DATE is null then 'Not clocked out' else
[code]....
The first SQL gives me 66 rows while second gives me 40 rows. For 26 people , no rows are returned which means these people donot have a clock in record between two timestamps.
How can we modify query to show those 26 people as 'Not clocked In'
I have a onchange event on a column which calls the following code: function SetVal(pThis) { //get the curren row index on change var currIndex = $('select[name="'+pThis.name+'"]').index(pThis); var currValue = pThis.Value; alert(currValue); $s('P21_mat_id_hold',currValue);
OS: RHEL 5.7 64 bit DB: 11.2.0.2 Standard Edition 64 bit
Everyday EOD is run and after the eod, users are requesting to receive a mail confirming the same from the database. For this we need to configure automated email which will be sent to a list of users email ID immediately after the EOD is done.
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
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?
.I created the following procedure to implement Event Driven Reporting:
CREATE OR REPLACE procedure ABC.eve_drv_rep as myPlist system.SRW_PARAMLIST; myIdent system.SRW.Job_Ident; BEGIN myPlist := system.SRW_PARAMLIST(system.SRW_PARAMETER('','')); system.srw.add_parameter(myPlist,'GATEWAY','apsIP/reports/rwservlet');
[code]...
Procedure is created successfully but when I execute this procedure, I get the following error:
ORA-20001'Error sending email.Error: ORA-20999 ORA-06512: at "Nml.Eve_Drv_Rep", line 17 ORA-06512: at line 1
2ndly I want to know that whether I should give IP address or Application Server Machine Name in GATEWAY parameter? And in SERVER parameter either I should give the Application Server Machine Name or Report Server name installed on Application Server Machine?Lastly from where I can get the complete list of Parameters to be added in SRW.Add_Parameter like SERVER, GATEWAY etc.
note that we r using Database 10g Rel.2 and Forms and Reports services on Application Server and the report being called was developed in Reports 10g.
I'm using Skillbuilders Modal Page 2.0.0. plugin on Apex 4.2, and I can't seem to get the event End Open to work. stumbled upon this thread Skillbuilder Modal Page - Dynamic Title; nothing happens even if I set an alert to pop up on End Open
In a 3-node RAC setup; one node is showing high CPU utilization around 40~50%. The CPU utilization was less than 20% 10 days back but from 9th oldest day it jumped and consistently shows the double figure. I ran AWR reports on all three nodes and found one node with high CPU utilization and shows below tops events-
EVENT WAITS TIME(S) AVG WAIT(MS) %TOTAL CALL TIME WAIT CLASS CPU time 5,802 34.9
RFS ping 15 5,118 33,671 30.8 Other
Log file sequential read 234,831 5,036 21 30.3 System I/O
Sql*Net more data from client 24,1711,08745 6.5 Network
Db file sequential read130,939 4533 2.7 User I/O
Findings:- On AWR report(file attached) for node= sipd207; we can see that "RFS PING" wait event takes 30% of the waits and "log file sequential read" wait event takes 30% of the waits that occurs in database.
1)Are these symptoms of undersized log buffer? 2)I feel Network wait can be reduced by tweaking SDU & TDU values based on MDU.
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