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:
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.
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.
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.
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):
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:
Recent comments
1 year 4 weeks ago
1 year 16 weeks ago
1 year 20 weeks ago
1 year 21 weeks ago
1 year 25 weeks ago
1 year 47 weeks ago
2 years 15 weeks ago
2 years 44 weeks ago
3 years 29 weeks ago
3 years 29 weeks ago