[Repost]List of X$ Tables

This is a summary list of X$ Table Definitions - Last revision was 7.3.2
The main purpose of this note is to show the naming conventions.

[K]ernel Layer
[2]-Phase Commit
[G]lobal [T]ransaction [E]ntry
X$K2GTE  - Current 2PC tx
X$K2GTE2 - Current 2PC tx
[C]ache Layer
[B]uffer Management
Buffer [H]ash
X$BH - Hash Table
Buffer LRU Statistics
X$KCBCBH - [C]urrent [B]uffers (buckets) - lru_statistics
X$KCBRBH - [R]ecent [B]uffers (buckets) - lru_extended
Buffer [WAIT]s
X$KCBWAIT  - Waits by block class
X$KCBFWAIT - Waits by File
[W]orking Sets - 7.3 or higher
X$KCBWDS - Set [D]escriptors
[C]ontrol File Management
[C]ontrol [F]ile List - 7.0.16 or higher
X$KCCCF - Control File Names & status
[D]atabase [I]nformation
X$KCCDI - Database Information
Data [F]iles
X$KCCFE - File [E]ntries ( from control file )
X$KCCFN - [F]ile [N]ames
[L]og Files
X$KCCLE - Log File [E]ntries
X$KCCLH - Log [H]istory ( archive entries )
Thread Information
X$KCCRT - [R]edo [T]hread Information
[F]ile Management
X$KCFIO - File [IO] Statistics
[L]ock Manager Component ( LCK )
[H]ash and Bucket Tables - 7.0.15 to 7.1.1, and 7.2.0 or higher
X$KCLFH - File [H]ash Table
X$KCLFI - File Bucket Table
X$LE - Lock [E]lements
X$LE_STAT - Lock Conversion [STAT]istics
X$KCLFX - Lock Element [F]ree list statistics - 7.3 or higher
X$KCLLS - Per LCK free list statistics - 7.3 or higher
X$KCLQN - [N]ame (hash) table statistics - 7.3 or higher
[R]edo Component
[M]edia recovery  - kcra.h - 7.3 or higher
X$KCRMF - [F]ile context
X$KCRMT - [T]hread context
X$KCRMX - Recovery Conte[X]t
[F]ile read
X$KCRFX - File Read Conte[X]t -  7.3 or higher
Reco[V]ery Component
[F]ile [H]eaders
X$KCVFH - All file headers
X$KCVFHMRR - Files with [M]edia [R]ecovery [R]equired
X$KCVFHONL - [ONL]ine File headers
[K]ompatibility Management - 7.1.1 or higher
X$KCKCE - [C]ompatibility Segment [E]ntries
X$KCKTY - Compatibility [TY]pes
X$KCKFM - Compatibility [F]or[M]ats ( index into X$KCKCE )
[D]ata Layer
Sequence [N]umber Component
X$KDNCE - Sequence [C]ache [E]ntries - 7.2 or lower
[S]equence Enqueues - common area for enqueue objects
X$KDNSSC - [C]ache Enqueue Objects - 7.2 or lower
X$KDNSSF - [F]lush Enqueue Objects - 7.2 or lower
X$KDNST - Cache [ST]atistics - 7.2 or lower
Inde[X] Block Component
X$KDXHS - Index [H]i[S]togram
X$KDXST - Index [ST]atistics
[G]eneric Layer
[H]eap Manager
X$KGHLU - State (summary) of [L]R[U] heap(s) - defined in ksmh.h
[I]nstantiation Manager
[C]ursor [C]ache
X$KGICC - Session statistics - defined in kqlf.h
X$KGICS - System wide statistics - defined in kqlf.h
[L]ibrary Cache Manager  ( defined and mapped from kqlf )
Bind Variables
X$KKSBV - Library Object [B]ind [V]ariables
Object Cache
X$KGLOB - All [OB]jects
X$KGLTABLE   - Filter for [TABLE]s
X$KGLBODY    - Filter for [BODY] ( packages )
X$KGLTRIGGER - Filter for [TRIGGER]s
X$KGLINDEX   - Filter for [INDEX]es
X$KGLCLUSTER - Filter for [CLUSTER]s
X$KGLCURSOR  - Filter for [CURSOR]s
Cache Dependency
X$KGLDP - Object [D]e[P]endency table
X$KGLRD - [R]ead only [D]ependency table - 7.3 or higher
Object Locks
X$KGLLK - Object [L]oc[K]s
Object Names
X$KGLNA - Object [NA]mes (sql text)
X$KGLNA1 - Object [NA]mes (sql text) with newlines - 7.2.0 or higher
Object Pins
X$KGLPN - Object [P]i[N]s
Cache Statistics
X$KGLST - Library cache [ST]atistics
Translation Table
X$KGLTR - Address [TR]anslation
Access Table
X$KGLXS - Object Access Table
Authorization Table - 7.1.5 or higher
X$KGLAU - Object Authorization table
Latch Cleanup - 7.0.15 or higher
X$KGLLC - [L]atch [C]leanup for Cache/Pin Latches
[K]ompile Layer
[S]hared Objects
X$KKSAI - Cursor [A]llocation [I]nformation - 7.3.2 or higher
[L]oader
[L]ibrary
X$KLLCNT - [C]o[NT]rol Statistics
X$KLLTAB - [TAB]le Statistics
[M]ulti-Threaded Layer
[C]ircuit component
X$KMCQS - Current [Q]ueue [S]tate
X$KMCVC - [V]irtual [C]ircuit state
[M]onitor Server/dispatcher
[D]ispatcher
X$KMMDI - [D]ispatcher [I]nfo (status)
X$KMMDP - [D]ispatcher Config ( [P]rotocol info )
[S]erver
X$KMMSI - [S]erver [I]nfo ( status )
X$KMMSG - [SG]a info ( global statistics)
X$KMMRD - [R]equest timing [D]istributions
s[Q]l Version and Option Layer
Kernel [V]ersions
X$VERSION - Library versions
Kernel [O]ptions - 7.1.3 or higher
X$OPTION - Server Options
[Q]uery Layer
[D]ictionary Cache Management
X$KQDPG - [PG]a row cache cursor statistics
[F]ixed Tables/views Management
X$KQFCO - Table [CO]lumn definitions
X$KQFDT - [D]erived [T]ables
X$KQFSZ - Kernel Data structure type [S]i[Z]es
X$KQFTA - Fixed [TA]bles
X$KQFVI - Fixed [VI]ews
X$KQFVT - [V]iew [T]ext definition - 7.2.0 or higher
[R]ow Cache Management
X$KQRST - Cache [ST]atistics
X$KQRPD - [P]arent Cache [D]efinition - 7.1.5 or higher
X$KQRSD - [S]ubordinate Cache [D]efinition - 7.1.5 or higher
[S]ervice Layer
[B]ackground Management
[D]etached Process
X$KSBDD - Detached Process [D]efinition (info)
X$KSBDP - Detached [P]rocess Descriptor (name)
X$MESSAGES - Background Message table
[I]nstance [M]anagement - 7.3 or higher
X$KSIMAT - Instance [AT]tributes
X$KSIMAV - [A]ttribute [V]alues for all instances
X$KSIMSI - [S]erial and [I]nstance numbers
[L]ock Management
[E]vent Waits
X$KSLED - Event [D]escriptors
X$KSLEI - [I]nstance wide statistics since startup
X$KSLES - Current [S]ession statistics
[L]atches
X$KSLLD - Latch [D]escriptor (name)
X$KSLLT - Latch statistics [ + Child latches @ 7.3 or higher ]
X$KSLLW - Latch context ( [W]here ) descriptors - 7.3 or higher
X$KSLPO - Latch [PO]st statistics - 7.3 or higher
X$KSLWSC- No[W]ait and [S]leep [C]ount stats by Context -7.3 or higher
[M]emory Management
[C]ontext areas
X$KSMCX - E[X]tended statistics on usage - 7.3.1 or lower
Heap Areas
X$KSMSP - SGA Hea[P]
X$KSMPP - [P]GA Hea[P] - 7.3.2 and above
X$KSMUP - [U]GA Hea[P] - 7.3.2 and above
X$KSMHP - Any [H]ea[P] - 7.3.2 and above
X$KSMSPR- [S]hared [P]ool [R]eserved List - 7.1.5 or higher
[L]east recently used shared pool chunks
X$KSMLRU - LR[U] flushes from the shared pool
[S]GA Objects
X$KSMSD - Size [D]efinition for Fixed/Variable summary
X$KSMSS - Statistics (lengths) of SGA objects
SGA [MEM]ory
X$KSMMEM - map of the entire SGA - 7.2.0 or higher
X$KSMFSV - Addresses of [F]ixed [S]GA [V]ariables - 7.2.1 or higher
[P]arameter Component
X$KSPPI  - [P]arameter [I]nfo ( Names )
X$KSPPCV - [C]urrent Session [V]alues - 7.3.2 or above
X$KSPPSV - [S]ystem [V]alues - 7.3.2 or above
En[Q]ueue Management
X$KSQDN - Global [D]atabase [N]ame
X$KSQEQ - [E]n[Q]ueue Object
X$KSQRS - Enqueue [R]e[S]ource
X$KSQST - Enqueue [S]tatistics by [T]ype
[U]ser Management
[C]ost
X$KSUCF - Cost [F]unction (resource limit)
[L]icence
X$KSULL - Licence [L]imits
[L]anguage Manager
X$NLS_PARAMETERS - NLS parameters
X$KSULV - NLS [V]alid Values - 7.1.2 or higher
[MY] [ST]atistics
X$KSUMYSTA - [MY] [ST]atisics (current session)
[P]rocess Info
X$KSUPL - Process (resource) [L]imits
X$KSUPRLAT - [LAT]ch Holder
X$KSUPR - Process object
[R]esource
X$KSURU - Resource [U]sage
[S]tatistics
X$KSUSD - [D]escriptors (statistic names)
X$KSUSGSTA - [G]lobal [ST]atistics
[SE]ssions
X$KSUSECST - Session status for events
X$KSUSESTA - Session [STA]tistics
X$KSUSECON - [CON]nection Authentication - 7.2.1 or higher
X$KSUSE - [SE]ssion Info
X$KSUSIO - [S]ystem [IO] statistics per session
[T]imer
X$KSUTM - Ti[M]e in 1/100th seconds
Instance [X]
X$KSUXSINST - [INST]ance state
[T]race management
X$TRACE - Current traced events
X$TRACES - All possible traces
X$KSTEX - Code [EX]ecution - 7.2.1 or higher
E[X]ecution Management
Device/Node [A]ffinity - 7.3.2 and above
X$KSXAFA - Current File/Node Affinity
[T]ransaction Layer
Table [A]ccess [D]efinition
X$KTADM - D[M]L lock
[C]ontrol Component
X$KTCXB - Transaction O[B]ject
[S]or[T] Segments - 7.3 or higher
X$KTSTSSD - [S]ort [S]egment [D]escriptor - per tablespace statistics
[T]ablespace
X$KTTVS - [V]alid [S]aveundo
[U]ndo
X$KTURD - Inuse [D]escriptors
X$KTUXE - Transaction [E]ntry (table) - 7.3.2 or above
Performance Layer [V] - 7.0.16 or higher
[I]nformation tables
X$KVII - [I]nitialisation Instance parameters
X$KVIS - [S]izes of structure elements
X$KVIT - [T]ransitory Instance parameters
Security Layer [Z]
[D]ictionary Component
X$KZDOS - [OS] roles
[S]ecurity State
X$KZSPR - Enabled [PR]ivileges
X$KZSRO - Enabled [RO]les
[R]emote Logins - 7.1.1 or higher
X$KZSRT - [R]emote Password File [T]able entries
E[X]ecution Layer
Parallel Query (Execute [F]ast) - 7.1.1 or higher
[P]rocess and Queue Manager
Statistics - 7.1.3 or higher
X$KXFPYS - S[YS]tem Statistics
X$KXFPDP - [D]etached [P]rocess (slave) statistics
X$KXFQSROW - Table [Q]ueue Statistics - 7.3.2 or higher
[C]oordinator Component
X$KXFPCST - Query [ST]atistics
X$KXFPCMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]lave Component
X$KXFPSST - Query [ST]atistics
X$KXFPSMS - [M]essage [S]tatistics
X$KXFPCDS - [D]equeue [S]tatistics
[S]hared Cursor
X$KXSBD - [B]ind [D]ata - 7.3.2 and above
X$KXSCC - SQL [C]ursor [C]ache Data - 7.3.2 and above
[N]etwork Layer - 7.0.15 or higher
Network [CO]nnections
X$UGANCO - Current [N]etwork [CO]nnections

Script:Translate RDBA relative data block address

Script:Translate RDBA relative data block address

 

-- scripts/admin/rdba/rdba_to_file_block.sql
select trunc( to_number('&rdba','XXXXXXXX')
            / power(2,22) ) as rfile#
     , trunc(to_number('&rdba','XXXXXXXX')-
          trunc( to_number('&rdba','XXXXXXXX')
               / power(2,22))*power(2,22) ) as block#
  from dual
/

CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_str   VARCHAR2 (255) DEFAULT NULL;
   l_fno   VARCHAR2 (15);
   l_bno   VARCHAR2 (15);
BEGIN
   l_fno :=
      DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                       'xxxxxxxx'
                                                      )
                                           );
   l_bno :=
      DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                        'xxxxxxxx'
                                                       )
                                            );
   l_str :=
         'datafile# is:'
      || l_fno
      || CHR (10)
      || 'datablock is:'
      || l_bno
      || CHR (10)
      || 'dump command:alter system dump datafile '
      || l_fno
      || ' block '
      || l_bno
      || ';';
   RETURN l_str;
END;
/

select getbfno('0x00400009') from dual;

Trace obtained enqueue information by set event 10704

Oracle事件10704可以帮助我们了解队列Enqueue的使用情况,使用方法如下:

[oracle@rh2 bdump]$ oerr ora 10704
10704, 00000, "Print out information about what enqueues are being obtained"
// *Cause:  When enabled, prints out arguments to calls to ksqcmi and
//          ksqlrl and the return values.
// *Action: Level indicates details:
//   Level: 1-4: print out basic info for ksqlrl, ksqcmi
//          5-9: also print out stuff in callbacks:  ksqlac, ksqlop
//          10+: also print out time for each line

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 10704 trace name context forever,level 10;
Statement processed.

SQL> lock  table tm in share mode;
Table(s) Locked.

SQL> oradebug tracefile_name;
/s01/admin/G10R2/udump/g10r2_ora_28400.trc

ksqgtl *** CU-9fec6e30-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:16.139
*** ksudidTrace: ksqgtl
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0
*** 2011-05-07 21:17:16.160
ksqrcl: CU,9fec6e30,0
ksqrcl: returns 0
*** 2011-05-07 21:17:23.884
ksqgtl *** CU-9fec69f8-00000000 mode=6 flags=0x10 timeout=300 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:23.884
*** ksudidTrace: ksqgtl
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0
*** 2011-05-07 21:17:23.898
ksqrcl: CU,9fec69f8,0
ksqrcl: returns 0
*** 2011-05-07 21:17:23.899
ksqgtl *** TM-0000d06b-00000000 mode=4 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0xa69d0a00, ktcdix=2147483647, topxcb=0xa69d0a00
        ktcipt(topxcb)=0x0
*** 2011-05-07 21:17:23.899
ksucti: init txn DID from session DID 0001-0017-00000008
ksqgtl:
        ksqlkdid: 0001-0017-00000008
*** 2011-05-07 21:17:23.899

*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0017-00000008
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-00000008
ksqgtl: RETURNS 0

该10704事件可以配合10046事件一起诊断异常的队列锁问题,记以录之!

脚本:格式化的V$SQL_SHARED_CURSOR报告

The function generates a summary report of the v$sql_shared_cursor view and additional diagnostic information depending on the reason code.
Counts all the versions that have ‘Y’ in any of the columns and if any have all ‘N’ too.

This script may be useful to diagnose ORA-600 [17059] errors

Running the Script
— Generate reports for all cursors with more than 100 versions using SQL_ID (10g and up)
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;

— Generate reports for all cursors with more than 100 versions using HASH_VALUE
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;

— Generate the report for cursor with sql_id cyzznbykb509s
select * from table(version_rpt(‘cyzznbykb509s’));

version_rpt3_12.sql

脚本:监控并行进程状态

脚本正文:

col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300 lines 300
col wait_event format a30
select
decode(px.qcinst_id,NULL,username, 
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,     
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid  
and sw.inst_id = s.inst_id   
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
  px.SERVER_SET, 
  px.INST_ID
/

set pages 300 lines 300
col wait_event format a30
select 
  sw.SID as RCVSID,
  decode(pp.server_name, 
         NULL, 'A QC', 
         pp.server_name) as RCVR,
  sw.inst_id as RCVRINST,
case  sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
  decode(bitand(p1, 65535),
         65535, 'QC', 
         'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
  bitand(p1, 16711680) - 65535 as SNDRINST,
  decode(bitand(p1, 65535),
         65535, ps.qcsid,
         (select 
            sid 
          from 
            gv$px_process 
          where 
            server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
            inst_id = bitand(sw.p1, 16711680) - 65535)
        ) as SNDRSID,
   decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE     
from 
  gv$session_wait sw,
  gv$px_process pp,
  gv$px_session ps
where
  sw.sid = pp.sid (+) and
  sw.inst_id = pp.inst_id (+) and 
  sw.sid = ps.sid (+) and
  sw.inst_id = ps.inst_id (+) and 
  p1text  = 'sleeptime/senderid' and
  bitand(p1, 268435456) = 268435456
order by
  decode(ps.QCINST_ID,  NULL, ps.INST_ID,  ps.QCINST_ID),
  ps.QCSID,
  decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP), 
  ps.SERVER_SET, 
  ps.INST_ID
/



set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30

select
decode(px.qcinst_id,NULL,username, 
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30)  operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s 
where px.sid=s.sid 
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
  decode(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID),
  px.QCSID,
  decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), 
  px.SERVER_SET, 
  px.INST_ID
/ 

示例输出:

Username     QC/Slave SlaveSet SID    Slave INS STATE    WAIT_EVENT                     QC SID QC INS Req. DOP Actual DOP
------------ -------- -------- ------ --------- -------- ------------------------------ ------ ------ -------- ---------- 
SCOTT      QC                923    1         WAIT     db file sequential read        923
 - p003      (Slave)  1        935    1         WAIT     PX Deq Credit: send blkd       923    1             4          4
 - p001      (Slave)  1        961    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p002      (Slave)  1        1035   1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p004      (Slave)  1        977    1         WAIT     PX Deq Credit: send blkd       923    1             4          4
 - p006      (Slave)  2        609    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p007      (Slave)  2        642    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p008      (Slave)  2        970    1         WAIT     PX Deq: Execution Msg          923    1             4          4
 - p005      (Slave)  2        953    1         WAIT     PX Deq: Execution Msg          923    1             4          4
SCOTT      QC                1003   1         WAIT     SQL*Net message from client    1003
 - p015      (Slave)  1        608    1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p011      (Slave)  1        639    1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p012      (Slave)  1        1115   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p000      (Slave)  1        1253   1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p010      (Slave)  1        1420   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p009      (Slave)  1        1421   1         WAIT     PX Deq Credit: send blkd       1003   1             8          8
 - p014      (Slave)  1        1417   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p013      (Slave)  1        1180   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p020      (Slave)  2        1422   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p023      (Slave)  2        1423   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p018      (Slave)  2        1424   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p021      (Slave)  2        1426   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p019      (Slave)  2        1428   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p016      (Slave)  2        1429   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p022      (Slave)  2        1427   1         WAIT     PX Deq: Execution Msg          1003   1             8          8
 - p017      (Slave)  2        1425   1         WAIT     PX Deq: Execution Msg          1003   1             8          8 

脚本:监控数据库中的活跃用户及其运行的SQL

脚本正文:

set linesize 120 pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
col event  for a30
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid     b1,
       b.osuser   c1,
       b.username c2,
       b.sid      b2,
       b.serial#  b3,
       b.event,
       a.sql_text
  from v$sqltext a, v$session b, v$process c
 where a.address = b.sql_address
      --   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
      --                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid, a.hash_value, a.piece
/      		  

REM TOP SESSION

with sessions as
 (select /*+ materialize */
   sess.inst_id,
   sess.sid,
   sess.serial#,
   sess.username,
   sess.module,
   sess.program,
   stat.value cpu_used_by_this_session,
   i.physical_reads,
   i.block_gets,
   sess.command,
   sess.status,
   sess.lockwait,
   decode(sess.sql_hash_value, 0, sess.prev_hash_value, sess.sql_hash_value) sql_hash_value,
   decode(sess.sql_address, '00', sess.prev_sql_addr, sess.sql_address) sql_address
    from gv$sesstat stat, gv$session sess, gv$sess_io i
   where stat.statistic# =
         (select statistic#
            from v$statname
           where name = 'CPU used by this session')
     and stat.sid = sess.sid
     and stat.inst_id = sess.inst_id
     and (stat.value > 100 or i.physical_reads > 100 or i.block_gets > 100)
     and sess.username is not null
     and i.sid = sess.sid
     and i.inst_id = sess.inst_id),
sqlarea as
 (select inst_id, sql_fulltext sql_text, hash_value, address from gv$sqlarea)
select *
  from sessions, sqlarea
 where sessions.inst_id = sqlarea.inst_id and sessions.sql_hash_value = sqlarea.hash_value and sessions.sql_address = sqlarea.address
 order by cpu_used_by_this_session desc
/

脚本:监控临时表空间使用率

针对字典管理临时表空间:

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from 
(select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_data_files where tablespace_name='TEMP') f;

针对本地管理表空间:

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from 
(select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

示例输出:

 select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
  2  from (select sum(used_blocks) tot_used_blocks from 
v$sort_segment where tablespace_name='TEMP') s, 
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

percent used
------------
           0

Oracle 8i 备份/恢复(Recovery Manager)

本文永久地址:https://www.askmaclean.com/archives/oracle-8i-备份恢复(recovery-manager).html

说明

Recovery Manager(RMAN)是从Oracle8 R8.0之后追加的管理oracle数据库备份/储存/恢复的工具。在此,我将解说oracle8i的RMAN中扩展的新功能。

 

  • RMAN基本功能的扩展
  • 媒介管理软件相关功能的扩展
  • OPS相关功能的扩展

在此对于RMAN命令的详细语法方面的内容,请参考《Oracle8i备份、恢复指南》

 

RMAN基本功能的扩展

1-1.复制备份set

考虑到Oracle的备份时,类似于归档日志以及控制文件的案例,某个备份受损可能造成毁灭性的故障。Oracle8i的RMAN中,可以一次制成多个类似的的备份。因此,在存储时某个备份无法使用的话,用于其他备份就OK了。

为了制成多个备份,需要通过RMAN命令的SET DUPLEX命令来指定复制数,执行备份。SET DUPLEX命令如下所示。

 

SET DUPLEX = {ON|OFF|1|2|3|4};

 

关键词ON意味着指定为2 。DUPLEX最多可以制成4个拷贝。以下是使用SET DUPLEX命令的范例。

RUN {

SET DUPLEX =2;

ALLOCATE CHANNEL ch1 TYPE ‘sbt_tape’;

BACKUP

FORMAT ‘arc_%s_%p_%c’ FILESPERSET 1

ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL ch1;

}

SET DUPLEX命令需要比分配channel(ALLOCATE CHANNEL命令)更早执行。另外,执行SET DUPLEX命令后,在分配到的所有channel中执行备份复制。
在制成tape装置的备份是,需要指定初始化参数BACKUP_TAPE_IO_SLAVES = TRUE。
执行复制备份时,用指定备份名的FORMAT句,可以用替换字符串“%”来指定。在上述例子中,可以用替换字符串“%”来指定。在上述例子中,备份set编号为10,备份piece为1时制成 ‘arc_10_1_1’、‘arc_10_1_2’两个备份。

通过RMAN制成克隆数据库

Oracle8i的RMAN中,使用RMAN的备份,在远程主机或本地主机中,制成数据库的拷贝(克隆数据库)。因为克隆数据库与真正的数据库结构完全相同,所以并不会对正式环境的数据库产生影响。可以作为测试专用数据库。

左:正式数据库、克隆数据库、制成克隆数据库 本地主机

右:克隆数据库、远程主机

通过RMAN制成克隆数据库是,以下操作可以使用RMAN完成。

  • 克隆数据库中所使用的数据库文件的还原。在远程主机中制成克隆数据库时,是经过Net8来传送文件,还原文件的。
  • 直到执行命令时,需要执行应用了REDO的不完整恢复
  • 启动指定不完整恢复之后的RESETLOGS选项的数据库
  • 对新数据库重命名
  • 制成控制文件

通过RMAN制成克隆数据库时,伴随着指定了RESETLOGS的数据库的启动,就无法在制成stand by数据库时使用。

 

以下命令是远程主机制成克隆数据库的例子。为了完成这个操作,需要实现在远程主机中进行以下操作。

 

  • 收集远程主机中的ora参数文件拷贝
  • 设定需要连接远程主机的net8
  • 编辑ora、tnsnames.ora
  • 启动listener

 

完成这些设定之后,请使用以下命令来制成克隆数据库。下例是通过Net8连接描述符来指定在本地设备中的克隆数据库,制成远程主机中的克隆数据库。

 

 

# 启动RMAN,这时请指定克隆数据库的连接描述符。
%rman connect target / auxiliary sys/passwd@clonedb catalog rman/rman/@rcat

# 以下是制成克隆数据库的RMAN命令。
RMAN>STARTUP NOMOUNT;
RMAN> RUN{
2>      ALLOCATE AUXILIARY CHANNEL ch1 TYPE DISK;
3>      DUPLICATE TARGET DATABASE TO CLONEDB 
4>         LOGFILE ‘/Oracle/dbs/log1.f’ SIZE 200K,
5>                 ‘/Oracle/dbs/log2.f’ SIZE 200K 
6>         NOFILECHECK;
7>    }


DUPLICATE命令中,需要原数据库与克隆数据库中所使用的数据库文件名不重复。因此,在同样的目录结构中,在远程主机中制成克隆数据库时,需要设置为指定NOFILECHECK选项而不执行这个检查。
具体的克隆数据库制作顺序请参考《执行例与结果》。

通过RMAN来制成克隆数据库的详细内容请参考「Oracle8i备份、恢复指南」。

启动/关闭数据库

Oracle8 R8.0中为了对数据库进行冷备份以及恢复,需要启动、关闭数据库时,需要使用Server Manager执行这些操作。因此通过RMAN来执行修复时,就会组合SVRMGR与RMAN来一起使用。一些脚本由此就会变得复杂。

在Oracle8i的RMAN中,作为RMAN的组合命令支撑数据库的STARTUP、SHUTDOWN、ALTER DATABASE命令。由此就可以仅凭RMAN启动、关闭数据库。

 

追加命令 功能
STARTUP … 启动数据库。可以指定SQL*Plus的STARTUP命令以及同样的选项。
SHUTDOWN … 关闭数据库。可以指定SQL*Plus的SHUTDOWN命令以及相关选项。
ALTER DATABASE … 将数据库从MOUNT状态变更为OPEN状况。

 

在以下RMAN命令中,获得启动中终止的数据库的冷备份。重启数据库。

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN{
    ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE';
    BACKUP
      FORMAT 'db_%s_%p'
     (DATABASE);
}
ALTER DATABASE OPEN;

 

扩展LIST命令

LIST命令是展示至今为止获得的备份的信息的命令。Oracle8i的RMAN中,LIST命令已经被扩展为执行时会展示各自的备份片的名称、所有的数据文件的名称,控制文件信息等。以下是展示表区域bo1的备份set的一览的例子。

 

 

RMAN> LIST BACKUPSET OF TABLESPACE bol;

RMAN-03022: compiling command: list

List of Backup Sets
Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
1       1          365975325  0  365975324  2          20-APR-99

    List of Backup Pieces
    Key     Pc# Cp# Status      Completion Time        Piece Name
    ------- --- --- ----------- ---------------------- -----------------------
    2       1   2   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_2
    1       1   1   AVAILABLE   20-APR-99              /bkup/bol_02at0los_1_1

    List of Datafiles Included
    File Name                                  LV Type Ckp SCN    Ckp Time
    ---- ------------------------------------- -- ---- ---------- ------------
    4    /oradata/je815/dbs/bolje815.dbf       0  Full 578102     20-APR-99

 

LIST命令中,仅会展示备份的统计数据的平均值。展示deleted、unavailable状态的备份信息时,请使用LIST ALL … 命令。

通过RMAN制成、更新恢复目录

Oracle8 R8.0中,需要使用SQL*Plus来制成、更新、删除、储存RMAN使用的信息的恢复目录的脚本。

Oracle8i中,变更为将这些操作作为RMAN的命令来执行了。这些命令是用RMAN启动时的选项RCVCAT来指定的,对于用户的架构来执行的。

 

追加命令 功能
CREATE CATALOG 制成现在连接的架构中的恢复目录。可以用TABLESPACE语句来指定制成目录的表区域。
UPDATE CATALOG 连接到旧版本的目录时,执行目录的更新。UPDATE CATALOG命令为了确认,连续执行两次,于是就会进行更新。
DROP CATALOG 删除现在连接的架构中的目录。

DROP CATALOG命令连续执行两次时,就会删除目录。

执行对于目录的操作的用户,需要重新赋予RECOVERY_CATALOG_OWNER权限。

另外,Oracle8i中为了制成目录,而附属为SQL的脚本catrman.sql。必须要用到RMAN的CREATE CATALOG命令。

 

以下的命令是执行目录更新的例子。

 

RMAN> UPGRADE CATALOG
RMAN-06435: recovery catalog owner is rman
RMAN-06442: enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> UPGRADE CATALOG
RMAN-06408: recovery catalog upgraded to version 08.01.05

 

媒介管理软件相关功能的扩展

2-1. 恢复目录以及媒介管理软件目录的项目检查

使用RMAN中的tape装置,执行备份时,oracle并不需要媒介管理软件。因为一般媒介管理软件中,是通过目录来管理tape的,作为系统来考虑时,就会存在RMAN以及媒介管理软件两个目录。

Oracle8 R8.0的RMAN需要用户管理两个目录的同步。比如重新使用(覆盖)媒介管理软件中的tape时,RMAN的目录中包含这些tape的备份信息就会被留下。因此,用户使用RMAN的目录管理命令,需要从恢复目录中删除这些备份信息。

Oracle8i的RMAN,就会执行恢复目录以及媒介管理软件中的目录同步命令。

 

 

追加命令 功能
ALLOCATE CHANNEL …

FOR MAINTENANCE ;

为了执行目录维护,需要分配将要使用的channel。以下展示的命令在执行前,需要用这个命令来分配channel。
CROSSCHECK … ;

CHANGE… CROSSCHECK ;

这些命令用于确认指定的备份是否存在(可以从RMAN进行访问)。执行命令时,RMAN无法访问备份的话,那个备份就会被标记以及EXPIRED ,被判断为无效。这个备份可以用LIST命令以及REPORT命令来确认。

以下是使用CROSSCHECK命令的脚本例。

 

ALLOCATE CHANNEL FOR MAINTENANCE TYPE ‘sbt_tape’;

CROSSCHECK BACKUPSET OF “/Oracle/dbs/tbs_8.f”;

没有连接恢复目录时,无法使用CROSSCHECK命令。这时,请使用CHANGE … CROSSCHECK命令。CHANGE … CROSSCHECK命令没有连接恢复目录时,也可以通过控制文件中的信息以及每集管理软件的信息来进行交互检测。

EXPIRED以及被标记的备份,可以在执行下次的CHANGE … CROSSCHECK命令时,访问备份时,会被标记为AVAILABLE。类似的功能就不会再用CROSSCHECK命令来执行。这是为了确认CROSSCHECK命令是否将状态变为AVAILABE。

CROSSCHECK命令,CHANGE … CROSSCHECK命令中,需要检测是否可以访问。因此,检测备份破损时,请使用VALIDATE命令。

2-2. 使用了媒介管理软件的固有的功能的数据库备份
(PROXY COPY)

使用Oracle8 R8.0的RMAN获得的备份,对数据文件的I/O,是由oracle的服务器进程来执行。因此媒介管理软件会执行oracle的服务器进程以及数据流的对策。

Oracle8i中媒介管理软件以及接口中,媒介管理软件自身对于数据库的文件追加了执行I/O的API。通过使用API,使用媒介管理软件的固有功能,就可以高速执行备份以及还原。Oracle中这个功能称为PROXY COPY。

 

通过这个API执行备份时,oracle将对象变更为备份模式之后,将包含在对象中的文件列表移交给媒介管理软件。媒介管理软件根据被移交的列表,使用各个公司自带的功能来启动数据库。

媒介管理软件对应PROXY COPY功能时,执行备份命令时,通过指定PRXY语句,可以执行使用了媒介管理软件固有功能的备份。

 

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP
  PROXY
  (TABLESPACE SYSTEM FORMAT ‘system_%s_%p’); 
} 

为了使用PROXY COPY功能需要对应PROXY COPY的媒介管理软件。不对应PROXY COPY时,指定PROXY语句时也可以通过API来执行备份。但是PROXY COPY功能无法使用时就会报错。

指定备份池

在大部分的内存管理软件中,可以在tape装置以及那个装置中使用的tape组作为“池”的概念来管理。换言之,各个tape装置以及tape装置都属于某个池,对其备份时进行选择时,可以选择想使用的tape装置以及tape。

Oracle8 R8.0中,因为可以在备份时选择池,媒介管理软件所参考的环境变量中,采取重新指定池的方法以及指定RMAN的ALLOCATE CHANNEL命令PARMS语句中的环境变量的方法。

Oracle8i中通过RMAN的BACKUP命令,可以直接指定池。通过BACKUP命令的POOL语句可以指定对应数字进行设定。

 

RUN{
ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’;
BACKUP 
  POOL 2
  (TABLESPACE SYSTEM FORMAT ‘system_%S_%P’);
} 


能否使用POOL语句来指定依赖于媒介管理软件。详细内容请参考媒介管理软件手册。

3. Oracle Prallel Server(OPS)相关的功能扩展

 

备份时的磁盘亲和性(Disk Affinity)以及分散负荷

MPP结构的OPS中,因为会连接到各个设备的各个磁盘中,访问速度并不是相等的。因此,为了使得各个设备中的磁盘I/O最优化,需要尽可能对连接到本地的设备执行I/O。

Oracle8 R8.0的OPS环境中,使用RMAN执行备份时,为了执行磁盘I/O的最优化,重要的是,在用户端中对各个文件分割合适的节点的channel(执行备份的服务器进程)。以下是RMAN的命令例,在由4个节点构成的OPS环境中,对各节点连接到本地设备上的数据文件进行备份。

 

 

 

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP 
      FILESPERSET 1 FORMAT ‘DBFILE_%s_%p’
      (DATAFILE ‘/dev/rdsk/lv1’ CHANNEL ch1)
      (DATAFILE ‘/dev/rdsk/lv2’ CHANNEL ch2)
      (DATAFILE ‘/dev/rdsk/lv3’ CHANNEL ch3)
      (DATAFILE ‘/dev/rdsk/lv4’ CHANNEL ch4);
}

Oracle8i的RMAN中,在执行备份时,以设备亲和性为基础,对备份对象数据文件进行集群化(group化)这时,备份对象的数据文件所属的节点中如果分配到channel的话,通过channel就可以进行备份。节点中没有分配到channel时,节点用于的数据文件,特别是不需要考虑到亲和性。对于可以使用的channel依次分配,执行备份。
以下的例子是设备亲和性有效的环境中,对之前页面的列表执行相同的备份的命令的例子。(数据文件 /dev/rdsk/lv1 ~ lv4包含于表领域tbs1中的情况)

 

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP
      FORMAT ‘tbs1_%s_%p’ 
      FILESPERSET 1
      (TABLESPACE tbs1);
}

另外在磁盘亲和性有效的环境中,指定包含在备份set中的磁盘数的最小值,可以对磁盘进行数据分散。这个设定可以通过指定RMAN的BACKUP命令中的DISKRATIO参数来设定。(在DISKRATIO参数中指定整数)

RUN{
    ALLOCATE CHANNEL ch1 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE1’;
    ALLOCATE CHANNEL ch2 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE2’;
    ALLOCATE CHANNEL ch3 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE3’;
    ALLOCATE CHANNEL ch4 TYPE ‘SBT_TAPE’ CONNECT ‘sys/passwd@NODE4’;
    BACKUP 
      FORMAT ‘%d_%s_%p’
      DISKRATIO 3
      (DATABASE);
}


在DISKRATIO中指定整数n时,各个备份set中所包含的数据文件的总计块数为了不超过n分之一,对各个数执行数据文件分割。
比如DISKRATIO指定为3时,就会如此调整备份set:对一个备份set,从一个备份set从一个磁盘读入的块数,就会小于整体块数的三分之一。(被分散到4个磁盘中)。
这是考虑到磁盘的亲和性的备份,支持MPP平台固有功能。您如果想查看自己使用的平台是否支持磁盘亲和性的话,请参考各个平台的管理者手册。

Oracle8i的OPS中不会考虑磁盘亲和性,日志归档以及复制数据文件的备份时,不会考虑到Load Balancing。

RBO基于规则的优化器access paths优先级

RBO基于规则的优化器access paths优先级:

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan

 

注意在不违反如上优先级的前提下,若有2个优化级一样的索引可用,则RBO会选择晚建的那个索引, 解决方法是重建你想要让RBO使用的那个索引,或者使用CBO…….. 😆

 

 

在Oracle 10g以后虽然RBO (optimizer_mode=RULE)仍可用,但是不受官方的支持认可。

Histograms: An Overview

NOTE: This article was originally created for version 7.3. Conceptually the ideas presented are
the same as in later versions but implementation details may have changed or have
been enhanced.

Histograms
==========

For uniformly distributed data, the cost-based approach makes fairly accurate
guesses at the cost of executing a particular statement. However, when the
data is not uniformly distributed, the optimizer cannot accurately estimate
the selectivity of a query. Beginning in release 7.3, for columns which do not
have uniform data distribution, Oracle will allow you to store histograms
describing the data distribution of a particular column.

When to Use Histograms
———————-

Histograms are stored in the dictionary and computed by using the DBMS_STATS
command on a particular column. Therefore, there is a maintenance and space
cost for using histograms. You should only compute histograms for columns
which you know have highly-skewed data distribution.

When to Not Use Histograms
————————–

Also, be aware that histograms, as well as all optimizer statistics, are
static. If the data distribution of a column changes frequently, it is
necessary to recompute the histogram for a given column. Histograms are not
useful for columns with the following characteristics:

  o all predicates on the column use bind variables
  o the column data is uniformly distributed
  o the column is not used in WHERE clauses of queries
  o the column is unique and is used only with equality predicates

How to Use Histograms
———————

Create histograms on columns that are frequently used in WHERE clauses of
queries and have a highly-skewed data distribution. You create a histogram
by using the ANALYZE and DBMS_STATS TABLE command for later versions. For example, if you want to create a 10-bucket histogram on the SAL column of the EMP table, issue the following
statement:

    DBMS_STATS.GATHER_TABLE_STATS (NULL,’EMP’, method_opt => ‘FOR COLUMNS sal SIZE 10’);
    ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

The SIZE keyword states the maximum number of buckets for the histogram.
You would create a histogram on the SAL column if there were an unusual
number of employees with the same salary and few employees with other
salaries.

The ‘FOR’ clause can be used with either COMPUTE STATISTICS or ESTIMATE
STATISTICS.  The following clauses can be used with the ANALYZE TABLE command:

    FOR TABLE  
        collect table statistics for the table
    FOR ALL COLUMNS
        collect column statistics for all columns in the table
    FOR ALL INDEXED COLUMNMS   
        collect column statistics for all indexed columns in the table
    FOR COLUMNS
        collect column statistics for the specified columns
    FOR ALL INDEXES
        all indexes associated with the table will be analyzed
    SIZE
        specifies the maximum number of partitions (buckets) in the
        histogram.
         Default value:    75
         Range of values:  1 – 254

 For DBMS_STATS, syntax is the following:

method_opt

Accepts:

      FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
    *

      FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]…]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

– integer : Number of histogram buckets. Must be in the range [1,254].
– REPEAT : Collects histograms only on the columns that already have histograms.
– AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
– SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

For 11.2, there is following additional parameter:

– extension : can be either a column group in the format of (column_name, Colume_name [, …]) or an expression

In 10g and 11g, the default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Choosing the Number of Buckets for a Histogram
———————————————-

The default number of buckets is 75. 10g onwards, the default value is FOR ALL COLUMNS SIZE AUTO.  This value provides an appropriate level of detail for most data distributions. However, since the number of buckets in the histogram, the sampling rate, and the data distribution all affect
the usefulness of a histogram, you may need to experiment with different
numbers of buckets to obtain the best results.

If the number of frequently occurring distinct values in a column is relatively
small, then it is useful to set the number of buckets to be greater than the
number of frequently occurring distinct values.

Viewing Histograms
——————

You can find information about existing histograms in the database through the
following data dictionary views:

    USER_TAB_HISTOGRAMS, ALL_TAB_HISTOGRAMS, and DBA_TAB_HISTOGRAMS.
    USER_PART_HISTOGRAMS, ALL_PART_HISTOGRAMS, and DBA_PART_HISTOGRAMS.
    USER_SUBPART_HISTOGRAMS, ALL_SUBPART_HISTOGRAMS, and DBA_SUBPART_HISTOGRAMS.

The number of buckets in each column’s histogram is found in these dictionary views :

 o USER_TAB_COL_STATISTICS, ALL_TAB_COL_STATISTICS,DBA_TAB_COL_STATISTICS
   (extracted from USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS)
 o USER_PART_COL_STATISTICS,ALL_PART_COL_STATISTICS, DBA_PART_COL_STATISTICS,
 o USER_SUBPART_COL_STATISTICS, ALL_SUBPART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS

These views have the same definition.

DBA_TAB_HISTOGRAMS

This view lists histograms on columns of all tables.

Column name           Represents This
———————————————————
OWNER                 Owner of table
TABLE_NAME            Table name
COLUMN_NAME           Column name
ENDPOINT_NUMBER       Endpoint number
ENDPOINT_VALUE        Normalized endpoint values for this bucket

DBA_TAB_COLUMNS

This view contains information which describes columns of all tables.
(NOTE: Views and clusters, although included in this view are not relevant
 to histograms.)

Column Name           Represents This
———————————————————-
OWNER                 Owner of table
TABLE_NAME            Table name
COLUMN_NAME           Column name
DATA_TYPE             Datatype of the column
DATA_LENGTH           Length of the column
DATA_PRECISION        Precision for NUMBER or FLOAT datatypes
DATA_SCALE            Digits to right of decimal
NULLABLE              NULL allowable?
COLUMN_ID             Sequence no. of column
DEFAULT_LENGTH        Length of default value
DATA_DEFAULT          Default value
NUM_DISTINCT          Number of distinct values for the column
LOW_VALUE             Smallest value for the column, expressed in hex
                        for the internal representation ofthe first 32
                        bytes of the value
HIGH_VALUE            Highest value for the column, expressed in hex for
                        the internal representation of the first 32 bytes
                        of the value
DENSITY               Density of the column (a measure of how distinct
                        the values are)
NUM_NULLS             The number of columns with null value
NUM_BUCKETS           The number of buckets in the histogram
LAST_ANALYZED         The date that analyze was last run on the table
SAMPLE_SIZE           The amount of data sampled

The column LAST_ANALYZED is useful in determining the last time
statistics, with or without histograms, were computed.  This is
often important to assess the reason for cost-based optimizer’s
choices of execution paths.  All tables involved in a query must be
regularly analyzed as data changes.

沪ICP备14014813号

沪公网安备 31010802001379号