How to lock/unlock statistics on a table?

               In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn't change.


-How to find table where statistics are locked.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; 


– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('<schema>', '<Table>');
-- To gather statistics on a table
SQL> exec dbms_stats.gather_index_stats('<schema>', '<Table>');

--To Lock statistics 

exec dbms_stats.lock_table_stats('<schema>', '<Table>');


– shows when stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = '<table_name>' and owner = '<Schema>';

CMAN [ Oracle Connection Manager ]

              
                     Oracle Connection Manager helps a client process with directions to a network address. The Oracle Connection Manager behaves like a listening router where a connection request is sent to the next node on the path to its destination; this next node could be the target server or a next step. Additionally Oracle Connection Manager can provide connection concentration, access control and support for multiple protocols simultaneously. Connection Manager can help to integrate multiple networks into a larger single network. Connection Manager can also provide acceptance and rejection filtering for different nodes and effectively act as a filtering firewall process, ie. network access control. Note that Oracle Connection Manager is only available with MTS and is is best utilized in concert with Oracle Names servers.
Oracle Connection Manager has two processes.
  1. The Connection Manager Administrative process (CMADMIN) is an Oracle Connection Manager administrative process. The utility cmctl will access CMADMIN through CMGW
  2. The Gateway process (CMGW) listens for client connection requests; defaulted on port 1630 using TCP/IP. Connection requests are passed to a listener and data is relayed between client and server processes.

Features of Oracle Connection Manager

Features of the Oracle Connection Manager are two-fold. The first is connection concentration or funneling (multiplexing) of many client connections into a single centralised connection. The second is that of support of multiple protocols on a single network.

Connection Concentration

MTS can be utilised to reduce the total number of concurrent connections between client and server machines, therebye conserving valuable server resources. Oracle Connection Manager will allow multiplexing (funneling) of multiple client network sessions to a single TCP/IP connection on an MTS target server. Connection concentration allows for multiple connections between two processes. This allows the server to service more connection requests since multiple client connection requests can be serviced by a single dispatcher process. Therefore use of multiple Connection Managers can increase the number of concurrent client connections serviced astronomically. In order to enable Oracle Connection Manager connection concentration the mts_dispatchers parameter must be set appropriately in the parameters file as shown below.
mts_dispatchers = "(PROTOCOL=TCP) (MULTIPLEX=ON))"

Multiple Protocol Support

Multiple protocol support allows for communication across networks using something introduced before the introduction of Net8 called MultiProtocol Interchange. MultiProtocol Interchange allows communcation between incompatible network protocols. Multiple protocol support through Oracle Connection Manager must be applied to tnsnames.ora files on client machines. Note the multiple protocol example tnsnames.ora file shown below.
TEST =
  (DESCRIPTION =
    (SOURCE_ROUTE = YES)
    (ADDRESS = (PROTOCOL = SPX) (SERVICE = cman))
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = TEST) (SERVER = SHARED))
  )
Oracle Connection Concentration using Oracle Connection Manager and Multi-Protocol Support

Configuration

Oracle Connection Manager Configuration

Net8 uses listener.ora and tnsnames.ora files. Oracle Names server uses a file called oname.ora. Oracle Connection Manager uses a configuration file called cman.ora. There are many configurable parameters for use with Oracle Connection Manager.

cman = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1630) (QUEUESIZE = 64)))
cman_admin = (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1830))
cman_profile = 
  (PARAMETER_LIST = 
    (MAXIMUM_RELAYS = 1 to 2048)
    (USE_ASYNC_CALL = YES)
  )
cman_rules = 
  (RULE_LIST = 
    (RULE = 
      (SRC = <hostname>)
      (DST = <hostname> | x)
      (SRV = <SID> | x)
      (ACT = accept | reject)
    )
  )

The example below shows configuration of the Oracle Connection Manager file cman.ora on the server where the Oracle Connection Manager will listen on multiple protocols for differnt client connection requests using those different protocols. The Oracle Connection Manager will then connect to the database server listener process for the client.

cman=
  (DESCRIPTION=
    (SOURCE_ROUTE=YES)
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=SPX)(SERVICE=cman))
      (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))
    )
    (CONNECT_DATA=(SERVICE_NAME=xyz.com))
  )
Note the default ports for non-administration connections (CMGW) as port 1630 and adminsitrative (CMADMIN) connections as port 1830. The cman_profile parameter contains Oracle Connection Manager parameter settings. The cman_rules parameter sets filtering rules for network access control across the network. Filtering rules will apply access rules and rights to connection requests between different nodes in a network. Note the optional values in the DST and SRV attributes of the cman_rules parameter settings shown above; x implies any host or database SID can be connected to. The cman_profilePARAMETER_LIST attribute shown above has 16 possible parameter settings. The MAXIMUM_RELAYS attribute will limit the maximum concurrent connections to be supported by a single Oracle Connection Manager CMGW process.

Configuration Parameter File Parameters on the Server

Oracle Connection Manager can only be used with a server MTS configuration. Simply change the MTS_DISPATCHERS parameter in the parameters file as shown below.
mts_dispatchers = "(PROTOCOL=TCP) (MULTIPLEX=ON))"
mts_dispatchers = "(PROTOCOL=SPX) (MULTIPLEX=ON))"

Configuring the tnsnames.ora File on the Client

Client configuration requires two tnsnames.ora configuration file entries for both the listener on the server and the Oracle Connection Manager on the server. In the example tnsnames.ora file below the highlighted section is the address required for the Oracle Connection Manager. Setting SOURCE_ROUTE=YES provides a source route of addresses through the Oracle Connection Manager to the listener. Note that the Oracle Connection Manager will make the connection to the listener on the server. Also note that when SOURCE_ROUTE=YES is set then client load balancing and failover options cannot be used.
<SID>=
  (DESCRIPTION=
    (SOURCE_ROUTE=YES)
    (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1630))
    (ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=xyz.com))
  )

The CMCTL Utility

CMCTL is the Oracle Connection Manager Control utility. CMCTL is executed as CMCTL <command> [ cman | cm ]cman executes both CMGW and CMADMIN, cm executes just CMGW. Note that where bothcman and cm are listed as optional, the default is cman.
  • Operational Commands.
    • START [ cman | cm ] - starts the Oracle Connection Manager.
    • STOP[NOW] - stop the Oracle Connection Manager.
    • SHUTDOWN [ normal | abort ] [ cman ] - Use SHUTDOWN not STOP and STOP NOW since SHUTDOWN executes all the functionality of both.
    • ACCEPT_CONNECTIONS [ ON | OFF ] - OFF will disable Oracle Connection Manager from accepting new connections.
    • CLOSE_RELAY [ n ] - close a relay (connection).
  • SET <commmand>
    • AUTHENTICATION_LEVEL [ 0 | 1 ] - security level setting. No authentication (0) or enforces use of SNS (Secure Network Service) for client connection request authentication to Oracle Connection Manager.
    • DISPLAYMODE [ COMPAT | VERBOSE ].
    • LOG_LEVEL [ 0 - 4] - no logging (0), basic (1), rule_list matching (2), relay blocking (3) and relay I/O counts (4).
    • RELAY_STATISTICS [ ON | OFF ] - bytes and packets in and out.
  • Information & Utility Commands.
    • STATUS [ cman | cm ].
    • STATS [ cman | cm ] - displays Oracle Connection Manager statistics including relays, delays and connection refusals.
    • SHOW <command>.
      • ADDRESS - displays current Oracle Connection Manager listening addresses.
      • ALL - executes SHOW ADDRESS, SHOW PROFILE and SHOW RULES.
      • DISPLAYMODE [ COMPAT | VERBOSE ].
      • PROFILE - shows parameter settings contained in the cman_profile parameters in the cman.ora file.
      • RELAY [ argument ] - shows relay status. A relay is the same as a connection. Shows active relays or specific relays of bytes, packets, probes plus client and server end-point addresses.
      • RULES - displays all Net8 access rules currently implemented.
    • EXIT, QUIT, HELP and VERSION - self explanatory.

Oracle Connection Manager Configuration Parameters

Oracle Connection Manager Configuration parameters are stored in the $ORACLE_HOME/network/admin/cman.ora file.

CMAN

CMGW listening addresses.
cman = 
  (ADDRESS_LIST = 
    (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1630))
    (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1631))
    (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1632))
  )
)

CMAN_ADMIN

CMADMIN listening addresses.
cman_admin = 
  (ADDRESS_LIST = 
    (ADDRESS = (PROTOCOL = TCP) (HOST = <hostname>) (PORT = 1830))
  )
)

CMAN_PROFILE

Oracle Connection Manager profiling.
cman_profile = 
  (PARAMETER_LIST = 
    ( ... )
    ( ... )
  )
  • ANSWER_TIMEOUT = [0-n] - timeout for connection request.
  • AUTHENTICATION_LEVEL = [0|1] - 0 lets everything through and 1 allows only SSN (Secure Network Services) connections.
  • LOG_LEVEL = [0-4] - 0 is none, 1 is basic, 2 is rules matching, 3 is relay blocking and 4 is relay I/O.
  • MAX_FREELIST_BUFFERS = [...] - maximum buffers kept by TNS freelist for later re-use after close of relay.
  • MAXIMUM_CONNECT_DATA = [...] - incoming connection request connect data string length.
  • MAXIMUM_RELAYS = [ON|OFF] - maximum concurrent connections.
  • RELAY_STATISTICS = [ON|OFF] - records statistics of bytes and packets.
  • REMOTE_ADMIN = [ON|OFF] - remote access to Oracle Connection Manager.
  • SHOW_TNS_INFO = [ON|OFF] - include TNS details in cman_pid.log.
  • TRACING = [ON|OFF].
  • TRACE_[DIRECTORY | FILELEN | FILENO | TIMESTAMP] = [] - $ORACLE_HOME/network/trace/cman_pid.trc with multiple files plus event level time-stamping.
  • USE_ASYNC_CALL = [ON|OFF].

CMAN_RULES

Network access control filtering for Oracle Connection Manager. No rules present implies all connections are accepted. The x character can be used as a wildcard character to replace any value or an IP-Address as(x.123.x.x).
cman_rules = 
  (RULE_LIST = 
    (RULE = 
      (SRC = <hostname>)
      (DST = <hostname> | x)
      (SRV = <SID> | x)
      (ACT = accept | reject)
    )
    (RULE = 
      (SRC = <hostname>)
      (DST = <hostname> | x)
      (SRV = <SID> | x)
      (ACT = accept | reject)
    )
  )
  • SRC - connection request source host or IP-Address. Machine hosting client to which rule applies.
  • DST - connection request target host or IP-Address. Machine hosting database to which rule applies.
  • SRV - database server <SID>. Oracle database service on machine hosting Oracle database to which rule applies.
  • ACT - accept or reject connection requests as described in SRCDST and SRV.

DBA Views

DBA Views

Storage Information
DBA_EXTENTS, DBA_FREE_SPACE, DBA_OBJECTS,
DBA_OBJECT_SIZE, DBA_SEGMENTS, DBA_TABLESPACES,
DBA_ROLLBACK_SEGS, DBA_UNDO_EXTENTS

Operating System
DBA_DATA_FILES, DBA_EXP_FILES, DBA_TEMP_FILES

Privileges
DBA_COL_PRIVS, DBA_PROFILES, DBA_ROLES,
DBA_ROLE_PRIVS, DBA_SYS_PRIVS, DBA_TAB_PRIVS,
DBA_UPDATABLE_COLUMNS

Indexes
DBA_INDEXES, DBA_IND_COLUMNS,
DBA_IND_EXPRESSIONS, DBA_IND_PARTITIONS

Tables/Views
DBA_TABLES, DBA_TAB_COLUMNS, DBA_TAB_PARTITIONS,
DBA_TAB_COMMENTS,
DBA_UNUSED_COL_TABS, DBA_VIEWS

Constraints
DBA_CONSTRAINTS, DBA_CONS_COLUMNS

Triggers
DBA_TRIGGERS, DBA_TRIGGER_COLS,
DBA_INTERNAL_TRIGGERS

Materialized Views
DBA_MVIEW_AGGREGATES, DBA_MVIEW_ANALYSIS,
DBA_MVIEW_DETAIL_RELATIONS, DBA_MVIEW_JOINS,
DBA_MVIEW_KEYS

Partitions
DBA_PART_COL_STATISTICS, DBA_PART_HISTOGRAMS,
DBA_PART_INDEXES, DBA_PART_KEY_COLUMNS,
DBA_PART_LOBS, DBA_PART_TABLES,
DBA_IND_SUBPARTITIONS, DBA_LOB_PARTITIONS,
DBA_LOB_SUBPARTITIONS

Objects, Methods and Types
DBA_OBJECT_TABLES, DBA_METHOD_PARAMS,
DBA_METHOD_RESULTS, DBA_TYPES, DBA_TYPE_ATTRS,
DBA_TYPE_METHODS, DBA_DIMENSIONS, DBA_LOBS

Operators
DBA_OPANCILLARY, DBA_OPARGUMENTS,
DBA_OPBINDINGS, DBA_OPERATORS

Summaries
DBA_SUMMARIES, DBA_SUMMARY_AGGREGATES,
DBA_SUMMARY_DETAIL_TABLES, DBA_SUMMARY_JOINS,
DBA_SUMMARY_KEYS

Miscellaneous
DBA_DB_LINKS, DBA_SOURCE, DBA_SEQUENCES,
DBA_SYNONYMS, DBA_USERS, DBA_OUTLINES,
DBA_JOBS, DBA_JOBS_RUNNING, DBA_LIBRARIES,
DBA_PENDING_TRANSACTIONS, DBA_RULESETS,
DBA_OUTLINE_HINTS, DBA_POLICIES,
DBA_SUBPART_KEY_COLUMNS, DBA_TS_QUOTAS,
DBA_JAVA_POLICY, USER_JAVA_POLICY

Dynamic Performance Views

Instance Level Tuning

v$GLOBAL_TRANSACTION, v$OBJECT_DEPENDENCY,
 v$SHARED_POOL_RESERVED, v$SORT_SEGMENT,
v$SYSTEM_CURSOR_CACHE, v$SORT_USAGE, v$STATNAME,
v$SYSSTAT, v$SYSTEM_EVENT, v$TRANSACTION, v$LATCH,
v$LIBRARYCACHE, v$ROLLSTAT, v$ROWCACHE,
vRSGASTAT, v$SQLAREA, v$SQLTEXT, v$WAITSTAT

Recovery Based Views
v$ARCHIVE, v$ARCHIVE_DEST, v$BACKUP_CORRUPTION,
v$BACKUP_DEVICE, v$BACKUP_REDOLOG,
v$DELETED_OBJECT, v$RECOVERY_LOG, v$RECOVER_FILE,
v$ARCHIVED_LOG, v$BACKUP, v$BACKUP_DATAFILE,
v$BACKUP_PIECE, v$BACKUP_SET,
v$RECOVERY_FILE_STATUS, v$RECOVERY_STATUS,
v$DATABASE_BLOCK_CORRUPTION,
v$DATABASE_INCARNATION

Cache Views
v$CACHE, 
v$LIBRARYCACHE, 
v$SUBCACHE,
v$DB_OBJECT_CACHE, 
v$ROWCACHE

Control File Views
v$CONTROLFILE, 
v$CONTROLFILE_RECORD_SELECTION

Cursor and SQL Views
v$SYSTEM_CURSOR_CACHE, v$OPEN_CURSOR, v$SQLAREA,
v$SQL, v$SQLTEXT, v$SQLTEXT_WITH_NEWLINES,
v$SQL_CURSOR, v$SQL_BIND_METADATA,
v$SQL_SHARED_MEMORY, v$SQL_BIND_DATA,
v$SQL_WORKAREA, v$SQL_WORKAREA_ACTIVE

Security Views
v$ENABLEDPRIVS, v$PWFILE_USERS

Session Views
v$ACCESS, v$MYSTAT, v$PROCESS, v$SESSION ,
v$SESSION_CONNECT_INFO, v$SESSION_CURSOR_CACHE,
v$SESSION_EVENT, v$SESSION_LONGOPS,
v$SESSION_OBJECT_CACHE, v$SESSION_WAIT, v$SESSTAT
(needs v$statname, v$session join), v$SESS_IO

Latch and Lock Views
v$BUFFER_POOL, v$CACHE_LOCK, v$CLASS_PING,
v$DLM_CONVERT_LOCAL, v$DLM_CONVERT_REMOTE,
v$DLM_LATCH, v$DLM_MISC, v$ENQUEUE_LOCK,
v$EVENT_NAME, v$FALSE_PING, v$FILE_PING, v$LATCH,
v$LATCHHOLDER, v$LATCHNAME, v$LATCH_CHILDREN,
v$LATCH_MISSES, v$LATCH_PARENT, v$LOCK,
v$LOCK_ACTIVITY, v$LOCK_ELEMENT, v$LOCKED_OBJECT,
v$LOCKS_WITH_COLLISIONS, v$PING, v$RESOURCE,
v$RESOURCE_LIMIT, v$TRANSACTION_ENQUEUE, v$LOCK

Instance Views
v$ACTIVE_INSTANCES, v$BGPROCESS, v$BH,
v$COMPATIBILITY, v$COMPATSEG, v$COPY_CORRUPTION,
v$DATABASE, v$DATAFILE, v$DATAFILE_COPY,
v$DATAFILE_HEADER, v$DBFILE, v$DBLINK, v$DB_PIPES,
v$INSTANCE, v$LICENSE, v$OFFLINE_RANGE, v$OPTION,
v$ROLLSTAT, v$SGA, v$SGA_STAT, v$TABLESPACE,
v$TRANSACTION, v$UNDOSTAT, v$VERSION

Fixed Views
v$FIXED_TABLE, v$FIXED_VIEW_DEFINITION,
v$INDEXED_FIXED_COLUMN

Miscellaneous Views
v$TIMER, v$TYPE_SIZE, v$SEQUENCES
MTS and Parallel Server Views
v$CIRCUIT, v$DISPATCHER, v$DISPATCHER_RATE, v$MTS,
v$QUEUE, v$REQDIST, v$SHARED_SERVER, v$THREAD

File Mapping
v$MAP_LIBRARY, v$MAP_FILE, v$MAP_FILE_EXTENT,
v$MAP_ELEMENT, v$MAP_EXT_ELEMENT,
v$MAP_SUBELEMENT, v$MAP_COMP_LIST,
v$MAP_FILE_IO_STACK