May-15

30-May

Caching large table in 12c :  Oracle 12c gives an option to cache large table into memory and control then Direct Path Reads of FTS.  Any table sizing more than 2% of SGA is considered as a large table.  DB_BIG_TABLE_CACHE_PERCENT_TARGET can be specified parentage of buffer cache reserved for big table cache.  This feature will work parallel queries under PARALLEL_DEGREE_POLICY = AUTO or ADAPTIVE.   However non-parallel queries directly can use this feature.  In RAC Automatic Degree of Parallelism should be set to use this feature.   Oracle considers a table for caching based on the ‘temperature’ of the object or heat map.

https://docs.oracle.com/database/121/REFRN/GUID-122865EE-4589-434D-8DD5-4E201C6CC739.htm#REFRN10340

There are two dictionary view gives the run time statistics

V$BT_SCAN_CACHE

V$BT_SCAN_OBJ_TEMPS

https://dbwhisperer.wordpress.com/2014/10/06/poor-mans-in-memory-caching/

26-May

From 11gR2 onwards, you can apply a archive log deletion policy – delete after shipping.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;

22-May

How to calculate free memory from the unix server:

Get the free memory using sar –r 5 1

oracle@:/home/oracle> $ sar -r 5 1
SunOS sdcdbc01p 5.10 Generic_147147-26 sun4u    05/22/2015
01:28:29 freemem freeswap
01:28:34 8418516 207417699

Free memory 8418516 memory pages.  The memory page size can be identified using the pagesize command.

oracle@:/home/oracle> $ pagesize
8192

Typically x86 Sun Solaris page size is 4KB, SPARC  is 8KB and Linux system are configured with 4KB.

You may get calculate the free memory as

oracle@:/home/oracle> $ sar -r 5 1
SunOS sdcdbc01p 5.10 Generic_147147-26 sun4u    05/22/2015
01:28:29 freemem freeswap
01:28:34 8418516 207417699
oracle@:/home/oracle> $ echo '8418516*8/1024/1024'|bc
64
oracle@:/home/oracle> $ pagesize
8192

To disable FTS direct path read set the event –

SQL> alter system set events '10949 trace name context forever'

15-May

12c ASM SCRUB feature:  12c offers a new ASM feature SCRUB to check ASM level block corruptions are repair if the DG is configured with NORMAL or HIGH redundancy.  You can either specify a diskgroup or disk or file to check for logical corruptions.

SQL> ALTER DISKGROUP +DATA SCRUB POWER AUTO;
SQL> ALTER DISKGROUP +DATA SCRUB DISK data_01 REPAIR POWER HIGH;
SQL> ALTER DISKGROUP +DATA SCRUB FILE ‘+DATA/ORCL/DATA.653.684833’ POWER LOW;

Power can be

AUTO :  default value

LOW :

MAX :

HIGH:

http://allthingsoracle.com/oracle-database-12c-new-features-part-3/

Oracle patches can be queried online from sqlplus now..

http://www.pythian.com/blog/oracle-database-12c-patching-dbms_qopatch-opatch_xml_inv-and-datapatch/

08-May

Adaptive stats collection, Partition enhancement etc in 12c

http://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14asktom-2079690.html

Oracle Magazine online

http://www.oracle.com/technetwork/issue-archive/2014/14-jan/index.html

07-May

A good blog on statistics and histogrmas.

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

Oracle documents on Adaptive Optimization –

http://www.oracle.com/technetwork/database/bi-datawarehousing/dbbi-tech-info-optmztn-092214.html

To change the ‘staleness of statisitics’, you may change the parentage of  a table staleness

SQL> exec DBMS_STATS.SET_TABLE_PREF(USER,’<table_name>’,’STALE_PERCENT’,’10’);

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.pdf

05-May

HANGANALYZE can run from a session  as

SQL> ALTER SESSION SET EVENTS ‘immediate trace name HANGANALYZE level 4’;

There can be multiple levels

1 – Minimum information

2 –  Minimum information

3 – Dump only hung process state

4 – Dump only leaf nodes in the wait chain

5 – Dump all processes in the wait chain

10 – Dump all processes

To dump Deadlock situation, use the following  in the INIT or SP file.

SQL>
Events=”60 trace name hanganalyze level 5”

You can also use ORADEBUG to run the HANGANALYZE

SQL> oradebug setmypid
SQL> oradebug dump hanganalyze 3
SQL> oradebug tracefile_name

 

Advertisements
  1. Syed Rafi Ullah
    June 23, 2015 at 4:04 pm

    Excellent information

  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: