Nov-14

25-Nov

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:

http://structureddata.org/2011/08/18/creating-optimizer-trace-files/

24-Nov

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

22-Nov

  • To place a table inmemory column store

SQL> ALTER TABLE test INMEMORY;

http://oracle-base.com/articles/12c/in-memory-column-store-12cr1.php

18-Nov

Notes on In-memory database 12.1.0.2

  • 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
      • PGA_AGGREGATE_TARGET /512MB

Oracle white paper on in-memory

http://www.oracle.com/technetwork/database/in-memory/overview/twp-oracle-database-in-memory-2245633.html

Craig about in-memory

http://shallahamer-orapub.blogspot.in/2014/11/off-may-not-be-totally-off-is-oracle-in.html

 

 

15-Nov

DB_BIG_TABLE_CACHE_PERCENT_TARGET (12.1.0.2)  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”.

https://docs.oracle.com/database/121/REFRN/refrn10340.htm#REFRN10340

Associated views: V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS

http://www.brentozar.com/archive/2014/07/whats-new-oracle-12-1-0-2/

Oracle direct read decision : http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: