Category: Oracle General

Automatic Big Table Caching in Oracle 12C

Oracle uses its buffer cache for caching or storing recently accessed table blocks. This helps Oracle to access the same blocks faster if they are required again. Since the buffer cache resides in memory and memory is expensive this cache size is always limited. Oracle uses a LRU (Least Recently Used) algorithm to keep the most recently access blocks in memory. It is a complex algorithm for buffer cache management, but lets simplify it by saying oracle keeps a simple queue which holds the most recently used data at the hot end. As we query more and more data, existing data in the queue is pushed backwards and finally moves out of the queue.

When you query some data which had already been moved out of cache, oracle finds this data is no longer in memory and it goes for physical reads (from disks) and this is an expensive and time consuming operation.

One of the big issue with this kind of a cache is, if you query a big table most of the queue can be replaced by the data from that table alone and all subsequent queries may go for physical reads. Oracle can’t allow this and such reads bypass the buffer cache to maintain its balance. Oracle usually avoids moving the blocks of huge tables into buffer cache by using direct path reads/writes which uses the PGA (Program Global Area) which is not a shared memory area. Since PGA is not shared among the users, such caching of data is not useful for other users of the database. And this may lead to extensive physical read operations.

Recent versions of oracle (12c) is trying to overcome this issue by identifying the big tables in the database and caching data from those tables effectively. This is done by reserving a part of buffer cache for storing big tables.

Let’s test this feature by creating couple of big tables (> 1 Million rows).

SQL> create table my_test_tbl
 2 (Name varchar2(100),
 3 Emp_No integer,
 4 Dept_no integer);

Table created.

SQL> insert into my_test_tbl
 2 select 'John Doe', level, mod(level,10)
 3 from dual
 4 connect by level <= 1000000; SQL> commit;

Commit complete.

SQL> select count(*) from my_test_tbl;


We need to analyze the table, so that the metadata will be updated.

SQL> analyze table my_test_tbl compute statistics;

We will have to set a parameter at system level so that a part of the buffer cache (40% in our case) will be allocated for caching big tables. First , lets check the size of buffer cache allocated for this database.

SQL> select component, current_size/power(1024,3) current_size_GB from v$memory_dynamic_components
 2 where component = 'DEFAULT buffer cache'

 -------------------- ---------------
 DEFAULT buffer cache 1.546875

We have 1.5GB of buffer cache, let’s allocate 40% of this for caching big tables.

SQL> show parameter big_table

 ------------------------------------ ----------- ------------------------------
 db_big_table_cache_percent_target string 0

SQL> alter system set db_big_table_cache_percent_target = 40;

System altered.

SQL> show parameter big_table

 ------------------------------------ ----------- ------------------------------
 db_big_table_cache_percent_target string 40

Now, if we query the table it will be cached in to the big table cache.

SQL> select count(*) from my_test_tbl;


Please make note that we don’t have to restart the DB for modifying this parameter. Lets check the caching of the table and how much of it is cached,

SQL> select * from V$BT_SCAN_CACHE;

bt_pic_1Clearly shows 40% is reserved for big tables.


We have already queried the table once and oracle had identified that the table is indeed a big one. Now we have table in cache, we can check the size of the table on disk and how much of it is cached. Since the V$BT_SCAN_OBJ_TEMPS table contains the object id we can join it with DBA_OBJECTS and find out the table name. Once we have the table name DBA_TABLES will give us the size of the table on disk (blocks).

SQL> select object_name from dba_objects where object_id = 92742


SQL> column table_name format a20
 select table_name, blocks from dba_tables
 where table_name = 'MY_TEST_TBL';

 -------------------- ----------

The whole table is cached now and the temperature is set to 1000, if we use this table more and more the temperature of this table will go up making it hot. Below code snippet will query my_test_tbl 10,000 times and this will help us to increase the temperature of the table.

SQL> declare
 2 l_count integer;
 3 begin
 4 for i in 1..10000
 5 loop
 6 select count(*) into l_count from my_test_tbl;
 7 end loop;
 8 end;
 9 /

PL/SQL procedure successfully completed.

Check the V$BT_SCAN_OBJ_TEMPS table again to see if the temperature value has gone up.


We can see the temperature of the table has gone up because of the frequent querying, now we are creating another table and see if that is also getting cached. We will have 2 million records in this table.

SQL> create table my_test_tbl2
 2 as select * from MY_TEST_TBL;

SQL> insert into my_test_tbl2 select * from my_test_tbl;

1000000 rows created.

SQL> analyze table my_test_tbl2 compute statistics;

Table analyzed.

SQL> select table_name, blocks from dba_tables
 where table_name = 'MY_TEST_TBL2';

 -------------------- ----------
 MY_TEST_TBL2 6224

SQL> select count(*) from MY_TEST_TBL2;


We can see the new table in cache with initial temperature value of 1000.


Lets run the snippet again to query the new table, this time we will query only 100 times.

Query V$BT_SCAN_OBJ_TEMPS again to see the new temperature value of second table.


This temperature value helps oracle to prioritize tables in memory and identify which table is frequently queried. Based on this information oracle decides which table stays in memory and which table has to move out.

We have to remember currently we don’t have any option to move individual tables to the cache. It is completely automated and done by Oracle’s discretion. Our table may  or may not be  moved to this cache, but if you have big tables which you think may get benefited from this option then you can check this option.


Access Oracle from Python

If you are a data engineer you might have to pull data from various sources and relational databases (RDBMs) is one of them. Python has separate modules to connect to each of relational databases (Like Oracle, MySQL, PostgresSQL etc.). Today we will discuss how we can connect and query Oracle database from Python.

I hope you have working Python environment set up already. Here I’m using Python CLI with Python 2.7 for this tutorial.

1. Download and install oracle 12c database.

I have downloaded Oracle 12C database from OTN ( and installed in my CentOS 7 laptop. Since it is installed in my laptop, server name is localhost and the database name is pdborcl.localdomain

2. We need to install an python module called cx_Oracle which can help us to connect and extract data from the Oracle instance. It gave me couple of errors when I first tried to install on my machine.

[oracle@ora-c7 ~]$ sudo pip install cx_Oracle
 Collecting cx_Oracle
 Using cached cx_Oracle-5.2.1.tar.gz
 Complete output from command python egg_info:
 Traceback (most recent call last):
 File "", line 1, in 
 File "/tmp/pip-build-kkqi2h/cx-Oracle/", line 170, in 
 raise DistutilsSetupError("cannot locate an Oracle software " \
 distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

 Command "python egg_info" failed with error code 1 in /tmp/pip-build-kkqi2h/cx-Oracle/

This is because some packages are missing and we need to install them before installing cx_Oracle. Lets install setuptools and ez-setup.

[root@ora-c7 oracle]# pip install --upgrade setuptools
 Collecting setuptools
 Downloading setuptools-28.7.1-py2.py3-none-any.whl (472kB)
 100% |████████████████████████████████| 481kB 857kB/s
 Installing collected packages: setuptools
 Found existing installation: setuptools 20.10.1
 Uninstalling setuptools-20.10.1:
 Successfully uninstalled setuptools-20.10.1
 Successfully installed setuptools-28.7.1

[root@ora-c7 oracle]# pip install ez_setup
 Collecting ez_setup
 Downloading ez_setup-0.9.tar.gz
 Installing collected packages: ez-setup
 Running install for ez-setup ... done
 Successfully installed ez-setup-0.9

Now lets try to reinstall cx_Oracle.

[root@ora-c7 oracle]# pip install cx_Oracle
 Collecting cx_Oracle
 Using cached cx_Oracle-5.2.1.tar.gz
 Installing collected packages: cx-Oracle
 Running install for cx-Oracle ... done
 Successfully installed cx-Oracle-5.2.1

Lets try to run a sample program which connects and pull some data. We will connect and get the oracle database version and query a table. This is a simple program we can do much complex processing with Cx_Oracle, I will list them in another blog.

Oracle version I have is 12C and the table has 100 records in it.

[oracle@ora-c7 ~]$ python
 Python 2.7.5 (default, Sep 15 2016, 22:37:39)
 [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux2
 Type "help", "copyright", "credits" or "license" for more information.
 >>> import cx_Oracle
 >>> from pprint import pprint
 >>> db=cx_Oracle.connect('******','*****','localhost:1521/pdborcl.localdomain')
 >>> print db.version
 >>> cursor = db.cursor()
 >>> cursor.execute('select count(*) from my_tab');
 >>> pprint(cursor.fetchall())

OEM Express in 12C

If you are looking for some basic database monitoring, Oracle 12C provides Enterprise Manager Express for that purpose. It is a light weight version of EM with limited features. I use it to monitor the oracle 12c instance installed on my laptop which runs on CentOS 7.0.

For accessing the EM express we have to enable the HTTP and HTTPS port for the database . Here is how you can check the HTTP and HTTPS port settings for your database.

SQL> select dbms_xdb.getHttpPort() from dual;


SQL> select dbms_xdb_config.getHttpsPort() from dual;


If you want to manually set up the HTTPS or HTTP port you can do that by logging into the database and running the below commands.

SQL> exec dbms_xdb_config.sethttpsport(5501);


SQL> exec dbms_xdb_config.sethttpport(8080);

In order to use EM Express, a database user needs to have been granted the EM_EXPRESS_BASIC or EM_EXPRESS_ALL role.  The DBA role includes both the EM_EXPRESS_BASIC and the EM_EXPRESS_ALL roles.

Once this is done use the below URL to access EM Express.


Login page.


Home Page.


Performance Home.


It may not have all the OEM features but a nice tool indeed.

Oracle INMEMORY option for heap table

Oracle inmemory options allows users to store their data in memory. This is how we can enable inmemory option which provides faster access to table data.

SQL> show parameter inmemory_size

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 inmemory_size                        big integer 0

Here in my database inmemory  size is set to zero. We can change it by editing this parameter, restart the database after changing this parameter.

SQL> alter system set inmemory_size=100M scope=spfile;

System altered.

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup open;
 ORACLE instance started.

Total System Global Area 2432696320 bytes
 Fixed Size 2927288 bytes
 Variable Size 637535560 bytes
 Database Buffers 1660944384 bytes
 Redo Buffers 13848576 bytes
 In-Memory Area 117440512 bytes
 Database mounted.
 Database opened.
 SQL> alter pluggable database all open;

Pluggable database altered.

Check the value of inmemory_size again for the database.

SQL> show parameter inmemory_size

NAME                                  TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
 inmemory_size                        big integer 112M

Now we can see the parameter is set to 112MB and this can be used to store our table data. Now inmemory store doesn’t contain any table and this can be verified by the below command.

SQL> select count(*) from v$im_segments;


Now, pin the table into inmemory store.

SQL> alter table my_tab inmemory;

Table altered.

SQL> select count(*) from v$im_segments;


Table is stored in INMEMORY store only after we query it first time.

SQL> select count(*) from v$im_segments;


SQL> select count(*) from my_tab;


SQL> select count(*) from v$im_segments;


Execution plan clearly show if the table is accessed from memory. So, lets get the execution plan for the above count statement.

 SQL> set autotrace traceonly explain;
SQL> select count(*) from my_tab;

Execution Plan
Plan hash value: 2076930067

| Id  | Operation            | Name   | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT        |         |       1 |       1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |         |       1 |          |         |
|   2 |   TABLE ACCESS INMEMORY FULL| MY_TAB |       3 |       1   (0)| 00:00:01 |

Top-N queries in oracle 12c

Oracle 12c has a new feature to select top N queries easily. This can be done by using the FETCH clause and the syntax is as follows.

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Lets create a table with 100 rows and see how this command works. We will insert a sequence of 1-100 into the table from dual using the connect by clause.

SQL> create table my_tab as
  2  select level id from dual
  3  connect by level <= 100;

Table created.

SQL> select count(*) from my_tab;


Now lets check how this feature now. We will sort the ids in descending order and pick the top 5/6 values from the list.

SQL> select * from my_tab
  2  order by id desc
  3  fetch first 5 rows only;


SQL> select * from my_tab
  2  order by id desc
  3  fetch first 6 percent rows only;


6 rows selected.

SQL> select * from my_tab
  2  order by id desc
  3  offset 5 rows fetch next 5 percent rows only;


Oracle Identity Columns in 12c

Identity columns are a new feature in Oracle 12c. We need the CREATE SEQUENCE privilege for using this feature. The syntax is given below.


There are two options to create an identity column, As mentioned ALWAYS clause will always populate the column automatically. Any attempt to insert a value (Even NULL) will throw an error. BY DEFAULT /ON NULL  will populate only if we don’t specify the value or we try to insert a null value. Otherwise it will insert the value we specify in our insert statement.

Lets check how this feature works. At first we will create the table with ALWAYS option.

SQL> create table my_tab
 2 (
 3 name varchar2(50),
 5 );

Table created.

We have created the column id as IDENTITY column and which will be always populated by oracle. It wont let us insert a value/NULL in that column.

SQL> insert into my_tab (name) values ('John');

1 row created.

SQL> insert into my_tab values ('Mike', 2);
 insert into my_tab values ('Mike', 2)
 ERROR at line 1:
 ORA-32795: cannot insert into a generated always identity column

SQL> insert into my_tab values ('Steve', NULL);
 insert into my_tab values ('Steve', NULL)
 ERROR at line 1:
 ORA-32795: cannot insert into a generated always identity column

SQL> insert into my_tab (name) values ('Steve');

1 row created.

SQL> select * from my_tab;

NAME                                               ID
-------------------------------------------------- ----------
John                                               1
Steve                                              2

Now lets recreate the table with DEFAULT option.

SQL> create table my_tab
 2 (
 3 name varchar2(20),
 5 );

Table created.

SQL> insert into my_tab values ('Tom', 1);

1 row created.

SQL> insert into my_tab (name) values ('James');

1 row created.

SQL> insert into my_tab values ('Nick', NULL);

1 row created.

SQL> select * from my_tab;

NAME                 ID
-------------------- ----------
Tom                  1
James                1
Nick                 2

DEFAULT clause will insert the value if we don’t specify in the INSERT statement or we try to insert a NULL value.

DDL Logging in Oracle 12C

Oracle 12C logs the DDL statements if enabled. This can be done by enabling one parameter at session/instance level.

SQL> show parameter ddl_logging

———————————— ———– ——————————
enable_ddl_logging boolean FALSE

By default DDL logging is disabled.

SQL> alter session set enable_ddl_logging=TRUE;

Session altered.

Now, Lets run few DDL commands.

SQL> desc test_tbl;
Name Null? Type
—————————————– ——– —————————-

SQL> alter table test_tbl add dept_id number;

Table altered.

SQL> truncate table test_tbl;

Table truncated.

SQL> drop table test_tbl;

Table dropped.

SQL> alter session set enable_ddl_logging=FALSE;

Session altered.

Lets check the contents of DDL log, which is created in text as well as xml formats.

[oracle@ora-c7 log]$ pwd

[oracle@ora-c7 log]$ cat ddl_orcl.log
Thu Oct 20 21:52:32 2016
diag_adl:alter table test_tbl add dept_id number
diag_adl:truncate table test_tbl
diag_adl:drop table test_tbl
[oracle@ora-c7 log]$

This could be really useful if you want to monitor the DDL operations in your oracle database.