Feed aggregator

Oracle – Pinning table data in the Buffer Cache

The Anti-Kyte - Thu, 2016-06-23 15:13

As I write, Euro 2016 is in full swing.
England have managed to get out of the Group Stage this time, finishing second to the mighty…er…Wales.
Fortunately Deb hasn’t mentioned this…much.

In order to escape the Welsh nationalism that is currently rampant in our house, let’s try something completely unrelated – a tale of Gothic Horror set in an Oracle Database…

It was a dark stormy night. Well, it was dark and there was a persistent drizzle. It was Britain in summertime.
Sitting at his desk, listening to The Sisters of Mercy ( required to compensate for the lack of a thunderstorm and to maintain the Gothic quotient) Frank N Stein was struck by a sudden inspiration.
“I know”, he thought, “I’ll write some code to cache my Reference Data Tables in a PL/SQL array. I’ll declare the array as a package header variable so that the data is available for the entire session. That should cut down on the amount of Physical IO my application needs to do !”

Quite a lot of code later, Frank’s creation lurched off toward Production.
The outcome wasn’t quite what Frank had anticipated. The code that he had created was quite complex and hard to maintain. It was also not particularly quick.
In short, Frank’s caching framework was a bit of a monster.

In case you’re wondering, no, this is not in any way autobiographical. I am not Frank (although I will confess to owning a Sisters of Mercy album).
I am, in fact, one of the unfortunates who had to support this application several years later.

OK, it’s almost certain that none of the developers who spawned this particular delight were named after a fictional mad doctor…although maybe they should have been.

In order to prevent others from suffering from a similar misapplication of creative genius, what I’m going to look at here is :

  • How Oracle caches table data in Memory
  • How to work out what tables are in the cache
  • Ways in which you can “pin” tables in the cache (if you really need to)

Fortunately, Oracle memory management is fairly robust so there will be no mention of leeks

Data Caching in Action

Let’s start with a simple illustration of data caching in Oracle.

To begin with, I’m going to make sure that there’s nothing in the cache by running …

alter system flush buffer_cache
/

…which, provided you have DBA privileges should come back with :

System FLUSH altered.

Now, with the aid of autotrace, we can have a look at the difference between retrieving cached and uncached data.
To start with, in SQL*Plus :

set autotrace on
set timing on

…and then run our query :

select *
from hr.departments
/

The first time we execute this query, the timing and statistics output will be something like :

...
27 rows selected.

Elapsed: 00:00:00.08
...

Statistics
----------------------------------------------------------
	106  recursive calls
	  0  db block gets
	104  consistent gets
	 29  physical reads
	  0  redo size
       1670  bytes sent via SQL*Net to client
	530  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  7  sorts (memory)
	  0  sorts (disk)
	 27  rows processed

If we now run the same query again, we can see that things have changed a bit…

...
27 rows selected.

Elapsed: 00:00:00.01
...

Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  8  consistent gets
	  0  physical reads
	  0  redo size
       1670  bytes sent via SQL*Net to client
	530  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 27  rows processed

The second run was a fair bit faster. This is mainly because the data required to resolve the query was cached after the first run.
Therefore, the second execution required no Physical I/O to retrieve the result set.

So, exactly how does this caching malarkey work in Oracle ?

The Buffer Cache and the LRU Algorithm

The Buffer Cache is part of the System Global Area (SGA) – an area of RAM used by Oracle to cache various things that are generally available to any sessions running on the Instance.
The allocation of blocks into and out of the Buffer Cache is achieved by means of a Least Recently Used (LRU) algorithm.

You can see details of this in the Oracle documentation but, in very simple terms, we can visualise the workings of the Buffer Cache like this :

lru_algorithm

When a data block is first read from disk, it’s loaded into the middle of the Buffer Cache.
If it’s then “touched” frequently, it will work it’s way towards the hot end of the cache.
Otherwise it will move to the cold end and ultimately be discarded to make room for other data blocks that are being read.
Sort of…

The Small Table Threshold

In fact, blocks that are retrieved as the result of a Full Table Scan will only be loaded into the mid-point of the cache if the size of the table in question does not exceed the Small Table Threshold.
The usual definition of this ( unless you’ve been playing around with the hidden initialization parameter _small_table_threshold) is a table that is no bigger than 2% of the buffer cache.
As we’re using the default Automated Memory Management here, it can be a little difficult to pin down exactly what this is.
Fortunately, we can find out (provided we have SYS access to the database) by running the following query :

select cv.ksppstvl value,
    pi.ksppdesc description
from x$ksppi pi
inner join x$ksppcv cv
on cv.indx = pi.indx
and cv.inst_id = pi.inst_id
where pi.inst_id = userenv('Instance')
and pi.ksppinm = '_small_table_threshold'
/

VALUE      DESCRIPTION
---------- ------------------------------------------------------------
589        lower threshold level of table size for direct reads

The current size of the Buffer Cache can be found by running :

select component, current_size
from v$memory_dynamic_components
where component = 'DEFAULT buffer cache'
/

COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
DEFAULT buffer cache                                                251658240

Now I’m not entirely sure about this but I believe that the Small Table Threshold is reported in database blocks.
The Buffer Cache size from the query above is definitely in bytes.
The database we’re running on has a uniform block size of 8k.
Therefore, the Buffer Cache is around 614 blocks.
This would make 2% of it 614 blocks, which is slightly more than the 589 as being reported as the Small Table Threshold.
If you want to explore further down this particular rabbit hole, have a look at this article by Jonathan Lewis.

This all sounds pretty good in theory, but how do we know for definite that our table is in the Buffer Cache ?

What’s in the Buffer Cache ?

In order to answer this question, we need to have a look at the V$BH view. The following query should prove adequate for now :

select obj.owner, obj.object_name, obj.object_type,
    count(buf.block#) as cached_blocks
from v$bh buf
inner join dba_objects obj
    on buf.objd = obj.data_object_id
where buf.class# = 1 -- data blocks
and buf.status != 'free'
and obj.owner = 'HR'
and obj.object_name = 'DEPARTMENTS'
and obj.object_type = 'TABLE'
group by obj.owner, obj.object_name, obj.object_type
/

OWNER                OBJECT_NAME          OBJECT_TYPE          CACHED_BLOCKS
-------------------- -------------------- -------------------- -------------
HR                   DEPARTMENTS          TABLE                            5

Some things to note about this query :

  • the OBJD column in v$bh joins to data_object_id in DBA_OBJECTS and not object_id
  • we’re excluding any blocks with a status of free because they are, in effect, empty and available for re-use
  • the class# value needs to be set to 1 – data blocks

So far we know that there are data blocks from our table in the cache. But we need to know whether all of the table is in the cache.

Time for another example…

We need to know how many data blocks the table actually has. Provided the statistics on the table are up to date we can get this from the DBA_TABLES view.

First of all then, let’s gather stats on the table…

exec dbms_stats.gather_table_stats('HR', 'DEPARTMENTS')

… and then check in DBA_TABLES…

select blocks
from dba_tables
where owner = 'HR'
and table_name = 'DEPARTMENTS'
/

    BLOCKS
----------
	 5

Now, let’s flush the cache….

alter system flush buffer_cache
/

…and try a slightly different query…


select *
from hr.departments
where department_id = 60
/
DEPARTMENT_ID DEPARTMENT_NAME		     MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
	   60 IT				    103        1400

We can now use the block total in DBA_TABLES to tell how much of the HR.DEPARTMENTS table is in the cache …

select obj.owner, obj.object_name, obj.object_type,
    count(buf.block#) as cached_blocks,
    tab.blocks as total_blocks
from v$bh buf
inner join dba_objects obj
    on buf.objd = obj.data_object_id
inner join dba_tables tab
    on tab.owner = obj.owner
    and tab.table_name = obj.object_name
    and obj.object_type = 'TABLE'
where buf.class# = 1
and buf.status != 'free'
and obj.owner = 'HR'
and obj.object_name = 'DEPARTMENTS'
and obj.object_type = 'TABLE'
group by obj.owner, obj.object_name, obj.object_type, tab.blocks
/

OWNER	   OBJECT_NAME	   OBJECT_TYP CACHED_BLOCKS TOTAL_BLOCKS
---------- --------------- ---------- ------------- ------------
HR	   DEPARTMENTS	   TABLE		  1	       5

As you’d expect the data blocks for the table will only be cached as they are required.
With a small, frequently used reference data table, you can probably expect it to be fully cached fairly soon after the application is started.
Once it is cached, the way the LRU algorithm works should ensure that the data blocks are constantly in the hot end of the cache.

In the vast majority of applications, this will be the case. So, do you really need to do anything ?

If your application is not currently conforming to this sweeping generalisation then you probably want to ask a number of questions before taking any precipitous action.
For a start, is the small, frequently accessed table you expect to see in the cache really frequently accessed ? Is your application really doing what you think it does ?
Whilst where on the subject, are there any rogue queries running more regularly than you might expect causing blocks to be aged out of the cache prematurely ?

Once you’re satisfied that the problem does not lie with your application, or your understanding of how it operates, the next question will probably be, has sufficient memory been allocated for the SGA ?
There are many ways you can look into this. If your fortunate enough to have the Tuning and Diagnostic Packs Licensed there are various advisor that can help.
Even if you don’t, you can always take a look at V$SGA_TARGET_ADVICE.

If, after all of that, you’re stuck with the same problem, there are a few options available to you, starting with…

The Table CACHE option

This table property can be set so that a table’s data blocks are loaded into the hot end of the LRU as soon as they are read into the Buffer Cache, rather than the mid-point, which is the default behaviour.

Once again, using HR.DEPARTMENTS as our example, we can check the current setting on this table simply by running …

select cache
from dba_tables
where owner = 'HR'
and table_name = 'DEPARTMENTS'
/

CACHE
-----
    N

At the moment then, this table is set to be cached in the usual way.

To change this….

alter table hr.departments cache
/

Table HR.DEPARTMENTS altered.

When we check again, we can see that the CACHE property has been set on the table…

select cache
from dba_tables
where owner = 'HR'
and table_name = 'DEPARTMENTS'
/

CACHE
-----
    Y

This change does have one other side effect that is worth bearing in mind.
It causes the LRU algorithm to ignore the Small Table Threshold and dump all of the selected blocks into the hot end of the cache.
Therefore, if you do this on a larger table, you do run the risk of flushing other frequently accessed blocks from the cache, thus causing performance degradation elsewhere in your application.

The KEEP Cache

Normally you’ll have a single Buffer Cache for an instance. If you have multiple block sizes defined in your database then you will have a Buffer Cache for each block size. However, you can define additional Buffer Caches and assign segments to them.

The idea behind the Keep Cache is that it will hold frequently accessed blocks without ageing them out.
It’s important to note that the population of the KEEP CACHE uses the identical algorithm to that of the Buffer Cache. The difference here is that you select which tables use this cache…

In order to take advantage of this, we first need to create a KEEP Cache :

alter system set db_keep_cache_size = 8m scope=both
/

System altered.

Note that, on my XE 11gR2 instance at least, the minimum size for the Keep Cache appears to be 8 MB ( or 1024 8k blocks).
We can now see that we do indeed have a Keep Cache…

select component, current_size
from v$memory_dynamic_components
where component = 'KEEP buffer cache'
/

COMPONENT               CURRENT_SIZE
----------------------  ------------
KEEP buffer cache       8388608

Now we can assign our table to this cache….

alter table hr.departments
    storage( buffer_pool keep)
/

Table altered.

We can see that this change has had an immediate effect :

select buffer_pool
from dba_tables
where owner = 'HR'
and table_name = 'DEPARTMENTS'
/

BUFFER_POOL
---------------
KEEP

If we run the following…

alter system flush buffer_cache
/

select * from hr.departments
/

select * from hr.employees
/

…we can see which cache is being used for each table, by amending our Buffer Cache query…

select obj.owner, obj.object_name, obj.object_type,
    count(buf.block#) as cached_blocks,
    tab.blocks as total_blocks,
    tab.buffer_pool as Cache
from v$bh buf
inner join dba_objects obj
    on buf.objd = obj.data_object_id
inner join dba_tables tab
    on tab.owner = obj.owner
    and tab.table_name = obj.object_name
    and obj.object_type = 'TABLE'
where buf.class# = 1
and buf.status != 'free'
and obj.owner = 'HR'
and obj.object_type = 'TABLE'
group by obj.owner, obj.object_name, obj.object_type,
    tab.blocks, tab.buffer_pool
/   

OWNER      OBJECT_NAME          OBJECT_TYPE     CACHED_BLOCKS TOTAL_BLOCKS CACHE
---------- -------------------- --------------- ------------- ------------ -------
HR         EMPLOYEES            TABLE                       5            5 DEFAULT
HR         DEPARTMENTS          TABLE                       5            5 KEEP

Once again, this approach seems rather straight forward. You have total control over what goes in the Keep Cache so why not use it ?
On closer inspection, it becomes apparent that there may be some drawbacks.

For a start, the KEEP and RECYCLE caches are not automatically managed by Oracle. So, unlike the Default Buffer Cache, if the KEEP Cache finds it needs a bit more space then it’s stuck, it can’t “borrow” some from other caches in the SGA. The reverse is also true, Oracle won’t allocate spare memory from the KEEP Cache to other SGA components.
You also need to keep track of which tables you have assigned to the KEEP Cache. If the number of blocks in those tables is greater than the size of the cache, then you’re going to run the risk of blocks being aged out, with the potential performance degradation that that entails.

Conclusion

Oracle is pretty good at caching frequently used data blocks and thus minimizing the amount of physical I/O required to retrieve data from small, frequently used, reference tables.
If you find yourself in a position where you just have to persuade Oracle to keep data in the cache then the table CACHE property is probably your least worst option.
Creating a KEEP Cache does have the advantage of affording greater manual control over what is cached. The downside here is that it also requires some maintenance effort to ensure that you don’t assign too much data to it.
The other downside is that you are ring-fencing RAM that could otherwise be used for other SGA memory components.
Having said that, the options I’ve outlined here are all better than sticking a bolt through the neck of your application and writing your own database caching in PL/SQL.


Filed under: Oracle, SQL Tagged: alter system flush buffer_cache, autotrace, buffer cache, dba_objects, dbms_stats.gather_table_stats, Default Buffer cache, how to find the current small table threshold, Keep Cache, lru algorithm, small table threshold, Table cache property, v$bh, v$memory_dynamic_components, what tables are in the buffer cache, x$ksppcv, x$ksppi

Unable to Retrieve sys_refcursor values from remote function

Tom Kyte - Thu, 2016-06-23 15:09
Hi, i have created a function in DB1 that returns a sys_refcursor as output which is giving the result as desired in DB1. But when other database DB2 is trying to execute the function using dblink, that cursor is not returning any values. It is no...
Categories: DBA Blogs

Want to skip record if it's length not matching with required length while loading data in oracle external table

Tom Kyte - Thu, 2016-06-23 15:09
Hi Tom, I want to load data from fixed length file to oracle external table. I have specified length for each column while creating external table so data for most records getting loaded correctly. But if record length dosent match then data gets...
Categories: DBA Blogs

how to use Connection String in VB.NET using Oracle Wallet ?

Tom Kyte - Thu, 2016-06-23 15:09
In vb.net we could use following connection string but i recently do practical on oracle wallet done successfully in SQL PLUS Tools but main question is i want to use this connection string (username and password and tnsping) using oracle wallet sto...
Categories: DBA Blogs

Tree and "Youngest Common Ancestor"

Tom Kyte - Thu, 2016-06-23 15:09
<code>Hello Tom, I could finally ask you a question... I have a table like this: create table tree(name varchar2(30), id number, pid number, primary key(id), foreign key(pid) references tree(id)); with sample data: insert into tree va...
Categories: DBA Blogs

How to split comma seperated column of clob datatype and insert distinct rows into another table?

Tom Kyte - Thu, 2016-06-23 15:09
Hi, I need to split the comma separated values of clob datatype column in one table and insert only the distinct rows in another table. The details of the table are given below. The toaddress column in Table A is of datatype CLOB. Table B has ...
Categories: DBA Blogs

Maintaining Partitioned Tables

Tom Kyte - Thu, 2016-06-23 15:09
<code>Hi Tom, I need to build a table that will hold read-only data for up to 2 months. The table will have a load (via a perl script run half hourly) of 3 million new rows a day. Queries will be using the date col in the table for data eliminati...
Categories: DBA Blogs

Handling ORA-12170: TNS:Connect timeout occurred & ORA-03114: not connected to ORACLE failures

Tom Kyte - Thu, 2016-06-23 15:09
Hi Tom, We had a scenario where the sqlplus connection failed with "ORA-12170: TNS:Connect timeout occurred" in one instance & "ORA-03114: not connected to ORACLE" in another instance while executing from a shell script, but in both the cases retu...
Categories: DBA Blogs

Single Sign on ( SSO ) in Oracle Apex

Tom Kyte - Thu, 2016-06-23 15:09
Hi Tom , I am suffering in implementing Single Sign on ( SSO ) in Oracle apex using Custom authentication scheme. i have two applications like <code> <b> App id</b> <b> Application Name</b> 101 - ...
Categories: DBA Blogs

Version Control for PLSQL

Tom Kyte - Thu, 2016-06-23 15:09
Hi Tom, A couple years ago, I saw demo of how to version your PLSQL code. I have been searching for the code, all morning and I cannot find it anywhere. Can you point me to where/how I can version my PLSQL package? Thanks, Greg
Categories: DBA Blogs

Create second Ora 12c environment on same AWS server, now CDB/PDB type

Tom Kyte - Thu, 2016-06-23 15:09
Hi, I have an Oracle 12c running on AWS on CentOS Linux rel 7.1. The database has been installed as a standalone non-CDB database. This DEV database is used for development. I have to install a second environment, for QA, on the same server. This ...
Categories: DBA Blogs

Using Elasticsearch with PeopleSoft

PeopleSoft Technology Blog - Thu, 2016-06-23 13:00

We reported in April that PeopleSoft is planning to offer Elasticsearch as an option.  Our original plan was to make Elasticsearch available with the first generally available release of PeopleTools 8.56.  We have since revised that plan.  We now plan to make Elasticsearch available with PeopleTools 8.55/Patch 10.  This will enable us to offer Elastic as an option a bit sooner.

Oracle-PeopleSoft will continue to support Oracle's Secure Enterprise Search (SES) with PeopleSoft through the support of PeopleTools 8.54 at a minimum.  We are evaluating whether to extend that support, and we'll announce further support plans in the near future.  It's important for customers to know that if they have deployed SES, they will be supported for some time until they make the transition to Elastic.  Elasticsearch will be the only option offered in PeopleTools 8.56.

As described in the original announcement, we plan to provide guidance on migration from SES to Elastic as well as deployment guidance to help with peformance tuning, load balancing and failover.  We are also planning to produce training for Elastic with PeopleSoft.  We are also presenting a session at Oracle Open World on Elasticsearch with PeopleSoft.  We want to make the move to Elasticsearch as quick and easy as possible for our customers.  Based on customer feedback, we believe Elastic will be embraced by PeopleSoft customers, and it will provide significant benefits.

Remote DBA Benefits Comparison Series- Cost Reduction

Chris Foot - Thu, 2016-06-23 10:30

Introduction

I’ve been working in the IT profession for close to 30 years now. In virtually all facets related to database administration, I’ve had the good fortune of performing a fairly varied set of tasks for my employers over the last couple of decades.    

Question is: upgrade now to 12.1.0.2 or wait for 12.2 ?

Yann Neuhaus - Thu, 2016-06-23 04:27

Let’s look at Release Schedule of Current Database Releases (Doc ID 742060.1)
12.2.0.1 is planned for 2HCY2016 on platforms Linux x86-64, Oracle Solaris SPARC (64-bit), Oracle Solaris x86-64 (64-bit).
2HCY2016 starts next week but we can imagine that it will not be released immediately and anyway we will have to wait a few months to download the on-premise(s) version. Add another couple of months to get at least one Proactive Bundle Patch to stabilize that new release. So maybe we can plan for production upgrade on Jan. 2017 for Linux platform, and Apr. or Jul. 2017 for Windows platform, right? How does that cope with 11.2.0.4 and 12.1.0.1 end of support?

Is delay for 12.2 a problem?

My opinion is that long time for new release is not a problem. Most of customers want stable supported release, not new features available only with options and that may introduce bugs. As long as we have support, PSUs and Proactive Bundle patchsets, everything is ok. We can’t blame software regressions after upgrade, and at the same time look forward to get new releases in a short period of time.
So in my opinion, waiting 6 months or 1 year to get 12.2 is not a problem except for book authors that wait for the general availability of 12.2 to release their book https://www.amazon.com/Oracle-Database-Release-Multitenant-Press/dp/1259836096 ;)

Is ‘cloud first’ a problem?

I don’t think that ‘cloud first’ is a problem by itself. We will have to learn 12.2 features and test them before upgrading our databases, and the Oracle Public Cloud is good for that. But I fear that customers will feel forced to go to the cloud, which is wrong. Was the same when 12.1.0.2 was released for Enterprise Edition. They feel forced to qui Standard Edition but that was probably not the goal. Especially when those that have quit Standard Edition One did it to go to open-source RDBMS.

Is ‘multitenant first’ a problem?

Yes, ‘cloud first’ may mean ‘multitenant first’ because that’s the only architecture available for 12c on the Oracle DBaaS. First, you can install a non-CDB if you choose ‘virtual image’. And anyway, OPC trial is the good occasion to test 12.2 and multitenant at the same time. Let me repeat that multitenant architecture has lot of features available without the multitenant option.

Upgrade planning

Back to the ground, the problem in my opinion is the incertitude.
Free extended support for 11.2.0.4 ends on 31-May-2017 and we don’t know yet if we will have a stable (i.e with few PSUs) 12.2 release at that time for on-premises, especially for Windows which will come later than Linux.
Remember that 12.1.0.2 on Windows came two months after the Linux one. And another two months for AIX.

12.1.0.1 support ends on 31-Aug-2016 and 12.2 will not be out at that time, at least for on-premises.

So what?

Customers that expected to get 12.2 before the end of 12.1.0.1 or 11.2.0.4 support will now (since the announcement of 2HCY2016 last month and the ‘cloud first’ recent announcement) have to plan an intermediate upgrade to 12.1.0.2 before going to 12.2. And because of the ‘Release 1′ myth, they are afraid of that. Our mission, as consultants and Oracle partners, is to explain that the myth has no reason behind it. Look at Mike Dietrich blog about that. Hope you will be convinced that version, releases and patchsets can bring regressions and should be carefully tested, whatever it’s the 1st, 2nd or 4th number on the version identification that is incremented. New ORACLE_HOME is new software.

Then, once in 12.1.0.2 you will have the time to plan an upgrade to 12.2 after learning, testing, changing administration scripts/procedures/habits to the era of multitenant. And you will be ready for the future.

The customers in 11.2.0.4 that do not want to plan that intermediate upgrade will have the option to pay for extended support which ends on 31-DEC-2019.

 

Cet article Question is: upgrade now to 12.1.0.2 or wait for 12.2 ? est apparu en premier sur Blog dbi services.

database option - RMAN

Pat Shuff - Thu, 2016-06-23 02:07
Technically, database backup is not an option with database cloud services, it is bundled into the service as it is with on premise systems. Previously, we talked about backup and restore through the database cloud console. Unfortunately, before we an talk about Data Guard and how to set it up we need to dive a little deeper into RMAN. The first step in setting up Data Guard is to replicate data between two database instances. The recommended way of doing this is with RMAN. You can do this with a backup and recover option or duplicate option. We will look primarily at the duplicate option.

The topic of RMAN is a complex and challenging subject to tackle. There are many configuration options and ways to set up backups and data sets as well as many ways to recover rows, tables, or instances. Some books on RMAN include

Fortunately, to setup Data Guard, we don't need to read all of this material but just need to know the basics. Unfortunately, we can't just click a button to make Data Guard work and automatically setup the relationships, replicate the data, and start log shipping. The key command that we need to get the backup from the primary to the standby is the RMAN command. We can execute this from the primary or the standby because RMAN provides a mechanism to remotely call the other system assuming that port 1521 is open between the two database instances
$ rman target user1/password1@system1 auxiliary user2/password2@system2
In this example user1 on system1 is going to backup the instance that it default connects to and replicates to system2 using the user2 credentials. This command can be executed on either system because we are specifically stating what the source is with the name target and what the standby is with the name auxiliary. Once we connect we can then execute
rman> duplicate target database for standby from active database;
What this will do is replicate the database on system1 and push it to system2. The command will also setup a barrier point in time so that changes to system1 are shipped from this point forward when you enable Data Guard. According to Oracle Data Guard 11gR2 Administration Beginner's Guide (Chapter 2) the output of this command should look something like
Starting Duplicate Db at 26-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
...
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u02/app/oracle/oradata/orcl/control01.ctl';
   restore clone controlfile to  '/u02/app/oracle/flash_recovery_area/orcl/control02.ctl' from
 '/u02/app/oracle/oradata/orcl/control01.ctl';
}
executing Memory Script
....
sql statement: alter database mount standby database
...
Starting backup at 26-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u02/app/oracle/oradata/orcl/system01.dbf tag=TAG20120726T160751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:04
channel ORA_DISK_1: starting datafile copy
...
sql statement: alter system archive log current
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=789667774 file name=/u02/app/oracle/oradata/orcl/system01.dbf
...
Finished Duplicate Db at 26-JUL-12
In this example we copied the system01.dbf file from system1 across the network connection and wrote it to /u02/app/oracle/oradata/orcl/system01.dbf on system2.

Let's take a step back and talk about RMAN a little bit to understand what is going on here. If we look at Oracle RMAN Pocket Reference it details some of the benefits of using RMAN over file system copy or disk cloning to backup a database. These include

  • Incremental backups that only copy data blocks that have changed since the last backup.
  • Tablespaces are not put in backup mode, thus there is no extra redo log generation during online backups.
  • Detection of corrupt blocks during backups.
  • Parallelization of I/O operations.
  • Automatic logging of all backup and recovery operations.
  • Built-in reporting and listing commands.
I would add
  • Compression of data as it is written
  • Encryption of data as it is written
  • Tiered storage of backups to disk and secondary target (cheaper disk, cloud, tape, etc)
When RMAN executes it creates a recovery catalog database which is basically a table in the sys area that records the schema within the catalog database and the tables (and supporting objects) within the schema that contain data pertaining to RMAN backup and recovery operations performed on the target. It also stores details about the physical structure of the target database, a log of backup operations performed on the target database’s datafiles, control files, and archived redo log files as well as stored scripts containing frequently used sequences of RMAN commands

When we execute a backup command we create a backup set that is written to the recovery catalog. The backup set is given a tag that we can reference and restore from. If we do daily incrementals we might want to use a part of the date to create the tag. We can restore to a specific point or date in time from our incremental backups.

If we are worried about having usernames and passwords being passed in via the command line or embedded in scripts we could store this password in the database with the orapwd command. This creates a username/password pair and stores it where RMAN can easily pull it from the database. We do need to give the rmanadmin user rights to execute as SYSDBA but this is easily done with a grant command. Once we do this we can drop the username and password from the rman command and only pass in the username@system parameter. The key reason that you might want to do this is invoking the command from the command line with the password exposes the password through the ps command which can be executed by any user. Embedding the password with the orapwd command helps hide this password.

The nice thing about RMAN is that you can backup and restore parts rather than all of a database. You can execute

RMAN> backup tablespace system, user;
RMAN> backup '/u01/app/oracle/oradata/ORCL/system01.dbf';
RMAN> backup incremental level 4 cumulative database skip readonly;
which will backup the user and system tables, backup the system01.dbf file and all of the tables that it includes, and do a backup of the data that has changed since the last level 4 backup and user previous lower level backups to aggregate changes into the current backup. Note that these three commands do significantly different things. We can look at what we have backed up using the
RMAN> list backups;
to see our backup set and where they are stored. When we looked at the database backup cloud service we went through a backup and recovery.

If we had done a list backups after this backup we would have noticed that the data written to SBT_TAPE was really written to cloud storage and potentially to local disk. We can then point our standby system to this backup set and restore into our database instance. This is done by importing the catalog or registering the target when we do the backup. The registration is done with a command like

$ rman target / catalog rman_901/rman_901@rman_catalog
where we are backing up a local database signified by the "/" and adding the host rman_catalog with username rman_901 and password rman_901.

My recommendation is to look at chapter 12 of Oracle Database 12c Oracle RMAN Backup & Recovery because it details how to use the duplicate option for rman. This is key to setting up Data Guard because it replicates a table from a primary system onto a standby system just prior to starting the Data Guard services. The command could be as simple as

RMAN> duplicate target database to standby1;
This will duplicate your existing instance from your on premise to a cloud or other on premise instance identified by the label standby1. This typically correlates to an ip address of a secondary system and could be a short name like this or a fully qualified domain name. We could get more complex with something like
RMAN> duplicate target database to standby1 
pfile=/u02/app/oracle/oradata/ORCL/init.ora
log_file_name_convert=('primary','standby');
This will do the same thing that the previous command did but read the init.ora file for the ORCL instance and convert anything in the /u02/app/oracle/oradata/ORCL/primary on our existing system to /u02/app/oracle/oradata/ORCL/standby on our target standby1 system. This is an easy way to replicate data from a PDB called primary to a PDB called standby prior to setting up a Data Guard relationship. The steps recommended to create and configure an RMAN copy are
  1. On your standby server, build your auxiliary database directory structures (aka your target directory)
  2. On your primary server, make a copy of the target init.ora file so that it can be moved to the standby server.
  3. Move the target init.ora file to the auxiliary site with scp or other software to copy files.
  4. Start or restart the standby instance in NOMOUNT mode
  5. Configure the listener.ora at the standby site
  6. Configure the tnsnames.ora file at the primary site
  7. Create a password file at the standby server
  8. Move the FRA files from primary to standby
  9. From the primary system, run your duplicate command within RMAN
You can add parameters to allow for parallel copies of the data. You probably should not compress or encrypt the data since we will be pulling it from the backup and writing it into a database. We could potentially compress the data but it will not compress the data on the target system, only compress it for transmission across the internet or local network.

In summary, we needed to dive a little deeper into RMAN than we did before. RMAN is needed to duplicate data from our primary to the target prior to log shipping. There are some complexities associated with RMAN that we exposed and the steps needed to get a secondary site ready with rman are not trivial and need an experienced operating system admin and DBA to get this working. One of the new features of provisioning a cloud database service is a checkbox to create a Data Guard replica in another data center. One of the new features of installing a 12.2.2 database instance is also rumored to have a clone to cloud with Data Guard checkbox. As you install a new on premise database or in cloud database these complex steps are done behind the scenes for you as you would expect from a platform as a service model. Amazon claims to do this with site to site replication and restarting the database in another zone if something happens but this solution requires a reconnection from your application server and forcing your users to reauthenticate and reissue commands in flight. Using Data Guard allows your application server to connect to your primary and standby databases. If the primary fails or times out, the application server automatically connects to the standby for completion of the request. All of this is dependent upon RMAN working and replicating data between two live databases so that log shipping can assume that both servers are in a known state with consistent data on both systems.

Kill DB sessions the easy way with SQLcl

Kris Rice - Wed, 2016-06-22 14:37
Seems I can not tweet these animated GIFs anymore. So this is another very short blog post to show real case for adding a new command into SQLcl which can be downloaded here. It's hard annoying tedious to find SID and SESSION then alter to kill that when needed.  What if there was a simple kill command. Here a sample one that takes in the 2 options:   kill sid serial#   Example :   kill 1 2 OR

OTN Community Managers Speak Out About OTN Summit

OTN TechBlog - Wed, 2016-06-22 12:00

Wondering why you should register for the OTN Summit?  Listen to Bob Rhubart, OTN Architect Community Manager and Javed Mohammed, OTN System Community Manager explain what their tracks will cover and why you should attend one of the OTN Summits coming up in July.

Middleware Track - 


Systems Track -

&lt;p&gt; &lt;/p&gt;

Register Today!

July 12
 - 9:30 am - 1 pm (PST)

July 13
 - 9:30 am - 1 pm (BST)

July 14
 - 9:30 am - 1 pm (IST)



database option - Data Guard

Pat Shuff - Wed, 2016-06-22 11:17
To steal liberally from Larry Carpenter's book on Data Guard, Data Guard is a product of more than 15 years of continuous development. We can trace the roots of today’s Data Guard as far back as Oracle7 in the early 1990s. Media recovery was used to apply archived redo logs to a remote standby database, but none of the automation that exists today was present in the product.

Today we are going to look at the material on Data Guard and discuss the differences between Data Guard, Active Data Guard, and Golden Gate. We are going to look at what it takes to replicate from an on premise system to the cloud and from the cloud to an on premise system. It is important to know that you can also synchronize between two cloud instances but we will not cover this today.

If we look at the books that cover this topic they include

Note that there are not any 12c specific books written on Data Guard. This is primarily due to the technology not changing significantly between the 11g and 12c releases. The key new release in 12c is far sync support. We will cover that more later. There are also books written on Active Data Guard and Golden Gate as well If we take a step back and look at high availability, Data Guard is used to provide this functionality between systems. Oracle Data Guard provides the management, monitoring, and automation software to create and maintain one or more standby databases to protect Oracle data from failures, disasters, human error, and data corruptions while providing high availability for mission critical applications. Data Guard is included with Oracle Database Enterprise Edition and in the cloud the High Performance Edition. Oracle Active Data Guard is an option for Oracle Database Enterprise Edition and included in the Extreme Performance Edition in the cloud.

The home page for Data Guard provides links to white papers

There are also a significant number of blogs covering high availability and Data Guard. My recommendation would be to attend the Oracle Education Class or follow one of the two tutorials that cover Basic Data Guard Features and Active Data Guard Features. In both of these tutorials you learn how to use command line features to configure and setup an active - standby relationship between two databases. Larry Carpenter has done a really good job of detailing what is needed to setup and configure two database instances with these tutorials. The labs are a bit long (50+ pages) but cover the material very well and work with on premise systems or cloud systems if you want to play.

The key concepts around Data Guard are the mechanisms for replication and how logs are shipped between systems. The basic foundation of Data Guard centers around replication of changes. When an insert or update is made to a table, this change is captured by the log writer and replicated to the standby system. If the replication mechanism is physical replication the data blocks changed are copied to the standby system. If the replication mechanism is logical replication the sql command is copied to the standby system and executed. Note that the select or read statements are not recorded and copied, only the commands that write to storage or update information in the database. By capturing the changes and shipping them to the standby system we can keep the two systems in synchronization. If a client is trying to execute a select statement on the primary database and the primary fails or goes offline, the select statement can be redirected to the standby for the answer. This results in seconds of delay rather than minutes to hours as is done with disk replication or recovery from a backup. How the replication is communicated to the standby system is also configurable. You can configure a write and release mechanism or a wait for commit mechanism. With the write and release mechanism, the logs are copied to the standby system and the primary system continues operation. With the wait for commit mechanism the primary stalls until the standby system commits the updates.

Significant improvements were made in 11g with the log writer service (LNS) and the redo apply service (RNS). The LNS has the ability to delay the shipping of the logs in the asynchronous update mode and can compress the logs. The RNS knows how the LNS is configured and can get decompress the logs and apply them as was done before. This delay allows for the LNS to look for network congestion and ship the logs when the network is not so overloaded. The compression allows the packet size to be smaller to reduce contention on the network and make the replication more efficient. It is important to note that you can have a single LNS writing to multiple RNS targets to allow for replication not in a one to one configuration but in a one to many configuration. It is also important to note that this technology is different from table cloning or data masking and redaction that we talked about earlier. The assumption is that there is a master copy of the data on the target system and we only ship changes between the systems when an update occurs on the primary.

The key difference between Data Guard and Active Data Guard is the state of the target database. With Data Guard, the database can not have any active sessions other than the RNS agent. You can not open the database for read only to do backups or analytics. Having an active sessions blocks the RNS agent from committing the changes into the database. Active Data Guard solves this problem. The RNS agent understands that there are active connections and can communicate changes to the active sessions if they are reading data from updated areas. A typical SQL connection uses buffering to minimize reads from the disk. Reads are done from an in memory buffer to speed up requests. The problem with reading data on a standby system is invalidation of these buffers. With Data Guard, there is no mechanism to invalidate buffers of sessions on other connections. With Active Data Guard, these mechanisms exist and updates are not only written to the disk but the cache for the other connections are updated.

Golden Gate is a more generic case of Active Data Guard. One of the limitations of Data Guard is that you must have the same chip set, operating system, and database version for replication. Translations are not done when changes are shipped from primary to standby. You can't for example replicate from a Sparc Server to an X86 server running the same version of the Oracle database. One uses little endian while the other uses big endian to store the bits on disk. Physical replication between these two systems would require a byte translation of every change. Data Guard does not support this but Golden Gate does. Golden Gate allows you to no only ship changes from one database instance to a different chip architecture but a different chip architecture on a different operating system running a different database. Golden Gate was originally crated to replicate between database engines so that you could collect data with SQL Server and replicate the data to an Oracle database or MySQL database so that you could do analytics on a different database engine than your data collection engine. With Golden Gate there is a concept similar to the LNS and RNS but the agents are more intelligent and promote the data type to a master view that can be translated into the target type. When we define an integer it might mean 32 bits on one system but 64 bits on another system. Golden Gate is configured to lead fill from 32 to 64 and truncate from 64 to 32 appropriately based on your use cases and configurations.

To replicate between two systems we basically need an open port from the primary system and the standby system to ship the logs. We also need a landing area to drop the change logs so that the RNS can pick up the changes and apply them. This prohibits Amazon RDS from enabling Data Guard, Active Data Guard, or Golden Gate since you do not have file system access. To run Data Guard in Amazon or Azure you need to deploy the Oracle database on a compute or IaaS instance and purchase the perpetual license with all of the options associated with the configuration. The beautiful thing about Data Guard is that it uses the standard port 1521 to communicate between the servers. There are special commands developed to configure and setup Data Guard that bridge between the two systems. As data is transmitted it is done over port 1521 and redirected to the RNS agent. We can either open up a network port in the cloud or create an ssh tunnel to communicate to our standby in the cloud. The communication works in both directions so we can flip which is primary and which is standby with a command or a push of a button with Enterprise Manager.

The important conversation to have about data protection is not necessarily do I have a copy of it somewhere else. We can do that with RMAN backups or file backups to replicate our data in a safe and secure location. The important conversation to have is how long can we survive without access to the data. If an outage will cost us thousands per minute, we need to look at more than file replication and go with parallel database availability. Data Guard provides this mechanism to keep an active database in another location (on premise or in the cloud) and provides for not only a disaster recovery solution but a way or offloading services from our primary production system. We can break the replication for a few hours and stop the redo apply on the standby while we do a backup. The logs will continue to be shipped just not applied. When the backup is finished we grind through the logs and apply the changes to the standby. We have a window of vulnerability but we have this while we are running backups on our primary system as well. We can now offload the backups to our standby system and let the primary continue to run as needed without interruption. In effect what this does is take all of the small changes that happen throughout the day and ship them to a secondary system so there is a trickle effect on performance. If we do an incremental backup at night we basically block the system while we ship all these changes all at once.

In summary, Data Guard is included with the High Performance Edition of the database and a free part of any on premise Enterprise Edition database. Active Data Guard is included with Extreme Performance Edition of the database and can be matched to synchronize an on premise or in cloud database that is also licensed to run Active Data Guard. There is a ton of reference material available on how Data Guard, Active Data Guard, and Golden Gate works. There are numerous tutorials and examples on how to configure and setup the service. It is important to know that you can use the cloud for this replication and a Dr to the Cloud whitepaper is available detailing how to do this.

Red Samurai Oracle JET Mobile Hybrid App Live on Android

Andrejus Baranovski - Wed, 2016-06-22 10:38
We have implemented Oracle JET mobile hybrid app in less than 30 minutes and deployed it to Android device. This was done as a demo to the customer and could prove Oracle JET offers effective approach for hybrid mobile app development. Development process isn't much complicated with JET once you familiarize yourself with JavaScript development process and learn how to interact with REST services.

Red Samurai app based on JET (using out of the box JET template NavDrawer) is deployed on Android:


Out of the box JET allows to implement form validation, multi select and date entry controls:


Charts are rendered fast, animation is not lagging behind:


JET offers various grouping controls, to arrange content on the page:


Various types of charts can be rendered in responsive layout dashboard and are resized out of the box:


JET allows to build more complex screens, with large amount of UI components:


I'm happy with JET mobile hybrid offering. Stay tuned for more posts on this topic in the future.

Conditional SQL- 6

Jonathan Lewis - Wed, 2016-06-22 08:16

An odd little anomaly showed up on the OTN database forum a few days ago where a query involving a table covered by Oracle Label Security (OLS) seemed to wrap itself into a non-mergeable view when written using traditional Oracle SQL, but allowed for view-merging when accessed through ANSI standard SQL. I don’t know why there’s a difference but it did prompt a thought about non-mergeable views and what I’ve previously called “conditional SQL” – namely SQL which holds a predicate that should have been tested in the client code and not passed to the database engine.

The thought was this – could the database engine decide to do a lot of redundant work if you stuck a silly predicate inside a non-mergeable view: the answer turns out to be yes. Here’s a demonstration I’ve run on 11g and 12c:


rem
rem     Script:         conditional_fail.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2016
rem

create table t2
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level &lt;= 1e4
)
select
        rownum                  id,
        mod(rownum,1e5)         n1,
        lpad('x',100,'x')       padding
from
        generator       v1,
        generator       v2
where
        rownum &lt;= 1e6
;

create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level &lt;= 1e4
)
select
        rownum                  id,
        mod(rownum,1e3)         n1,
        lpad('x',100,'x')       padding
from
        generator       v1,
        generator       v2
where
        rownum &lt;= 1e5
;
-- gather simple statistics (not needed in 12c) 

begin
        dbms_stats.gather_table_stats(
                ownname          =&gt; user,
                tabname          =&gt; 'T1',
                method_opt       =&gt; 'for all columns size 1'
        );
        dbms_stats.gather_table_stats(
                ownname          =&gt; user,
                tabname          =&gt; 'T2',
                method_opt       =&gt; 'for all columns size 1'
        );
end;
/

create index t2_i1 on t2(id);

variable b1 number
variable b2 number

exec :b1 := 1; :b2 := 0

There’s nothing terribly significant about the data, beyond the fact that I’ve got a “small” table and a “large” table that I can use to encourage the optimizer to do a hash join. I’ve also created a couple of bind variables and set them to values that ensure that we can see that b1 is definitely not smaller than b2. So here’s a simple query – with a mergeable inline view in the first instance which is then hinted to make the view non-mergeable.


select
        t1.n1, count(*), sum(v1.n1)
from
        t1,
        (select t2.id, t2.n1 from t2 where :b1 &lt; :b2) v1
where
        t1.n1 = 0
and     v1.id = t1.id
group by
        t1.n1
;

select
        t1.n1, count(*), sum(v1.n1)
from
        t1,
        (select /*+ no_merge */ t2.id, t2.n1 from t2 where :b1 &lt; :b2) v1
where
        t1.n1 = 0
and     v1.id = t1.id
group by
        t1.n1
;

Clearly, for our values of b1 and b2, the query will not return any data. In fact we can go further and say that the presence of the “bind variable predicate” in the inline view either has no effect on the volume of data returned or it eliminates all the data. But the presence of the no_merge hint makes a difference to how much work Oracle does for the “no data” option. Here are the two plans, pulled from the memory of an 11g instance after enabling rowsource execution statistics – first when the view is mergeable:


-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |        |   508 (100)|      0 |00:00:00.01 |
|   1 |  SORT GROUP BY NOSORT          |       |      1 |      1 |   508   (2)|      0 |00:00:00.01 |
|*  2 |   FILTER                       |       |      1 |        |            |      0 |00:00:00.01 |
|   3 |    NESTED LOOPS                |       |      0 |    100 |   508   (2)|      0 |00:00:00.01 |
|   4 |     NESTED LOOPS               |       |      0 |    100 |   508   (2)|      0 |00:00:00.01 |
|*  5 |      TABLE ACCESS FULL         | T1    |      0 |    100 |   208   (4)|      0 |00:00:00.01 |
|*  6 |      INDEX RANGE SCAN          | T2_I1 |      0 |      1 |     2   (0)|      0 |00:00:00.01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| T2    |      0 |      1 |     3   (0)|      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1&lt;:B2)
   5 - filter(&quot;T1&quot;.&quot;N1&quot;=0)
   6 - access(&quot;T2&quot;.&quot;ID&quot;=&quot;T1&quot;.&quot;ID&quot;)

Notice how, despite t2 being the second table in the join, the bind variable predicate has worked its way to the top of the execution plan and execution has terminated after the run-time engine has determined that 1 is not less than zero.

Compare this with the plan when the view is non-mergeable:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |  2300 (100)|      0 |00:00:00.01 |    1599 |       |       |          |
|   1 |  SORT GROUP BY NOSORT  |         |      1 |      1 |  2300   (4)|      0 |00:00:00.01 |    1599 |       |       |          |
|*  2 |   HASH JOIN            |         |      1 |    100 |  2300   (4)|      0 |00:00:00.01 |    1599 |  2061K|  2061K| 1109K (0)|
|   3 |    JOIN FILTER CREATE  | :BF0000 |      1 |    100 |   208   (4)|    100 |00:00:00.01 |    1599 |       |       |          |
|*  4 |     TABLE ACCESS FULL  | T1      |      1 |    100 |   208   (4)|    100 |00:00:00.01 |    1599 |       |       |          |
|   5 |    VIEW                |         |      1 |   1000K|  2072   (3)|      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |     FILTER             |         |      1 |        |            |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |      JOIN FILTER USE   | :BF0000 |      0 |   1000K|  2072   (3)|      0 |00:00:00.01 |       0 |       |       |          |
|*  8 |       TABLE ACCESS FULL| T2      |      0 |   1000K|  2072   (3)|      0 |00:00:00.01 |       0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;V1&quot;.&quot;ID&quot;=&quot;T1&quot;.&quot;ID&quot;)
   4 - filter(&quot;T1&quot;.&quot;N1&quot;=0)
   6 - filter(:B1&lt;:B2)
   8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,&quot;T2&quot;.&quot;ID&quot;))

Thanks to the no_merge hint the bind variable predicate has not been promoted to the top of the plan, so the run-time engine has produced a plan that requires it to access data from table t1 before visiting table t2. In fact the optimizer has decided to do a hash join gathering all the relevant data from t1 and building an in-memory hash table before deciding that 1 is greater than zero and terminating the query.

Be careful if you write SQL that compares bind variables (or other pseudo-constants such as calls to sys_context) with bind variables (etc.); you may find that you’ve managed to produce code that forces the optimizer to do work that it could have avoided if only it had found a way of doing that comparison at the earliest possible moment.

These plans were from 11g, but 12c can behave the same way although, with my specific data set, I had to add the no_push_pred() hint to the query to demonstrate the effect of hash join appearing.

Footnote 1

An interesting side effect of this plan is that it has also allowed a Bloom filter to appear in a serial hash join – not something you’d normally expect to see, so I changed the predicate to :b1 > :b2 to see if the no_merge hint combined with the Bloom filter was faster than merging without the Bloom filter. Here are the two plans, pulled from memory. Running this on 11g I had to hint the hash join when I removed the no_merge hint:


Non-mergeable view - Bloom filter appears
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |      1 |        |  2300 (100)|      1 |00:00:00.12 |   17725 |       |       |          |
|   1 |  SORT GROUP BY NOSORT  |         |      1 |      1 |  2300   (4)|      1 |00:00:00.12 |   17725 |       |       |          |
|*  2 |   HASH JOIN            |         |      1 |    100 |  2300   (4)|    100 |00:00:00.12 |   17725 |  2061K|  2061K| 1106K (0)|
|   3 |    JOIN FILTER CREATE  | :BF0000 |      1 |    100 |   208   (4)|    100 |00:00:00.01 |    1599 |       |       |          |
|*  4 |     TABLE ACCESS FULL  | T1      |      1 |    100 |   208   (4)|    100 |00:00:00.01 |    1599 |       |       |          |
|   5 |    VIEW                |         |      1 |   1000K|  2072   (3)|   1605 |00:00:00.10 |   16126 |       |       |          |
|*  6 |     FILTER             |         |      1 |        |            |   1605 |00:00:00.09 |   16126 |       |       |          |
|   7 |      JOIN FILTER USE   | :BF0000 |      1 |   1000K|  2072   (3)|   1605 |00:00:00.08 |   16126 |       |       |          |
|*  8 |       TABLE ACCESS FULL| T2      |      1 |   1000K|  2072   (3)|   1605 |00:00:00.07 |   16126 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(&quot;V1&quot;.&quot;ID&quot;=&quot;T1&quot;.&quot;ID&quot;)
   4 - filter(&quot;T1&quot;.&quot;N1&quot;=0)
   6 - filter(:B1&gt;B2)
   8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,&quot;T2&quot;.&quot;ID&quot;))

View merging allowed - no Bloom filter
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |  2300 (100)|      1 |00:00:07.56 |   17725 |       |       |          |
|   1 |  SORT GROUP BY NOSORT|      |      1 |      1 |  2300   (4)|      1 |00:00:07.56 |   17725 |       |       |          |
|*  2 |   FILTER             |      |      1 |        |            |    100 |00:00:07.56 |   17725 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |    100 |  2300   (4)|    100 |00:00:07.56 |   17725 |  2061K|  2061K| 1446K (0)|
|*  4 |     TABLE ACCESS FULL| T1   |      1 |    100 |   208   (4)|    100 |00:00:00.01 |    1599 |       |       |          |
|   5 |     TABLE ACCESS FULL| T2   |      1 |   1000K|  2072   (3)|   1000K|00:00:01.94 |   16126 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1&gt;:B2)
   3 - access(&quot;T2&quot;.&quot;ID&quot;=&quot;T1&quot;.&quot;ID&quot;)
   4 - filter(&quot;T1&quot;.&quot;N1&quot;=0)

Things to note – the Bloom filter eliminated all but 1,605 rows from the tablescan before passing them to the hash join operation to probe the hash table; the run time of the query without filtering was 7.56 seconds (!) compared to 0.12 seconds with the Bloom filter. Fortunately I decided that this was too good to be true BEFORE I published the results and decided to re-run the tests with statistics_level set back to typical and found that most of the difference was CPU time spent on collecting rowsource execution statistics. The query with the Bloom filter was still faster, but only just – the times were more like 0.09 seconds vs. 0.12 seconds.

Footnote 2

The source of the problem on OTN was that as well as using OLS the query in question included a user-defined function. Since you can write a user-defined function that “spies” on the data content and uses (e.g.) dbms_output to write data to the terminal this poses a security risk; if a predicate calling that function executed before the security predicate had been tested then your function could output data that your query shouldn’t be able to report. To avoid this security loophole Oracle restricts the way it merges views (unless you set optimizer_secure_view_merging to false). For a more detailed explanation and demonstration of the issues, see this item on Christian Antognini’s blog.

I still don’t know why the ANSI form of the query managed to bypass this issue, but the predicate with the user-defined function was applied as a filter at the very last step of the plan, so perhaps there was something about the transformation Oracle took to get from ANSI syntax to its internal syntax (with cascading lateral views) that made it possible for the optimizer to recognize and eliminate the security threat efficiently.


Pages

Subscribe to Oracle FAQ aggregator