What is SGA?
The SGA is a read/write
memory area that, along with the Oracle background processes, makes up a
database instance. All server processes that execute on behalf of users can
read information in the instance SGA. Several processes write to the SGA during
database operation. Each database instance has its own SGA. Oracle Database
automatically allocates memory for an SGA at instance startup and reclaims the
memory at instance shutdown.
--------------------------------------------------------------------------------------------------------------------------
What background process refreshes materialized views?
The Job Queue Processes (J000, Jnnn)
--------------------------------------------------------------------------------------------------------------------------
What background process refreshes materialized views?
The Job Queue Processes (J000, Jnnn)
--------------------------------------------------------------------------------------------------------------------------
What are
important components of SGA?
The most important SGA
components are the following:
Database Buffer Cache
Redo Log Buffer
Shared Pool
Large Pool
Java Pool
Streams Pool
Fixed SGA
--------------------------------------------------------------------------------------------------------------------------
What is
shared pool?
The shared pool caches
various types of program data. For example, the shared pool stores parsed SQL,
PL/SQL code, system parameters, and data dictionary information. The shared
pool is involved in almost every operation that occurs in the database. For
example, if a user executes a SQL statement, then Oracle Database accesses the
shared pool.
The shared pool is divided
into several subcomponents:
• Library Cache
• Data Dictionary Cache
• Server Result Cache
• Reserved Pool
--------------------------------------------------------------------------------------------------------------------------
What is PMON
process? And state its functions?
Process Monitor Process
(PMON)
The process monitor (PMON)
monitors the other background processes and performs process recovery when a
server or dispatcher process terminates abnormally.
PMON is responsible for
cleaning up the database buffer cache and freeing resources that the client
process was using. For example, PMON resets the status of the active
transaction table, releases locks that are no longer required, and removes the
process ID from the list of active processes.
PMON also registers information
about the instance and dispatcher processes with the Oracle Net listener (see
"The Oracle Net Listener"). When an instance starts, PMON polls the
listener to determine whether it is running. If the listener is running, then
PMON passes it relevant parameters. If it is not running, then PMON
periodically attempts to contact it.
--------------------------------------------------------------------------------------------------------------------------
What is SMON
process? and state its functions?
System Monitor Process
(SMON)
The system monitor process
(SMON) is in charge of a variety of system-level cleanup duties. The duties
assigned to SMON include:
• Performing instance
recovery, if necessary, at instance startup. In an Oracle RAC database, the
SMON process of one database instance can perform instance recovery for a
failed instance.
• Recovering terminated
transactions that were skipped during instance recovery because of file-read or
tablespace offline errors. SMON recovers the transactions when the tablespace
or file is brought back online.
• Cleaning up unused
temporary segments. For example, Oracle Database allocates extents when
creating an index. If the operation fails, then SMON cleans up the temporary
space.
• Coalescing contiguous
free extents within dictionary-managed tablespaces.
SMON checks regularly to
see whether it is needed. Other processes can call SMON if they detect a need
for it.
--------------------------------------------------------------------------------------------------------------------------
What are
mandatory background processes of oracle database in 11g?
The following are oracle
instance mandatory background processes:
• Process Monitor Process
(PMON)
• System Monitor Process
(SMON)
• Database Writer Process
(DBWn)
• Log Writer Process (LGWR)
• Checkpoint Process (CKPT)
• Manageability Monitor
Processes (MMON and MMNL)
• Recoverer Process (RECO)
these process starts
automatically when database instance is started. the other database
background processes are optional.
--------------------------------------------------------------------------------------------------------------------------
What is DBWn
process? And state its functions?
DBWR - Database Writer or
Dirty Buffer Writer process is responsible for writing dirty buffers from the
database block cache to the database data files. Generally, DBWR only writes
blocks back to the data files on commit, or when the cache is full and space
has to be made for more blocks. The possible multiple DBWR processes in RAC
must be coordinated through the locking and global cache processes to ensure
efficient processing is accomplished.
--------------------------------------------------------------------------------------------------------------------------
What is LGWR
process? And state its functions?
LGWR - Log Writer process
is responsible for writing the log buffers out to the redo logs. In RAC, each
RAC instance has its own LGWR process that maintains that instance’s thread of
redo logs.
--------------------------------------------------------------------------------------------------------------------------
What is MMNL
process? And state its functions?
The manageability monitor
lite process (MMNL) writes statistics from the Active Session History (ASH)
buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.
--------------------------------------------------------------------------------------------------------------------------
What is MMON
process? And state its functions?
The manageability monitor
process (MMON) performs many tasks related to the Automatic Workload Repository
(AWR). For example, MMON writes when a metric violates its threshold value,
taking snapshots, and capturing statistics value for recently modified SQL
objects.
--------------------------------------------------------------------------------------------------------------------------
What is RECO
process? And state its functions?
Recoverer Process (RECO):
In a distributed database,
the recoverer process (RECO) automatically resolves failures in distributed
transactions. The RECO process of a node automatically connects to other
databases involved in an in-doubt distributed transaction. When RECO
reestablishes a connection between the databases, it automatically resolves all
in-doubt transactions, removing from each database's pending transaction table
any rows that correspond to the resolved transactions.
--------------------------------------------------------------------------------------------------------------------------
What is CKPT
process? And state its functions?
The checkpoint process
(CKPT) updates the control file and data file headers with checkpoint
information and signals DBWn to write blocks to disk. Checkpoint information
includes the checkpoint position, SCN, location in online redo log to begin
recovery, and so on. CKPT does not write data blocks to data files or
redo blocks to online redo log files.
-------------------------------------------------------------------------------------------------------------------------
What is SCN
(System Change Number) ?
The system change number
(SCN) is an ever-increasing value that uniquely identifies a committed version
of the database at a point in time. Every time a user commits a transaction
Oracle records a new SCN in redo logs.
SCN is a value that is
incremented whenever a dirty read occurs.
SCN is incremented whenever
a deadlock occurs.
SCN is a value that keeps
track of explicit locks.
SCN is a value that is
incremented whenever database changes are made.
Oracle uses SCNs in control
files datafile headers and redo records. Every redo log file has both a log
sequence number and low and high SCN. The low SCN records the lowest SCN
recorded in the log file while the high SCN records the highest SCN in the log
file.
--------------------------------------------------------------------------------------------------------------------------
What is a
Checkpoint?
A Checkpoint is a database
event which synchronizes the modified data blocks in memory with the datafiles
on disk.
It offers Oracle the means
for ensuring the consistency of data modified by transactions.
A checkpoint
occurs on five types of events:
- At each switch of
the redo log files.
- When the delay for
LOG_CHECKPOINT_TIMEOUT is reached.
- When the size in
bytes corresponding to :
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
is
written on the current redo log file.
- Directly by the
ALTER SYSTEM SWITCH LOGFILE command.
- Directly with the
ALTER SYSTEM CHECKPOINT command.
The following are the
parameter that will be used to adjust time or interval of how frequently
its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT =
3600; # Every one hour
LOG_CHECKPOINT_INTERVAL =
1000; # number of OS blocks.
During a
checkpoint the following occurs:
- The database
writer (DBWR) writes all modified database blocks in the buffer cache back to
datafiles,
- Checkpoint
process (ckpt) updates the headers of all the datafiles to indicate when the
last checkpoint occurred (SCN)
--------------------------------------------------------------------------------------------------------------------------
What are
Various types of checkpoints in Oracle?
Full
checkpoint:Writes block
images to the database for all dirty buffers from all instances
Thread
checkpoint: Writes
block images to the database for all dirty buffers from one instance
File
checkpoint: Writes
block images to the database for all dirty buffers for all files of a
tablespace from all instances(generally happens when When a tablespace is put
into backup mode or take it offline)
Parallel Query
checkpoint:Writes block
images to the database for all dirty buffers belonging to objects accessed by
the query from all instances(happens when we use parallel option in query)
Object
checkpoint:Writes block
images to the database for all dirty buffers belonging to an object from all
instances(happens when table is truncated, dropped)
Log switch
checkpoint:Writes the
contents of the dirty buffers whose information is protected by a redo
log to the database
Incremental
checkpoint:incremental
checkpointing triggeres DBWR to write some dirty blocks from time to time so as
to advance the checkpoint and reduce the instance recovery time.
--------------------------------------------------------------------------------------------------------------------------
What is
LOG_CHECKPOINT_TIMEOUT?
log_checkpoint_timeout is
the number of seconds since last checkpoint before another is performed.
its defalut value is 1800
seconds (30 minutes)
if log_checkpoints_to_alert
is set to true then it writes checkpoints to alert log
use alter system set
log_checkpoint_timeout=0 scope=both to eliminate time-based checkpoints
--------------------------------------------------------------------------------------------------------------------------
What is
LOG_CHECKPOINT_INTERVAL?
LOG_CHECKPOINT_INTERVAL
specifies the frequency of checkpoints in terms of the number of redo log file
blocks that can exist between an incremental checkpoint and the last block
written to the redo log. This number refers to physical operating system
blocks, not database blocks.
If it is set greater than
redo log file size, checkpoints occur at log switches thus reduces the
occurrence of checkpoint.it will be Ignored if set to zero.
--------------------------------------------------------------------------------------------------------------------------
What is large
pool? and where it is used?
Oracle Large Pool is an
optional memory component of the oracle database SGA. This area is used for
providing large memory allocations in many situations that arise during the
operations of an oracle database instance.
1. Session memory for the a
shared server and the Oracle XA Interface when distributed transactions are
involved
2. I/O Server Processes
3. Parallel Query Buffers
4. Oracle Backup and
Restore Operations using RMAN
Large Pool plays an
important role in Oracle Database Tuning since the allocation of the memory for
the above components otherwise is done from the shared pool. Also due to the
large memory requirements for I/O and Rman operations, the large pool is better
able to satisfy the requirements instead of depending on the Shared Pool Area.
Usage of a Large Pool Area
allows the shared pool to primarily cache SQL and avoid the overhead casued by
shrinking the SQL Cache Area
--------------------------------------------------------------------------------------------------------------------------
What are
logfile states?
“CURRENT” state
means that redo records are currently being written to that group. It will be
until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing
redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state.
As we know log file keep changes made to the data blocks then data blocks are
modified in buffer cache (dirty blocks). These dirty blocks must be written to
the disk (RAM to permanent media).
And when a redolog group
contains no redo records belonging to a dirty buffer it is in an “INACTIVE”
state. These inactive redolog can be overwritten.
One more state ‘UNUSED’
initially when you create new redo log group its log file is empty on that time
it is unused. Later it can be any of the above mentioned state.
--------------------------------------------------------------------------------------------------------------------------
What is log
switch?
A Log Switch is an event in
which the log writer (LGWR process) stops logging in one log group and jumps to
another log group. It is normal that a log group fills up with records. When
records fill up the log group, Log Switch event is triggered. You can actually
trigger the switch manually by issuing the command:
ALTER SYSTEM SWITCH
LOGFILE;
--------------------------------------------------------------------------------------------------------------------------
What is the
difference between PGA and UGA?
When you are running
dedicated server then process information stored inside the process global area
(PGA)
When you are using shared
server then the process information stored inside user global area (UGA).
--------------------------------------------------------------------------------------------------------------------------
If any one of
those mandatory background processes is killed/not running, the instance will
be aborted ?
Yes, instance will be
aborted if we kill any of PMON,SMON,CKPT,DBWn,LGWR
--------------------------------------------------------------------------------------------------------------------------
What are the
components of physical database structure of Oracle database?
Control, redo log and data
files.
--------------------------------------------------------------------------------------------------------------------------
What are the
components of logical database structure of Oracle database?
Table, index, views and
tablespace
--------------------------------------------------------------------------------------------------------------------------
What is an
Oracle Instance?
An oracle instance is a
combination of memory structures and background processes
--------------------------------------------------------------------------------------------------------------------------
When you
start an Oracle DB which file is accessed first?
Parameter file
(PFILE/SPFILE) in nomount state
--------------------------------------------------------------------------------------------------------------------------
Which Process
reads data from Datafiles?
Server process
--------------------------------------------------------------------------------------------------------------------------
Which Process
writes data in Datafiles?
DBWR
--------------------------------------------------------------------------------------------------------------------------
What is DB
Buffer Cache Advisor?
DB Buffer Cache Advisor
Advices about the optimum size of buffer cache. Related view V$DB_CACHE_ADVICE
--------------------------------------------------------------------------------------------------------------------------
Can you
change SHARED_POOL_SIZE online?
Yes. Range of values
Minimum: the granule size(84M for 64-bit, 32M for 32-bit) Maximum: operating
system-dependent
--------------------------------------------------------------------------------------------------------------------------
How to change
shared pool size?
ALTER SYSTEM SET
SHARED_POOL_SIZE =500M;
--------------------------------------------------------------------------------------------------------------------------
Name some
memory areas Oracle uses,and state their purpose?
Memory Areas in oracle SGA
(System Global Area)+PGA(Program Global Area)
SGA=DBBC (Database Buffer
Cache)+RLB(Redo log Buffer)+Shared pool Large Pool + Streams pool + JAVA pool
+Fixed SGA
--------------------------------------------------------------------------------------------------------------------------
What is kept
in the Database Buffer Cache?
Data
blocks from data files
--------------------------------------------------------------------------------------------------------------------------
What is
difference between Multithreaded/Shared Server and Dedicated Server?
In Shared Server
architecture MANY sessions (user connections) have ONE server process
associated. In Dedicated Server architecture ONLY ONE sessions (user
connections) have ONLY ONE server process associated. Because Shared Server
architecture use a multi-thread architecture (one server process has more
threads), a Shared Server is named MTS (Multi-Thread Server) as well.
--------------------------------------------------------------------------------------------------------------------------
How would you
force a log switch manually?
ALTER SYSTEM SWITCH
LOGFILE;
--------------------------------------------------------------------------------------------------------------------------
When a user process fails, what background process cleans up after it?
PMON
When a user process fails, what background process cleans up after it?
PMON
--------------------------------------------------------------------------------------------------------------------------
How to find out background processes ?
select SID,PROGRAM from v$session where TYPE='BACKGROUND';
How to find out background processes ?
select SID,PROGRAM from v$session where TYPE='BACKGROUND';
--------------------------------------------------------------------------------------------------------------------------
Name some background processes description?
SQL> select name,description from V$bgprocess;
--------------------------------------------------------------------------------------------------------------------------
Name some background processes description?
SQL> select name,description from V$bgprocess;
--------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment