Top 60 Oracle Blogs

Recent comments

September 2018

Stats time

I wrote a note a couple of years ago explaining how I used to get a rough idea (with some errors) of how much time was spent in the overnight stats collection by each object. One of the nice little enhancements that appeared in 12c was the appearance of a couple of functions that can report information about this type of thing, and more. These are the dbms_stats function report_stats_operations() and report_single_stats_operation() with the following definitions:

Modifying tables without losing materialized views

Whilst the ever increasing speed of storage and servers, and features likes In-memory are decreasing the need for large numbers of materialized views in databases, there are still plenty of use cases where they can be a useful performance or integrity tool.

But what if that materialized view takes minutes or hours to build? Then even if it is fast refresh enabled, than fast refresh is no of use if we have to rebuild the entire materialized view from scratch due to an structural change in the underlying table. 

For example, let’s say I have a table and a simple fast refresh materialized view on that table as below:

Recursive queries on IM_DOMAIN$ at each cursor execution

At POUG 2018 conference I explained join methods by putting gdb breakpoints on the qer (Query Execution Rowsource) functions that are behind the execution plan operations. I was a bit annoyed by several calls when running a Hash Join because of recursive, internal queries on the dictionary. There are a lot of queries on the dictionary during hard parse, but this was at execution time on a query that had already been parsed before. This is new in 12.2 and seems to be related to In-Memory Global Dictionary Join Groups feature, the execution checking and setting up the Join Group aware Hash Join.

However, I must mention that even if this seems to be related with In-Memory I don’t have it enabled here:

How Not to Collect Optimizer Statistics in an Application Engine Program

I was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ORA-06533: Subscript beyond count 
ORA-06512: at "SYS.DBMS_STATS"…

It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.
This blog is not about the bug, but how to manage the workaround.

Subquery Order

From time to time I’ve wanted to optimize a query by forcing Oracle to execute existence (or non-existence) subqueries in the correct order because I know which subquery will eliminate most data most efficiently, and it’s always a good idea to look for ways to eliminate early. I’ve only just discovered (which doing some tests on 18c) that Oracle introduced the /*+ order_subq() */ hint that seems to be engineered to do exactly that.

Here’s a very simple (and completely artificial) demonstration of use.

Complex materialized views? Try a table first

Just a quick post today that arose from an AskTOM question a little while back. Over the years and across the evolution of various versions of the Oracle database, the amount of “power” you could pack into a CREATE TABLE statement as grown. For example, I can do a CREATE-TABLE-AS-SELECT, along with a partitioning clause, including an explicit CREATE INDEX command and add constraints all in one atomic operation.

ASM Filter Driver — simple test on filtering

Here is a simple test on ASM Filter Driver showing that when filtering is enabled the disks presented to ASM are protected from external writes.

On a 12.2 Grid Infrastructure installation I have my disks labeled with ASM Filter Driver (AFD):

Active Session History in PostgreSQL: blocker and wait chain

While the active session history extension for PostgreSQL is still in beta, some information is added to it.

The pg_active_session_history view is currently made of: