SYS JOB Causing Waits
Oct 25, 2013
I am using, Release 11.2.0.3.0 of oracle. In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'. After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention ' that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),
Now i can see two of the oracle jobs has been scheduled at this particular time.
1)dbms_autotask_prvt.age
2)dbms_scheduler.auto_purge
And these were having frequency daily.So, i am suspecting these were experiencing the waits. Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB? Or should change the schedule time to some less peak hour or whether these are 'not required' and can be disabled?
View 3 Replies
ADVERTISEMENT
Jul 1, 2010
We had a massive jump in cluster waits specifically; gc buffer busy acquire during an RMAN backup. We identified the cause of the waits to a few hot blocks with a table that may well need re-building in terms of ITLs and PCTFREE (Although I thorght ASSM would manage PCTFREE and PCTUSED..)
What happens during an RMAN backup that may cause huge cluster waits on hot tables? Is there some crazy redo issue going on or maybe flashback?
Either way the spike is there and we can pinpoint the activity on the database but just dont understand why RMAN would cause the issue.We have just found that the LARGE_POOL has not been set, in fact there is no SGA_TARGET either! Could this have an effect on RMAN and cluster waits?
View 3 Replies
View Related
Oct 8, 2013
in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?
View 3 Replies
View Related
Mar 22, 2013
os version is solaris 10
db version 11.2.0.3
how to tune log file sysnc waits in 11gr2 what parameters are involving in tuning log file sync and what would be its optimal values.
View 9 Replies
View Related
Nov 23, 2010
We have a table (call it PROBLEM table) that varies between 60 and 100 million records from one data archival process to the next.The table has data from all 50 states and is range partitioned by each states unique code and has a primary key based on the following:
STATE the data is from
BMRK YEAR the year the data was last benchmarked
AREA location the data is from
SERIES industry the data is located in
YEAR the data was reported
MONTH the data was reported
DATA TYPE the type of data that was reported
ESTIMATE TYPE the type of statistical estimate that was produced from the data
REPORT ID report id unique to each state
REPT SFC state the reporter resided in
We have developed a job that will process data from other tables and create some statistics resulting in approx 2.2 million inserts, .5 million updates and .5 million deletes on PROBLEM table during each job run. Once this table is loaded another process (call this PROBLEM_PROCESS)takes off that reads this updated information to produce some statistical data that is stored in another table. This data is produced by summing anywhere between 2 and 5000 records per query from the PROBLEM table.Here is the query (call it PROBLEM_QUERY)
select round(sum(cm_value*sample_weight),3),round(sum(pm_value*sample_weight),3)
from matched_sample
where state_fips_code = :1
and area_fips_code = :2
and series_type_code = 'B'
and year = :3
and month = :4
[code]...
When we run this job it is very fast (20 minutes) until it gets to the PROBLEM_QUERY, at this point we get DB file scattered read waits, DB file sequential read waits and async IO waits and the job has run for as long as 2 days. When I look at grid, the PROBLEM query is doing a full table scan and ignoring the index.
Our systems people advised us to re-organize and re-index that data in the PROBLEM table after all of the DML and before the PROBLEM_PROCESS takes off. If we do this, it works and cuts our time down alot. However, the re-organize and re-index process adds eight hours to this process. Note: When we re-roganized the data we create a copy table on another set of table spaces and insert the data into the copy sorting the data on the primary key columns. After this we re-index the new table and drop the old table renaming the new table. When we do this later, we move the data back to another set of table spaces. So, we move the data back and for between table spaces A and B we can say.
We have Oracle installed on a M5000 server with Solaris as the OS the binaries and data files are stored on a NetApp Storage array(model 3160) of 500 1TB SATA 7200 RPM drives. However, there are 128 other databases on the NetApp filer as well.
IMO the array and the slow disks are the problem. I believe this because we are catering to the slow disks by re-organizing and re-indexing the PROBLEM table during each run. I don't believe this should be neccessary. We normally re-organize and re-index our data each week in our production system after many more transactions than this.
Our systems people state it is our application. Oracle Support tells us the statistics are out of date but have not answered us on why the statistics are out of date and the index is abandoned after 1 run.
View 7 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
Oct 3, 2010
Is there any relationship b/w tuning BUFFER CACHE and BUFFER BUSY WAITS?
1) Buffer Busy Waits are happening as the User process found the same Datablock is being used by another user in the BUFFER CACHE.
2) And also happens, when the server process found the same Datablock are being used in the Datafile.
View 5 Replies
View Related
Nov 5, 2012
I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.
DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN (
SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI,
SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS
FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME
,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM
TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI,
[code]...
View 1 Replies
View Related
Feb 28, 2011
I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.
View 17 Replies
View Related
Dec 4, 2012
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
View 6 Replies
View Related
Apr 7, 2011
Following query is hanging either with 'Sequential access read' or 'Latch Free' wait event Important thing is the table which is self joined in subquery here does not have any index at all While it was hanged I tried to get trace of it and terminated twice. As such haven't got 'row source generataion' The table has only 120000 records and it shall update 34000 records
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT inv1.btn_due_date
FROM invoice_header inv1
WHERE inv.dct_code = inv1.dct_code AND inv1.release = 'A5')
[code]...
During 'sequential read' using p1,p2 values tried to get what the session is reading and found that it is using the table itself.
During lath free I found following
SELECT name, 'Child '||child#, gets, misses, sleeps
FROM v$latch_children
WHERE addr= (select p1raw from v$session_wait where sid=18)
UNION
[code]...
However instead of self join when I creaed global temporary table as
create global temporary table t as select * from invoice_header where release='A5'
And used it in the update as
UPDATE invoice_header inv
SET inv.modified_due_date =
(SELECT t.btn_due_date
FROM t
WHERE inv.dct_code = t.dct_code AND t.release = 'A5')
WHERE inv.release = 'A5' AND inv.btn_due_date >= TRUNC (SYSDATE)
It updated the records in a second!!
Questions are
1) why it is producing 'sequential read' wait event when there is no index access or else why it is doing single block access when FTS is required?
2) Why is the 'latch free' wait event here and what it indicates here with 'cache buffer handles'?
Is it because we are reading and updating the same segment?
know in case DDL of table is required. It has all nullable columns and no index at all. Since it is 9i I am unable to use MERGE effectively in this case
View 14 Replies
View Related
Aug 3, 2010
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..
SELECT * FROM EVENT_SUB
OR
INSERT INTO EVENT_SUB...
OR
ALTER TABLE EVENT_SUB
WHERE....
So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.
Sometimes i use this:
select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID'
Then, I would simply recompile the invalid objects.
For indexes, i do...
alter index <name> rebuild;
BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.
"A system error has occurred.
View 3 Replies
View Related
May 29, 2013
In my case , the archives are getting generating in excess count, how to get sql text/sid information.
View 2 Replies
View Related
May 2, 2006
My layout is working well enough triggers though are causing me some problems. I am getting the following error message just about every time I write a trigger so I am getting the feeling I am doing something fundamentally wrong. Like there is something I am just not getting because I am getting this error on the simplest of actions.
The error:
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.
--------------------------------------------------------------
ORA-01403: no data found
The trigger is this:
cs3_prog.set_date_mode;
this calls the following procedure in the package cs3_prog:
procedure set_date_mode IS
BEGIN
:cs3_data.street := 'something';
end;
this in the past did a lot more but I kept throwing stuff out to see what could have been causing the problem. This does actually work if I ok through the first error message and then try entering data again into the field the trigger is tied to. On this second firing of the trigger is does successfully alter the value.
Just the fact that this is happening every time I create a trigger and it seems to happen regardless of what I have the trigger doing leads me to believe I have something fundamentally wrong and I would love to have it set straight.
View 11 Replies
View Related
Jul 21, 2013
I'm using the oracle xml db 11g in my website application. Last week I found that using the deletexml() to delete a node in the xml may cause a terrible problem that the rest data in the database had wrong xml format. I wonder is it a bug when using binary xml storage. My xml data is describe below,
<root> <nodes> <node id="1"> <name>John</name> <age>16</age> <hobby>football</hobby> </node> <node id="2"> <name>Alex</name> <age>22</age> <hobby>table tenisl</hobby> </node> ..... <nodes></root>
I using deletexml() to delete just a node once time, "update projects set object_value=deletexml(object_value, '/root/nodes/node[@id="1"]')" Usually it successes and the rest data is as expected, but sometime, I find that after deleting a node, any query in the database except "select * from projects" gets null. And the rest data has wrong format like below,
<root> <nodes> <node id="1"> <name>John</name> <age>/hobby> </node> <node id="2"> <name>Alex</name> <age>22</age> <hobby>table tenisl</hobby> </node> ..... <nodes></root>
Just one format error causes the whole xml data valueless, but it seems that in most time the deletexml() works well.
View 8 Replies
View Related
Jul 27, 2012
Oracle 11.2; application express 4.1...I have a table with a unicode clob column into which I insert descriptive text. Within these text entries I have inserted CR/LF (00D00A). However when I display the text entries in a report, the text streams instead of appearing with separate lines.
My report statement:
select text_entry_dt, entry_authy_nm, entry_text
from obj_text
where obj_id = :P41_FIND_ID
and text_type_cd = 'DES'
hat to do to obtain line separation within the entry_text content?
View 3 Replies
View Related
May 23, 2013
Oracle 11g R2 on WIndows 20o8 R2 Standard edition
I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.
I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.
From v$session, the program is OMS.
From v$process, the hostname is the server itself.
I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?
View 3 Replies
View Related
Jan 2, 2013
We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.
MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
A.ID
[code]....
View 5 Replies
View Related
Nov 16, 2010
We are trying to import data into existing tables in a schema using data pump
However the foreign key tables are being imported first and then the master table data thus violating the constraints
Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)
Is it a normal behaviour during data pump import?
Is it possible that the keys being sequence generated are causing this?
As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?
View 3 Replies
View Related
Feb 24, 2013
Oracle Version: 11.1.0.7.0
Active Dataguard
Statspack has been configured for Active Dataguard on Primary database.We got an spike of Buffer busy waits for about 5 min in Active Dataguard, this was causing worse Application SQL's response time during this 5 min window.Below is what i got from statspack report for one hour
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 18611 21-Feb-13 22:00:02 236 2.2
End Snap: 18613 21-Feb-13 23:00:02 237 2.1
Elapsed: 60.00 (mins)
[code]...
Why there could sudden spike of demand on UNDO data in Active Data Guard ?
View 2 Replies
View Related
Aug 27, 2012
We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.
This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.
Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.
View 5 Replies
View Related
