Skip navigation.

Feed aggregator

Thoughts and notes, Thanksgiving weekend 2014

DBMS2 - Sun, 2014-11-30 19:48

I’m taking a few weeks defocused from work, as a kind of grandpaternity leave. That said, the venue for my Dances of Infant Calming is a small-but-nice apartment in San Francisco, so a certain amount of thinking about tech industries is inevitable. I even found time last Tuesday to meet or speak with my clients at WibiData, MemSQL, Cloudera, Citus Data, and MongoDB. And thus:

1. I’ve been sloppy in my terminology around “geo-distribution”, in that I don’t always make it easy to distinguish between:

  • Storing different parts of a database in different geographies, often for reasons of data privacy regulatory compliance.
  • Replicating an entire database into different geographies, often for reasons of latency and/or availability/ disaster recovery,

The latter case can be subdivided further depending on whether multiple copies of the data can accept first writes (aka active-active, multi-master, or multi-active), or whether there’s a clear single master for each part of the database.

What made me think of this was a phone call with MongoDB in which I learned that the limit on number of replicas had been raised from 12 to 50, to support the full-replication/latency-reduction use case.

2. Three years ago I posted about agile (predictive) analytics. One of the points was:

… if you change your offers, prices, ad placement, ad text, ad appearance, call center scripts, or anything else, you immediately gain new information that isn’t well-reflected in your previous models.

Subsequently I’ve been hearing more about predictive experimentation such as bandit testing. WibiData, whose views are influenced by a couple of Very Famous Department Store clients (one of which is Macy’s), thinks experimentation is quite important. And it could be argued that experimentation is one of the simplest and most direct ways to increase the value of your data.

3. I’d further say that a number of developments, trends or possibilities I’m seeing are or could be connected. These include agile and experimental predictive analytics in general, as noted in the previous point, along with: 

Also, the flashiest application I know of for only-moderately-successful KXEN came when one or more large retailers decided to run separate models for each of thousands of stores.

4. MongoDB, the product, has been refactored to support pluggable storage engines. In connection with that, MongoDB does/will ship with two storage engines – the traditional one and a new one from WiredTiger (but not TokuMX). Both will be equally supported by MongoDB, the company, although there surely are some tiers of support that will get bounced back to WiredTiger.

WiredTiger has the same techie principals as SleepyKat – get the wordplay?! – which was Mike Olson’s company before Cloudera. When asked, Mike spoke of those techies in remarkably glowing terms.

I wouldn’t be shocked if WiredTiger wound up playing the role for MongoDB that InnoDB played for MySQL. What I mean is that there were a lot of use cases for which the MySQL/MyISAM combination was insufficiently serious, but InnoDB turned MySQL into a respectable DBMS.

5. Hadoop’s traditional data distribution story goes something like:

  • Data lives on every non-special Hadoop node that does processing.
  • This gives the advantage of parallel data scans.
  • Sometimes data locality works well; sometimes it doesn’t.
  • Of course, if the output of every MapReduce step is persisted to disk, as is the case with Hadoop MapReduce 1, you might create some of your own data locality …
  • … but Hadoop is getting away from that kind of strict, I/O-intensive processing model.

However, Cloudera has noticed that some large enterprises really, really like to have storage separate from processing. Hence its recent partnership to work with EMC Isilon. Other storage partnerships, as well as a better fit with S3/object storage kinds of environments, are sure to follow, but I have no details to offer at this time.

6. Cloudera’s count of Spark users in its customer base is currently around 60. That includes everything from playing around to full production.

7. Things still seem to be going well at MemSQL, but I didn’t press for any details that I would be free to report.

8. Speaking of MemSQL, one would think that at some point something newer would replace Oracle et al. in the general-purpose RDBMS world, much as Unix and Linux grew to overshadow the powerful, secure, reliable, cumbersome IBM mainframe operating systems. On the other hand:

  • IBM blew away its mainframe competitors and had pretty close to a monopoly. But Oracle has some close and somewhat newer competitors in DB2 and Microsoft SQL Server. Therefore …
  • … upstarts have three behemoths to outdo, not just one.
  • MySQL, PostgreSQL and to some extent Sybase are still around as well.

Also, perhaps no replacement will be needed. If we subdivide the database management world into multiple categories including:

  • General-purpose RDBMS.
  • Analytic RDBMS.
  • NoSQL.
  • Non-relational analytic data stores (perhaps Hadoop-based).

it’s not obvious that the general-purpose RDBMS category on its own requires any new entrants to ever supplant the current leaders.

All that said – if any of the current new entrants do pull off the feat, SAP HANA is probably the best (longshot) guess to do so, and MemSQL the second-best.

9. If you’re a PostgreSQL user with performance or scalability concerns, you might want to check what Citus Data is doing.

Categories: Other

How Linux Works, 2nd Edition What Every Superuser Should Know by Brian Ward; No Starch Press

Surachart Opun - Sun, 2014-11-30 08:23
Everyone knows about Linux. It's a popular operating system that is the software on a computer that enables applications and the computer operator to access the devices on the computer to perform desired functions.
You can read more on link what I pointed to it. For me, Linux is a great operating system that I can use it as Desktop and Server. I have used it over ten years. It's very interesting operation system. I have used/worked it with many Open Source Software such as Apache HTTP, Bind, Sendmail, Postfix, Cyrus Imap, Samba and etc. It's operating system that I can play with programming languages as C, PHP, JAVA, Python, Perl and etc. I don't wanna say "too much".
Today, I have a chance to pick up some... a book that was written about Linux - How Linux Works, 2nd Edition What Every Superuser Should Know by Brian Ward. It's a cool book that you can learn about Linux as Starter and Linux Administrator. You could learn some things you have never used, but find in this book. It's fun to learn. However, A book, it's not support every skills in Linux. You will learn
  • How Linux boots, from boot loaders to init implementations (systemd, Upstart, and System V)
  • How the kernel manages devices, device drivers, and processes
  • How networking, interfaces, firewalls, and servers work
  • How development tools work and relate to shared libraries
  • How to write effective shell scripts 
It might not be something too much for learning as you are expecting. However, It 's a good book that you can enjoy to read a book about Linux. There's easy to read and understanding in a book. It's for some people who are starting with Linux and Linux Administrators who are enjoying to learn and want to get something new that can use in their fields.

Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

Security improvements in MySQL 5.7

Yann Neuhaus - Sun, 2014-11-30 05:24

If you have a look on the last mysql 5.7.4 version or later you will probably see that there are several security improvements. The list of added security features and improvements can be seen on the following page: http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

There are three main improvements that are shortly described in this blog:

1. Nonempty plugin column
2. Password lifetime policy
3. mysql_install_db secured

 

Nonempty plugin column

As of MySQL 5.7.2, the server requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value. The following error will occor when trying to connect with user having empty plugin colum:

 

2014-11-30T10:41:04.943384Z 2 [Note] Access denied for user 'sbtest'@'localhost' (using password: YES)

 

If the user is connected when you update the plugin column, MySQL will behave as described below:

1. The user connect to the database:

 

mysql -u sbtest -p --socket=/u00/app/mysql/admin/mysqld5/socket/mysqld5.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.4-m14 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

2. Update column with root user:

mysql> update mysql.user set plugin='' where user='sbtest';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


3. With the root user you flush the privileges:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


4. The following message will appear in the sbtest session:

2014-11-30T22:08:16.652477Z 8 [Warning] User entry 'sbtest'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.



Password Lifetime policy

Since mysql 5.7.4 MySQL enables database administrators to expire account passwords manually and to establish a policy for automatic password expiration. How does it work ?

Two new columns have been added to MySQL:

 

| password_last_changed | timestamp            | YES | | NULL |
| password_lifetime     | smallint(5) unsigned | YES | | NULL |

 

These two columns allow to see when password has been changed and to set a password lifetime.

You can establish a global password policy by setting the variable default_password_lifetime in the option file. By default this variable is set to 360. It means that all users will have to change their password once per year. A value of 0 disables automatic password expiration.

As stated in the documentation, the global password expliration policy can be overridden as desired for individual accounts using the ALTER USER statement.


Example:

mysql> ALTER USER 'sbtest'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.00 sec)


A client session operates in restricted mode if the account password has been expired. In restricted mode, operations performed in the session result in an error until the user issues a SET PASSWORD statement to establish a new account password:

 

mysql> alter user 'sbtest'@'localhost' password expire interval 1 day;

[mysqld5] mysql -u sbtest -p

mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> set password=password('sbtest');
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sysbench           |
+--------------------+
2 rows in set (0.00 sec)

 

To remove the password expiration policy simple use "expire never" as presented in the following example:

mysql>alter user 'sbtest'@'localhost' password expire never;

 

mysql_install_db secured

MySQL deployments installed using mysql_install_db now are secure by default. The following changes have been implemented as the default deployment characteristics:

The installation process creates only a single root account and not anymore anonymous-user accounts.

Example on MySQL 5.7.4:

 

mysql> select user,password, host from mysql.user;

+-------------+-------------------------------------------+-----------+
| user        | password                                  | host      |
+-------------+-------------------------------------------+-----------+
| root        | *7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57 | localhost |
+-------------+-------------------------------------------+-----------+
1 rows in set (0.00 sec)

 

Example on mysql 5.6.20:

 

mysql> select user,password, host from mysql.user;

+-------------+-------------------------------------------+----------------+
| user        | password                                  | host           |
+-------------+-------------------------------------------+----------------+
| root        | *7D2ABFF56C15D67445082FBB4ACD2DCD26C0ED57 | localhost      |
| root        |                                           | thinkpad-t540p |
| root        |                                           | 127.0.0.1      |
| root        |                                           | ::1            |
|             |                                           | localhost      |
|             |                                           | thinkpad-t540p |
+-------------+-------------------------------------------+----------------+
6 rows in set (0.01 sec)

 

After the installation you can also note that there is no "test" database anymore.

Example on MySQL 5.7.4:


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


Example on MySQL 5.6.20:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

 

Conclusion

Oracle did some significative security improvements on MySQL 5.7 version. This improvements will help database administrators to deploy MySQL with a better security level than with previous versions. Some improvements have also been done on the mysql_secure_installation script. The list of improvement and additional features can be seen on the following URL: http://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installation.html

Cloud Adapters for ORACLE Service Cloud (RightNow Cloud 12.1.3) Released

The ORACLE Cloud Adapter for ORACLE Service Cloud (RightNow 12.1.3) reaches general availability! With the ORACLE Cloud Adapters Integrations between Cloud and On-Premise Applications are...

We share our skills to maximize your revenue!
Categories: DBA Blogs

When Oracle resets session statistics

Yann Neuhaus - Sat, 2014-11-29 15:59

During our Oracle 12c New Features workshop I had a very good question about whether the session statistics are reset or not when doing ALTER SESSION SET CONTAINER. My initial thought was that they were not reset because it's the same session (same SID and SERIAL#). But when I'm not 100% sure about something, I test it. And once again, it proves that even the instructor can learn something new when giving a workshop, thanks to the great interaction with the participants.

My test was very simple, querying the 'logon' statistics after an ALTER SESSION SET CONTAINER and I came with the following tweet:

Quizz: How can I be connected and get 0 logons from V$MYSTAT ? pic.twitter.com/YZPQNU8FiH

— Franck Pachot (@FranckPachot) November 26, 2014

Of course that needs more investigation. This is about session statistics. What about session events? and session time model?

SQL> connect / as sysdba
Connected.

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'logon%';

NAME                                     VALUE
----------------------------------- ----------
logons cumulative                            1
logons current                               1

I'm connected to the root container. I generate a bit of activity (using dbms_system.wait_for_event for fake i/o activity):

SQL> exec for i in 1..1e5 loop dbms_system.wait_for_event('db file sequential read', 0 , 0 ); end loop;
PL/SQL procedure successfully completed.

SQL> exec for i in 1..100 loop dbms_system.wait_for_event('db file sequential read', 0 , 1 ); end loop;
PL/SQL procedure successfully completed.

SQL> declare s date:=sysdate; begin loop exit when sysdate>s+60/24/60/60; end loop; end;
  2  /
PL/SQL procedure successfully completed.

and here are my session statistics:

Time Model show 60 seconds of CPU and the 100 seconds of I/O is in the DB time (time model values are in microseconds):

SQL> select stat_name,value from v$sess_time_model where sid=sys_context('userenv','sid') and stat_name in ('DB time','DB CPU');

STAT_NAME                                VALUE
----------------------------------- ----------
DB time                              165084940
DB CPU                                61119000

Session Events shows those 100 seconds of I/O and the longest call took 1 second (values are in centisecond when not with 'MICRO'):

SQL> select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');

EVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT
------------------------------ ----------- ----------------- ----------
Disk file operations I/O                19              1407          0
db file sequential read             100100         104060605        101
SQL*Net message to client               33               123          0
SQL*Net message from client             32             13842          0

Session Statistics where time is in centiseconds show 60 seconds of CPU and 100 + 60 of DB time:

SQL> select name,value from v$mystat join v$statname using(statistic#) where name = 'DB time' or name like 'CPU%' or name like 'SQL*Net%client';

NAME                                     VALUE
----------------------------------- ----------
CPU used when call started                6118
CPU used by this session                  6118
DB time                                  16513
user I/O wait time                       10406
SQL*Net roundtrips to/from client           33

Ok, so that is consistent. And this has to be consistent as we often compare information for those 3 sources.

Let's now change to another container with ALTER SESSION SET CONTAINER:

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set container=PDB1;

Session altered.

and look at the same statistics now:

SQL> select stat_name,value from v$sess_time_model where sid=sys_context('userenv','sid') and stat_name in ('DB time','DB CPU');

STAT_NAME                                VALUE
----------------------------------- ----------
DB time                              103907514
DB CPU                                    2000
SQL> select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');

EVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT
------------------------------ ----------- ----------------- ----------
Disk file operations I/O                25              2084          0
db file sequential read             100100         104060605        101
SQL*Net message to client               42               180          0
SQL*Net message from client             41             17774          0
SQL> select name,value from v$mystat join v$statname using(statistic#) where name = 'DB time' or name like 'CPU%' or name like 'SQL*Net%client';

NAME                                     VALUE
----------------------------------- ----------
CPU used when call started                  11
CPU used by this session                    11
DB time                                      8
user I/O wait time                           0
SQL*Net roundtrips to/from client            5

All the session statistics have been reset and now have low values. And only them: events and time model still show cumulative values from the begining of the session.

So this unfortunately brings inconsistency. Here:

  • session statistic DB time cannot be compared with time model
  • SQL*Net roundtrips cannot be compared to the count of 'SQL*net message to client' event
  • CPU info from session statistics cannot be compared with the time model
  • session statistic wait time cannot be compared with session event time
and this how I can be connected and see 0 logons for my session:

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'logon%';

NAME                                     VALUE
----------------------------------- ----------
logons cumulative                            0
logons current                               0

Now, as we are talking about resetting statistics, there is something else we can do. As you see above the session keeps track of the longest time for each wait event. Here above, the maximum I/O time was 1 second. We can reset that if we want:

SQL> exec dbms_system.kcfrms;
PL/SQL procedure successfully completed.

we still have all cumulative values for the session, but the high water mark of wait event duration has been reset:

SQL> select event,total_waits,time_waited_micro,max_wait from v$session_event where sid=sys_context('userenv','sid');

EVENT                          TOTAL_WAITS TIME_WAITED_MICRO   MAX_WAIT
------------------------------ ----------- ----------------- ----------
Disk file operations I/O                29              2263          0
db file sequential read             100100         104060605          0
SQL*Net message to client               49               209          0

Finally, I made other tests that I do not reproduce here:

If you ALTER SESSION SET CONTAINER with your current container then statistics are not reset.

If you ALTER SESSION SET CURRENT_SCHEMA the statistics are not reset.

So I'll stay with a reconnect when I want to reset all session statistics with wait events and time model as well.

Adaptive Form with Dynamic ADF LOV Value Binding

Andrejus Baranovski - Sat, 2014-11-29 05:25
Previously I had a post about dynamic ADF attribute binding creation and dynamic ADF form generation - Adaptive Form with Dynamic ADF Attribute Value Binding. Blog reader was asking how to generate dynamic ADF LOV binding using similar approach. This is possible and actually documented in Eugene Fedorenko post here - Dynamic LOV binding. I will use the same piece of code to extend my sample application with dynamic ADF LOV binding support.

Here you can download updated sample application - DynamicAttributeBindingApp_v2.zip. This application is updated with LOV definition for JobId attribute in ADF BC:


Once you are generating dynamic components on the UI and getting VO attributes to render, you should not be surprised there will be more attribute entries returned than you can see defined in VO. Additional attributes are for View Accessors, and we don't need them while generating dynamic ADF UI. This can be controlled by checking attribute kind property for attribute definition. If attribute kind is not of rowset kind, we can display it:


There is another method in the sample app, it checks for attribute type. This helps to decide what kind of UI component to render - input text, input date, LOV, etc.:


Dynamic ADF LOV binding is constructed from helper method below, this method is invoked from ADF UI LOV component:


Here is the essential part of the updated sample app - LOV binding creation method. LOV binding is constructed, similar as ADF attribute binding, by pointing to the iterator, LOV server binding name, attribute name and ID:


Dynamic ADF UI components are stamped through ADF UI iterator:


There is a special Facet to support ADF LOV UI component creation. UI component is created by pointing to the helper method, where ADF LOV binding is constructed:


LOV generated dynamically works well on runtime, user can open a list and select a value:


LOV validation also works, try to enter invalid value (not available in the list), user will be prompted to enter existing value instead:

Upgrade Oracle GoldenGate 12.1.2.0.x to 12.1.2.1.x

DBASolved - Fri, 2014-11-28 22:33

Recently, I’ve been engaged in a conversation on the OTN community pages about upgrading Oracle GoldenGate 12c from 12.1.2.0.0 to the 12.1.2.1.0.  During the discussion I mentioned that you can upgrade using the Oracle Universal Installer (OUI) that is now available with Oracle GoldenGate 12c.  The upgrade process I’m going to show you here is an in-place upgrade of Oracle GoldenGate 12c for Oracle Database 12c.

Note: Before doing any upgrades of Oracle GoldenGate, make sure to stop all processes and backup your existing binaries and associated files needed for your environment.

The first thing that needs to be done is to download the 12.1.2.1.0 binaries from either edelivery.oracle.com or My Oracle Support (Image 1).

Image 1:

edelivery_ogg121210.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

After downloading the new binaries to a location where they can be extracted; they need to be unzipped.

 $ unzip ./OracleGoldenGate12121.zip -d ./ggate12121 

Once the binaries are unzipped, lets go into the ./ggate12121 directory to find the runInstaller.  On my system the runInstaller is found at this location.

/media/sf_Oracle/GGATE/GG12c/ggate12121/fbo_ggs_Linux_x64_shiphome/Disk1

Before running the runInstaller, I need to make sure that all my Oracle GoldenGate processes are down.  Since this is on my target (test) system, that means the manager, all replicats and collector processes should be stopped.  A simple “ps -ef” command can help identify what is running.

 $ ps -ef | grep dirpm oracle 2401 1 0 22:47 ? 00:00:00 ./mgr PARAMFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirprm/MGR.prm REPORTFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirrpt/MGR.rpt PROCESSID MGR USESUBDIRS oracle 2407 2401 1 22:47 ? 00:00:02 /opt/app/oracle/product/12.1.2/oggcore_1/replicat PARAMFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirprm/REP.prm REPORTFILE /opt/app/oracle/product/12.1.2/oggcore_1/dirrpt/REP.rpt PROCESSID REP USESUBDIRS $ ps -ef | grep server oracle    2486  1848  0 22:51 pts/0    00:00:00 grep server 

After identifying the processes, they need to be stopped from within GGSCI.

 Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54 Operating system character set identified as UTF-8. Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. GGSCI (ggtest2.acme.com) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING <br />REPLICAT RUNNING REP 45:06:32 00:04:35 GGSCI (ggtest2.acme.com) 2> stop er * Sending STOP request to REPLICAT REP ... Request processed. GGSCI (ggtest2.acme.com) 3> stop mgr ! Sending STOP request to MANAGER ... Request processed.<br />Manager stopped. GGSCI (ggtest2.acme.com) 9> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED <br />REPLICAT STOPPED REP 45:14:08 00:00:05 

To verify that everything has been stopped, the “ps -ef” command can be ran to verify. After verifying that everything is stopped, the runInstaller can be used to start the OUI for the upgrade (Image 2).

Image 2:

ogg_upgrade12121.png

 

 

 

 

 

 

 

Notice that there are 5 steps to the OUI.  Also notice that there is not an “upgrade” option.  Not to worry, we can still perform the upgrade.  Since this is an upgrade of Oracle GoldenGate 12c for Oracle Database 12c, make sure to select the option for Oracle Database 12c (default), then click the next button.

On the Installation screen, the location of the existing binaries need to be selected from the drop down box for Software Location.  In the example, the location is /opt/app/oracle/product/12.1.2/oggcore_1.  Also notice in image 3, that I do not want the manager process to start.  After making sure everything is correct and as expected, click Next.

Note: The information on this screen is read from the oraInventory files.  Make sure you know where the oraInventory located and set as needed.

Image 3:

NewImage

 

 

 

 

 

 

 

 

 

 

The wizard now moves to the Summary screen (Image 4).  On this screen, the typical information is seen. Click Install when ready.

Image 4:

ogg_upgrade12121_4.png

 

 

 

 

 

 

 

 

Image 5 shows the progress of the install/upgrade.

Image 5:

ogg_upgrade12121_5.png

 

 

 

 

 

 

 

 

 

 

 

After the install/upgrade is done (Image 6), we get a nice message saying that it was successful.

Image 6:

ogg_upgrade12121_6.png

 

 

 

 

 

 

 

Once the upgrade is complete, then the Oracle GoldenGate processes (manger, replicats) can be restarted.  Notice that the version of Oracle GoldenGate 12c that is running now is 12.1.2.1.0

 [oracle@ggtest2 Disk1]$ cd /opt/app/oracle/product/12.1.2/oggcore_1 [oracle@ggtest2 oggcore_1]$ pwd /opt/app/oracle/product/12.1.2/oggcore_1 [oracle@ggtest2 oggcore_1]$ . oraenv ORACLE_SID = [oracle] ? remote12c The Oracle base has been set to /opt/app/oracle [oracle@ggtest2 oggcore_1]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.</p>
<p>GGSCI (ggtest2.acme.com) 1&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED &lt;br /&gt;REPLICAT STOPPED REP 45:14:08 00:24:08 GGSCI (ggtest2.acme.com) 2&gt; start mgr Manager started. GGSCI (ggtest2.acme.com) 3&gt; start replicat rep Sending START request to MANAGER ... REPLICAT REP starting GGSCI (ggtest2.acme.com) 4&gt; info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP 45:39:08 00:00:01 

As you can tell the upgrade from Oracle GoldenGate 12c (12.1.2.0.0) to Oracle GoldenGate 12c (12.1.2.1.0) can be completed using the Oracle Universal Installer that now comes with Oracle GoldenGate 12c.  I wish Oracle would give an option for an upgrade in the OUI, it would cut down on some confusion when it comes to upgrades with Oracle GoldenGate using the OUI.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

Log Buffer #399, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-11-28 09:52

This Log Buffer Edition brings some freshly picked blog posts right out of the gardens of Oracle, SQL Server and MySQL.

Oracle:

Suppose you have a process A that you want to run faster. This process doesn’t commit (much), so it doesn’t wait on log file sync. However, there is another multi-threaded process, B, that commits very frequently, and spends a lot of time on “log file sync”.

New Solaris 11 CPU package to install and track CVE security fixes.

Upgrade/Migrate/Consolidate to Oracle 12c and Parallel Multitenant Upgrade Internals.

CVE metadata in Solaris IPS packages for improved Compliance reporting.

Why Your Supply Chain Needs Science?

SQL Server:

SQL Server expert David Poole discusses how teams can work together and share templates in Management Studio.

Explaining The SQL Server 2014 Analysis Services Data Mining Model Lift Chart.

By this stage, you should be familiar with the basics of SQL Server indexes. We’ve discussed what an Index actually is, as well as some of the most common types you’re likely to encounter. Now that we’ve seen some simple examples of how Indexes can be useful, we’re going to delve deeper into nonclustered indexes, as we’ll see how they can improve the performance of more complex queries.

The Project Deployment Model introduced in SSIS 2012 speeds up the deployment of database projects in which there may be hundreds of SSIS packages per project. Not only that, but deployments can be configured differently for each environments such as test and staging, and there are now ways of monitoring the status and performance of packages and of versioning the SSIS Catalog.

A technique to deal with lack of metadata for stored procedures when used with SSIS.

MySQL:

How to mess up your data using ONE command in MySQL/Galera.

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

Nasty MySQL Replication Bugs that Affect Upgrade to 5.6.

(More) Secure local passwords in MySQL 5.6 and up.

Alternatives for chunking bulk deletes in common_schema.

Categories: DBA Blogs

Line Numbers

Jonathan Lewis - Fri, 2014-11-28 06:49

One of the presentations I went to at the DOAG conference earlier on this month was called “PL/SQL Tuning, finding the perf. bottleneck with hierarchical profiler” by Radu Parvu from Finland. If you do a lot of PL/SQL programming and haven’t noticed the dbms_hprof package yet make sure you take a good look at it.

A peripheral question that came up at the end of the session asked about problems with line numbers in pl/sql procedures; why, when you get a run-time error, does the reported line number sometimes look wrong, and how do you find the right line. I can answer (or give at least one reason for) the first part, but not the second part; Julian Dontcheff had an answer for the second bit, but unfortunately I failed to take a note of it.

Here’s the SQL to create, run and list a very simple (and silly) procedure.


define m_value = 3
set timing on

create or replace procedure silly
as
        m_n number;
begin
        for i in 1..1000000 loop
                m_n := exp(sqrt(ln(&m_value)));
        end loop;
end;
/

execute silly

list

Here’s the output I got from running this, and the thing I want you to note is the time it takes to run, and the line number reported for the assignment:


Procedure created.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
  1  create or replace procedure silly
  2  as
  3          m_n number;
  4  begin
  5          for i in 1..1000000 loop
  6                  m_n := exp(sqrt(ln(&m_value)));
  7          end loop;
  8* end;

It seems to take my instance o.oo seconds to perform 1,000,000 evaluations of the exponent of the square root of the natural logarithm of 3 at line 6 of the code. But let’s make two changes; first, let’s try that with the value -1 (which is a little careless if you know your logarithms).


Procedure created.

Elapsed: 00:00:00.02
BEGIN silly; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TEST_USER.SILLY", line 5
ORA-06512: at line 1

Notice that the 6502 error is reported at line 5, not line 6.

Now let’s go back to the value 3, but start the script with the command: alter session set plsql_optimize_level = 0; (the default level is 2, the range is 0 to 3):


Session altered.

Elapsed: 00:00:00.00

Procedure created.

Elapsed: 00:00:00.01
BEGIN silly; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "TEST_USER.SILLY", line 6
ORA-06512: at line 1

Reducing the pl/sql optimisation level to zero (or one) results in the the error message reporting the problem at line 6 – which matches our original code. So let’s check the effect of running the code at level zero with a valid number for the input.


Session altered.

Elapsed: 00:00:00.00

Procedure created.

Elapsed: 00:00:00.02

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.56


The run time for our pl/sql call has gone up from 0.00 seconds to 24.56 seconds.

Like all good compilers the pl/sql compiler had recognised (at level 2) that we were assigning a constant inside a loop, so it had (effectively) rewritten our code to move the assignment outside the loop, effectively swapping lines 6 and 5, storing the change in the “object” level code, but not in the database copy of the source. The benefit we get (from the default settings) is a saving of 25 seconds of CPU, the (small) penalty we pay is that the lines reported for run-time errors aren’t always going to identify the text that caused the problem.


Windows Server 2012 R2: solving .NET Framework 3.5 installation problems

Yann Neuhaus - Thu, 2014-11-27 20:57

I faced a problem at a customer site last week when I tried to install the .NET Framework 3.5 - a prerequisite for installing SQL Server 2012 on a Windows Server 2012 R2. I opened the Server Manager and then navigated to the Manage, Add Roles and Features section:

 

b2ap3_thumbnail_pic01.jpg

 

I selected the .NET Framework 3.5 Features option:

 

b2ap3_thumbnail_pic02.jpg

 

I specified an alternate source path:

 

b2ap3_thumbnail_pic03.jpg

 

... and surprise! Even though an ISO of Windows Server 2012 R2 was mapped to my D: drive, the installation failed with this strange error: "The source file could not be found..."

After some investigations, I found that this problem is quite common and that Microsoft has published a fix ... which unfortunately does not work for me!

I tried the same installation with different ways: command prompt, PowerShel l... but absolutely NO RESULT.

I finally decided to open a PowerShell console to check the Windows Features available on my server with the cmdlet Get-WindowsFeature:

 

b2ap3_thumbnail_Pic1.jpg

 

Strangely, the status of the .NET Framework 3.5 is not showing "Available", but "Removed"!

 

b2ap3_thumbnail_Pic2.jpg

 

So, how do I change this state from removed to available?

After some investigations and after having tried some fixes provided by persons who faced to the same problem as me, I finally found the Standalone Offline Installer tool that solved my problem by enabling the .NET Framework 3.5 (many thanks to Abbodi1406).

I downloaded this exe file and executed it on my server.

An installer screen appeared:

 

b2ap3_thumbnail_pic4.jpg

 

After clicking on the Next button, a command prompt screen appeared which showed the completion state of the process.

pic6.jpg

 

As soon as the process was finished, I went back to my PowerShell screen to check if my .NET Framework 3.5 is now available - by running my PowerShell cmdlet Get-WindowsFeature:

 

b2ap3_thumbnail_pic10.jpg

 

The .NET Framework 3.5 now was available and I as able to restart the installation process from the beginning by navigating to the server manager, selecting the concerned feature and giving the alternate source path.

pic8.jpg

I finally succeded in installing my .NET Framework 3.5!

I hope that my blog post will help some of you to resolve this installation problem ;-)

How to *really* send a script to the background

Laurent Schneider - Thu, 2014-11-27 10:55

Let’s check this small script

foo.sh


#!/bin/sh
echo foo.1:`date` | tee $HOME/tmp/foo.txt
sleep 3
echo foo.2:`date` | tee -a $HOME/tmp/foo.txt


$ $HOME/tmp/foo.sh
foo.1:Thu Nov 27 17:34:53 CET 2014
foo.2:Thu Nov 27 17:34:56 CET 2014

Very obvious, I write to the console, wait three seconds, then write to the console.

Ok, let’s take another script that would call this script in the background using &

bar.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh &
echo bar.2:`date`


$ $HOME/tmp/bar.sh
bar.1:Thu Nov 27 17:36:32 CET 2014
bar.2:Thu Nov 27 17:36:32 CET 2014
$ 
foo.1:Thu Nov 27 17:36:32 CET 2014
foo.2:Thu Nov 27 17:36:35 CET 2014

bar is printing the date, calling foo in the background, then printing the date, then it returns to you, and foo is still running.

BUT this is only in a relative background …

Let’s try this


$ time $HOME/tmp/bar.sh > /dev/null

real    0m0.01s
user    0m0.00s
sys     0m0.00s

So it takes no time to run bar you believe ?

Let’s try, for instance, over ssh (or cron or whatever)


$ time ssh localhost $HOME/tmp/bar.sh > /dev/null
real    0m3.81s
user    0m0.01s
sys     0m0.01s

running bar suddenly waits 3 seconds for foo to finish.

To be sure the script is sent to the farest background, you need to close the file descriptors, stdin, stdout, stderr

I rewrote it as

baz.sh


#!/bin/sh
echo bar.1:`date`
$HOME/tmp/foo.sh <&- >&- 2>&- &
echo bar.2:`date`


$ time ssh localhost $HOME/tmp/baz.sh >/dev/null
real    0m0.44s
user    0m0.00s
sys     0m0.00s

Now the script baz is immediately finished and does not wait for foo to complete

The mess that is fast-refresh join-only Materialized Views

Dominic Brooks - Thu, 2014-11-27 09:23

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only materialized view?
  2. Show me it working
  3. What’s it done to my commit time?
  4. How can this be avoided this with _mv_refresh_use_stats?
  5. Is it any different if I use MLOG stats?
  6. Why might I not want to mess with _mv_use_refresh_stats?


First, why am I looking at a fast refresh, on-commit join-only materialized view.

I have got two datasets which only produce a small resultset when they are joined.
But neither is, on their own, highly selective.

The model below might not be 100% representative of my real-world situation.
I’ve tried to sanitise and simplify but as a result I’ve then had to bump up the volumes a bit to make the point.

drop table t1;
drop table t2;

create table t1
as
select rownum oid
,      case when rownum <= 1000000 then 'Y' else 'N' end flag
,      rownum oid_t2
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t1_1 on t1 (oid);
create index i_t1_2 on t1(flag);
create index i_t1_3 on t1(oid_t2);

create table t2
as
select rownum oid
,      case when rownum >= 999901 then 'Y' else 'N' end flag
,      rpad('X',200,'X') padding
from   dual
connect by rownum <= 10000000
order by dbms_random.value;

create unique index i_t2_1 on t2 (oid);
create index i_t2_2 on t2(flag);

Currently, I can crunch my data in a few seconds:

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Gives

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:15.91 |     606K|    303K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:15.91 |     606K|    303K|   238M|  7667K|  292M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.54 |     303K|      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |      0 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:09.78 |     303K|    303K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But I want to query these tables quite frequently and preferably I want this driving logic to be subsecond.
And there will have reasonable inserts and updates incoming at various times.

So, one of the better options is a join-only materialized view.


So, let’s get going:

create materialized view log on t1 with rowid;
create materialized view log on t2 with rowid;

create materialized view mv_t1_t2
refresh fast on commit
enable query rewrite
as
select t1.rowid   t1_rowid
,      t1.oid     t1_oid
,      t1.flag    t1_flag
,      t1.oid_t2  t1_oid_t2
,      t1.padding t1_padding
,      t2.rowid   t2_rowid
,      t2.oid     t2_oid
,      t2.flag    t2_flag
,      t2.padding t2_padding
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

create index i_mv_t1_t2_1 on mv_t1_t2(t1_rowid);
create index i_mv_t1_t2_2 on mv_t1_t2(t2_rowid);

Tangent alert!
Now, if I wasn’t going to go off on this tangent because the article will be long enough anyway but when running up the article, a number of questions about
“hy was this not being rewritten?” came up so, we might as well cover more bases.

So, if we run our SELECT now, what happens?

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    100 |00:00:08.22 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    100 |00:00:08.22 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.69 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------  

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

Bother!

We could go digging around but let’s try the easy way out – DBMS_MVIEW.EXPLAIN_REWRITE

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message
  measure_in_msg        VARCHAR2(30),   -- Measure in current message
  join_back_tbl         VARCHAR2(30),   -- Join back table in message
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used
  reerved2              VARCHAR2(10))   -- currently not used;

DECLARE
l_sql CLOB :=
q'{select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y'}';
BEGIN
 DBMS_MVIEW.EXPLAIN_REWRITE(l_sql,'MV_T1_T2','mysql');
END;
/

select message from rewrite_table;
MESSAGE
-------------------------------------
QSM-01150: query did not rewrite
QSM-01001: query rewrite not enabled

Yep – easy one.
Let’s set query_rewrite_enabled to true (would be system level if this was not just a test/demo) and repeat.

alter session set query_rewrite_enabled = true;
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |    101 |00:00:00.01 |      16 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |      1 |    101 |    101 |00:00:00.01 |      16 |
--------------------------------------------------------------------------------------------------

Great – I have my subsecond response time.

But what about updates to the data?

First of all, for completeness, let’s observe what happens when we make an uncommitted change.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated
select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.27 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.27 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.63 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.65 |     303K|       |       |          |
---------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------   

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

I’ve lost my usage of the MV.
This is expected.
Why?
DBMS_MVIEW.EXPLAIN_REWRITE tells us:

MESSAGE
------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01279: query rewrite not possible because DML operation occurred on a table referenced by materialized view MV_T1_T2

We can’t use the materialized view because it’s stale.
To use it we have to consider whether we want to run with query_rewrite_integrity set to stale_tolerated.
In this case, not.

If I continue to leave the update uncommitted and check another session, the it continues to use the MV.

But, another tangent!!
In that other session, if I set statistics_level to all, then it won’t use the MV and DBMS_MVIEW.EXPLAIN_REWRITE can’t tell me why.
I’m going to conveniently leave that investigation for another time but just show the evidence:

alter session set statistics_level = all;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |    101 |00:00:08.10 |     606K|       |       |          |
|*  1 |  HASH JOIN                   |        |      1 |    860K|    101 |00:00:08.10 |     606K|   238M|  7667K|  260M (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |      1 |    860K|   1000K|00:00:01.49 |     303K|       |       |          |
|*  3 |    INDEX RANGE SCAN          | I_T1_2 |      1 |    860K|   1000K|00:00:00.22 |    1814 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T2     |      1 |   8275K|   9000K|00:00:02.72 |     303K|       |       |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                                                         

   1 - access("T1"."OID_T2"="T2"."OID")
   3 - access("T1"."FLAG"='Y')
   4 - filter("T2"."FLAG"='Y')

But

alter session set statistics_level = typical;
alter session set query_rewrite_enabled = true;

select *
from   t1
,      t2
where  t1.flag   = 'Y'
and    t1.oid_t2 = t2.oid
and    t2.flag   = 'Y';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |          |        |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MV_T1_T2 |    101 |
----------------------------------------------------------   

Note
-----
   - Warning: basic plan statistics not available.....

So… anyway… we have our fast-refresh on commit materialized view.


What happens when we commit my previous change?

set timing on
update t1 set flag = 'Y' where oid = 1000001;

1 rows updated.
Elapsed: 00:00:00.029

commit

committed.
Elapsed: 00:00:02.098

Gadzooks! My COMMIT now takes two seconds!

At this point, if you read a lot of technical articles, you might get lucky and have that vague sense of nagging familiarity about having read about this at some point…

Alberto Dell’Era has an excellent set of articles which helps understand what is going on here and what we might do about it.

If we trace my session, we can find out what is contributing to this unpleasant two seconds.
I’m going to cheat, flush the shared pool, do the update and commit and check v$sql
Here is a summary of the significant contributing sql snippets.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
dt1mmbxbp6uk7            1                          1                 1.9356 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE(
5qah9xjyhapg1            0       828077931          1               0.010237 /* MV_REFRESH (DEL) */ DELETE FROM "E668983_DBA"."MV_T1_T2" SNA$ WHERE "T1_ROWID
4agmycb29dd1p            0                          1               0.001747 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000589 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000518 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmlty

A couple of interesting observations.
First is that we can see the XID$$ mechanism which Alberto mentions in his articles linked to above.

Second is that the INSERT is the major contributor to our commit time, followed by the DELETE.
We shall focus on these.

These are the prettified statements:

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM   ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
         FROM "DOM"."T1" "MAS$"
         WHERE ROWID IN (SELECT  /*+ HASH_SJ */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                         FROM "DOM"."MLOG$_T1" "MAS$"
                         WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE  "JV$"."FLAG"='Y'
AND    "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y';

With plan:

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL         |            |      1 |        |      0 |00:00:01.92 |     303K|       |       |          |
|   2 |   NESTED LOOPS                   |            |      1 |        |      1 |00:00:01.92 |     303K|       |       |          |
|   3 |    NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|   4 |     VIEW                         |            |      1 |      1 |      1 |00:00:01.92 |     303K|       |       |          |
|*  5 |      HASH JOIN RIGHT SEMI        |            |      1 |      1 |      1 |00:00:01.92 |     303K|   832K|   832K|  387K (0)|
|   6 |       TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  7 |        INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID| T1         |      1 |    860K|   1000K|00:00:01.58 |     303K|       |       |          |
|*  9 |        INDEX RANGE SCAN          | I_T1_2     |      1 |    860K|   1000K|00:00:00.21 |    1814 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN            | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------ 

Predicate Information (identified by operation id):
--------------------------------------------------- 

   5 - access(ROWID=CHARTOROWID("MAS$"."M_ROW$$"))
   7 - access("MAS$"."XID$$"=:1)
   9 - access("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE
FROM   "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  HASH_SJ  */ *
                      FROM   (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"
                              WHERE "MAS$".XID$$ = :1 )MAS$)
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|*  2 |   HASH JOIN SEMI              |              |      1 |    101 |      1 |00:00:00.01 |       3 |   963K|   963K| 1252K (0)|
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------  

   2 - access("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

If you have read Alberto’s blogs then you will notice the problematic HASH JOIN RIGHT SEMI in the INSERT
And you will also be familiar with a couple of solutions which now present themselves.

It’s great that there are known workarounds to this problem but, as I hope to demonstrate, the fact that there are at least three separate codepaths through the fast refresh mechanism worries me particularly when we see that
at least one of these alternative code paths causes bugs with other variations on the MV refresh mechanism. What a mess!!


First solution to the problem is paramter _mv_refresh_use_stats.
Repeat update & commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

set timing on
update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.035

commit;
committed.
Elapsed: 00:00:00.174

That’s better – 100ms for my commit.

What do our INSERT and DELETE statements look like now?
Note the different SQL IDs – these are completely different statements.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
av53npjd112vx            1       374625119          1               0.013106 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
5a6ugwh5v1j5x            0       466548951          1               0.009088 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
gpk46p11kbp3d            0      1735585849          1               0.001665 SELECT OLD_NEW$$, COUNT(*)  FROM "DOM"."MLOG$_T1"   WHERE SNAPTIME$$ > :1 AND SN
4agmycb29dd1p            0                          1               0.001615 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1               0.000538 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000515 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$     

For both the INSERT and the DELETE, we’ve lost the HASH_SJ hint and we’ve gained a CARDINALITY hint and a NO_SEMIJOIN.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*
       FROM   "DOM"."T1" "MAS$"
       WHERE  ROWID IN (SELECT /*+ CARDINALITY(MAS$ 0)  NO_SEMIJOIN */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                        FROM   "DOM"."MLOG$_T1" "MAS$"
                        WHERE  "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y'
AND   "JV$"."OID_T2"="MAS$0"."OID"
AND    "MAS$0"."FLAG"='Y'

Giving plan:

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL           |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                     |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                    |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                           |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                  |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       VIEW                         | VW_NSO_1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   7 |        SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   8 |         TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  9 |          INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |       TABLE ACCESS BY USER ROWID   | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 11 |     INDEX UNIQUE SCAN              | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID     | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("MAS$"."XID$$"=:1)
  10 - filter("MAS$"."FLAG"='Y')
  11 - access("JV$"."OID_T2"="MAS$0"."OID")
  12 - filter("MAS$0"."FLAG"='Y')
/* MV_REFRESH (DEL) */
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  NO_SEMIJOIN  */ *
                      FROM   (SELECT  /*+ CARDINALITY(MAS$ 0) */  CHARTOROWID("MAS$"."M_ROW$$") RID$
                              FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)

with plan:

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT                |              |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   1 |  DELETE                         | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |       9 |       |       |          |
|   2 |   NESTED LOOPS                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                         | VW_NSO_1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   4 |     SORT UNIQUE                 |              |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_MLOG$_T1   |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
|*  7 |    INDEX RANGE SCAN             | I_MV_T1_T2_1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("MAS$"."XID$$"=:1)
   7 - access("T1_ROWID"="RID$")

There’s no doubt that that is faster for our specific circumstances.

What about the second workaround available?

Let’s reset “_mv_refresh_use_stats” and look at locking stats on the MV logs.

alter session set "_mv_refresh_use_stats" = false;

session SET altered.

begin
  dbms_stats.gather_table_stats(USER,'MLOG$_T1');
  dbms_stats.gather_table_stats(USER,'MLOG$_T2');
  dbms_stats.lock_table_stats(USER,'MLOG$_T1');
  dbms_stats.lock_table_stats(USER,'MLOG$_T2');
end;
/

anonymous block completed

select table_name, num_rows from user_tables where table_name in ('MLOG$_T1','MLOG$_T2');

TABLE_NAME                     NUM_ROWS
------------------------------ --------
MLOG$_T1                              0
MLOG$_T2                              0

What happens then?

update t1 set flag = 'Y' where oid = 1000001;

Elapsed: 00:00:00.026

commit;

committed.
Elapsed: 00:00:00.169

Very similar to the effects of “mv_refresh_use_stats”, but exactly the same? No.

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS ELAPSED_TIME/1000/1000 SQL_FULLTEXT
------------- ------------ --------------- ---------- ---------------------- --------------------------------------------------------------------------------
69xq38c4ts0j2            1       190791364          1               0.013053 /* MV_REFRESH (INS) */ INSERT INTO "DOM"."MV_T1_T2" SELECT /*+ NO_MERGE("JV$") *
dzzb43k6h3vhs            0                          1               0.009172 /* MV_REFRESH (DEL) */ DELETE FROM "DOM"."MV_T1_T2" SNA$ WHERE "T1_ROWID" IN (SE
4agmycb29dd1p            0                          1               0.000924 delete from "DOM"."MLOG$_T2" where xid$$ = :1
f8wzsn9dzcusb            0      1745227344          1                0.00064 delete from "DOM"."MLOG$_T1" where xid$$ = :1
ar6vnyxkss2kq            0       375101422          1               0.000632 select dmltype$$ from "DOM"."MLOG$_T1"  where xid$$ = :1  group by dmltype$$

We’ve got completely different SQL IDs again.

In both INSERT and DELETE we’ve lost any hint to do or avoid a semi-join and there’s no CARDINALITY hint.

/* MV_REFRESH (INS) */
INSERT INTO "DOM"."MV_T1_T2"
SELECT /*+ NO_MERGE("JV$") */ "JV$"."RID$","JV$"."OID","JV$"."FLAG","JV$"."OID_T2","JV$"."PADDING","MAS$0".ROWID,"MAS$0"."OID","MAS$0"."FLAG","MAS$0"."PADDING"
FROM ( SELECT "MAS$"."ROWID" "RID$"  ,  "MAS$".*  FROM "DOM"."T1" "MAS$"
       WHERE ROWID IN (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                       FROM "DOM"."MLOG$_T1" "MAS$"
                       WHERE "MAS$".XID$$ = :1 )) "JV$", "T2" AS OF SNAPSHOT(:B_SCN)  "MAS$0"
WHERE "JV$"."FLAG"='Y' AND "JV$"."OID_T2"="MAS$0"."OID" AND "MAS$0"."FLAG"='Y'
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL          |            |      1 |        |      0 |00:00:00.01 |      15 |       |       |          |
|   2 |   NESTED LOOPS                    |            |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
|   3 |    NESTED LOOPS                   |            |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
|   4 |     VIEW                          |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   5 |      NESTED LOOPS                 |            |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|   6 |       SORT UNIQUE                 |            |      1 |      1 |      1 |00:00:00.01 |       2 | 73728 | 73728 |          |
|   7 |        TABLE ACCESS BY INDEX ROWID| MLOG$_T1   |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  8 |         INDEX RANGE SCAN          | I_MLOG$_T1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*  9 |       TABLE ACCESS BY USER ROWID  | T1         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 10 |     INDEX UNIQUE SCAN             | I_T2_1     |      1 |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | T2         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("MAS$"."XID$$"=:1)
   9 - filter("MAS$"."FLAG"='Y')
  10 - access("JV$"."OID_T2"="MAS$0"."OID")
  11 - filter("MAS$0"."FLAG"='Y')
DELETE FROM "DOM"."MV_T1_T2" SNA$
WHERE  "T1_ROWID" IN (SELECT /*+ NO_MERGE  */ *
                      FROM (SELECT  CHARTOROWID("MAS$"."M_ROW$$") RID$
                            FROM "DOM"."MLOG$_T1" "MAS$"   WHERE "MAS$".XID$$ = :1 )MAS$)
--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT              |              |      1 |        |      0 |00:00:00.01 |      13 |
|   1 |  DELETE                       | MV_T1_T2     |      1 |        |      0 |00:00:00.01 |      13 |
|   2 |   NESTED LOOPS SEMI           |              |      1 |    101 |      1 |00:00:00.01 |       5 |
|   3 |    INDEX FULL SCAN            | I_MV_T1_T2_1 |      1 |    101 |    101 |00:00:00.01 |       1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| MLOG$_T1     |    101 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |     INDEX RANGE SCAN          | I_MLOG$_T1   |    101 |      1 |    101 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T1_ROWID"=CHARTOROWID("MAS$"."M_ROW$$"))
   5 - access("MAS$"."XID$$"=:1)

So, to briefly summarise the above, we have at least three clear codepaths through the fast refresh mechanism which result in completely different internal SQL being generated.
Multiple code paths are added complexity when it comes to combining other features, upgrades, testing, etc

It’s then no great surprise when such multiple codepaths lead to other bugs.

So which approach are we meant to do?

Should we just take the default longer commit on the chin?

I’m not sure.

But I can come up with an illustration of why we should think twice about doing down the “_mv_refresh_use_stats” approach.
Firstly it’s an underscore parameter so we should really get Oracle Support approval before setting it (which means a fair bit of back and forth until you get someone who knows what you’re going on about)


Secondly, we can quickly find some bugs around the usage of this parameter with the newer COMMIT SCN (also covered in Alberto’s blogs above)

drop materialized view log on t1;
drop materialized view log on t2;
create materialized view log on t1 with rowid, commit scn;
create materialized view log on t2 with rowid, commit scn;

drop materialized view mv_t1_t2;
create materialized view mv_t1_t2...

Then go back to our update and commit:

alter session set "_mv_refresh_use_stats" = true;

session SET altered.

update t1 set flag = 'Y' where oid = 1000001;

1 rows updated

commit;

SQL Error: ORA-12008: error in materialized view refresh path
ORA-00904: "SNAPTIME$$": invalid identifier
12008. 00000 -  "error in materialized view refresh path"
*Cause:    Table SNAP$_<mview_name> reads rows from the view
           MVIEW$_<mview_name>, which is a view on the master table
           (the master may be at a remote site).  Any
           error in this path will cause this error at refresh time.
           For fast refreshes, the table <master_owner>.MLOG$_<master>
           is also referenced.
*Action:   Examine the other messages on the stack to find the problem.
           See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>,
           <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink>
           still exist.

We don’t actually need COMMIT SCN in our ON-COMMIT MV because we’re using the XID.
It’s a newer mechanism for FAST REFRESH ON DEMAND

I know that I don’t need COMMIT SCN in my case above but the fact that it’s so easy to find an issue means that I’m reluctant to do down the parameter route.

Note that there are quite a few bug notes around this in Oracle Support.
A lot of the advice is conflicting, not least because the behaviour of “_mv_refresh_use_stats” changes mid 10.1 something.

At the moment, I think I’m going to go with the locked stats on the MLOGs but it’s worrying.

What a mess!

References:
Lots of Oracle doc ids…


Integrating Oracle MAF Application With Social Services

v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} Nowadays we widely use social networks every...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle AVDF - Database Firewall Policies

Yann Neuhaus - Thu, 2014-11-27 04:13

The successful deployment of a Database Firewall depends on an effective policy. Oracle AVDF includes preconfigured firewall policies as described in the Firewall Policy page in the Policy tab of the Audit Vault Server console.

These include policies that log all SQL statements, or log only unique SQL statements. In addition, the Database Firewall policy editor enables you to design your own policies quickly and efficiently.

Policy rules can depend on any combination of the SQL statement type, name of the database user, IP address of the database client, operating system user name, client program name, or any exceptions you specify.

 

First policy and global concept

1. Log in to the Audit Vault Server console as an auditor, and click on the Policy tab:

 

001_20141122-190809_1.png

 

2. Under the Policy menu, click Firewall Policy.

3. Click Create Policy.

The Create Policy dialog appears. Select the Database Type from the drop-down list (choice between IBM DB2, Microsoft SQL Server, MySQL, Oracle Database, Sybase ASE, Sybase SQL Anywhere), Enter a Policy Name and Optionally, enter a Description:

 

002.png

 

3. Click on “Create”. The new policy is created, and the policy's Overview page appears:

 

003.png

 

When you create a new policy, or click an existing policy name in the Firewall Policies page, that policy's Overview page appears. This page shows the policy rules that are being applied to the statement types (clusters) being monitored by the Database Firewall, as well as exceptions and other rules that may apply.

The policy's Overview page is divided into these sub-sections:

  • Exception Rules - Lists exceptions you have created. The rules that you have assigned to SQL statement clusters will not apply to these exceptions. You can move the rules up or down in the list. The rules are evaluated in the order listed.
  • Analyzed SQL - Displays the number of SQL statement clusters for which you have defined policy rules, and their policy actions (such as Warn or Block).
  • Novelty Policies (Any) - Lists special policies you have created for specific statement classes and/or specific tables in your secured target databases. If you have identified specific tables in a policy in this section, the policy rule applies if it matches Any of the tables.
  • Novelty Policies (All) - Lists special policies you have created for specific statement classes and/or specific tables in your secured target databases. If you have identified specific tables in a policy in this section, the policy rule applies if it matches All of the tables.
  • Default Rule - Shows the default rule for any statements that are not matched by the rules set for Analyzed SQL clusters, Exceptions, or Novelty Policies.
  • Policy Controls - Lets you configure firewall policy settings, create policy profiles, as well as sets of filters to use in defining profiles and Exception rules.

 

Practical case

Developing a policy is an iterative process that keeps refining and improving the policy with new data. In order to be able to create a policy statements have to be executed on the database. The examples below present some possibilities provided by Firewall Policy.

These examples are based on a very simple context with two schemas/users:

  1. PSI
  2. GRS

PSI schema contains three tables:

  1. PSI.CREDIT_CARD containing Credit Cards numbers
  2. PSI.EMP containing employees’ salaries
  3. PSI.TEST containing one non sensitive row

 

SQL> select table_name from dba_tables where owner='PSI';

TABLE_NAME

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

EMP

CREDIT_CARD

TEST

 

Novelty Policy

The table CREDIT_CARD contains credit cards numbers and EMP contains Employee salary. These two tables are very sensitive and nobody can have a look on these tables:

 

 

GRS

PSI

PSI.EMP

NOK

NOK

PSI.CREDIT_CARD

NOK

NOK

PSI.TEST

OK

OK

 

The first step in order to create this policy is to create a novelty rule. Novelty policies specify the action, logging level, and threat severity to use for specific types of statements and/or statements that operate on selected tables. Novelty policies can be used to loosen or tighten your normal policy rules if certain statements are encountered. In our context we want to create a novelty policy that will block all access to these tables:

1. In the Audit Vault Server console, select the “Policy” tab.

2. From the Policy menu, click “Firewall Policy”.

3. Click on the newly created Firewall Policy named “MyPolicy

4. Click Add Novelty Rule in section Novelty Policy (Any):

5. In the Novelty Policy Details dialog, define the following:

a. Novelty Rule: Enter a name for this rule: MyNR

b. Statement Classes: Select one or more types of statements that SQL statements must match in order to apply this rule. In this example we have to select “Data Manipulation Read Only”

c. Policy Controls: Select the Action, Logging Level, and Threat Severity for this rule from the appropriate drop-down list. In this example we have to select “Block” for action and specify in the substitution field, the statement below:

select 'You do not have access to this table' from dual

6. Affected Tables: Select the table(s) to use for matching statements to this policy. In order to have tables in this list, tables have to be accessed first. If there is no activity on the database the list will be empty. In our specific case we select tables: PSI.EMP and PSI.CREDIT_CARD and we click on “Add Tables”:

 

004.png

 

7. Click on “Create”.

8. Now we can test this policy. For the moment this policy will block access to any user trying to have access to these two tables.In order to apply this policy we have to save the policy by clicking on “save” and then “publish”.

9. Click on “Secured Targets”

10. Click on the target where you want to apply the policy

11. Click on Firewall Policy

12. Select the Policy “MyPolicy”

13. Now you can check that the policy is applied by doing a select on this table.

 

C:Usersadministrateur>sqlplus psi/psi@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 13:36:14 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;


'YOUDONOTHAVEACCESSTOTHISTABLE'

------------------------------------
You do not have access to this table
SQL>

 

We can execute the same query with user GRS, the result will be the same:

 

C:Usersadministrateur>sqlplus grs/grs@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 13:36:14 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;


'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------
You do not have access to this table

SQL>
  Exception Rule

The table CREDIT_CARD contains credit cards numbers and EMP contains Employee salary. These two tables are still very sensitive but since PSI has been promoted Chief Financial Officer he need access to these tables. Therefore we will create an exception for him:

 

GRS

PSI

PSI.EMP

NOK

OK

PSI.CREDIT_CARD

NOK

OK

PSI.TEST

OK

OK

  1. In order to change the policy this one has to be unused. Click on secured targets, select the target, and click on firewall policy and change the policy to “log all”:

 

005.png

 

2. Now you can make modification to your policy “MyPolicy”.

First of all we need to create a Profile. Within a firewall policy, a profile lets you define a different set of policy rules based on the session data associated with SQL statements. To define the profile, you use the session filters you defined in the Policy Controls section of the firewall policy. These session filters filter SQL statements based on:

  • IP addresses
  • Database user login names
  • Client program names (for example, SQL*Plus)
  • Operating system user names

In this example we will create a profile based on Database user login named. This user will be PSI.

3.Click on Policy

4.Click on Firewall Policy

5. Click on MyPolicy

6. Click on Database User Set

7. Create a new set by clicking on “Create New Set”

8. Enter values for field New Set Name and member:

 

006.png

 

9.Click on “Create Set”

10. Click on “Cancel”

11.Click on “Profiles”

12. Create a new Profile by clicking on “Create New Profile”

13. Enter the Profile Name and select “UsersHavingAccessToMyTable” in the field “DB User Set”:

 

007.png

 

14. Click on “Create Profile”

 

008.png

 

Now we have to create an exception based on this profile. An exception determines the action, logging level, and threat severity to use when certain session data is encountered. For example, an exception could specify rules for statements that originate (or do not originate) from selected client IP addresses or database user names. In this example, the exception will be based on database user name.

Exceptions override all other policy rules. For example, you may want to override the normal policy rules if SQL statements originate from an administrator, or if they originate from anywhere other than a specific IP address.

You can define many exceptions and control the order in which they are evaluated. Each Exception has its own Action, Logging, and Threat Severity settings.

15. Click on policy

16. Click on firewall policy

17. Click on your newly created policy “MyPolicy”

18. Click on “Add Exception”

19. Enter the expception rule name : “ExceptionForPSITable”

20. Into DB User Set select “Include” and select “UsersHavingAccessToblMyTable”

21. In Policy Control click on “Pass”

22. Click on “Create”:

 

009.png

 

23. Click on “Save” and “Publish”

24. Apply this policy to the target

Now, the user PSI can access to all his tables and user GRS have no access to sensitive tables.

 

C:Usersadministrateur>sqlplus grs/grs@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 14:09:07 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;

'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------

You do not have access to this table

SQL> select * from psi.credit_card;

'YOUDONOTHAVEACCESSTOTHISTABLE'

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

You do not have access to this table


SQL> select * from psi.test;

NAME
----------
hello C:Usersadministrateur>sqlplus psi/psi@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 14:18:54 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.credit_card;

NAME               ID
---------- ----------

Larry     4,8960E+15
John       7,8623E+15

SQL> select * from psi.emp;


NAME             SAL
---------- ----------
Larry         150000
John           80000

SQL> select * from psi.test;

NAME
----------
hello
SQL>
  Analyzed SQL

With the exception we granted access to PSI database user to all his table. Since PSI didn’t present good results to shareholders he has been replaced by a new CFO and this one decided that PSI has now only access to credit card number but is not anymore allowed to make select statement on employees’ salaries table:

 

 

GRS

PSI

PSI.EMP

NOK

NOK

PSI.CREDIT_CARD

NOK

OK

PSI.TEST

OK

OK

  1. First of all we have to remove the exception we did before by clicking on the exception rule and clicking on “delete”

  2. In the policy overview click on “Modify SQL”3.Click on “Change”

  3. Select “Secured Target”, select the profile “ProfileForPrivsUsers” and enter in Event Time last 24 hours.

  4. Click on “Apply”

  5. Click on column header “User Name”

  6. Select “psi”

  7. A line looking like the one below should appear in the list

  8. Select this line

  9. Click on “Set Policy”

  10. In the Action list click on “Pass”:


    0010.png


  11. If you have a look on the list of SQL, the following statement should appear.


    0013.png

  12. Create a profile for user GRS named “ProfileForStdUsers” as we did for user PSI. This profile won't have the possibility to execute statement “Select * from psi.credit_card”.

  13. In “Analyze SQL” select profile “ProfileForStdUsers” and filter on GRS user as we did in step 6.

  14. Select the following statement:


    0014.png


  15. Click on “Set Policy”

  16. Select action “Block”

  17. In the field Substitution enter the following: “select 'Your profile does not allow access to this statement' from dual ”

  18. Now your section “Analyzed SQL” should look like the screenshot below:

  19. Save and Publish the modification done on this policy's

  20. Apply this policy to your target, click on secured targets, Firewall Policy and select “MyPolicy” in the list.

Now we can test the access:


C:Usersadministrateur>sqlplus grs/grs@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 16:33:55 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;


'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------
You do not have access to this table

SQL> select * from psi.credit_card;
'

YOURPROFILEDOESNOTALLOWACCESSTOTHISSTATEMENT'
----------------------------------------------------
Your profile does not allow access to this statement

SQL> select * from psi.test;

NAME

----------

hello

 

C:Usersadministrateur>sqlplus psi/psi@souk
SQL*Plus: Release 12.1.0.2.0 Production on Mer. Nov. 12 16:35:35 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select * from psi.emp;

'YOUDONOTHAVEACCESSTOTHISTABLE'
------------------------------------
You do not have access to this table


SQL> select * from psi.credit_card;


NAME               ID
---------- ----------
Larry     4,8960E+15
John       7,8623E+15

SQL> select * from psi.test;

NAME
----------
hello

 

This blog is not intended to be a comprehensive description of all AVDF policies functionnalities but I do hope that it provided you a good overview of some basic functionnalities.

HOWTO: Create a Structured XMLIndex, using the In-Memory Column Store

Marco Gralike - Thu, 2014-11-27 03:41
In Oracle database version 12.1.0.2, Oracle introduced the Oracle In-Memory Database option. It is possible…

White Papers

Oracle in Action - Wed, 2014-11-26 23:44

RSS content

Oracle’s Approach to Performance Tuning Part-I by Darrick Addison

Oracle’s Approach to Performance Tuning Part-II by Darrick Addison

SQL Plan Management in 11g (Oracle White Paper)

SQL Plan Management in 12c (Oracle White Paper)

Adaptive Cursors And SQL Plan Management (Arup Nanda)

Partitioning in 11g (Oracle White paper)

Oracle Database Parallel Execution Fundamentals (Oracle White Paper)

Understanding Parallel Execution Part-I (Randolf Geist)

Understanding Parallel Execution Part-II (Randolf Geist)

Oracle Active Dataguard 11g (Oracle White Paper)

Oracle 11g RAC (Oracle White paper)

Oracle 11gR2 RAC (Oracle White Paper)

Oracle Single Client Access Name (Oracle White Paper)

Oracle RAC One Node (Oracle White Paper)

11g R2 RAC : Architecture, Best Practices And Troubleshooting (Kai Yu)

Automatic Workload Management With Oracle RAC (Oracle White Paper)

RAC Administering Parallel Execution (Riyaz Shamsudeen)

Using RAC Parallel Instance Groups (Chris Lawson)

Oracle 12c RAC (Oracle White paper)

Maximize Availability with Oracle 12c (Oracle White Paper)



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [White Papers], All Right Reserved. 2014.

The post White Papers appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Oracle 12c privilege analysis rocks

Yann Neuhaus - Wed, 2014-11-26 14:18

12c came with a very nice feature: privilege analysis. You don't know which privileges are required? then just grant DBA, run your application, and check which minimal privileges are needed. And today, I've discovered how it is very powerful: you can even see privileges used internally, even when not done by SQL, and even not documented.

It starts like that, with a question from Vladimir Sitnikov (who publishes very interesting stuff from his twitter account) in the tone of a challenge:

@FranckPachot Ok, ace. Do you think dbms_utility.get_parameter_value requires special grants (e.g. in current 11gR2)?

— Vladimir Sitnikov (@VladimirSitnikv) November 26, 2014

So I got to the doc which has a special security model for some functions but nothing about get_parameter_value.

Then I created a simple user with only CREATE SESSION privilege and got:

SQL> drop user TEST;
User dropped.
SQL> grant create session to TEST identified by TEST;
Grant succeeded.
SQL> connect TEST/TEST
Connected.
SQL> variable i number
SQL> variable s varchar2(1000)
SQL> variable t number
SQL> exec :t:=DBMS_UTILITY.GET_PARAMETER_VALUE('NLS_LENGTH_SEMANTICS',:i,:s);
BEGIN :t:=DBMS_UTILITY.GET_PARAMETER_VALUE('NLS_LENGTH_SEMANTICS',:i,:s); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 140
ORA-06512: at line 1

So, which privileges do you need? Let's try the 12c privilege analysis:

SQL> grant dba to TEST;
Grant succeeded.

SQL> connect / as sysdba
Connected.

SQL> exec dbms_privilege_capture.create_capture (name=>'demo',type =>dbms_privilege_capture.g_role,roles=>role_name_list('DBA'));
PL/SQL procedure successfully completed.

SQL> exec dbms_privilege_capture.enable_capture (name=>'demo');
PL/SQL procedure successfully completed.

SQL> connect TEST/TEST
Connected.

SQL> exec :t:=dbms_utility.get_parameter_value('NLS_LENGTH_SEMANTICS',:i,:s);
PL/SQL procedure successfully completed.

SQL> print s

S
--------------------------------------------------------------
BYTE

SQL> connect / as sysdba
Connected.

SQL> exec dbms_privilege_capture.disable_capture(name=>'demo');
PL/SQL procedure successfully completed.

SQL> exec dbms_privilege_capture.generate_result(name=>'demo');
PL/SQL procedure successfully completed.

SQL> select object_owner,object_name,obj_priv from dba_used_objprivs ;

OBJECT_OWN OBJECT_NAME     OBJ_PRIV
---------- --------------- ----------
SYS        V_$PARAMETER    SELECT

SQL> select path from dba_used_objprivs_path ;

PATH
--------------------------------------------------------------
GRANT_PATH('TEST', 'DBA', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'SELECT_CATALOG_ROLE')

SQL> exec dbms_privilege_capture.drop_capture (name=>'demo');
PL/SQL procedure successfully completed.

I've granted the DBA privilege and have run the privilege analysis capture on that role while calling the function. And bingo: you need to be granted SELECT on V_$PARAMETER (which come into DBA role through the SELECT_CATALOG_ROLE) ... which sounds legitimate as the goal is to get a parameter value. 

But do you know what? DBMS_UTILITY.GET_PARAMETER_VALUE do not execute any select statement. That behavior is documented in that package for other function, but not for the GET_PARAMETER_VALUE one:

Rem The dbms_utility package is run-as-caller (psdicd.c) only for
Rem its name_resolve, compile_schema, analyze_schema, wait_on_pending_dml,
Rem and expand_sql_text procedures. This package is not run-as-caller
Rem w.r.t. SQL (psdpgi.c) so that the SQL works correctly (runs as
Rem SYS). The privileges are checked via dbms_ddl.

 

That function calls a C function (KSPGPNICD) so we don't know what happens behind. If you sql_trace it, you don't see anything about V_$PARAMETER.

But privilege analysis show the required privileges anyway, and that rocks.

Happy Thanksgiving

Oracle AppsLab - Wed, 2014-11-26 13:55

Editor’s note: Here’s a first post from one of our new team members, Thao Nguyen (@thaobnguyen), who runs our Emerging Interactions team, the Research and Design part of the R, D & D.

That last D is Development if that’s unclear. Anyway, like Thao says, Happy Thanksgiving for those who celebrate it, and for those who don’t enjoy the silence in our absence. To Thao’s question, I’m going with Internet. Yes, it’s a gadget because it’s a series of tubes, not a big truck.

Find the comments to add the gadget for which you are most thankful.

Tomorrow is Thanksgiving and this seems like a good time to put my voice out on The AppsLab (@theappslab). I’m Thao, and my Twitter (@thaobnguyen) tagline is “geek mom.” I’m a person of few words and those two words pretty much summarize my work and home life. I manage The AppsLab researchers and designers. Jake welcomed us to the AppsLab months ago here, so I’m finally saying “Thank you for welcoming us!”

Photo by floodllama on Flickr used under Creative Commons

Photo by floodllama on Flickr used under Creative Commons

As we reflect on all the wonderful things in our lives, personal and professional, I sincerely want to say I am very thankful for having the best work family ever. I was deeply reminded of that early this week, when I had a little health scare at work and was surround by so much care and support from my co-workers. Enough of the emotional stuff, and onto the fun gadget stuff . . . .

My little health scare led me to a category of devices that hadn’t hit my radar before – potentially, life saving, personal medical apps. I’ve been looking at wearables, fitness devices, healthcare apps, and the like for a long time now but there is a class of medical-grade devices (at least recommended by my cardiologist) that is potentially so valuable in my life, as well as those dear to me . . . AliveCor. It is essentially turns your smartphone into an ECG device so you can monitor your heart health anytime and share it with your physician. Sounds so cool!

Back to giving thanks, I’m so thankful for all the technology and gadgets of today – from the iPhone and iPad that lets me have a peaceful dinner out with the kids to these medical devices that I’ll be exploring now. I want to leave you with a question, “What gadget are you most thankful for?”Possibly Related Posts:

Lunchtime quiz

Jonathan Lewis - Wed, 2014-11-26 06:41

There was a question on OTN a few days ago asking the following question:

Here’s a query that ran okay on 11g, but crashed with Oracle error “ORA-01843: not a valid month” after upgrade to 12c; why ?

The generically correct answer, of course, is that the OP had been lucky (or unlucky, depending on your point of view) on 11g – and I’ll explain that answer in another blog posting.

That isn’t the point of this posting, though. This posting is a test of observation and deduction. One of the respondants in the thread had conveniently supplied a little bit of SQL that I copied and fiddled about with to demonstrate a point regarding CPU costing, but as I did so I thought I’d show you the following and ask a simple question.’


drop table T;

Create Table T
As
with
periods as (
                  Select 'January' period, 1 cal  From Dual
        union all Select 'February' period , 2 cal From Dual
        union all Select 'March' period , 3 cal From Dual
        union all Select 'April' period , 4 cal From Dual
        union all Select 'May'  period, 5 cal From Dual
        union all Select 'June' period, 6 cal From Dual
        union all Select 'July' period, 7 cal From Dual
        union all Select 'August' period, 8 cal From Dual
        union all Select 'September' period, 9 cal  From Dual
        union all Select 'October' period, 10 cal From Dual
        union all Select 'November' period, 11 cal From Dual
        Union All Select 'December' Period, 12 Cal From Dual
        Union All Select '13 Series' Period, Null Cal  From Dual
)
Select  Period,Cal
from periods;

prompt  ==================================
prompt  When we invoke below SQL it works.
prompt  ==================================

set autotrace on explain

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,
                to_date('November, 2014','Month, YYYY') col2
        From  T
        Where  Cal > 0
        )
;

prompt  ================================================
prompt  But when we add comparison operations , it fails
prompt  ================================================

select *
from    (
        select
                Period,
                Cal,
                to_date(Period || ', ' || 2014,'Month, YYYY')   col1,
                to_date('November, 2014','Month, YYYY')         col2
        From  T
        Where  Cal > 0
        )
where
        col1 >= col2
;

set autotrace off



All I’ve done is create a table then run and generate the execution plans for two queries – with a comment that if you try to run one query it will succeed but if you try to run the other it will fail (and raise ORA-01843). As far as the original supplier was concerned, both queries succeeded in 11g and the failure of the second one appeared only in 12c. In fact, for reasons that I won’t discuss here, it is POSSIBLE for the failure to appear in 11g as well, though not necessarily with this exact data set.

Here’s the COMPLETE output I got from running the code above on an 11.2.0.4 instance:



Table dropped.


Table created.

==================================
When we invoke below SQL it works.
==================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
January            1 01-JAN-14 01-NOV-14
February           2 01-FEB-14 01-NOV-14
March              3 01-MAR-14 01-NOV-14
April              4 01-APR-14 01-NOV-14
May                5 01-MAY-14 01-NOV-14
June               6 01-JUN-14 01-NOV-14
July               7 01-JUL-14 01-NOV-14
August             8 01-AUG-14 01-NOV-14
September          9 01-SEP-14 01-NOV-14
October           10 01-OCT-14 01-NOV-14
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |   228 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |   228 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CAL">0)

Note
-----
   - dynamic sampling used for this statement (level=2)

================================================
But when we add comparison operations , it fails
================================================

PERIOD           CAL COL1      COL2
--------- ---------- --------- ---------
November          11 01-NOV-14 01-NOV-14
December          12 01-DEC-14 01-NOV-14

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    19 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CAL">0 AND TO_DATE("PERIOD"||', '||'2014','Month,
              YYYY')>=TO_DATE(' 2014-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


So this is the question. What’s the anomaly in this output ?

Bonus question: What’s the explanation for the anomaly ?

Answers:

If I had asked why the query might, or might not, crash – the answer would be about the order of predicate evaluation, and simply collecting stats (or not) might have made a difference. Ever since “system stats”  and “CPU costing” appeared the optimizer has been able to change the order in which it applies filter predicates to a table (there’s a pdf of an article of mine from Oracle magazine in the 9i / 10g timeline linked at this URL) .  In this case, applying the “cal > 0″ predicate first luckily eliminates the rows that would fail the second predicate. Since the effect is driven by the optimizer’s stats this type of failure could occur ANY TIME you have a predicate that requires coercion between types to take place – which is one reason why you see the injunctions to use the correct data types; and why, if you need coercion to work around incorrect data types you have to consider writing your own functions to trap and resolve the necessary errors raised by Oracle’s implicit conversion mechanisms.

For a quick sketch of the optimizer strategy, the arithmetic is roughly:  predicate A costs c1 and predicate B costs c2; if I apply predicate A to every row I have to apply predicate B to only N surviving rows; if I apply predicate B to every row I have to apply predicate A to M surviving rows; which is smaller: (input_rows * c1 + N * c2) or (input_rows * c2 + M * c1).

The answer to the question I actually asked is this, though: I stressed the fact that this was the COMPLETE output because, as Narenda highlighted in comment 7 below –  the first query shows a note about dynamic sampling and the second query does not. This is a little surprising; we don’t have stats on the table, and the two queries are different so we have to optimizer both of them.  In 12c, of course, it’s possible that the optimizer may have done something clever with statistics feedback (formerly cardinality feedback) and created an SQL directive – but even then we should have seen a note about that.

For the bonus question: given the second output doesn’t report dynamic sampling we should be curious why not – did the optimizer simply decide not to try, did it try then decide not to use the results for some reason, or is there some other reason.  The obvious next step is to look at the 10053 (optimizer) trace – where you find that the optimizer DID do dynamic sampling or rather, it tried to do dynamic sampling but the query generated to take the sample failed with Oracle error ORA-01843, as suggested by Chinar Aliyev in comment 9  and expanded by Mohamed Houri in comment 11.

The irony of the sampling problem (hinted by Chinar Aliyev in comment 10) is that you could be in a position where you have a large table and oracle picks a small sample which happens to miss any of the problem rows and then return a sample that persuades the optimizer to pick an execution plan that is bound to find a problem row; alternatively the SQL used to generate the sample might apply the predicate in an order that manages to eliminate the problem rows, while the final plan derived after sampling persuades the optimizer to use the predicate in the order B, A.