I was told by client to pull out a report from one of database. But we were not allowed to use GUI tools like sql developer.. etc rather sqlplus. As we already know sqlplus commandline needs manual formatting for columns .Its odd sometimes,when we do lot of manual work, isnt it? :j
Execute the script before you run any query or report and it works for most of the queries.
All the dynamic and static view columns were pulled out from all_tab_columns
For your ease!
Filename: colformat.sql
!rm -rf /home/oracle/COLX.sql
!rm -rf /home/oracle/COLY.sql
set serveroutput on
create or replace procedure COLX as
begin
for x in (
select column_name from
all_tab_columns where data_type <> 'NUMBER')
loop
DBMS_OUTPUT.PUT_LINE('column ' || x.column_name || ' format a15 justify left' );
end loop;
end;
/
create or replace procedure COLY as
begin
for y in (
select column_name from
all_tab_columns where data_type='NUMBER')
loop
DBMS_OUTPUT.PUT_LINE('column ' || y.column_name || ' format 999999999 justify center' );
end loop;
end;
/
set heading off
set termout off
set echo off
spool COLX.sql
exec COLX
spool off
spool COLY.sql
exec COLY
spool off
set lines 500
set pages 1000
set numwidth 50
set long 1000000000
set heading on
@COLX.sql
@COLY.sql
Couple of days back,i was dead hungry waiting to order food in a queue in restaurant.It was bit frustating that my hunger ate me bit by bit rather i control it.But there were 16 people in front of me and there was only one counter to process bill. It took almost hours for the clerk to process each bill for less people.This delay can be avoided if there are more than one counter process the bill or one counter with experienced clerk who can process the bill faster.
Here there are certain points to be noted
There are 16 people in a queue waiting for a resource to consume which is paying bill and getting food.To prepare food for multiple people faster,there should be sufficient cook to cook recipe ordered by different customer.One who stands in the queue at 16th position will have to wait in the counter row and thats me eventhough i have other emergency work.
Think the same in oracle performance where 16 user processes is the customer waiting for clerk and cook to process the task. Cook and Clerk resources are CPU,memory,IO,network etc on the server which are consumed to serve the task(cooking and payment).Food(data) is the objective or result which is the expected by the customer(user).To get the food(data) some people may have huge list of food orders to be placed and some may want only coffee or snacks.If multiple people ordered coffee and vending machine is repaired or under repair,then this problem is not visible to the customer.However,the customer waits and knows that something is not right in the background which lets them wait.The problem may be due to multiple scenarios like shortage issues,repairment,lack of resource etc.These scenarios can be called as wait interface or wait events in oracle term
But if i have sufficient resource power and sufficient infrastructure in hand to serve multiple requests,then there would be win win situation for both customer and vendor.
In oracle as well,if there is less CPU resource,then parallelism cant be utilized efficiently.If there is less memory power,then sorting and cache will be in contention etc and so on.
If multiple cooks are available then food can be prepared faster.
ILLUSTRATION OF WAIT INTERFACE WITH REAL TIME EXAMPLE
Types of wait events:
foreground
Background
Idle
Non Idle
RESPONSE TIME = SERVICE TIME + WAIT TIME or CPU TIME + QUEUE TIME
CPU used for normal work (CPU_W) = CPU used when call started – parse time CPU – recursive CPU usage
In 11g, below are the list of wait events which i used to describe as 4c’s in wait class .These 4c’s include Cluster,commit,concurrency and configuration.These events are most common waits in both RAC and standalone databases.We will see cluster events in another post part 2.
kish@exdbx<>select name,wait_class from v$event_name where wait_class in ('Commit','Concurrency','Configuration','Cluster') order by wait_class;
NAME WAIT_CLASS
---------------------------------------------------------------- ----------------------------------------------------------------
retry contact SCN lock master Cluster
gc buffer busy acquire Cluster
gc buffer busy release Cluster
pi renounce write complete Cluster
gc current request Cluster
gc cr request Cluster
gc cr disk request Cluster
gc cr multi block request Cluster
gc current multi block request Cluster
gc block recovery request Cluster
gc cr block 2-way Cluster
gc cr block 3-way Cluster
gc cr block busy Cluster
gc cr block congested Cluster
gc cr failure Cluster
gc cr block lost Cluster
gc cr block unknown Cluster
gc current block 2-way Cluster
gc current block 3-way Cluster
gc current block busy Cluster
log file sync Commit
enq: BB - 2PC across RAC instances Commit
latch: cache buffers chains Concurrency
Shared IO Pool Memory Concurrency
logout restrictor Concurrency
os thread startup Concurrency
Streams apply: waiting for dependency Concurrency
pipe put Concurrency
securefile chain update Concurrency
buffer busy waits Concurrency
db flash cache invalidate wait Concurrency
enq: TX - index contention Concurrency
latch: Undo Hint Latch Concurrency
latch: In memory undo latch Concurrency
latch: MQL Tracking Latch Concurrency
Streams apply: waiting to commit Configuration
checkpoint completed Configuration
Global transaction acquire instance locks Configuration
statement suspended, wait error to be cleared Configuration
enq: SQ - contention Configuration
sort segment request Configuration
Below are the wait events w.r.t application and user I/O which also should be noted when there is a performance issue
kish@exdbx<>select name,wait_class from v$event_name where wait_class in ('Application','User I/O') order by wait_class;
NAME WAIT_CLASS
---------------------------------------------------------------- ----------------------------------------------------------------
enq: PW - flush prewarm buffers Application
WCR: replay lock order Application
enq: RO - fast object reuse Application
enq: KO - fast object checkpoint Application
enq: TM - contention Application
enq: TX - row lock contention Application
Wait for Table Lock Application
enq: RC - Result Cache: Contention Application
Streams capture: filter callback waiting for ruleset Application
Streams: apply reader waiting for DDL to apply Application
SQL*Net break/reset to client Application
SQL*Net break/reset to dblink Application
External Procedure initial connection Application
External Procedure call Application
enq: UL - contention Application
OLAP DML Sleep Application
enq: RO - contention Application
Parameter File I/O User I/O
Disk file operations I/O User I/O
Disk file I/O Calibration User I/O
Disk file Mirror Read User I/O
Disk file Mirror/Media Repair Write User I/O
direct path sync User I/O
Datapump dump file I/O User I/O
dbms_file_transfer I/O User I/O
DG Broker configuration file I/O User I/O
Data file init write User I/O
Log file init write User I/O
Shared IO Pool IO Completion User I/O
Below are the dynamic views which are useful when tuning wait events
v$session_event
v$session_wait
v$session_wait_class
v$session_wait_history
v$system_event
v$system_wait
v$system_wait_class
v$event_name
v$event_histogram
v$active_session_history
v$waitstat
Information gathered from v$session_* views are aggregated and collected into v$system_* views
v$session_* views show the current connections wait statistics and v$system_* views shows the collected wait stats from instance startup
v$session_wait > v$system_event
v$session_event > v$system_event
Below are the static views for tuning
DBA_HIST_ACTIVE_SESS_HISTORY
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT
DBA_HIST_SESSMETRIC_HISTORY
DBA_HIST_SESS_TIME_STATS
DBA_HIST_SERVICE_WAIT_CLASS
DBA_HIST_WAITSTAT
Let check the histogram buckets for most common exadata wait event.There are different number of buckets in the wait_time_milli column
Cell single block physical read has 12 buckets upto 2048 ms;Cell multiblock physical read has 10 buckets upto 512 ms;
wait_count for Cell single block physical read ranges from 707*1ms to 3*2048ms
Here second bucket shows 2*1069ms of waits which is higher than the 1st bucket (707)
But if you take Cell multiblock physical read,there are few buckets(10) upto 512 ms.From this information,smaller number of long waits and large number of shorter waits can be derived.
Whenever a session needs to access a datablock in buffer cache ,the same block is accessed or modified by some other session.The other session is either reading the same data block into buffer cache from datafile,or it is modifying the one in buffer cache.
To guarantee that reader session has a consistent image of the datablock with dirty block or free block,the session modifying the block marks the block header with flag to intimate other sessions know that the change is taking place and to wait until complete change is applied.Below are the most common classes of buffer which undergo BBW are,
kish@exdbx<>select * from v$waitstat where count > 0;
CLASS COUNT TIME
------------------ ---------- ----------
data block 56 61
segment header 2 7
1st level bmb 2 2
2nd level bmb 2 0
undo header 4 0
undo block 1 0
Control file parallel write:
Control file parallel write(CFPW) occur if session waits until all controlfiles are updated with the latest SCN by CKPT process and other backup and recovery information to maintain consistency of datafile and redolog files.Every 3 seconds CKPT(checkpoint) process writes the checkpoint position in all members of online redologs for recovery purpose.Oracle also store the unrecoverable SCN operations like DML performed using nologging options.
Here the session waits for the I/O on OS holding CF enqueue to complete the write operations on the controlfiles.
When it is considered problematic? If there are enormous waits and elapsed time observed on CFPW,then it might be an indication of slow I/O subsystem or slow writes by CKPT process.
USERNAME SID SERIAL# EVENT
-------------------- ---------- ---------- ------------------------------
WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
--------------- ---------- --------------- -------------------
(oracle) 12 1 control file sequential read
System I/O 0 0 WAITING
DB file parallel read:
Dont get fooled by the naming convention of this wait event.This event is purely occur during the recovery of the database.It also occurs during read of single or multiple block parallely from datafile to database buffer cache.
DB file parallel write:
Similar to previous event,this events is responsible for writing the dirty buffer from db buffer to datafile where DBWR plays a major role.The session waits for the I/O subsystem on OS for the writes to complete.This may also impact the read requests simultaneously during write on the same disk.DBWR perform batch writes to the datafile either synchronously or asynchronously.
If you dont see this event in your database,then disk_async_io parameter must be set to false
When db file parallel write average wait time > 100ms ,then you have slow IO subsystem.
kish@exdbx<>select KSPPINM from x$ksppi where KSPPINM in ('fast_start_mttr_target','_db_large_dirty_queue','_db_block_max_dirty_target');
KSPPINM
--------------------------------------------------------------------------------
_db_large_dirty_queue
fast_start_mttr_target
DB file scattered read:
You must have heard of this most common wait event,which occur during SELECT statement and a process waits for multiblock read to complete.Multiple datablocks from datafile are read by server process and datablocks are collected and scattered randomly into database buffercache in a non contiguous manner.Also we can notice full table scan or index fast full scan during this event
DB_FILE_MULTIBLOCK_READ_COUNT is the parameter which determines the number of datablock which can be read from the datafile.This event is usual and occur most of the time.Consider this event as a nightmare,when you observe significant amount of wait compared to other events.
SQL> select event,sample_time, session_state,count(*) from v$active_session_history where event like '%scattered%' group by event,sample_time, session_state;
EVENT SAMPLE_TIME SESSION_STATE COUNT(*)
-------------------- -------------------- -------------------- ----------
db file scattered re 29-MAR-21 11.39.35.0 WAITING 1
ad 15 AM
This wait falls under USER I/O and if there is high wait observed,then most probably it is an application issue
Three parameters are used for this event :file#,first block# and block count
kish@exdbx<>select a.sid,b.name,a.value from v$sesstat a ,v$statname b
2 where a.statistic# = b.statistic# and a.value <> 0
3 and b.name = 'table scan blocks gotten' order by 3,1;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
50 table scan blocks gotten 1
36 table scan blocks gotten 2
51 table scan blocks gotten 38
7 table scan blocks gotten 44
5 table scan blocks gotten 187
26 table scan blocks gotten 298
62 table scan blocks gotten 501
60 table scan blocks gotten 9566
22 table scan blocks gotten 18935
9 rows selected.
DB file sequential read:
This event is also common when a process waits for a single block read to complete.This event is displayed by oracle ,when there is index scan,undo segments,table access by rowid,rebuilding controlfiles,dumping datafile headers.If you observe significant wait time for single block read,then you include this event in your trouleshooting list.
Recommendation:
Since it is totally IO based event where we need to concentrate on reducing the physical and logical IO’s by tuning the sql query used by application. Index reads causes heavy IO when blocks are requested from disk to buffercache and if there is poor extent outline in the datafiles,then demand for IO increases.
Reduce the average wait time by tuning the sql query
Here the thumb rule is to maintain AVERAGE_WAIT time should not be more than 10ms as a standard measure.
Clustering factor:
Clustering factor of an index should be equal to the number of blocks in a table which means rows are ordered in the table as per the index.If the clustering factor is equal to number of rows in table,then probably you have bad clustering factor.Index requires a rebuild.
Optimal solution is to rewrite the query by writing efficiently queries with better logic
DB file single write:
This event is also one of DBWR event ,occur when datafile headers are updated by oracle.If your database has ample number of datafiles ,then you see this event in your top events
Direct path read(DPR):
This event occurs when oracle directly read the datablocks using PGA instead SGA buffer.DPR can be performed using sync or async I/O based on the hardware.Init parameter DISK_AYNC_IO determine the type of IO during block read.
There are some scenarios where in DPR can be expected
Parallel server execution
sorting(sort by/group by clause on a select query)
hash joins
If the session cannot attain sync IO,then it has to wait until there is a acknowledgement and IO complete.
If session can attain async IO,then multiple DPR requests are sent and they continue to process the blocks that are already cached in PGA.
Direct path write:
If a session writes datablocks using PGA from data buffer to the datafiles.Multiple write requests would be sent to continue processing.OS handles the IO operation.
Scenarios when this event is posted:
INSERT operation with APPEND like IAS or CTAS
Parallel DML operations
For LOB segments,specifically there is a direct path write (lob) event
USERNAME SID SERIAL# EVENT
-------------------- ---------- ---------- ------------------------------
WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
--------------- ---------- --------------- -------------------
SYS 60 391 direct path write
User I/O 0 0 WAITING
Enqueue:
An enqueue is a shared memory structure which serialize the control to acquire to the database resource.To access a memory structure say for instance a particular transaction,the process must get the enqueue lock of resource.Eg: If A acquires an enqueue lock on resource X and B also needs to access resource X,then B has to wait in queue to attain the respective enqueue when A releases X
kish@exdbx<>select sid,sql_id from v$session where sid in ('66','58');
SID SQL_ID
---------------------------------------- -------------
58 au7znvpsmyyff
66 5s96y62hq2rhk
Find the sql which are under TX enqueue
kish@exdbx<>select sql_text,sql_id from v$sql where sql_id in ('au7znvpsmyyff','5s96y62hq2rhk');
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID
-------------
update tabx set order_id=369 where mod(order_id,4)=1
5s96y62hq2rhk
update tabx set order_id=369 where mod(order_id,5)=1
au7znvpsmyyff
Identify the lock mode to get the lock type held on the object
This event occurs when there is no room for datablocks to get accomodate or to build a CR image of block on the buffer cache of SGA.Either the buffer cache is small or the dirty buffers are not flushed to the disk in regular intervals.The server process will notify DBWR to clean the dirty buffers to disk as part the algorithm rules.
Latch free:
When a process holding a latch on a memory structure,another process requiring the latch needs to wait in queue.Oracle maintains consistency with the help of latch.Only one process is allowed to either update or modify the data structures on a latch.Processes which need to attain the latch need to wait
Below are the list of latchname filtered by latch.There are some more latch which were not listed here!Each latch is identified by latch#
SQL> select * from v$latchname where name like '%latch%';
LATCH# NAME HASH
---------- ---------------------------------------------------------------- ----------
2 hot latch diags 1435869216
18 ksupkttest latch 1143481739
20 ksim membership request latch 3846407325
25 ksbxic instance latch 3429070552
31 enqueue freelist latch 1930520624
.........
...........
Library cache pin:
This event is related to library cache concurrency where a session pins an object in library cache in shared pool to read or modify them.Thus preventing others sessions from updating the same object.
It occurs mainly when executing
PL/SQL procedures
Views
When this event is considered problematic?
Dynamic SQL inside PL/SQL procedure has to perform recompilation of the code from the calling procedure
Blocking session
Solution:
Shared pool needs to be tuned
Blocking session must be taken care
SQL> select s.sid,kglpnmod "Mode",kglpnreq "Req" from x$kglpn p,v$session s where p.kglpnuse=s.addr and kglpnhdl='&P1RAW';
Enter value for p1raw:
We can get the locks and pins detail in db_object_cache view which is very useful when tuning library cache.If these locks on objects are significant,then its time to have a look on these locks
SQL> select * from (select owner,locks,pins from v$db_object_cache order by locks desc) where rownum < 6 and owner is not null;
OWNER LOCKS PINS
---------------------------------------------------------------- ---------- ----------
SYS 9 0
SYS 7 0
SYS 7 0
SYS 5 0
SYS 5 0
lets test the same with a scenario
Open two sessions A and B and execute procedure with dbms_lock upto 30 seconds time
kish@exdbx<>create or replace procedure x as
2 begin
3 dbms_lock.sleep(30);
4 end;
5 /
Elapsed: 00:00:00.15
kish@exdbx<>exec x
Elapsed: 00:00:30.01
At the sametime,compile the procedure from session B.Note that the command hanged for 26 seconds until the procedure complete execution
kish@exdbx<>alter procedure x compile;
Procedure altered.
Elapsed: 00:00:26.53
Simultaneously observe the locks and pins for the procedure with recent timestamp.
kish@exdbx<>select name,owner,pins,locks,timestamp from v$db_object_cache where name like '%DBMS_LOCK%' order by timestamp;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER PINS LOCKS
---------------------------------------------------------------- ---------------------------------------- ----------------------------------------
TIMESTAMP
----------------------------------------------------------------------------
DBMS_LOCK
SYS 1 1
2013-08-24/11:41:29
Library cache lock:
A session must acquire a lock on library cache to prevent other sessions access of same object at the same time to maintain consistency or to maintain dependency or to locate an object on library cache.
Log buffer space:
When session has to write redo on log buffer,but has no space to write new information.LGWR process flush log from log buffer to redolog file as per the 3 rules of LGWR(every 3 seconds,every 1/3rd of log buffer full and user commit).This event tells us that LGWR cant write the redo information faster and cope up with the high redo transactions generated by application.
This event is posted
Redolog files are on disks with I/O contention
if log buffer size is not optimal
Log file parallel write:
When session waits for LGWR process to write redo information from log buffer to redolog members in group.If LGWR writes redo in ASYNC mode,then it parallely writes the redo information to the active redologfile else it writes in serial manner.
When process waits for blocks to be read from online redolog files this event is posted.When ARCH process archives the redo to archivelog file,this event is posted.
*Archiving needed* message indicates that ARCH process is not coping up with LGWR process and is slow in archiving the redo to archivelog file than writing the redo to redolog file.This occurs only if database is in archivelog mode and LGWR has to wait until the redos are archived from one of the member being full.If archiving is failed,then this event is posted
*Checkpoint incomplete* indicate that LGWR wait for log file switch to complete because checkpoint for the logfile is not completed.
This is also an indication of redolog file size being small
kish@exdbx<>select bytes/1048576 from v$log;
BYTES/1048576
----------------------------------------
50
50
50
50
Let check the current time of database
kish@exdbx<>select to_char(sysdate,'dd-mm-yy hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
07-04-21 23:10:05
Lets run the log advisory report for past one hour where there was high DML activity which generated high redo generation.As you can see the size recommendation is very high at time when there was application activity on the database
kish@exdbx<>SELECT
(SELECT
ROUND (AVG (BYTES) / 1024 / 1024, 2)
FROM
V$LOG) AS "Redo size (MB)",
ROUND ( (20 / AVERAGE_PERIOD) * (SELECT AVG (BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
(SELECT AVG ( (NEXT_TIME - FIRST_TIME) * 24 * 60) AS
AVERAGE_PERIOD
FROM
V$ARCHIVED_LOG
WHERE
FIRST_TIME > SYSDATE - 1
AND
TO_CHAR (FIRST_TIME, 'HH24:MI') BETWEEN '22:00' AND '23:00');
Redo size (MB) Recommended Size (MB)
---------------------------------------- ----------------------------------------
50 2045.45
This doesnt mean that we blindly increase the redolog size because this also increase the mean time for recovery during instance crash(MTTR) determined by fast_start_mttr_target.Depending on the database and its behaviour,a deep analysis of optimal redo is required to determine the redolog size.
Log file sync:
User session performs DML transactions like insert,update or delete on the database and issues a commit or rollback to save the records.When user issues a commit or rollback,the changes made on the objects must be immediately saved by LGWR to members of online redolog files to maintain data protection.Until LGWR write the redos from log buffer to redolog files ,the server process has to wait for an acknowledgement from the LGWR.
If the session waits on same log buffer#,seq# of v$session_wait with an increment on those columns,then the session waits due to slow LGWR process.Here we need to identify if LGWR itself is slow due to short transaction commits or due to slow disks like RAID-5.
LOG FILE SYNC
Solution:
Tune LGWR
Batch the transactions to reduce the commits
Check if all the members of redolog file are equally and optimally sized
SQL*NET message from client:
This wait event is posted if a client sits in front of sqlplus or sql developer terminal and process wait for a message from the client to action.This may indicate that the client go for break and come back after sometime and the process sits idle until clients provide any input.Another scenario may be batch programs run on the system non interactively which doesnot have control over client.The event itself state that problem is not from database but from network or application layer.
SYS 60 391 SQL*Net more data from dblink
Network 0 0 WAITING
SYS 37 45 SQL*Net message to client
Network -1 0 WAITED SHORT TIME
SQL*NET message to client:
This event is contradictory to previous event.The client process is too busy in some task and server process has to wait for the acknowledgement from the client or due to network latency
USERNAME SID SERIAL# EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE
-------------------- ---------- ---------- ------------------------------ --------------- ---------- --------------- -------------------
SYS 74 249 SQL*Net message to client Network -1 0 WAITED SHORT TIME
ASM file metadata operation:
This event posts waits when the ASM processes perform operations very slow than the database process or the database process sends multiple request faster than the ASM can respond during a smart scan.
VSSwriter is one of the services on windows used specifically for writing backup and recovery information in the form of snapshots and create volume based and component based shadow copies
Manages and implements Volume Shadow Copies used for backup and other purposes. If this service is stopped, shadow copies will be unavailable for backup and the backup may fail. If this service is disabled, any services that explicitly depend on it will fail to start.
If oracle database reside on windows OS,then oracle VSS writer enables all types of backup like log,copy,full,incremental differential and cumulative
All the options used in vssadmin are self explanatory
C:\Windows\system32>vssadmin
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2013 Microsoft Corp.
Error: Invalid command.
---- Commands Supported ----
Delete Shadows - Delete volume shadow copies
List Providers - List registered volume shadow copy providers
List Shadows - List existing volume shadow copies
List ShadowStorage - List volume shadow copy storage associations
List Volumes - List volumes eligible for shadow copies
List Writers - List subscribed volume shadow copy writers
Resize ShadowStorage - Resize a volume shadow copy storage association
vssadmin is a useful utility to control the vss writers and for listing status of all writers on the host and volumn shadow copy
VSS writer process may stop sometime due to unstable state of resource like CPU,memory etc or host itself.This may lead to failure of dependent process like backup if scheduled at the time
C:\Windows\system32>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2013 Microsoft Corp.
Writer name: 'Task Scheduler Writer'
Writer Id: {d61d61c8-d73a-4eee-8cdd-f6f9786b7124}
Writer Instance Id: {1bddd48e-5052-49db-9b07-b96f96727e6b}
State: [1] Stable
Last error: No error
Writer name: 'VSS Metadata Store Writer'
Writer Id: {75dfb225-e2e4-4d39-9ac9-ffaff65ddf06}
Writer Instance Id: {088e7a7d-09a8-4cc6-a609-ad90e75ddc93}
State: [1] Stable
Last error: No error
Writer name: 'Performance Counters Writer'
Writer Id: {0bada1de-01a9-4625-8278-69e735f39dd2}
Writer Instance Id: {f0086dda-9efc-47c5-8eb6-a944c3d09381}
State: [1] Stable
Last error: No error
Writer name: 'System Writer'
Writer Id: {e8132975-6f93-4464-a53e-1050253ae220}
Writer Instance Id: {6f04bff6-5501-4e09-a045-843c66492a22}
State: [5] Waiting for completion
Last error: No error
Writer name: 'Shadow Copy Optimization Writer'
Writer Id: {4dc3bdd4-ab48-4d07-adb0-3bee2926fd7f}
Writer Instance Id: {f6747060-ebb0-4c11-a94f-4e687c5d7981}
State: [1] Stable
Last error: No error
Writer name: 'ASR Writer'
Writer Id: {be000cbe-11fe-4426-9c58-531aa6355fc4}
Writer Instance Id: {3266cf90-c973-4df6-ad30-ab57ff536b9f}
State: [1] Stable
Last error: No error
Writer name: 'MSSearch Service Writer'
Writer Id: {cd3f2362-8bef-46c7-9181-d62844cdc0b2}
Writer Instance Id: {09a979e6-c403-4a37-bb07-eac141c6dcb6}
State: [5] Waiting for completion
Last error: No error
Writer name: 'IIS Config Writer'
Writer Id: {2a40fd15-dfca-4aa8-a654-1f8c654603f6}
Writer Instance Id: {ef00cb03-3995-4411-9ded-556d4a80a7bc}
State: [1] Stable
Last error: No error
Writer name: 'WMI Writer'
Writer Id: {a6ad56c2-b509-4e6c-bb19-49d8f43532f0}
Writer Instance Id: {23134a5e-23be-4087-ab74-1cf091229962}
State: [5] Waiting for completion
Last error: No error
Writer name: 'Registry Writer'
Writer Id: {afbab4a2-367d-4d15-a586-71dbb18f8485}
Writer Instance Id: {fc569deb-26d1-4e5d-bf76-304e493b09b7}
State: [1] Stable
Last error: No error
Writer name: 'COM+ REGDB Writer'
Writer Id: {542da469-d3e1-473c-9f4f-7847f01fc64f}
Writer Instance Id: {a8228371-281e-4d2e-9bff-f29f36b4aa06}
State: [1] Stable
Last error: No error
The error occur when multiple session try to load data on the same table with direct path load operation + parallel
SQL> begin
2 for i in 1 .. 100000 loop
3 insert /*+append */ into sales select * from demo_dup;
4 end loop;
5 commit;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 3
[oracle@exdbadm01 ~]$ oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.
Workaround:
There are no constraints on the table where data load happen so constraints error were skipped and ora 12838 is thrown.Four sessions were connected to same database and all sessions perform insert with append option
kish@exdbx<>select a.sid,a.serial#,a.username,a.sql_id,a.event,b.sql_text from v$session a,v$sql b where a.sql_id=b.sql_id;
SID SERIAL# USERNAME SQL_ID EVENT
---------------------------------------- ---------------------------------------- ------------------------------ ------------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
36 133 TEST b08gc4pv7cprr enq: TM - contention
INSERT /*+append */ INTO SALES SELECT * FROM DEMO_DUP
71 657 TEST b08gc4pv7cprr enq: TM - contention
INSERT /*+append */ INTO SALES SELECT * FROM DEMO_DUP
Execute the insert with no append because by default direct path load work in parallel and rows are appended above highwatermark ignoring the free space gaps below.If constraints are present on the table,then oracle force conventional data load to maintain consistency
Either exclude append hint from the insert or run one insert with append ,commit it and then go for next insert and so on
You know the append hint would insert the records above the high watermark ignoring the free space below which were deleted
Session 1:
SQL> begin
2 for i in 1 .. 100000 loop
3 insert /*+append */ into sales select * from demo_dup;
4 end loop;
5 commit;
6 end;
7 /
commited
After transaction 1 commit from session 1,run next transaction in session 2
Session 2:
SQL> begin
2 for i in 100000 .. 100001 loop
3 insert /*+append */ into sales select * from demo_dup;
4 end loop;
5 commit;
6 end;
7 /
[oracle@exdbadm01 ohasd]$ grep 'MASTER' ohasd.log|tail
2021-04-07 16:23:05.580: [ CRSPE][2999859520]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Configuring] new [Starting]
2021-04-07 16:23:05.582: [ CRSPE][2999859520]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Starting] new [Running]
2021-04-08 13:39:13.858: [ CRSPE][839031104]{0:0:2} PE Role|State Update: old role [INVALID] new [MASTER]; old state [Enabling: waiting for role] new [Configuring]
2021-04-08 13:39:13.858: [ CRSPE][839031104]{0:0:2} PE MASTER NAME: exdbadm01
2021-04-08 13:39:14.569: [ CRSPE][839031104]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Configuring] new [Starting]
2021-04-08 13:39:14.571: [ CRSPE][839031104]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Starting] new [Running]
2021-04-09 13:21:01.639: [ CRSPE][2623547712]{0:0:2} PE Role|State Update: old role [INVALID] new [MASTER]; old state [Enabling: waiting for role] new [Configuring]
2021-04-09 13:21:01.639: [ CRSPE][2623547712]{0:0:2} PE MASTER NAME: exdbadm01
2021-04-09 13:21:02.548: [ CRSPE][2623547712]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Configuring] new [Starting]
2021-04-09 13:21:02.551: [ CRSPE][2623547712]{0:0:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Starting] new [Running]
[oracle@exdbadm01 ~]$ grep 'MASTER' /u01/app/11.2.0/log/exdbadm01/cssd/ocssd.log|tail
2021-04-09 14:06:25.972: [ CSSD][1021745472]clssgmTestSetLastGrockUpdate: grock(CLSN.RLB.exdbx.MASTER), updateseq(5) msgseq(6), lastupdt<0x20992b0>, ignoreseq(0)
2021-04-09 14:06:25.972: [ CSSD][1021745472]clssgmGrockOpTagProcess: Request to commission member(1) using key(1) for grock(CLSN.RLB.exdbx.MASTER)
2021-04-09 14:06:25.972: [ CSSD][1021745472]clssgmUpdateGrpData: grock(CLSN.RLB.exdbx.MASTER), commissioner(1/1)
2021-04-09 14:06:25.973: [ CSSD][1021745472]clssgmQueueGrockEvent: groupName(CLSN.RLB.exdbx.MASTER) count(2) master(2) event(18), incarn 1, mbrc 1, to member 1, events 0xa0, state 0x0
2021-04-09 14:06:25.973: [ CSSD][1021745472]clssgmHandleGrockRcfgUpdate: grock(CLSN.RLB.exdbx.MASTER), updateseq(6), status(0), sendresp(1)
2021-04-09 14:06:26.554: [ CSSD][1021745472]clssgmTestSetLastGrockUpdate: grock(CLSN.RLB.exdbx.MASTER), updateseq(6) msgseq(7), lastupdt<0x2098dd0>, ignoreseq(0)
2021-04-09 14:06:26.554: [ CSSD][1021745472]clssgmGrockOpTagProcess: Request to commission member(-1) using key(1) for grock(CLSN.RLB.exdbx.MASTER)
2021-04-09 14:06:26.554: [ CSSD][1021745472]clssgmUpdateGrpData: grock(CLSN.RLB.exdbx.MASTER), commissioner(-1/0)
2021-04-09 14:06:26.554: [ CSSD][1021745472]clssgmQueueGrockEvent: groupName(CLSN.RLB.exdbx.MASTER) count(2) master(2) event(18), incarn 0, mbrc 1, to member 1, events 0xa0, state 0x0
2021-04-09 14:06:26.554: [ CSSD][1021745472]clssgmHandleGrockRcfgUpdate: grock(CLSN.RLB.exdbx.MASTER), updateseq(7), status(0), sendresp(1)
4)crsd logfile
[oracle@exdbadm01 ~]$ grep 'MASTER' /u01/app/11.2.0/log/exdbadm01/crsd/crsd.log|tail
2021-04-07 16:37:55.261: [ OCRMAS][1307658560]th_master: NEW OCR MASTER IS 2
2021-04-07 16:37:57.188: [ CRSPE][1370696000]{1:39863:2} PE MASTER NAME: exdbadm02
2021-04-08 14:38:20.591: [ OCRMAS][1711864128]th_master:12: I AM THE NEW OCR MASTER at incar 1. Node Number 1
2021-04-08 14:38:23.231: [ CRSPE][1849760064]{1:43178:2} PE Role|State Update: old role [INVALID] new [MASTER]; old state [Enabling: waiting for role] new [Configuring]
2021-04-08 14:38:23.231: [ CRSPE][1849760064]{1:43178:2} PE MASTER NAME: exdbadm01
2021-04-08 14:38:24.739: [ CRSPE][1849760064]{1:43178:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Configuring] new [Starting]
2021-04-08 14:38:24.745: [ CRSPE][1849760064]{1:43178:2} PE Role|State Update: old role [MASTER] new [MASTER]; old state [Starting] new [Running]
2021-04-09 14:02:49.296: [ OCRMAS][2666457408]proath_master: SUCCESSFULLY CONNECTED TO THE MASTER
2021-04-09 14:02:49.296: [ OCRMAS][2666457408]th_master: NEW OCR MASTER IS 2
2021-04-09 14:02:52.784: [ CRSPE][2729494848]{1:57928:2} PE MASTER NAME: exdbadm02
5)Recent OCR backup taken by any node is MASTER B)
#This script will check the top oracle process which consume high CPU from OS and map the OSPID to database spid
_check_high_CPU(){
CPU_LOG=/tmp/CPU.f
CPU_LEVEL=15.0
CPU_FACTOR=ps -eo pcpu,pid,user,etime|grep 'oracle'|sort -nr|head -1|awk '{print $1,$2,$3}'
#echo $CPU_FACTOR > /tmp/TOP_CPU_PROCESS.f
if [ echo "$CPU_FACTOR"|head -1|awk '{print $1}' > $CPU_LEVEL ];then echo "HIGH CPU";fi
SPID=echo "$CPU_FACTOR"|awk '{print $2}'
echo "$SPID"
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOF > $CPU_LOG
DEFINE SPID='$SPID'
set lines 200
set pages 1000
set echo on
column SPID format a20
column USERNAME format a20
column PROGRAM format a20
column SQL_ID format a20
SELECT se.inst_id,
se.sid,
se.serial#,
se.sql_id,
pr.spid,
se.username,
se.program
FROM gv\$session se
INNER JOIN gv\$process pr ON pr.addr = se.paddr AND pr.inst_id = se.inst_id
WHERE p.spid='${SPID}'
order by se.sid;
EOF
exit;
grep "sid" $CPU_LOG
if [ $? -eq 0 ];then
mailx -s "`$ORACLE_SID`:HIGH CPU USAGE" youremail.gmail.com
fi
}
_check_high_CPU
