oracle events...
Does anyone know what these oracle events are and how the wait times can be reduced. This is an extract from statspack output on 9.2.0.4 on HP 11.11 Top 5 Timed Events~~ % TotalEvent Waits Time (s) Ela Time --- pipe put 780 3,904 44.65wait for unread message on broadcast channel 3,016 3,034 34.70PX Deq: Signal ACK 33,937 678 7.76db file scattered read 6,575,215 499 5.70PX qref latch 222 200 2.29 -Want to chat insttantly with your online friends? Get the FREE Yahoo! Messenger
Re: oracle events...
It appears that you are using AQ, as that is what 'unread message' waits are for. Apparently also DBMS_PIPE, as per the 'pipe put'. I don't know if AQ makes any use of DBMS_PIPE. The numbers provided aren't enough to tell if this might be a problem. Your database has spent almost 2 hours waiting on these two events. If your database has been up for 3 hours, then these probably need some attention. If it's been up for a month, accumulating 2 hours of waits over that period may not represent any problem. I do find it interesting that the 'pipe put' waits are about 5 seconds each on average, as that's a pretty long wait. You may want to discuss this with the app folks. A good buy would be Optimizing Oracle Performance by Cary Millsap and Jeff Holt. It goes into some detail about how to use Oracle traces to pinpoint problems. HTH Jared Zabair Ahmed [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 05:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:oracle events... Does anyone know what these oracle events are and how the wait times can be reduced. This is an extract from statspack output on 9.2.0.4 on HP 11.11 Top 5 Timed Events ~~ % Total EventWaits Time (s) Ela Time --- pipe put 7803,904 44.65 wait for unread message on broadcast channel3,0163,034 34.70 PX Deq: Signal ACK 33,937 678 7.76 db file scattered read 6,575,215 499 5.70 PX qref latch 222 200 2.29 - Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger
Oracle events - #s names
From V9.2.0 ORA-1: controlfile debug event, name 'control_file' ORA-10001: controlfile crash event1 ORA-10002: controlfile crash event2 ORA-10003: controlfile crash event3 ORA-10004: controlfile crash event4 ORA-10005: trace latch operations for debugging ORA-10006: testing - block recovery forced ORA-10007: log switch debug crash after new log select, thread ORA-10008: log switch debug crash after new log header write, thread ORA-10009: log switch debug crash after old log header write, thread ORA-10010: Begin Transaction ORA-10011: End Transaction ORA-10012: Abort Transaction ORA-10013: Instance Recovery ORA-10014: Roll Back to Save Point ORA-10015: Undo Segment Recovery ORA-10016: Undo Segment extend ORA-10017: Undo Segment Wrap ORA-10018: Data Segment Create ORA-10019: Data Segment Recovery ORA-10020: partial link restored to linked list (KSG) ORA-10021: latch cleanup for state objects (KSS) ORA-10022: trace ktsgsp ORA-10023: Create Save Undo Segment ORA-10024: Write to Save Undo ORA-10025: Extend Save Undo Segment ORA-10026: Apply Save Undo ORA-10027: latch cleanup for enqueue locks (KSQ) ORA-10028: latch cleanup for enqueue resources (KSQ) ORA-10029: session logon (KSU) ORA-10030: session logoff (KSU) ORA-10031: sort debug event (S*) ORA-10032: sort statistics (SOR*) ORA-10033: sort run information (SRD*/SRS*) ORA-10035: parse SQL statement (OPIPRS) ORA-10036: create remote row source (QKANET) ORA-10037: allocate remote row source (QKARWS) ORA-10038: dump row source tree (QBADRV) ORA-10039: type checking (OPITCA) ORA-10040: dirty cache list ORA-10041: dump undo records skipped ORA-10042: trap error during undo application ORA-10043: check consistency of owner/waiter/converter lists in KSQ ORA-10044: free list undo operations ORA-10045: free list update operations - ktsrsp, ktsunl ORA-10046: enable SQL statement timing ORA-10047: trace switching of sessions ORA-10048: Undo segment shrink ORA-10049: protect library cache memory heaps ORA-10050: sniper trace ORA-10051: trace OPI calls ORA-10052: don't clean up obj$ ORA-10053: CBO Enable optimizer trace ORA-10054: trace UNDO handling in MLS ORA-10055: trace UNDO handing ORA-10056: dump analyze stats (kdg) ORA-10057: suppress file names in error messages ORA-10058: use table scan cost in tab$.spare1 ORA-10059: simulate error in logfile create/clear ORA-10060: CBO Enable predicate dump ORA-10061: disable SMON from cleaning temp segment ORA-10062: disable usage of OS Roles in osds ORA-10063: disable usage of DBA and OPER privileges in osds ORA-10064: thread enable debug crash level , thread ORA-10065: limit library cache dump information for state object dump ORA-10066: simulate failure to verify file ORA-10067: force redo log checksum errors - block number ORA-10068: force redo log checksum errors - file number ORA-10069: Trusted Oracle test event ORA-10070: force datafile checksum errors - block number ORA-10071: force datafile checksum errors - file number ORA-10072: protect latch recovery memory ORA-10073: have PMON dump info before latch cleanup ORA-10074: default trace function mask for kst ORA-10075: CBO Disable outer-join to regular join conversion ORA-10076: CBO Enable cartesian product join costing ORA-10077: CBO Disable view-merging optimization for outer-joins ORA-10078: CBO Disable constant predicate elimination optimization ORA-10079: trace data sent/received via SQL*Net ORA-10080: dump a block on a segment list which cannot be exchanged ORA-10081: segment High Water Mark has been advanced ORA-10082: free list head block is the same as the last block ORA-10083: a brand new block has been requested from space management ORA-10084: free list becomes empty ORA-10085: free lists have been merged ORA-10086: CBO Enable error if kko and qka disagree on oby sort ORA-10087: disable repair of media corrupt data blocks ORA-10088: CBO Disable new NOT IN optimization ORA-10089: CBO Disable index sorting ORA-10090: invoke other events before crash recovery ORA-10091: CBO Disable constant predicate merging ORA-10092: CBO Disable hash join ORA-10093: CBO Enable force hash joins ORA-10094: before resizing a data file ORA-10095: dump debugger commands to trace file ORA-10096: after the cross instance call when resizing a data file ORA-10097: after generating redo when resizing a data file ORA-10098: after the OS has increased the size of a data file ORA-10099: after updating the file header with the new file size ORA-10100: after the OS has decreased the size of a data file ORA-10101: atomic redo write recovery ORA-10102: switch off anti-joins ORA-10103: CBO Disable hash join swapping ORA-10104: dump hash join statistics to trace file ORA-10105: CBO Enable constant pred trans and MPs w WHERE-clause ORA-10106: CBO Disable evaluating correlation pred last for NOT IN ORA-10107: CBO Always use bitmap index ORA-10108: CBO Don't use bitmap index ORA-10109: CBO Disable move of negated predicates ORA-10110: CBO Try index rowid range scans ORA-10111: Bitmap
