Frequent and Fast Refresh Materialized Views has a Big Problem!


While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem.

My customer have a few frequently fast refreshed materialized views. One of them is refreshed every 10 seconds (you might say that they can use an on commit refresh but since the base tables has too much DML, it decrease the performance and causes some locks while refreshing the materialized view (MV) ). First of all high amount of child cursors are belongs to refresh commands of MV.

one of them to insert new/changed rows and one for deleted or updated rows. you can also see a MERGE command too. both have more than 19.100 child cursors. whenever you wanted to learn why there is a new child cursor, V$SQL_SHARED_CURSOR is the view you should check:

Since I have already realized what is the problem, I just selected the necessary columns above. so reason of too much cursor is something called “Flashback cursor(1)”. Here is the “Flashback Queries” are involved. you might want to check Stew Ashton’s post:

Basically “Flashback Queries are Evil”. Flashback queries can not be shared so every time you run a flashback query ( select * from table1 as of ….) Oracle will create a new child cursor. Stew Ashton suggest a way to share it and it is beautiful (dbms_flashback.enable_at_system_change_number) which also I’ve just learned. if you are able to change your query you can use this but we don’t have a change to change the query that Oracle runs to refresh a materialized view.

All commands to refresh materialized views are flashback queries (instead of “as of timestamp” or “as of scn” they use “as of snapshot”) so they can not use a shared cursor. Now, please do the math, a materialized view which is refreshed every 10 seconds and almost every refresh runs both delete and insert commands against materialized view and non of those sql statements can share a cursor so they are creating new ones. Voila! I have more than 19.100 child cursor of both insert and delete statements.

Since I am so lazy, I just think that if I can leave them as they are but when I checked just the memory used by those cursors, I am shocked.

just the 2 sql statement’s cursors are using more almost 5GB of memory. my whole memory on this server is 48GB so this is huge to this DB.

I just flushed the cursors immediately:

of course all child cursors are gone but they are as expected started to spawn again because MV refresh is continuing it’s job.

So this is reported as a bug in Oracle and as a workaround you set a hidden parameter:

be careful while using MV’s. they can cause a lot of pains but very powerful tools.

have a nice day.

EDIT: I just tested _cursor_obsolete_threshold parameter on my local 19.6 database but I am not sure how it is work exactly! it does not start removing cursor immediately when the count exceed 100 but it does flush some after a while. Probably there is a time interval to check obsolete cursors. In any case I will flush the cursors every hour just in case.

Is It Possible to Change SQL Statement in the Database?


Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in this case instead of asking to someone else, you can change that sql in the database before it runs!

After 12c, Oracle gave us an API, Sql Translation Framework, to run a different sql statement than the one sent by the application (or any client). We can affect a sql statements by using hints and database parameters but this is a whole new area. instead of “select * from customers” you can force Oracle to run “select count(*) from invoices“! you can change data source and event column list!

Main purpose of Sql Translation Framework to decrease the cost of converting a non Oracle application to an Oracle application. What! Let’s say you have small third party application which is run on Microsoft SQL Server database. Let say this is a small reporting tool and it is running some Select statements. you can easily move tables from sql server to Oracle but changing application could take much more longer time. At this point any basic select statement can run on any database for example “select * from invoices”; so no need to change anything in here but in sql server there is a useful command called TOP which allows you to get top n rows after an order by. So if your small reporting application runs this query: “select top 10 * from invoices order by amount desc” Oracle can not run this because TOP is a sql server command not Oracle.

Sql Translation Framework is involving at this point and allow you to run a different sql than the one issued. Whenever “select top 10 * from invoices order by amount desc” select statement issued we can tell Oracle to run “select * from invoices order by amount desc fetch first 10 rows only” statement. Pretty cool.

So this is the main purpose but since we can change the sql statements, this can let us to change poorly designed sql statements. Of course the main solution should be changing the source (change the sql which is called from the application) but this might not be possible all the time. for example, an application which we can not alter the source code might be running these queries (maybe we don’t have the source code or it is too expansive to ask for a change) or there might be an emergency, let’s say somehow a poor sql statement has been deployed to production system and it causes a very big performance problem. Since it might be fixed right away and deploying whole application all over again can take too much time. In this kind of situations, we can simply use sql translation framework.

I will demonstrate simple sql translation not an application translation (it will require more steps).

Steps to translate a sql to another:

  1. create a translation profile: main record for your tranlations. there could be more than one sql in a translation profile. you can simply think that as a translations groups to managed easily.
  2. register an sql to translate: the query you want to translate and the query you want translated to.
  3. grant necessary privileges to users who will use translations
  4. set sql_translation_profile parameter and 10601 event

That’s it.

as you can see I run an “invalid” sql statements. top 10 is a structure works on sql server but now I am able to run it in Oracle becuase I translated it into FETCH FIRST clause. please remember only exact sql will be translated. What I mean is if I add a order by or where clause to my translated query it won’t work:

Oracle does not know the version with “order by ” so you must add it too.

Also I mentioned about we can use this to implement a new version for poorly designed statements. Let’s test a union / union all query:

this is our test table. table contains 2 sets of data and LEVEL_ID is unique so same row won’t be listed in both group. Let’s assume our developer has written this sql:

Query takes 1.09 seconds. this query has a logical error. union is unnecessary, union all would return same data (since level_id is unique) and that’s because union all would work faster. if we can not change the source of the query then we can use sql translation framework:

there is one important thing here. consider ENTER character because if you are using it will be carriage return and new line but in linux it is just new line. so while testing you must be careful about enter char.

now let’s test it again:

as you can see performance of the query has increased almost 10 times. that is because it worked as union all not union. unfortunately you can not understand it by checking execution plan because it is the same cursor so you will be seeing old execution plan. That’s also why I compare timings. Of course you can check SQL_TRANSLATION_PROFILE_ID in V$Session while running the query.

This is a great tool but don’t get too involved into this. Creating too many translations can cause too much headache. you might loose in them. So I suggest you to use it wisely.

By the way this also works with bind variables too:

after running select * from hr.employees where employee_id = :emp_id with any emp id, you will get 3 rows from locations table. if you are careful you will see that I added a :emp_id = :emp_id condition to translated query. that is because same bind variables must be listed on the second query otherwise you will get an error.

I hope this helps you. There are too many different things in it so this is just an introduction.

have a nice day.

Performance Gain of NOCOPY Parameters


Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters?

I assume you know about types of parameters like IN or OUT so I won’t explain it all over. Basically out let you modify the parameter inside a plsql block and return that value back to caller variable. You might think this is kind of a pointer operation but it is not. when you modify a parameter inside a block that new value won’t be affecting caller variable. it will store on a temporary variable and after plsql block completed modified value will be assign back to caller variable.

adding a NOCOPY to your in out variable actually make it a kind of pointer and whenever you modifed your parameter in plsql block it will affect the caller variable even if plsql block raise an exception! without nocopy, since data is copied to a new variable every procedure call, it will increase your memory consumption and timing. especially if you are using big variables like CLOB or PLSQL Collections (arrays).

So Let’s test different variable types, call them many times and see if there is a difference when we use NOCOPY.

I didn’t think about the sample code so it is a little bit messy. Basically, I am just using overloaded functions with different variable types and pass them to a procedure many times.

PS: I am setting plsql_optimize_level to 1 to prevent Oracle modify my sloppy code. Since I don’t actually run a business logic and Oracle can remove many of those code like assigning a dummy variable which is never used.

this is my package code:

I will test VARCHAR2, DATE, NUMBER, CLOB and a COLLECTION of Varchar2. Call every each of them with and without NOCOPY and check the timings.

My sp_main procedure gets 3 arguments, first is the tested type parameter, second is calling type, COPY or NOCOPY and third one is number of calls.

So big types like clob and collection has the biggest difference. What surprises me are NUMBER and DATE types. they have almost no difference when used NOCOPY.

Here are the results:

as you can see Varchar2 has almost twice of the difference and please consider, when you work with bigger variable data and more than one variable, difference will be much more than this.

As I said before, CLOB and the COLLECTION has the  biggest difference. I mostly use NOCOPY with my collection and clob parameters even if I won’t be modifying them. Of course that requires a little bit more attention. Modifying by mistake it can cause headaches.

Using NOCOPY for those parameters is definitely increase your performance but also can cause some inconsistent situations. Let’s say you pass a collection using NOCOPY to a procedure and started to modify it in the procedure (in a loop maybe) after doing some job your plsql raise an unexpected error. In this case your variable will be partially modified and this is not something we desire. you must be careful using NOCOPY against this kind of situations. When you did not use a NOCOPY, you won’t be dealing with this and your collection (or other type of variable) will be consistent.

I hope this help you increase your codes performance.

I wish you healthy days.



Since number is one of the smallest type it is not much possible to demonstrate performance gain with just one parameter. I just added 15 more number parameter to my test procedures and result is like this:

Native Compilation of a PLSQL Object


you’ve probably heard about “NATIVE” compilation about plsql objects. I wanted to demonstrate how it is work and whether it really does matter or not.

By default Oracle compiles every plsql object as “INTERPRETED” which means, your plsql code transform into a intermediate form which interpreted every time when you run it. This part is important; you compile your code and whenever you run your procedure/function, that code is interpreted by Oracle and executed. This is a huge performance gap because Oracle creates another layer to run your code between your code and cpu/os etc. A NATIVE compiled code, on the other hand, is transformed into a native code which can be run by cpu directly. So a Native compiled plsql object will run much more faster than Interpreted one.

By saying “faster” I must also add “what” will be faster. for example an SQL statement (select, dml etc) will not affected by compilation type because those commands can not be run by cpu directly. They are Oracle Database commands and they must be run by Oracle processes so if your plsql object (procedure/function etc) just contains sql statements then compiling it as NATIVE probably won’t make any difference. Only direct cpu operations will be affected like multiplying a value and assigning it to another variable or loops etc. Basically we can say plsql specific commands will run faster when they compiled as NATIVE.

this compilations is bind to a parameter: PLSQL_CODE_TYPE. By default it’s value is INTERPRETED and you can change it to NATIVE. that way every object that you will create after that will be compiled as NATIVE but not the ones created until now. you can query your objects compilation type on DBA_PLSQL_OBJECT_SETTINGS (instead of DBA you can use, ALL or USER of course). if you don’t want to make NATIVE as your default compilation method then you can compile just one object as you wish:

this allows you to compile your previous objects with different compilation method than PLSQL_CODE_TYPE parameter. also you can set PLSQL_CODE_TYPE parameter just for your session and the objects that you will create or compile on that session will be compiled as accordingly.

So Let’s make an example and see the difference:

my PLSQL_CODE_TYPE parameter is in it’s default value and I created a procedure called SP_X. That procedure just run some plsql code like loops, if statements and assignments. I just want to remind that oracle optimize plsql codes while compiling them so I just make it a little bit harder to optimize (simply I could just use PLSQL_OPTIMIZE_LEVEL but just leaving everything by default).

When I checked my procedure I confirmed that it is compiled INTERPRETED:

So let’s run and see timings. To make sure I will run it 3 times:

so it is around 11 or 12 seconds to run on my laptop. Results will be different on different machines of course.

Now compile procedure as NATIVE and re run again:

it is almost two times faster than INTERPRETED compilation! that is amazing just a simple change can increase your plsql object’s speed.

So the question is why this is not default and why does even INTERPRETED exist? Basic answer is “platform independency” . When you compile your code NATIVE it is depended on your Cpu, OS etc etc. that means if you change those components your code might not work! You must recompile those objects. if you compile your code INTERPRETED, that code will run as long as Oracle runs on a platform. This makes it portable.

You can also compile Oracle database pre build objects as NATIVE (objects in SYS schema for example) this will improve some performance on the core operations of database. of course you need dba privileges to do that like shutdown database and open it in upgrade mode then you can call oracle_home/rdbms/admin/dbmsupgnv.sql file. of course you should set PLSQL_CODE_TYPE as NATIVE on database first. whole steps can be found in “database plsql reference” to compile entire objects in database as NATIVE.

if you have plsql objects which runs very frequently and contains mostly plsql instead of sql then you can compile them as NATIVE.

I wish you healthy days.

Min/Max on same column


Let’s do a simple trick. You want to get min and max value of a column which is already indexed. As you know if you have an btree index on a column and try to get a Min or Max value then you will see an “Index Full Scan(Min/Max)” line in the execution plan. since indexes store data in order, it is enough to look for the first or last index leaf to get min or max value.

as you see, we are able to see INDEX FULL SCAN (MIN/MAX) step and our query cost is 2. same thing happens if I replace Max with a Min function.

what if we ask for both, Min and Max ?

suddenly, Oracle stops using index and change index search with a full table scan. Why? despite of seeing very high cost (cost is 394, it was 2 when it used index) maybe this is because table is so small and Oracle decided to use a full table instead of index scan? let’s check:

now we have 1.1 million rows in the table and it is not that small now. I’ve already rebuild the index and gather the statistics so let’s try again:

as we can see our table is not that small and even so Oracle uses a full table scan with cost 6367. even in that big table index scan cost does not change much:

just index scan cost is 3. so instead of full table scan, you can select min first and then run max as different 2 queries and your total cost is almost 6. this is much much less than Oracle’s default execution plan. Even using a HINT won’t work because Oracle is not able to do that with both min and max aggregate functions. I didn’t go deeper but as I understand Oracle can use just one directional index search (asc or desc) not both so instead of using an index it decides full table scan operation.

You might think that maybe this is because column contains null values and not marked as a not null and somehow this is effecting the optimizer? yes and no. let’s delete null values and modify column not null:

huh! now it changed and used the index with a cost 707. this is much better than full table scan (6367 cost) but if you pay attention, you will see that this is not “Index Full Scan (Min/Max)“, instead this is “Index Fast Full Scan”. in a Index Full Scan (min/max) Oracle just read first or last leaf almost nothing more and this is more than enough to get what we need but in that case Oracle has read entire index (allleaves) so this cost as a lot! as I said running min and max in a 2 different query will cost us 6 (each of them is 3).

so how can we workaround?

as you see we are so close to total cost of 6. Optimizer does a lot for us but sometimes we need to help.

Have healthy days, please stay home if you can. stay safe.

Oracle 18c New feature Private Temporary Table


18c introduced a new object called Private Temporary Table (I will call it PTT in this post). PTT is just another version of Global Temporary Table(GTT). The data is temporary and will be stored for a time. Data in temporary tables (both PTT and GTT) can be queried by the session which inserts the data. I will assume that you have experience with GTTs.

As you know GTTs are created once and can be used many times. GTT is an object and it is listed in Dba_Objects view as an object but PTTs are not! they are session objects (actually PTT is just an memory array that stores your data in your session). GTTs store data until you commit your transactions or sessions and same thing applies to PTTs but after the data erased PTTs are gone! They drop themselves.

So PTTs are specific to your session and only your session, create the table use it and then it will drop itself. Also it has a special naming convention. A new parameter “private_temp_table_prefix” is the mandatory prefix for PTTs. Default value is ORA$PTT.

I created 2 tables ORA$PTT_X_TRN (End of life is transaction) and ORA$PTT_X_SESS (end of life is session). as you can see I inserted 1 row each table and after commit, my query to ORA$PTT_X_TRN has got an error “table or view does not exists”. so these are just session/transaction objects.

Personally I didn’t like this much. They are not much different from GTTs and have some cons compared to GTTs. Documentation says, PTTs are stored in Memory and GTTs are on disk but this is not entirely true. Oracle always stores data in memory as long as it can so GTT’s data are also in memory. Let’s do some performance check between GTTs and PTTs:

First insert performance one by one:

so timing is almost same. I run this for a few times so all results are similar.

How about bulk insert:

on my example, GTT is faster on bulk insert but after many times testing, on some of them PTT were faster so timing not much difference at all. Also you can see that redo size, db block gets etc are very similar.

How about querying?

again not much difference and update:

no difference. Also there is a very huge con for PTTs. You can not create index on a PTT! This makes a huge difference because many of my simple tests will be changed and GTTs become the leader.

So I will continue to use GTTs instead of PTTs. This PTT is look like table variable of Microsoft sql server db but not same. we still have to use Execute immediate to create a PTT and create table script will be a string so to check the syntax we have to copy the code and test it first etc etc. so not much usable to me. By the way I’am already against to create temp table just for a small code because whenever I convert a mssql db procedure to pslql procedure, I saw huge performance gaps because of those temp tables.

thanks for reading.

Using Temp instead of Undo for GTTs

Hello everyone,

with 12c we have a very nice new option; Temp Undo for GTTs (Global Temporary Tables). as you know temporary tables are used for storing temporary data. those tables data are stored in TEMP tablespace so they are not vital. Temporary tables can generates very little redo data so they are  faster than heap tables. if you experience an instance crash those data will be lost but as I said those data is not vital.

even we say gtts generates less redo, they still cause to generate redo because of UNDO! temporary table data does not need to recover after an instance crash but you might need to use a “rollback”. whenever you run a DML on a table Oracle will copy the original data to UNDO tablespace in case you need to rollback your operation and if you run a rollback, original data will be copied back to table from UNDO tablespace. By the way, that is why commit is so fast and rollback takes as much as the dml operation itself.

So, whenever you run a DML, oracle genrates 2 basic group of redo data. one for the change of table blocks itself and one for the change of undo blocks. GTTs does not generate (at least very little) redo data so they will have a better performance but your dml will cause original data to copy to UNDO and that will cause an UNDO block change and that will cause to redo log data. GTTs cause to generate redo log data indirectly. After your dml on the GTTs if you issue a rollback original data will be copied from undo to GTT.

With 12c, we have an option to choose where undo data to be written and we can choose to write undo data to temporary tablespace itself. Point is almost there won’t be any redo log data and that will make our GTTs much more faster. To achieve that you need to set a new database parameter called TEMP_UNDO_ENABLED. by default this parameter’s value is false and you can set in system or session.

Let’s demonstrate this but I would like to add a note before we go! if you use a GTT on a session, parameter’s value change won’t affect the result. before using the GTT you must set temp_undo_enabled parameter (or simply set in system level)

first to check the redo size I wrote a small package that will show us used redo size difference between the previous call of this package and current.

whenever you call pkg_get_stats.sp_get_redo_size procedure, it will write the difference yo dbms_output.

so Let’s start with a new fresh sqlplus session (always use sqlplus for tests because UIs might do some extra jobs which can affect your results). My test case will show us three things.

1- How much redo generated for dml operations (including table create command )
2- How much temp space is being used by that session
3- How much time required to complete the dmls

session1 without using temp undo:

I suppressed dml results. as you can see job completed in 24 seconds, generated ~733 KB redo and used 559KB temp space. Total generated data is around 1292KB.

Now let’s run the same code after enabling temp undo:

session 2 with temp undo:

as you can see total run time decreased to 15 from 24 which is a huge gain for performance. almost no redo generated but of course temp usage increased even the total amount (1118KB) is less than previous (1292 KB).

1- You will probably get faster execution time! I say probably because if your undo tablespace has much more faster disk structures than temp then you might experience a performance loss! on my system they have same disk structure and disk performance.
2- You will generate less redo which is so much important. Don’t think this is just a performance issue. if you have a data guard on a distance location, every redo log data will be shipped to that location over network. decreasing the generated redo size will decrease your network traffic also the amount of job on data guard db because unnecessary temp table insert/update/delete operations won’t be run. Again a performance gain for everything.
3- You will need less UNDO space since your GTTs won’t use it anymore.


1- You will need more space on TEMPORARY tablespace because it will use for undo data of GTTs
2- your TEMPORARY tablespaces should be on fast disks so you can get the performance gain.

In conclusion, I can’t wait to see the active usage results and I will active this on a customer’s db these days. I will also share the information about active usage.

thanks to all.

Edit: I already used this new feature on one of my customer’s report and got very good results. Performance of the report increased around %10 and redo size increased significantly but I want to be cautious before set this parameter database level and I use it on session level for specific operations.