Little script for finding tables for which dynamic sampling was used

You can always download latest version here:
Current source code:

col owner         for a30;
col tab_name      for a30;
col top_sql_id    for a13;
col temporary     for a9;
col last_analyzed for a30;
col partitioned   for a11;
col nested        for a6;
col IOT_TYPE      for a15;
with tabs as (
         to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,1))  owner
        ,to_char(regexp_substr(sql_fulltext,'FROM "([^"]+)"."([^"]+)"',1,1,null,2))  tab_name
        ,count(*)                                                                    cnt
        ,sum(executions)                                                             execs
        ,round(sum(elapsed_time/1e6),3)                                              elapsed
        ,max(sql_id) keep(dense_rank first order by elapsed_time desc)               top_sql_id
      from v$sqlarea a

To Skip, Or Not To Skip

An interesting issue presented itself just recently with a logical standby database I manage. The database is used for generating reports and the client wanted to skip all DML activity for a given schema as it wasn’t necessary for reporting purposes. I had done this in version 10.2.0.x; it was a simple procedure on a low-traffic database:

alter database stop logical standby apply;
exec dbms_logstdby.skip('DML','','%')
alter database start logical standby apply;


12c Upgrade and Concurrent Stats Gathering

I was upgrading an Exadata test database from to and I came across a failure scenario I had not encountered before. I’ve upgraded a few databases to both and for test purposes, but this was the first one I’d done on Exadata. And the first time I’d encountered such a failure.

I started the upgrade after checking with the (more...)

enhanced SQL trace

As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits.
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)

Comparing SQL Execution Times From Different Systems

Comparing SQL Execution Times From Different Systems

Suppose it's your job to identify SQL that may run slower in the about-to-be-upgrated Oracle Database. It's tricky because no two systems are alike. Just because the SQL run time is faster in the test environment doesn't mean the decision to upgrade is a good one. In fact, it could be disastrous.

For example; If a SQL statement runs 10 seconds in production and runs 20 seconds in (more...)


I tested this on an Oracle 11.1 database.
Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:
SQL> grant create session to andrew
  2  identified by reid
  3  /
Grant succeeded.
SQL> select profile (more...)

rsyslog: Send logs to Flume

Good day for learning something new. After read Flume book, that something popped up in my head. Wanted to test "rsyslog" => Flume => HDFS. As we know, forwarding log to other systems. We can set rsyslog:
For rsyslog:
[root@centos01 ~]# grep centos /etc/rsyslog.conf
*.* @centos01:7777
Came back to Flume, I used Simple Example for reference and changed a bit. Because (more...)

Oracle In-Memory Column Store Internals – Part 1 – Which SIMD extensions are getting used?

This is the first entry in a series of random articles about some useful internals-to-know of the awesome Oracle Database In-Memory column store. I intend to write about Oracle’s IM stuff that’s not already covered somewhere else and also about some general CPU topics (that are well covered elsewhere, but not always so well known in the Oracle DBA/developer world).

Before going into further details, you might want to review the Part 0 of this series and also our (more...)

Poor Man’s In-Memory Caching


It is common knowledge, or at least it should be, that Oracle has
released version in June 2014. which has a feature called
“Oracle In-Memory”. The name is a tad confusing, since many
features were called “in-memory” before, from “Very Large
Memory” to Times 10 product.

This “In Memory” option is a licensed product featuring
columnar storage, in addition to the normal buffer storage, almost
identical to IBM’s “Blu acceleration” (more...)


Well, the annual spectacle of enormous proportions has come to a conclusion again. And thats probably the first reason I’d recommend OpenWorld to anyone who works with Oracle who has never been to it. It’s a jaw dropping moment just to see the scale of the event, and how impressively its organised in terms of facilities, lunches, registration and the like.

But onto the technical elements, here’s my impressions of this years conference:

1) Big (more...)

OOW 2014: Day 1

Disclosure: I'm attending Openworld at the invitation of the OTN ACE Director program who are paying for my flights, hotel and conference fee. My employer has helpfully let me attend on work time, as well as sending other team mates because they recognise the educational value of attending. Despite that, all of the opinions expressed in these posts are, as usual, all my own.

After the very welcome tradition of breakfast at Lori's Diner, I (more...)

Oracle OpenWorld 2014

Dear Readers,

This year's Oracle Open World 2014 was a lot of learning and meeting old and new friends.
What a fun and great conference!

The big highlights for me were:

Oracle 12c In Memory Database- in sight and everywhere this was a key mantra at the event.

Big Data SQL- another new thing that intrigues me is using SQL to manage Big Data applications with Hadoop, NoSQL and other applications.

Engineered systems- Oracle demo (more...)

Bug 5497611

I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):

This produced the following ORA-00600 message several times in the alert log:
Wed Oct  1 18:13:21 2014
Errors in (more...)

Extending an ACFS filesystem dynamically.

To extend an ACFS cluster filesystem dynamically, we need to use the acfsutil command:

node01:/u01/grid>/sbin/acfsutil size +10G /u02
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required
                           version ( for unlimited volume expansions.

Oh dear, not, so you can only extend volumes dynamically a (more...)

Abstract Review Tips

Yes, this is for my RMOUG abstract reviewers, but it may help other conferences and user groups, too.  We have some incredible content at RMOUG, (Rocky Mtn. Oracle User Group) Training Days conference and its all due to a very highly controlled, thought out process that has evolved over time to ensure that we have an abstract selection process that is as fair as possible and offers new speakers opportunities as well.

I’m going to (more...)

OCP 12C – Real-Time Database Operation Monitoring

What is Real Time Database Operation Monitoring ? Real Time Database Operation Monitoring will help you track the progress of a set of sql statements and let you create a report. Real Time Database Operation Monitoring acts as a superset of all monitoring components like : ASH, DBMS_MONITOR … You can generate Active Reports which are [...]

The post OCP 12C – Real-Time Database Operation Monitoring appeared first on Oracle DBA Scripts and Articles (Montreal).

RAC, 12c and Direct Path Reads

Yes, direct path reads again :) No worries I’m already a bit bored from digging in this algorithm, so I think this is going to be my last post about it. Till they change it again of course. And yes, in they did. For introduction to DPR, you can check my previous post, because […]

OOW14 Session: The Art and Craft of Tracing

A big thanks to all those who braved attending my session on the final day of Oracle Open World 2014. I hope you enjoyed it and found it valuable.

You can download the slide deck as well as the scripts I mentioned here.

As always, I would love to hear from you.

OCP 12C – RMAN and Flashback Data Archive

RMAN Enhancements New Privilege A new SYSBACKUP privilege is created in Oracle 12c,  it allows the grantee to perform BACKUP and RECOVERY operations with RMAN SQL in RMAN You can now use SQL Statements in RMAN like you would do in SQL*PLUS : BEFORE : RMAN> SQL “alter system switch logfile”; NOW : RMAN> alter system switch logfile; [...]

The post OCP 12C – RMAN and Flashback Data Archive appeared first on Oracle DBA Scripts and Articles (more...)

OCP 12C – Privileges

User Task-Specific Administrative Privileges To continue with the objective of separating duties and the least privileges, Oracle 12c introduce new administratives privileges all destinated to accomplish specific duties: SYSBACKUP : Used for RMAN operations like BACKUP, RESTORE, RECOVER SYSDG : Used to administer DATAGUARD, In 12c when you use DGMGRL commandline interface your are automatically [...]

The post OCP 12C – Privileges appeared first on Oracle DBA Scripts and Articles (Montreal).