You can dump 10053 trace for any SQL in the shared pool using DBMS_SQLDIAG.DUMP_TRACE package.  For example to get the 10053 trace for the SQL : 5um3p9r9qbpfj is:

exec dbms_sqldiag.dump_trace(p_sql_id=>'5um3p9r9qbpfj',p_child_number=>0,p_component=>'Compiler', p_file_id=>'MY_TRACE_DUMP');

There can be multiple levels of tracing, where Compiler is the highest level.  Below are the list of levels in the order.

SQL> oradebug doc component SQL_Compiler

  SQL_Compiler                 SQL Compiler

    SQL_Parser                 SQL Parser (qcs)

    SQL_Semantic               SQL Semantic Analysis (kkm)

    SQL_Optimizer              SQL Optimizer

      SQL_Transform            SQL Transformation (kkq, vop, nso)

        SQL_MVRW               SQL Materialized View Rewrite

        SQL_VMerge             SQL View Merging (kkqvm)

        SQL_Virtual            SQL Virtual Column (qksvc, kkfi)

      SQL_APA                  SQL Access Path Analysis (apa)

      SQL_Costing              SQL Cost-based Analysis (kko, kke)

        SQL_Parallel_Optimization SQL Parallel Optimization (kkopq)

    SQL_Code_Generator         SQL Code Generator (qka, qkn, qke, kkfd, qkx)

      SQL_Parallel_Compilation SQL Parallel Compilation (kkfd)

      SQL_Expression_Analysis  SQL Expression Analysis (qke)

      SQL_Plan_Management      SQL Plan Managment (kkopm)

See a blog:


Oracle vault components

    • Realms: group of related schemas, objects, roles combined together to apply a single security rule
    • Command Rule: Rule defining with vault what kind of DML and DDL can be executed for a defined user
    • Factors: a named attribute such as user location, Server IP address or a session user vault can secure.
    • Identity: is a value for factor. A factor can have multiple identities
    • Rule sets: is a collection of one or more rules


  • To place a table inmemory column store



Notes on In-memory database

  • Column store size : INMEMORY_SIZE, default value is 0. It not managed by Automatic Memory Management and should have a 100MB minimum.  Being static part of SGA, database restart is required to change the INMEMORY_SIZE parameter.
  • V$INMEMORY_AREA displays the memory allocation 1MB pool and 64KB pool
  • There are two pools in in-memory
    • 64KB – stores the metadata about the column store
    • 1MB pools store the actual column formatted data
  • Views supporting inmemory
    • v$im_segments
    • v$im_segments_detail
    • v$im_user_segments
    • v$im_tbs_ext_map
    • v$im_seg_ext_map
    • v$im_header
    • v$im_col_cu
    • v$im_smu_head
    • v$im_smu_chunk
    • v$im_column_level
    • dba_tables – columns
      • inmemory_compression
      • inmemory_priority
      • inmemory_distribute
    • Worker processes populate the inmemory column store and named as W00n. For example ora_w001_orcl is the first inmemory worker process populating the inmemory data.
    • Number of worker processes are defined with the parameter INMEMORY_MAX_POPULATE_SERVERS and the default value is MIN of below
      • Half of the CPU cores

Oracle white paper on in-memory

Craig about in-memory




DB_BIG_TABLE_CACHE_PERCENT_TARGET (  will work only when PARALLEL_DEGREE_POLICY is set to AUTO or ADAPTIVE.    It is the memory allocated in buffer cache for “Automatic Big Table Caching” under the “In Memory Parallel Execution”.

Associated views: V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS

Oracle direct read decision :

X$KCBOQH.NUM_BUF keeps the number of buffers of an extent cached in the buffer cache.

_SMALL_TABLE_THRESHOLD defines the threshold size of a table to branded as a small table so that a FTS will place the table blocks in the buffer cache.





  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: