EVENT:10052 "Stop SMON from cleaning up obj$"

Text:   Stop SMON from cleaning up obj$
——————————————————————————-

NOTE:
Events should NEVER be set by customers unless advised to do so by
Oracle Support Services. You must read <Note:75713.1> before setting ANY
event on a system.

Usage:
~~~~~~
To disable cleanup of OBJ$ by SMON set this event in the init.ora file
thus:

EVENT=”10052 trace name context forever”

Explanation:
~~~~~~~~~~~~
Setting event 10052 stops SMON trying to clean up OBJ$.

The implication of this is that OBJ$ will grow with entries for
non-existent objects, eg: objects which have been dropped and
object entries created for negative dependency reasons.

The usual reason for setting this event is to prevent SMON from
crashing / spinning in the OBJ$ cleanup code whilst diagnostics
or further investigations are performed. It is also sometimes
used in Parallel Server environments so that OBJ$ cleanup is
only performed on selected nodes.

@   The routine it disables is <Function:kqlclo> (KQL Clean Obj$) which scans
@   obj$ for objects marked as non-existent and then deletes them if
@   they are not child dependencies of anything.

Script to Detect Tablespace Fragmentation

create table SPACE_TEMP (
 TABLESPACE_NAME        CHAR(30),
 CONTIGUOUS_BYTES       NUMBER)
/

declare
  cursor query is select *
          from dba_free_space
                  order by tablespace_name, block_id;
  this_row        query%rowtype;
  previous_row    query%rowtype;
total           number;

begin
  open query;
  fetch query into this_row;
  previous_row := this_row;
  total := previous_row.bytes;
  loop
 fetch query into this_row;
     exit when query%notfound;
     if this_row.block_id = previous_row.block_id + previous_row.blocks then
        total := total + this_row.bytes;
        insert into SPACE_TEMP (tablespace_name)
                  values (previous_row.tablespace_name);
     else
        insert into SPACE_TEMP values (previous_row.tablespace_name,
               total);
        total := this_row.bytes;
     end if;
previous_row := this_row;
  end loop;
  insert into SPACE_TEMP values (previous_row.tablespace_name,
                           total);
end;
.
/

set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report'  skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES"       format 999,999,999,999
column "COUNT"                  format 999
column "TOTAL BYTES"            format 999,999,999,999
column "TODAY"   noprint new_value new_today format a1
rem
select TABLESPACE_NAME  "TABLESPACE NAME",
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;

select tablespace_name, count(*) "# OF EXTENTS",
         sum(contiguous_bytes) "TOTAL BYTES"
from space_temp
group by tablespace_name;

spool off

drop table SPACE_TEMP
/

example output:

SQL> @TFSTSFRM

Table created.

PL/SQL procedure successfully completed.

                            Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
EXAMPLE                              32,768,000

                            Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
SYSAUX                                3,211,264

                            Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
SYSTEM                              371,130,368
SYSTEM                                  393,216

                            Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
UNDOTBS1                             13,500,416
UNDOTBS1                                524,288
UNDOTBS1                                458,752
UNDOTBS1                                458,752
UNDOTBS1                                327,680
UNDOTBS1                                262,144
UNDOTBS1                                196,608
UNDOTBS1                                131,072
UNDOTBS1                                131,072
UNDOTBS1                                131,072
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536
UNDOTBS1                                 65,536

                            Contiguous Extents Report

TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
USERS                            10,995,367,936
USERS                                 1,048,576
USERS                                   393,216
USERS                                   262,144
USERS                                   196,608

26 rows selected.

                            Contiguous Extents Report

TABLESPACE_NAME                # OF EXTENTS      TOTAL BYTES
------------------------------ ------------ ----------------
EXAMPLE                                   1       32,768,000
UNDOTBS1                                 17       16,580,608
USERS                                     7   10,997,268,480
SYSAUX                                    1        3,211,264
SYSTEM                                    2      371,523,584

Table dropped.

Script to Collect Data Guard Diagnostic Information

Overview
——–

This script is intended to provide an easy method to provide information
necessary to troubleshoot Data Guard issues.

Script Notes
————-

This script is intended to be run via sqlplus as the SYS or Internal user.

Script
——-

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -

-- NAME: dg_prim_diag.sql  (Run on PRIMARY with a LOGICAL or PHYSICAL STANDBY)


set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool dg_prim_diag_&&dbname&&timestamp&&suffix
set linesize 79
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

-- In the following the database_role should be primary as that is what
-- this script is intended to be run on.  If protection_level is different
-- than protection_mode then for some reason the mode listed in
-- protection_mode experienced a need to downgrade.  Once the error
-- condition has been corrected the protection_level should match the
-- protection_mode after the next log switch.

column role format a7 tru
column name format a10 wrap

select name,database_role role,log_mode,
protection_mode,protection_level
from v$database;

-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
-- archiver failed to archive a log last time, but will try again within 5
-- minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log
-- switching is waiting for.  Note that if ALTER SYSTEM SWITCH LOGFILE is
-- hung, but there is room in the current online redo log, then value is
-- NULL

column host_name format a20 tru
column version format a9 tru

select instance_name,host_name,version,archiver,log_switch_wait
from v$instance;

-- The following query give us information about catpatch.
-- This way we can tell if the procedure doesn't match the image.

select version, modified, status from dba_registry
where comp_id = 'CATPROC';

-- Force logging is not mandatory but is recommended.  Supplemental
-- logging must be enabled if the standby associated with this primary is
-- a logical standby. During normal operations it is acceptable for
-- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru

select force_logging,remote_archive,
supplemental_log_data_pk,supplemental_log_data_ui,
switchover_status,dataguard_broker
from v$database;

-- This query produces a list of all archive destinations.  It shows if
-- they are enabled, what process is servicing that destination, if the
-- destination is local or remote, and if remote what the current mount ID
-- is.

column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
column mid format 99

select dest_id "ID",destination,status,target,
schedule,process,mountid  mid
from v$archive_dest order by dest_id;

-- This select will give further detail on the destinations as to what
-- options have been set.  Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
net_timeout net_time,delay_mins delay,reopen_secs reopen,
register,binding
from v$archive_dest order by dest_id;

-- The following select will show any errors that occured the last time
-- an attempt to archive to the destination was attempted.  If ERROR is
-- blank and status is VALID then the archive completed correctly.

column error format a55 wrap

select dest_id,status,error from v$archive_dest;

-- The query below will determine if any error conditions have been
-- reached by querying the v$dataguard_status view (view only available in
-- 9.2.0 and above):

column message format a80

select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- The following query will determine the current sequence number
-- and the last sequence archived.  If you are remotely archiving
-- using the LGWR process then the archived sequence should be one
-- higher than the current sequence.  If remotely archiving using the
-- ARCH process then the archived sequence should be equal to the
-- current sequence.  The applied sequence information is updated at
-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",
max(log_sequence) "Last Archived"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
where ad.dest_id=al.dest_id
and al.dest_id=ads.dest_id
group by ads.dest_id;

-- The following select will attempt to gather as much information as
-- possible from the standby.  SRLs are not supported with Logical Standby
-- until Version 10.1.

set numwidth 8
column ID format 99
column "SRLs" format 99
column Active format 99

select dest_id id,database_mode db_mode,recovery_mode,
protection_mode,standby_logfile_count "SRLs",
standby_logfile_active ACTIVE,
archived_seq#
from v$archive_dest_status;

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system.  Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby;

-- The following query is run on the primary to see if SRL's have been
-- created in preparation for switchover.

select group#,sequence#,bytes from v$standby_log;

-- The above SRL's should match in number and in size with the ORL's
-- returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Non-default init parameters.

set numwidth 5
column name format a30 tru
column value format a48 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

Overview
——–

This script is intended to provide an easy method to provide information
necessary to troubleshoot Data Guard issues.

Script Notes
————-

This script is intended to be run via sqlplus as the SYS or Internal user.

Script
——-

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -

-- NAME: DG_phy_stby_diag.sql


set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool dgdiag_phystby_&&dbname&&timestamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

--
-- ARCHIVER can be  (STOPPED | STARTED | FAILED) FAILED means that the archiver failed
-- to archive a -- log last time, but will try again within 5 minutes. LOG_SWITCH_WAIT
-- The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that
-- if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online
-- redo log, then value is NULL

column host_name format a20 tru
column version format a9 tru
select instance_name,host_name,version,archiver,log_switch_wait from v$instance;

-- The following select will give us the generic information about how this standby is
-- setup.  The database_role should be standby as that is what this script is intended
-- to be ran on.  If protection_level is different than protection_mode then for some
-- reason the mode listed in protection_mode experienced a need to downgrade.  Once the
-- error condition has been corrected the protection_level should match the protection_mode
-- after the next log switch.

column ROLE format a7 tru
select name,database_role,log_mode,controlfile_type,protection_mode,protection_level
from v$database;

-- Force logging is not mandatory but is recommended.  Supplemental logging should be enabled
-- on the standby if a logical standby is in the configuration. During normal
-- operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru
select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui,
switchover_status,dataguard_broker from v$database;

-- This query produces a list of all archive destinations and shows if they are enabled,
-- what process is servicing that destination, if the destination is local or remote,
-- and if remote what the current mount ID is. For a physical standby we should have at
-- least one remote destination that points the primary set but it should be deferred.

COLUMN destination FORMAT A35 WRAP
column process format a7
column archiver format a8
column ID format 99

select dest_id "ID",destination,status,target,
archiver,schedule,process,mountid
from v$archive_dest;

-- If the protection mode of the standby is set to anything higher than max performance
-- then we need to make sure the remote destination that points to the primary is set
-- with the correct options else we will have issues during switchover.

select dest_id,process,transmit_mode,async_blocks,
net_timeout,delay_mins,reopen_secs,register,binding
from v$archive_dest;

-- The following select will show any errors that occured the last time an attempt to
-- archive to the destination was attempted.  If ERROR is blank and status is VALID then
-- the archive completed correctly.

column error format a55 tru
select dest_id,status,error from v$archive_dest;

-- Determine if any error conditions have been reached by querying thev$dataguard_status
-- view (view only available in 9.2.0 and above):

column message format a80
select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- The following query is ran to get the status of the SRL's on the standby.  If the
-- primary is archiving with the LGWR process and SRL's are present (in the correct
-- number and size) then we should see a group# active.

select group#,sequence#,bytes,used,archived,status from v$standby_log;

-- The above SRL's should match in number and in size with the ORL's returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Query v$managed_standby to see the status of processes involved in the
-- configuration.

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

-- Verify that the last sequence# received and the last sequence# applied to standby
-- database.

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

-- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next
-- gap that is currently blocking redo apply from continuing. After resolving the
-- identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again
-- on the physical standby database to determine the next gap sequence, if there is
-- one.

select * from v$archive_gap;

-- Non-default init parameters.

set numwidth 5
column name format a30 tru
column value format a50 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

Overview
——–

This script is intended to provide an easy method to provide information
necessary to troubleshoot Data Guard issues.

Script Notes
————-

This script is intended to be run via sqlplus as the SYS or Internal user.

Script
——-

- - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -

-- NAME: dg_lsby_diag.sql  (Run on LOGICAL STANDBY)


set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool dg_lsby_diag_&&dbname&&timestamp&&suffix

set linesize 79
set pagesize 180
set long 1000
set trim on
set trims on
alter session set nls_date_format = 'MM/DD HH24:MI:SS';
set feedback on
select to_char(sysdate) time from dual;

set echo on

-- The following select will give us the generic information about how
-- this standby is setup.  The database_role should be logical standby as
-- that is what this script is intended to be ran on.

column ROLE format a7 tru
column NAME format a8 wrap
select name,database_role,log_mode,protection_mode
from v$database;

-- ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the
-- archiver failed to archive a log last time, but will try again within 5
-- minutes. LOG_SWITCH_WAIT The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log
-- switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is
-- hung, but there is room in the current online redo log, then value is
-- NULL

column host_name format a20 tru
column version format a9 tru
select instance_name,host_name,version,archiver,log_switch_wait
from v$instance;

-- The following query give us information about catpatch.
-- This way we can tell if the procedure doesn't match the image.

select version, modified, status from dba_registry
where comp_id = 'CATPROC';

-- Force logging and supplemental logging are not mandatory but are
-- recommended if you plan to switchover.  During normal operations it is
-- acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

column force_logging format a13 tru
column remote_archive format a14 tru
column dataguard_broker format a16 tru

select force_logging,remote_archive,supplemental_log_data_pk,
supplemental_log_data_ui,switchover_status,dataguard_broker
from v$database;

-- This query produces a list of all archive destinations.  It shows if
-- they are enabled, what process is servicing that destination, if the
-- destination is local or remote, and if remote what the current mount ID
-- is.

column destination format a35 wrap
column process format a7
column archiver format a8
column ID format 99
column mid format 99

select dest_id "ID",destination,status,target,
schedule,process,mountid  mid
from v$archive_dest order by dest_id;

-- This select will give further detail on the destinations as to what
-- options have been set.  Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
net_timeout net_time,delay_mins delay,reopen_secs reopen,
register,binding
from v$archive_dest order by dest_id;

-- Determine if any error conditions have been reached by querying the
-- v$dataguard_status view (view only available in 9.2.0 and above):

column message format a80

select message, timestamp
from v$dataguard_status
where severity in ('Error','Fatal')
order by timestamp;

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system.  Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby;

-- Verify that log apply services on the standby are currently
-- running. If the query against V$LOGSTDBY returns no rows then logical
-- apply is not running.

column status format a50 wrap
column type format a11
set numwidth 15

SELECT TYPE, STATUS, HIGH_SCN
FROM V$LOGSTDBY;

-- The DBA_LOGSTDBY_PROGRESS view describes the progress of SQL apply
-- operations on the logical standby databases.  The APPLIED_SCN indicates
-- that committed transactions at or below that SCN have been applied. The
-- NEWEST_SCN is the maximum SCN to which data could be applied if no more
-- logs were received. This is usually the MAX(NEXT_CHANGE#)-1 from
-- DBA_LOGSTDBY_LOG.  When the value of NEWEST_SCN and APPLIED_SCN are the
-- equal then all available changes have been applied.  If your
-- APPLIED_SCN is below NEWEST_SCN and is increasing then SQL apply is
-- currently processing changes.

set numwidth 15

select
(case
when newest_scn = applied_scn then 'Done'
when newest_scn <= applied_scn + 9 then 'Done?'
when newest_scn > (select max(next_change#) from dba_logstdby_log)
then 'Near done'
when (select count(*) from dba_logstdby_log
where (next_change#, thread#) not in
(select first_change#, thread# from dba_logstdby_log)) > 1
then 'Gap'
when newest_scn > applied_scn then 'Not Done'
else '---' end) "Fin?",
newest_scn, applied_scn, read_scn from dba_logstdby_progress;

select newest_time, applied_time, read_time from dba_logstdby_progress;

-- Determine if apply is lagging behind and by how much.  Missing
-- sequence#'s in a range indicate that a gap exists.

set numwidth 15
column trd format 99

select thread# trd, sequence#,
first_change#, next_change#,
dict_begin beg, dict_end end,
to_char(timestamp, 'hh:mi:ss') timestamp,
(case when l.next_change# < p.read_scn then 'YES'
when l.first_change# < p.applied_scn then 'CURRENT'
else 'NO' end) applied
from dba_logstdby_log l, dba_logstdby_progress p
order by thread#, first_change#;

-- Get a history on logical standby apply activity.

set numwidth 15

select to_char(event_time, 'MM/DD HH24:MI:SS') time,
commit_scn, current_scn, event, status
from dba_logstdby_events
order by event_time, commit_scn, current_scn;

-- Dump logical standby stats

column name format a40
column value format a20

select * from v$logstdby_stats;

-- Dump logical standby parameters

column name format a33 wrap
column value format a33 wrap
column type format 99

select name, value, type from system.logstdby$parameters
order by type, name;

-- Gather log miner session and dictionary information.

set numwidth 15

select * from system.logmnr_session$;
select * from system.logmnr_dictionary$;
select * from system.logmnr_dictstate$;
select * from v$logmnr_session;

-- Query the log miner dictionary for key tables necessary to process
-- changes for logical standby Label security will move AUD$ from SYS to
-- SYSTEM.  A synonym will remain in SYS but Logical Standby does not
-- support this.

set numwidth 5
column name format a9 wrap
column owner format a6 wrap

select o.logmnr_uid, o.obj#, o.objv#, u.name owner, o.name
from system.logmnr_obj$ o, system.logmnr_user$ u
where
o.logmnr_uid = u.logmnr_uid and
o.owner# = u.user# and
o.name in ('JOB$','JOBSEQ','SEQ$','AUD$',
'FGA_LOG$','IND$','COL$','LOGSTDBY$PARAMETER')
order by u.name;

-- Non-default init parameters.

column name format a30 tru
column value format a48 wra
select name, value
from v$parameter
where isdefault = 'FALSE';

spool off

- - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

利用dbms_system包加速imp导入数据时的索引建立

imp数据导入时往往大多数的时间都消耗在了索引建立上,我们可以通过为导入会话设置一系列session级别的参数来加速索引的建立:

begin
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       'db_file_multiblock_read_count',
                                       64);
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       'sort_area_size',
                                       209715200);
  dbms_system.set_int_param_in_session(&sid,
                                       &serial,
                                       '_sort_multiblock_read_count',
                                       64);
end;

注意在PGA自动管理模式下(即当workarea_size_policy=AUTO时),自行指定的sort_area_size参数将无法生效。MOS上Bug 8939043叙述了目前dbms_system包的功能仅能修改session级别的布尔和数值类型参数,而无法修改字符串类型参数的问题;所以目前也还无法动态修改其他会话中的workarea_size_policy参数(虽然这个参数在session/system级别是可以动态修改的)。

Learning 11g New Background Processes

New Background Processes In 11g

ACMS (atomic controlfile to memory service) per-instance process is an agent that contributes to ensuring a distributed SGA memory update is either globally committed on success or globally aborted in the event of a failure in an Oracle RAC environment.

DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.This process implements the resource plans that may be configured for a database instance. It sets the resource plans in place and performs various operations related to enforcing/implementing those resource plans. The resource manager allows the administrators of a database to have fine grained control over the resources used by the database instance, by applications accessing the database, or by individual users accessing the database.

DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.

DIAG (diagnosability) process performs diagnostic dumps and executes global oradebug commands.In past releases, the DIAG process was used exclusively in a RAC environment. As of Oracle Database 11g, with the new ADR (Advanced Diagnostic Repository), it is responsible for monitoring the overall health of the instance, and it captures information needed in the processing of instance failures. This applies to both single instance configurations as well as multi-instance RAC configurations.

EMNC (event monitor coordinator) is the background server process used for database event management and notifications.The EMNC process is part of the AQ architecture. It is used to notify queue subscribers of messages they would be interested in. This notification is performed asynchronously. There are Oracle Call Interface (OCI) functions available to register a callback for message notification. The callback is a function in the OCI program that will be invoked automatically whenever a message of interest is available in the queue.The EMNn background process is used to notify the subscriber. The EMNC process is started automatically
when the first notification is issued for the instance. The application may then issue an explicit message_receive(dequeue) to retrieve the message.

FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.This process is new in Oracle Database 11g Release 1 and above. It is the key component of the new flashback data archive capability–the ability to query data “as of” long periods of time ago (for example, to query data in a table as it appeared one year ago, five years ago, and so on). This long term historical query capability is achieved by maintaining a history of the row changes made to every row in a table over time. This history, in turn, is maintained by the FBDA process in the background. This process functions by working soon after a transaction commits. The FBDA process will read the UNDO generated by that transaction and roll back the changes made by the transaction. It will then record these rolled back (the original values) rows in the flashback data archive for us.

GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment.

KATE performs proxy I/O to an ASM metafile when a disk goes offline.

MARK marks ASM allocation units as stale following a missed write to an offline disk.

SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.This process is part of the manageability infrastructure. It coordinates the proactive space management features of the database such as the processes that discover space that could be reclaimed and the processes that perform the reclamation.

VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority).Implements a consistent, fine-grained clock
for the Oracle instance. It is responsible for providing both wall clock time (human readable) as well as an extremely high resolution timer (not necessarily built using wall clock time, more of a ticker that increments for very small units of time) used to measure durations and intervals.

RCBG – this background process is responsible for processing data into server result cache

GEN0 – General task execution process which performs required tasks;This process provides, as expected by its name, a general task execution thread for the database. The main goal of this process is to offload potentially blocking processing (processing that would cause a process to stop while it occurs) from some other process and perform it in the background. For example, if the main ASM process needs to perform some blocking file operation, but that operation could safely be done in the background (ASM can safely continue processing before the operation completes), then the ASM process may request the GEN0 process to perform this operation and let GEN0 notify it upon completion. It is similar in nature to the slave processes described further below.

INSV Data Guard Broker INstance Slave Process; The internode servers(INSVs) maintain a connection between the nodes in the cluster to ensure that Broker on each node knows the state of the cluster. The primary database will always startup an INSV process even if the database is not a RAC; As other RAC instances start,the Broker will start the INSVs, and they will make queries between all the instances to determine the current state of each node in a Broker-controlled database. In this manner, the Broker is able to maintain information about each instance in the RAC. To make sure that this does not have a adverse impact to performance , this querying is optimized to avoid any unnecessary RAC traffic.

FSFP Data Guard Broker FSFO Pinger; We may see one more process on the primary database in Broker-controlled configuration: The Fast-Start Failover process(FSFP) which is used only when the primary is under the control of DataGurad’s automatic failover feature, Fast-Start Failover. The FSFP process will establish a connection to the Fast-Start Failover target database by connecting to a DRC process on that database, much like the NVS to DRC process connection.

* NSVn DataGuard Net Server(Nsvn) From 1 to n of these network server processes can exist. They are responsible for making contact with the remote database and sending across any work items to the remote database. Connection to the remote database are made using the same connect identifier that you specified for the database when you created the configuration. “DMON communicates with the standby databases using NSV processes. This prevents DMON from network hang.”

* DRCn These network receiver processes establish the connection from the source database NSVn process. An NSVn to DRCn connection is similar to the LogWriter Network Service(LSN) to the Remote File Servers(RFS) connection for Redo Transport. As with Redo Transport , when the Broker needs to send something(data or SQL, for example) between databases, it uses this NSV to DRC connection. These connections are started as need.

XDMG cell automation manager;XDMG monitors all configured Exadata cells for state changes, such as a bad disk getting replaced, and performs the required tasks for such events. Its primary tasks are to watch for inaccessible disks and cells and when they become accessible again, and to initiate the ASM ONLINE operation. The ONLINE operation is handled by XDWK.

* XDWK XDWK gets started when asynchronous actions such as ONLINE, DROP, and ADD an ASM disk are requested by XDMG. After a 5 minute period of inactivity, this process will shut itself down.

ABMR Auto BMR Background Process “In addition to the real time query capability of the 11g Active Data Guard feature, we can also add to our high availability capability by using the Automatic Block Media Repair feature whereby data block corruptions on the Primary database can be repaired by obtaining those blocks from the standby site – all performed by a background process (ABMR) transparent to the application.”

Some additional Processes not documented in 10G :

* PZ (PQ slaves used for global Views) are RAC Parallel Server Slave processes, but they are not normal parallel slave processes, PZnn processes (starting at 99) are used to query GV$ views which is done using Parallel Execution on all instances, if more than one PZ process is needed, then PZ98, PZ97,… (in that order) are created automatically.

* O00 (ASM slave processes) A group of slave processes establish connections to the ASM instance. Through this connection pool database processes can send messages to the ASM instance. For example opening a file sends the open request to the ASM instance via a slave. However slaves are not used for long running operations such as creating a file. The use slave (pool) connections eliminate the overhead of  logging into the ASM instance for short requests

* x000 – Slave used to expell disks after diskgroup reconfiguration

DESCRIPTION DEST
Auto BMR Listener ABMR
Auto BMR message ABMR
ACMS Watchdog Task ACMS
RM workload learning DBRM
Check for MDBRM to LDBRM message DBRM
Check for LDBRM to MDBRM message DBRM
DBRM Active Session Limit Event DBRM
Check queuing policy DBRM
RM running count check DBRM
DBRM Timeout Actions DBRM
shutdown DBRM DBRM
do RM action in DBRM DBRM
check for quiesce messages DBRM
unquiesce the instance during database close DBRM
unsubscribe to quiesce channel DBRM
subscribe to quiesce channel DBRM
IORM action DSKM
DSKM check for message from master DiskMon DSKM
SAGE CC Action DSKM
DSKM action DSKM
FSFP Wakeup FSFP
FSFP Shutdown Message FSFP
FSFP Register Observer Message FSFP
ASM UFG health check timeout GEN0
Diskgroup Resource Action GEN0
release quiesce enqueue GEN0
get quiesce enqueue GEN0
check for parameters from other instances GEN0
kcl downconvert object locks GEN0
kcl update persistent read mostly info GEN0
kcl initiate persistent read mostly GEN0
kcb L2 cache verify file header GEN0
Start Exadata specific backgrounds GEN0
KSVMSVC timeout action GEN0
Heartbeat action for HSM connectivity GEN0
Prepare flashback log GEN0
GEN0 Timeout KCQ cleanup GEN0
Global Txn SHUTdown GTX*
K2Q Timeout action GTX*
get instance lock GTX*
convert instance lock GTX*
free instance lock GTX*
INSV Receive Message INSV
INSV Shutdown Message INSV
INSV Interrupt INSV
Network Server forced NS**
Network Server shutdown NS**
Network Server wakeup NSA*
Network Server wakeup NSS*
NetSlave Interrupt Function NSV*
NetSlave Wakeup Message NSV*
NetSlave Shutdown Message NSV*
do work for new gpnp instances RMS0
RSM Set Parameter RSM0
RSM Wakeup RSM0
RSM Receive Message RSM0
SMCO Timout SMCO
SMCO Action SMCO
Infrequent timeout actions for Exadata disks auto manage module XDMG
Action to process msgs from instance processes XDMG
Interrupt actions for Exadata disks auto manage module XDMG

References:
F Background Processes
<Oracle Data Guard 11g Handbook>
<Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions>
New Background Processes In 11g [ID 444149.1]

Know more about Oracle Latches

Two purposes of latches were:Serialize Execution,Serialize Access

Match the characteristics with latches and enqueues.

Several modes of access                   Enqueue
FIFO queue acquisition                    Enqueue
Mostly exclusive access                   Latch
Non-deterministic acquisition             Latch
No atomicity                              Enqueue
Atomicity                                 Latch

Most latches have a level between 0 and 8.

Usually a process can request a latch X only:After obtaining latch Y and if level X > level Y.
Remember that the level of X, which is child latch, must be greater than level Y, which is most likely a parent latch.

The structure for parent and child latches can be seen by querying X$KSLLT .
There is one KSLLT structure for each parent and child latch in an instance.

The characteristic of latch acquisition in NO-WAIT mode is:
One attempt to get a latch, no spin or sleep .
This is typically for latches where there are many of the same type. The process will attempt to acquire another latch.

The sleep time on latches approximately doubles with each wait until upper limit is reached. This is an exponential backoff.

The statistic waiters_woken counts the number of times a posting for a latch has occurred.
It can be found in V$LATCH.This view contains aggregate statistics for latches

PMON invokes a clean up function for releasing latches if the holding process dies.PMON will initiate the clean up function.

_MAX_SLEEP_HOLDING_LATCH The maximum exponential backoff when waiting for a latch and already holding anothers
_MAX_EXPONENTIAL_SLEEP Controls the maximum time a process has to sleep before requesting the latch again
_SPIN_COUNT Designates the number of times a process will spin while trying to acquire a latchs

V$SESSION_WAIT dynamic performance view gives real time information regarding what sessions are currently waiting or have just waited for an event.

When diagnosing latch contention, we should typically look for Latches with the most impact.

We should use below formula  to calculate the total cost of latch spinning:

_spin_count * sleeps/misses

Statistics from V$LATCH with their associated description:

IMMEDIATE_MISSES Number of times the fast get failed when requested in “no-wait” mode

MISSES Number of times the fast get call failed when requested in “willing-to-wait” modes

SPIN_GETS Number of times where the fast get failed but the latch was acquired during the first spin before having to yield to the CPU

GETS Number of times the latch was obtained when requested in “willing-to-wait” mode

SLEEPi Number of times a “willing-to-wait” request had to sleep I times

IMMEDIATE_GETS Number of times the latch was obtained when requested in “no-wait” mode

We can set event 10005 to perform latch tracing, or oradebug dump latches 3 ;

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

[oracle@rh2 ~]$ oerr ora 10005
10005, 00000, "trace latch operations for debugging"
// *Document: NO
// *Cause:
// *Action: Enable tracing for various latch operations
// *Comment:
//    level 1 - trace latch gets and frees
//    level 4 - trace multiple posts by processes when latch is freed
//

Wait and latch (KSL) layer (10005)

We can set events 10005 (trace latch gets and frees) and 10073 (have PMON
dump info before latch cleanup).  Level does not matter for these events.
We can also set event="600 trace name LATCHES level 1" to dump latch info
when PMON hits the error (I'm not so sure this one is going to work).

 event = "600 trace name latches level 10"
 event = "10005 trace name context forever, level 1"

10005 event has been removed above 9i , try dump KSTDUMPCURPROC 1

SQL> oradebug setmypid;
SQL> oradebug unlimit;
SQL> oradebug dump KSTDUMPCURPROC 1
SQL> oradebug tracefile_name;

KSTDUMP: In-memory trace dump
TIME(usecs):SEQ# ORAPID   SID EVENT  OP DATA
========================================================================
D9396B13:000044F8    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 146/0x92 0/0x0
D9573C3D:00004568    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 146/0x92 0/0x0 time=1954087
D9573C4F:00004569    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 147/0x93 0/0x0
D9750D82:000045FC    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 147/0x93 0/0x0 time=1954097
D9750D8F:000045FD    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 148/0x94 0/0x0
D992DEA2:00004639    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 148/0x94 0/0x0 time=1954065
D992DEB2:0000463A    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 149/0x95 0/0x0
D9B0AFD8:000046A9    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 149/0x95 0/0x0 time=1954084
D9B0AFEB:000046AA    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 150/0x96 0/0x0
D9CE8110:00004742    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 150/0x96 0/0x0 time=1954083
D9CE811E:00004743    14     0 10005   1 KSL WAIT BEG [PX Idle Wait] 200/0xc8 151/0x97 0/0x0
D9EC5246:00004790    14     0 10005   2 KSL WAIT END [PX Idle Wait] 200/0xc8 151/0x97 0/0x0 time=1954086
D9ECA80D:00004792    14     0 10280   8 kst: process state object about to be deleted
E308018E:00006780    14     0 10280   1 kst: process state object created on 06-30 22:11:24.211
E308018F:00006781    14     0 10280   3 kst: process info: ospid=10380 pso_num=14 pso_serial#=2
E30801D0:00006782    14     0 10420   1 kso: new process: pid=10380 (legacy spawn)
E4ACF279:00006D4A    14     0 10280   8 kst: process state object about to be deleted
E4B2CD42:00006D4B    14     0 10280   1 kst: process state object created on 06-30 22:11:52.853
E4B2CD43:00006D4C    14     0 10280   3 kst: process info: ospid=10382 pso_num=14 pso_serial#=3
E4B2CDA3:00006D4D    14     0 10420   1 kso: new process: pid=10382 (legacy spawn)
KSTDUMP: End of in-memory trace dump


**** LGWR trace
05A46022:0000DEB0     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05A46025:0000DEB1     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05A46026:0000DEB2     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05A46027:0000DEB3     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05A46029:0000DEB4     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05A4602A:0000DEB5     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05A4602A:0000DEB6     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05A4602B:0000DEB7     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05A4602E:0000DEB8     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05D117DB:0000DF45     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930603
05D117E6:0000DF46     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D117E7:0000DF47     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D117E8:0000DF48     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D117ED:0000DF49     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05D117EE:0000DF4A     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05D117FE:0000DF4B     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05D117FF:0000DF4C     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05D11803:0000DF4D     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05D11804:0000DF4E     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05D11805:0000DF4F     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05D11807:0000DF50     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]
05D11808:0000DF51     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05D11808:0000DF52     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05D11809:0000DF53     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05D1180C:0000DF54     6   221 10005   1 KSL WAIT BEG [rdbms ipc message] 300/0x12c 0/0x0 0/0x0
05FDCFC0:0000DFE1     6   221 10005   2 KSL WAIT END [rdbms ipc message] 300/0x12c 0/0x0 0/0x0 time=2930610
05FDCFCB:0000DFE2     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[1] comment=[KSB action for X-instance calls]
05FDCFCC:0000DFE3     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[63] comment=[Scumnt mount lock]
05FDCFCD:0000DFE4     6   221 10254  71 KSBCTI: (LGWR) : (interrupt action) : acnum=[64] comment=[Poll system events broadcast channel]
05FDCFD1:0000DFE5     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[0] comment=[Monitor Cleanup]
05FDCFD2:0000DFE6     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[56] comment=[Redo writer IO's]
05FDCFEF:0000DFE7     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[57] comment=[Redo writer post action]
05FDCFF0:0000DFE8     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[58] comment=[Redo writer log switch operations]
05FDCFF3:0000DFE9     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[59] comment=[Redo writer generate offline immed marker]
05FDCFF4:0000DFEA     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[170] comment=[kfr Poke LGWR]
05FDCFF5:0000DFEB     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[171] comment=[kfr Incr Ckpt]
05FDCFF7:0000DFEC     6   221 10254  73 KSBCTI: (LGWR) : (timeout action)   : acnum=[172] comment=[kfr ACD relocation]

Know more about Buffer Cache and Latch

We can examine X$BH table to obtain buffer header information,The BH stands for buffer header.

Structures that maintain a list of data buffer headers are called:Hash buckets.

Hash buckets are grouped by relative DBA and class number.
Hash chains list data buffer headers in one hash bucket.

Cache Recycle Pool For randomly accessed large tables

Default Pool For normally accessed tables

Keep Pool for frequently updated small tables

Set DB_BLOCK_LRU_LATCHES parameter to create multiple LRU lists.This parameter sets the number of latches,
and it is best used on a multi-CPU system.

DB_BLOCK_BUFFERS sets the number of buffers to allocate in the buffer cache.
DB_BLOCK_LRU_EXTENDED_STATISTICS is obsolete in Oracle8i.
DB_WRITER_PROCESSES sets the number of database writer processes.

Cache buffers LRU chain protects LRU lists.

The total number of working sets per instance is defined by DB_BLOCK_LRU_LATCHES. There is one latch per set.

The number of DBWR processes cannot be greater than the number of working sets.

Oracle get buffers as below steps:

  1. get a buffer descriptor
  2. specify a mode in which a buffer should be obtained
  3. scan the appropriate hash chain
  4. find the appropriate buffer in the chain or read from disk
  5. attach a state object to the buffer header

Buffers are initially hashed to LRU_AUX.This contains buffers that are candidates for reuse.
LRU_MAIN List houses buffers that are in use (pinned or dirty).
LRU_W List is the write list for dirty buffers.
LRU_XR List is the reuse range list for buffers that are to be written for reuse.

DBWR processes operate on working sets that are assigned to them in a cyclical manner.

There are three reasons may cause DBWR write buffers to disk:

  • To provide checkpoints
  • To do free requests
  • To do ping writes(obsolete)

The write batch size controls the number of asynchronous writes slots allocated to each DBWR.The write batch size controls the number of asynchronous writes slots allocated to each DBWR.

Two Most important DBWR statistics: DBWR make free request,DBWR lru scans 。

Two circumstances where DBWR purges dirty buffers whose DBA falls between the lowest and highest DBA of the datafiles:

  • ALTER TABLESPACE…BEGIN BACKUP
  • Make a tablespace read only

Of the nine latches that protect the buffer cache,the three that are most important:

  • Cache buffers lru chain
  • Cache buffers chains
  • Checkpoint queue latch

each of the fixed tables below with its purpose:

  • X$KCBWAIT         Wait statistics by block class
  • X$KCBFWAIT      Wait statistics by file id
  • X$KCBBHS              DBWR histogram statistics
  • X$KCBWBPD         Buffer pool descriptors

A target buffer cache hit ratio is above 90%.
But 60% could be a valid hit ratio for a DSS or Data Warehouse application.

Two primary events that relate to the buffer cache:

  • Buffer busy waits
  • Free buffer waits

We can use the views and table:V$SYSTEM_EVENT, V$WAITSTAT, and X$KCBFWAIT to determine the reason and block class for buffer busy waits.

Script:To Report Information on Indexes

Reports index fragmentation statistics:

==========
Script #1:
==========

SET ECHO off
REM NAME:   TFSIFRAG.SQL
REM USAGE:"@path/tfsifrag schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Reports index fragmentation statistics
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                     Index Fragmentation Statistic
REM
REM    index name        S_EMP_USERID_UK
REM    leaf rows deleted            0
REM    leaf rows in use            25
REM    index badness            0.000
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr  = &&1
def name  = &&2

ttitle -
  center 'Index Fragmentation Statistic'   skip 2

set heading off

col name                 newline
col lf_blk_rows          newline
col del_lf_rows          newline
col ibadness newline

validate index &ownr..&name;

select
  'index name        '||name,
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                         Index Fragmentation Statistic


index name                   S_EMP_USERID_UK
leaf rows deleted            0
leaf rows in use             25
index badness                0.000




==========
Script #2:
==========

SET ECHO off
REM NAME:   TFSISTAT.SQL
REM USAGE:"@path/tfsistat schema_name index_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on INDEX_STATS
REM ------------------------------------------------------------------------
REM PURPOSE:
REM    Report index statistics.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM                                Index Statistics
REM
REM    S_EMP_USERID_UK
REM    ----------------------------------------------------------
REM    height                          1
REM    blocks                          5
REM    del_lf_rows                     0
REM    del_lf_rows_len                 0
REM    distinct_keys                  25
REM    most_repeated_key               1
REM    btree_space                 1,876
REM    used_space                    447
REM    pct_used                       24
REM    rows_per_key                    1
REM    blks_gets_per_access            2
REM    lf_rows                        25            br_rows               0
REM    lf_blks                         1            br_blks               0
REM    lf_rows_len                   447            br_rows_len           0
REM    lf_blk_len                  1,876            br_blk_len            0
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ownr        = &&1
def name        = &&2

ttitle -
  center  'Index Statistics'  skip 2

set heading off

col name   newline
col headsep              newline
col height               newline
col blocks               newline
col lf_rows              newline
col lf_blks        	 newline
col lf_rows_len          newline
col lf_blk_len           newline
col br_rows              newline
col br_blks              newline
col br_rows_len          newline
col br_blk_len           newline
col del_lf_rows          newline
col del_lf_rows_len      newline
col distinct_keys        newline
col most_repeated_key    newline
col btree_space          newline
col used_space    	 newline
col pct_used             newline
col rows_per_key         newline
col blks_gets_per_access newline

validate index &ownr..&name;

select
  name,
  '----------------------------------------------------------'    headsep,
  'height               '||to_char(height,     '999,999,990')     height,
  'blocks               '||to_char(blocks,     '999,999,990')     blocks,
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows,
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys,
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space,
  'used_space           '||to_char(used_space,'999,999,990')        used_space,
  'pct_used                     '||to_char(pct_used,'990')          pct_used,
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key,
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows,
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks,
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len,
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len
from
  index_stats
/

undef ownr
undef name
set verify on


==============
Sample Output:
==============

                                Index Statistics
S_EMP_USERID_UK
----------------------------------------------------------
height                          1
blocks                          5
del_lf_rows                     0
del_lf_rows_len                 0
distinct_keys                  	25
most_repeated_key               1
btree_space                 	1,876
used_space                    	447
pct_used                       	24
rows_per_key                    1
blks_gets_per_access            2
lf_rows                		25
br_rows                 	0
lf_blks				1
br_blks                 	0
lf_rows_len           		447
br_rows_len            		0
lf_blk_len          		1,876
br_blk_len              	0




==========
Script #3:
==========

SET ECHO off
REM NAME:   TFSIKEYS.SQL
REM USAGE:"@path/tfsikeys idx_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Shows the index keys for a particular table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM             Index Keys Summary
REM
REM    Uniqueness                Index Name                    Column Name
REM    ---------- ----------------------------------------  ------------------
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID
REM
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
def ixowner	= &&1
def tabname	= &&2

ttitle -
   center  'Index Keys Summary'  skip 2

col uniq    format a10 heading 'Uniqueness'  justify c trunc
col indname format a40 heading 'Index Name'  justify c trunc
col colname format a25 heading 'Column Name' justify c trunc

break -
  on indname skip 1 -
  on uniq

select
  ind.uniqueness                  uniq,
  ind.owner||'.'||col.index_name  indname,
  col.column_name                 colname
from
  dba_ind_columns  col,
  dba_indexes      ind
where
  ind.owner = upper('&ixowner')
    and
  ind.table_name = upper('&tabname')
    and
  col.index_owner = ind.owner
    and
  col.index_name = ind.index_name
order by
  col.index_name,
  col.column_position
/

undef ixowner
undef tabname
set verify on


==============
Sample Output:
==============


         Index Keys Summary


Uniqueness                Index Name                    Column Name
---------- ---------------------------------------- ----------------------
UNIQUE                SCOTT.S_EMP_ID_PK                        ID

UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID

Know More About Libarary Cache and Latches

Stored objects And Transient objects are stored in the library cache, neither Temporary objects nor Permanent objects.

The library cache is structured as a Hash table .But Library cache objects are composed of data heaps.
Oracle access hash tables through hash buckets.

SHARED_POOL_SIZE does the Oracle server determine the number of hash tables. When the the shared pool is larger, it can accommodate more object handles.

_KGL_BUCKET_CNT can be used to set the number of hash buckets and deprecated.
The minimum size of the hash table is 509 buckets in ORACLE 7.

For performance reasons, when the linked list has an average depth of 2 the Oracle server doubles the size of the hash table.

Locks manage concurrency whereas pins manage cache coherency.

There are two valid library cache pin modes: Share and Exclusive

An object handle is protected by a latch determined by the bucket it hashes into using the formula latch# = mod(bucket#, #latches) .

The hidden parameter _KGL_LATCH_COUNT is used to determine the number of child latches.”The default value should be adequate, but if contention for the library cache latch cant be resolved, it may be advisable to increase this value. The default value for _KGL_LATCH_COUNT is the next prime number after CPU_COUNT. This value cannot exceed 66 (See: <>). ”

Begin 10.2.0.2, mutex take place cursor pin latch.To avoid using Mutex latches, you can set _kks_use_mutex_pin=false .

CURSOR_SPACE_FOR_TIME has been deprecated in 10.2.0.5 and 11.1.0.7.

CURSOR_SPACE_FOR_TIME was originally introduced to try and help reduce latch contention by keeping cursors in memory in the SGA rather than allowing their data to be flushed from the shared pool. Such latch contention is avoided in current releases by the use of cursor mutexes and so this parameter is no longer relevant.

v$open_cursor lists kinds of library cache lock,x$kgllk – Details about Object locks


v$open_cursor

select inst_id,
       kgllkuse,
       kgllksnm,
       user_name,
       kglhdpar,
       kglnahsh,
       kgllksqlid,
       kglnaobj,
       kgllkest,
       decode(kgllkexc, 0, to_number(NULL), kgllkexc),
       kgllkctp
  from x$kgllk
 where kglhdnsp = 0
   and kglhdpar != kgllkhdl


SQL> select distinct kgllkctp from x$kgllk ;

KGLLKCTP
--------------------------------------------
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED

Oracle内部视图:X$BH

X$BH Fixed Table Buffer Cache Diagram

Column     Type Description
~~~~~~     ~~~~~ ~~~~~~~~~~~
ADDR        RAW(4) Hex address of the Buffer Header.
INDX        NUMBER Buffer Header number
BUF#        NUMBER
HLADDR      RAW(4) Hash Chain Latch Address
See . ADDR

LRU_FLAG    NUMBER     8.1+ LRU flag
           KCBBHLDF   0x01     8.1  LRU Dump Flag used in debug print routine
           KCBBHLMT   0x02     8.1  moved to tail of lru (for extended stats)
           KCBBHLAL   0x04     8.1  on auxiliary list
           KCBBHLHB   0x08     8.1  hot buffer - not in cold portion of lru

FLAG        NUMBER
           KCBBHFBD   0x00001       buffer dirty
           KCBBHFAM   0x00002  7.3  about to modify; try not to start io
           KCBBHFAM   0x00002  8.0  about to modify; try not to start io
           KCBBHNAC   0x00002  8.1  notify dbwr after change
           KCBBHFMS   0x00004       modification started, no new writes
           KCBBHFBL   0x00008       block logged
           KCBBHFTD   0x00010       temporary data - no redo for changes
           KCBBHFBW   0x00020       being written; can't modify
           KCBBHFWW   0x00040       waiting for write to finish
           KCBBHFCK   0x00080  7.3  checkpoint asap
                      0x00080  8.0  not used
           KCBBHFMW   0x00080  8.1  multiple waiters when gc lock acquired
           KCBBHFRR   0x00100       recovery reading, do not reuse, being read
           KCBBHFUL   0x00200       unlink from lock element - make non-current
           KCBBHFDG   0x00400       write block & stop using for lock down grade
           KCBBHFCW   0x00800       write block for cross instance call
           KCBBHFCR   0x01000       reading from disk into KCBBHCR buffer
           KCBBHFGC   0x02000       has been gotten in current mode
           KCBBHFST   0x04000       stale - unused CR buf made from current
                      0x08000  7.3  Not used.
           KCBBHFDP   0x08000  8.0  deferred ping
           KCBBHFDP   0x08000  8.1  deferred ping
           KCBBHFDA   0x10000       Direct Access to buffer contents
           KCBBHFHD   0x20000       Hash chain Dump used in debug print routine
           KCBBHFIR   0x40000       Ignore Redo for instance recovery
           KCBBHFSQ   0x80000       sequential scan only flag
           KCBBHFNW  0x100000  7.3  Set to indicate a buffer that is NEW
                     0x100000  8.0  Not used
           KCBBHFBP  0x100000  8.1  Indicates that buffer was prefetched
           KCBBHFRW  0x200000  7.3  re-write if being written (sort)
                     0x200000  8.0  Not used
           KCBBHFFW  0x200000  8.1  Buffer has been written once
           KCBBHFFB  0x400000       buffer is "logically" flushed
           KCBBHFRS  0x800000       ReSilvered already - do not redirty
           KCBBHFKW 0x1000000  7.3  ckpt writing flag to avoid rescan */
                    0x1000000  8.0  Not used
           KCBBHDRC 0x1000000  8.1  buffer is nocache
                    0x2000000  7.3  Not used
           KCBBHFRG 0x2000000  8.0  Redo Generated since block read
           KCBBHFRG 0x2000000  8.1  Redo Generated since block read
           KCBBHFWS 0x10000000 8.0  Skipped write for checkpoint.
           KCBBHFDB 0x20000000 8.1  buffer is directly from a foreign DB
           KCBBHFAW 0x40000000 8.0  Flush after writing
           KCBBHFAW 0x40000000 8.1  Flush after writing

TS#         NUMBER 8.X Tablespace number
DBARFIL     NUMBER 8.X Relative file number of block
DBAFIL      NUMBER 7.3 File number of block
DBABLK      NUMBER Block number of block
CLASS       NUMBER See Note 33434.1
              1,'data block',
              2,'sort block',
              3,'save undo block',
              4,'segment header',
              5,'save undo header',
              6,'free list',
              7,'extent map',
              8,'1st level bmb',
              9,'2nd level bmb',
              10,'3rd level bmb',
              11,'bitmap block',
              12,'bitmap index block',
              13,'file header block',
              14,'unused',
              15,'system undo header',
              16,'system undo block',
              17,'undo header',
              18,'undo block'                -- since 10g

STATE       NUMBER
           KCBBHFREE         0       buffer free
           KCBBHEXLCUR       1       buffer current (and if DFS locked X)
           KCBBHSHRCUR       2       buffer current (and if DFS locked S)
           KCBBHCR           3       buffer consistant read
           KCBBHREADING      4       Being read
           KCBBHMRECOVERY    5       media recovery (current & special)
           KCBBHIRECOVERY    6       Instance recovery (somewhat special)

MODE_HELD   NUMBER    Mode buffer held in (MODE pre 7.3)
   0=KCBMNULL, KCBMSHARE, KCBMEXCL

CHANGES     NUMBER
CSTATE      NUMBER
X_TO_NULL   NUMBER Count of PINGS out (OPS)
DIRTY_QUEUE NUMBER You wont normally see buffers on the LRUW
LE_ADDR     RAW(4) Lock Element address (OPS)
SET_DS      RAW(4) Buffer cache set this buffer is under
    OBJ         NUMBER       Data object number
TCH     NUMBER 8.1 Touch Count
TIM     NUMBER 8.1 Touch Time
BA          RAW(4)
CR_SCN_BAS  NUMBER       Consistent Read SCN base
CR_SCN_WRP  NUMBER       Consistent Read SCN wrap
CR_XID_USN  NUMBER CR XID Undo segment no
CR_XID_SLT  NUMBER CR XID slot
CR_XID_SQN  NUMBER CR XID Sequence
CR_UBA_FIL  NUMBER CR UBA file
CR_UBA_BLK  NUMBER CR UBA Block
CR_UBA_SEQ  NUMBER CR UBA sequence
CR_UBA_REC  NUMBER CR UBA record
CR_SFL      NUMBER
LRBA_SEQ    NUMBER } Lowest RBA needed to recover block in cache
LRBA_BNO    NUMBER }
LRBA_BOF    NUMBER }

HRBA_SEQ    NUMBER } Redo RBA to be flushed BEFORE this block
HRBA_BNO    NUMBER } can be written out
HRBA_BOF    NUMBER       }

RRBA_SEQ    NUMBER } Block recovery RBA
RRBA_BNO    NUMBER }
RRBA_BOF    NUMBER }
NXT_HASH    NUMBER Next buffer on this hash chain
PRV_HASH    NUMBER Previous buffer on this hash chain
NXT_LRU     NUMBER Next buffer on the LRU
PRV_LRU     NUMBER Previous buffer on the LRU
US_NXT      RAW(4)
US_PRV      RAW(4)
WA_NXT      RAW(4)
WA_PRV      RAW(4)
ACC         RAW(4)
MOD         RAW(4)

沪ICP备14014813号-2

沪公网安备 31010802001379号