Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

September 2018

Recursive queries on IM_DOMAIN$ at each cursor execution

Submitted by Franck Pachot on Sun, 2018-09-09 08:34

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

Submitted by davidkurtz on Thu, 2018-09-06 10:24

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

Submitted by Jonathan Lewis on Wed, 2018-09-05 12:09

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 12.2.0.1 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

Submitted by connor_mc_d on Mon, 2018-09-03 08:39

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

Submitted by Franck Pachot on Sun, 2018-09-02 14:56

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

Submitted by Bertrand Drouvot on Sat, 2018-09-01 10:26

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: