DATABASE WORK PROCESS

R3 Locks :
  • R3locks are monitored in SM12
  • R3locks are in the main Memory of the Instance
  • R3locks locks the entire transaction
  • R3locks are hold during the entire transaction
DB Locks:
  • DB Locks are monitored in DB01
  • DB Locks lock the Physical Tables/rows in DB
  • DB Locks locks the LUW only
  • DB Locks are hold for few milliseconds and Could not be displayed in DB01
R3 Buffers:
  • R3 buffers are monitored in St02
  • R3 buffers are based on parameter settings in Rz10 and table technical settings in SE13
  • R3 buffer resides in Memory and swapped out by the updated content when the content is modified
  • R3buffers are lost when the application server is shutdown/restarted
 DB Buffers:
  • DB buffer are monitored in ST04
  • DB buffers are determined by a parameter DB_CACHE_SIZE
  • DB buffer buffers the entire content and response comes from DB buffer only
  • DB Buffers are lost when the database server is shutdown/restarted
SYSTEM GLOBAL AREA:
  • It is built when the Oracle database is started.
  • It is used to store the Data Dictionary Cache, shared SQL Area, Library Cache, DB Cache and Log Buffer.
    It is built on Memory and Lost when the database is shutdown/terminated/aborted.
  • It is defined by parameter SGA_MAX_SIZE and DB Cache is defined by DB_CACHE_SIZE, shared SQL area is defined by parameter SHARED_POOL_SIZE and log buffer is defined by parameter log_buffer.
sqlplus>show sga;
Processes Area:
  1. The Oracle background Processes are built when the Oracle Database is started.Some of the Processes are LGWR,DBWR,ARCH,CKPT,SMON,PMON etc.
Log Writer:
It is used to write the committed data from log buffer to the disk. it is the first place where the committed records are written for replaying the updates if required.It is triggered in the following circumstances.
  • For every 3 seconds
  • 1/3 of Log buffer size
  • 1 MB of committed changes
  • Before DBWR writes to database.
  • Checkpoint for every 60 seconds.
System Change Number:
When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction’s redo entries in the redo log. SCNs are recorded in the redo log so that recovery can be performed until the last SCN.
SCN is also recorded in alert<SID>.log
Log Sequence Number:
When origlogA is Full it is closed for writing and origlogB will be opened for writing.The Switch between redo logs is called as Log switch.Each log size is 50 MB
origlogA, origlogB ,MirrorlogA MirrorlogB switch 11 to 12 to 13 to 14 11
LSN will be created(incremented)during the log switch.
Database Writer (DBWR):
The Content that is Updated in Log buffer is updated into DB Buffer as Dirty Buffer Content and marks the content unavailable to Users.Dirty Buffer Content means the content that is updated in database buffer but not updated in Physical data files.DBWR is triggered in the following circumstances.
  • Check point interval 60 sec
  • For every 3 seconds
  • User requests for the Dirty buffer content(it will write to DB and respond back to the User)
  • When ever the DB Cache Space is not sufficient in DB Buffer to hold the content then Dirty buffer will be written to Disk
Checkpoint Process (CKPT):
When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint. This is done by the CKPT process.it does not write any files to disk.(except ckpt logs).
Arch Archival Process:
ARCH process is triggered by the Log switch.
when origlogA, origlogB ,MirrorlogA MirrorlogB
switch 11 to 12 to 13 to 14 11 the switch occurs by the time 14 is filled 11 has to be emptied else the redo logs are overwritten.it is enabled by using the parameter log_archive_start=true(1). This Parameter is set to 1 on Production Systems so that archive process is active to write to oraarch directory.if this parameter is set to ‘0’ or false then online redo log files are recycled without writing to oraarch.
Arch:
Process writes from online redo log files to oraarch directory upon Log Switch.For Every Log switch the Log Sequence Number is incremented by 1.
System Monitor(SMON):
It is used to start the system consistently when the system is terminated abnormally.it Checks the redo log files to Roll Forward/Redo the system and also reads from PSAPUNDO to roll back/UNDO the uncommitted transactions
Process Monitor(PMON):
It is used to monitor the processes starts the hanging processes
releases the locks .

Comments

Post a Comment

Popular posts from this blog

SPOOL WORK PROCESS

Spool Process: It is the Only Process that is used to output the documents to a Printer, fax, email etc.. Spool Work Processes are created by using parameter risp/wp_no_spo=1 it is possible to configure more than one spool work process based on available resources. Spool Process Work Flow: User Submits the request to print a Document. The Dialog Process handles the request and print directly or schedules to print in the background.The Print requests are stored in the TemSe(Temporary Sequential Files) Temporary Sequential Files are stored either at File System or at database Level which is defined by parameter rspo/store_location. rspo/store_location=”G” or ‘DB’ G means global directory stores the TEMSE in /usr/sap/SID/SYS/global db mean the Tables TST01 and TST03.(TST01 Contains Print Attributes and TST03 Contains the Spool Data to be Printed) Due to Spool in DB the DB Size Increases, as a Result, the backup time increases and Tapes may not fit with the data for S...
Read more

ENQUEUE WORK PROCESS

It is used to lock and unlock the Objects during an update.These locks are displayed in SM12 and Held for longer hours until the transaction is committed or rollback.These locks are issued from a lock table which resides in the main memory of the instance where enqueue process is created/located. Enque Processes are configured by parameter rdisp/wp_no_enq=1. Generally One Enque Process for instance is sufficient,more then one is required in a massive update system. The Lock table size on the memory ranges between 4mb to 100 MB(200 MB in the Net weaver 7.3 Systems ) It is defined by parameter enque/table_size=4 to 200 MB. For 4mb of enqueue table size, the Number of lock/owners/objects/arguments can be restricted up to 3715. Enqueue Flow: User submits the Request for an Update. The Dialog or Background Process Handles the Request Dia or BTC Processes communicates with Enqueue Process and obtains a lock on the record so that no other user has to write access to that r...
Read more

DIALOG PROCESS

Dialog Process: It is the only process that is used to communicate with end users Interactively using Classic GUI or Through RFC or through Http Protocol. The Average Response Time for each dialog step is 600 ms.it is displayed through ST03n Each Process can execute up to a maximum runtime of 600 seconds.if any process goes beyond this time an additional time of 60 seconds is given and the process is timed out.(ST22)(it is not mandatory to allocate additional 60 seconds Each Process Serves 5-10 users.(Calculated based on ST07) Each Process Requires 75mb to 300 mb Memory Each Process can Handle the requests that are initiated for background, spool & update and update them in temporary tables. Dialog Process is not restricted to a User and Users are not restricted to a process which is referred as process multiplexing Parameters to configure dialog processes is rdisp/wp_no_dia There should be at least minimum two dialog processes and maximum up to 100 processes. The ...
Read more