Category: Oracle Performace Tuning

Informatica Performance Issue (Oracle Implicit Case Conversion/NVARCHAR2 to VARCHAR2 )

This blog is about an Informatica performance issue we faced because of Oracle implicit type conversions. We were asked to troubleshoot a new Informaitca workflow which was running way longer than expected. A quick check on the V$SESSION table provided us the SQL ID and the query.

The query was a single table update and the where clause has all the columns that were the part of primary key. Still the query was not using the index and were going for a full table scan.

| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
|   0 | UPDATE STATEMENT      |             |       |       | 22850 (100)|          |       |       |        |      |            |
|   1 |  UPDATE               | ########### |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR      |             |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000    |     1 |   122 | 22850   (1)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |             |     1 |   122 | 22850   (1)| 00:00:01 |     1 |    16 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| ########### |     1 |   122 | 22850   (1)| 00:00:01 |     1 |    16 |  Q1,00 | PCWP |            |

Predicate Information (identified by operation id): 
5 - access(:Z>=:Z AND :Z<=:Z) filter(("#######"=:20 AND SYS_OP_C2C("#######")=:14 AND SYS_OP_C2C("#######")=:15 AND SYS_OP_C2C("########")=:16 AND SYS_OP_C2C("########")=:17 AND SYS_OP_C2C("########")=:18 AND SYS_OP_C2C("#######")=:19 AND SYS_OP_C2C("#########")=:21))

You can clearly see the above plan that the query is doing for an FTS. The predicate information shows SYS_OP_C2C function is used against each column and this prevents the query from using indexes. We then changed the source and target column type in the transformation to VARCHAR2 and the query started picking the right index.

Hope this helps.


Load Testing Oracle Stored Procedures Using Apache JMeter

Last week I was asked to load test few oracle stored procedures created by another team. These stored procedures are invoked by java services and the delivery team wanted to test them in the performance environment before pushing them to production. I was asked to use an Open Source tool for this testing since the project doesn’t have budget to buy a commercial tool.

Since Apache JMeter is a free tool, we decided to use it for our testing. The stored procedure takes one INTEGER value as input and returns an INTEGER value and a cursor as out put. The input value came from a CSV file which has 100K records that we mined from the database. I have detailed the steps we used to create a database test plan, our biggest challenge was to receive cursor output from the stored procedure. Details on how we achieved this can be found below. I have also explained how you can download the necessary tools and configure them.

**Please note that I have simulated below tests in my laptop since the actual code belongs to my client. I have no authority to publish the actual code here, but the configurations used in both cases are same. **

First, Lets download the latest version of Apache JMeter.


JMeter needs a JDBC driver to connect to oracle database, You can find the JDBC connect for Oracle at the Oracle Technology Network (OTN) website.


First, download JMeter and unzip to the folder where you want to save it. Go to /bin folder and you can find windows batch executable (jmeter.bat), double-click on it and it will start the JMeter GUI. If you are in Linux there is a script in the bin directory which invokes jmeter UI.

Now, download the JDBC driver from OTN and move the .jar file to /lib folder. It will be automatically detected and used by JMeter.

You can also download a lot of JMeter plugins, which will make your testing easy and will help you present your results in beautiful graphs.

Go to the JMeter UI.


I had created a stored procedures in my local oracle instance, which will be tested by JMeter. This stored procedure accepts number value as input and return a number and a cursor.


Go back to the JMeter and create a new thread group, which will monitor and control our testing.


Change the thread group name, you can give whatever name you want. Here I gave “OracleLoadTest_Sproc” for my thread group. Also we can set the number parallel threads and ramp up period in this screen.


After this we have to configure our database connection. JMeter can connect to Oracle database using a JDBC driver.

Right click on thread group and add a new JDBC connection configuration.


Now configure the connection settings, Here I have specified the server name as localhost since it is installed on my laptop. You can find your DB server details in TNSNAMES.ORA file or can get it from your DBA. You also have to set your database username and password for establishing the connection.


Our input data is coming from a CSV file and we usually test with 100K input fields. Here I have used a small file just to make sure the setup is working.


You have to configure the source file name and column names here. These column names are used later as the input parameter for the stored procedure.


Now we have to add a constant throughput time which helps us to main a constant throughput through out our test window.


Here I had set the timer as 60 threads/minute (1 Thread/Sec).


Let us add the component that calls stored procedure. Add a JDBC request to the thread group and include stored procedure details in it.


We have to set the query type as Callable Statement and use the CALL command to invoke the stored procedure. We can also pass the values from CSV file to the stored procedure. Just to make sure the type for cursor variable is-10 for the stored procedure.


Now add few listeners which monitor the execution and summarize the output. This will help us to present the final results more clearly to the client. You can add as many listeners as you want, We are adding just two here.




We are all set here, lets start the execution and collect the test results.


Once the test is complete, listeners provide test summary and other execution details.


We can see the stored procedure invocation details (input values, output values etc.) also from the result tree screen.



One important factor is JMeter is a Java application and could use a lot of heap memory. By default JMeter 3.1 uses 512 MB of heap memory. You will definitely need more data to run tests in GUI mode. Edit the jemeter.bat using any good text editor like UltraEdit and add more memory based on your hardware configuration.

You can run JMeter from the command prompt if you have a memory constraint and save the results to a jtl file.

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.

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 trace files location

I recently found  a way to locate the trace files easily. Just query the V$DIAG_INFO table, which has the location details of trace files.


NAME                                   VALUE
—————————— ———————————————————————-
Diag Enabled                    TRUE
ADR Base                          /orabase
ADR Home                       /orabase/diag/rdbms/v0010n1c/v0010n1c
Diag Trace                         /orabase/diag/rdbms/v0010n1c/v0010n1c/trace
Diag Alert                          /orabase/diag/rdbms/v0010n1c/v0010n1c/alert
Diag Incident                   /orabase/diag/rdbms/v0010n1c/v0010n1c/incident
Diag Cdump                     /orabase/diag/rdbms/v0010n1c/v0010n1c/cdump
Health Monitor               /orabase/diag/rdbms/v0010n1c/v0010n1c/hm
Default Trace File           /orabase/diag/rdbms/v0010n1c/v0010n1c/trace/v0010n1c_ora_23500.trc
Active Problem Count   0
Active Incident Count   0

11 rows selected.

Statistics gathering issue

This issue came up few weeks ago. I was asked to look into a time consuming batch job and the job was running for 5-6 hours every day and client wanted to reduce the execution time. This is what the job does every day

  1. Drop the indexes
  2. Load data into the tables
  3. Recreate the indexes
  4. Gather statistics

This looked fine to me at first. The stats procedure was running for a few hours and the client was getting worried on the execution time. Once I tuned the queries in loading procedure I decided to work on the stats gathering script.

Here is a copy of stats gathering script I got. I could see a lot of issues with the way it is written and executing.


Some of the issues are

  1. The procedure collects the stats of all the tables irrespective of whether it is really changed or not.
  2. It does the same with all indexes
  3. Here index stats are collected twice; once when the indexes are created and secondly by the procedure.
  4. Parallelism is not used.

Here is the modified script and know what it got completed a lot faster!!


Oracle Cardinality hint

Recently I came across a query where the cost was comparatively high. I was wondering what could be the issue for a simple query to have such a high cost. This query has been called many times during the execution and this has considerably increased over all execution time of the process. The query was placed inside a procedure which has a collection as input parameter. This collection is used to filter data from a table with 544K rows.

The business logic was such that we can pass only 5 members to the collection at a time. I was surprised to see oracle does a FTS on the table to fetch 5 rows from an indexed column. I thought of digging a little bit deeper and find the root cause.

Original Query




Here, oracle calculates the size of collection as 8168 but we have only 5 elements in the collection. I thought of adding the cardinality hint and provide the optimizer some more information.

Modified Query with cardinality hint.




We can clearly see that oracle is calculating the collection size properly and able to generate the best plan. we were able to get a great performance difference with this small changes.