Infrastructure at your Service

All Posts By

Clemens Bleile

Jun 03 2021
Clemens Bleile

Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?

By | Oracle | No Comments

There was a dicsussion a couple of years ago about a mssing feature in Oracle that the Optimizer is not peeking values when SYS_CONTEXT is being used in a predicate. I.e. specifically when using SYS_CONTEXT in Views to simulate passing parameters to views. See the following links concerning this topic: https://connor-mcdonald.com/2016/10/20/taking-a-peek-at-sys_context https://blog.jooq.org/2016/10/20/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle https://community.oracle.com/ideas/15826 E.g. take the example of Connor McDonald here: [email protected]@PDB1> create table t ( x varchar2(10), y char(100)); Table created. [email protected]@PDB1> [email protected]@PDB1> insert…

Read More
Mar 23 2021
Clemens Bleile

Adding nmon to Oracle OSWatcher

By | Oracle | No Comments

nmon (nmon is short for Nigel’s performance Monitor for Linux on POWER, x86, x86_64, Mainframe & now ARM (Raspberry Pi)) is a nice tool to monitor a Linux system. Originally it came from the AIX-platform, where it is very popular. I asked myself if I can add nmon to the OSWatcher framework to automatically gather nmon data. It is actually possible. Here’s what I did: First I installed nmon: In my case (Oracle Enterprise Linux…

Read More
Mar 01 2021
Clemens Bleile

Oracle Blockchain Tables: COMMIT-Time

By | Oracle | No Comments

Oracle Blockchain Tables are available now with Oracle 19.10. (see Connor’s Blog on it), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables should be considered as an option. As Oracle writes in the documentation that blockchain tables could e.g. be used for…

Read More
Feb 08 2021
Clemens Bleile

Oracle autoupgrade on Windows and plugin to a Container DB with virtual accounts

By | Oracle | No Comments

In a project I recently had to upgrade an Oracle 12.2.-DB to 19.9. and at the same time migrate from the non-container architecture to the container architecture. The interesting part here is to do this on Windows. Actually both steps (upgrade and plugin) are possible using the autoupgrade tool , which is the preferred tool to do Oracle upgrades today. Before doing the upgrade at the customer, I performed 2 tests in my personal environment:…

Read More
Jan 08 2021
Clemens Bleile

Handling unified auditing spillover files on the standby-site

By | Oracle | No Comments

Switching to Oracle Unified Auditing may produce lots of data when e.g. auditing activities of the SYS-user. I.e. according the documentation you can do the following to audit similarly as in traditional auditing with audit_sys_operations=TRUE: SQL> CREATE AUDIT POLICY TOPLEVEL_ACTIONS ACTIONS ALL ONLY TOPLEVEL; SQL> AUDIT POLICY TOPLEVEL_ACTIONS BY SYS; REMARK1: You may check the Blog on traditional SYS-auditing here REMARK2: Tests done in this Blog were done with Oracle 19.9. Auditing toplevel operations were…

Read More
Oct 05 2020
Clemens Bleile

Automatic column formatting in Oracle sqlplus

By | Oracle | No Comments

Column formatting was always a pain in sqlplus when writing queries on the prompt. Most people use tools like SQL Developer or Quest TOAD which can scroll horizontally when running queries against a database, but as a consultant you are often still forced to use sqlplus. Here’s the issue: When running e.g. a query on a table T1 (which is a copy of ALL_OBJECTS) it looks by default as follows and is hard to read:…

Read More
Sep 24 2020
Clemens Bleile

Oracle DML (DELETE) and the Index Clustering Factor

By | Oracle | No Comments

As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without code changes. Recently I was confronted with the following issue: A process takes too long. Digging deeper I could see…

Read More
Aug 07 2020
Clemens Bleile

Merge-Statement crashes with ORA-7445 [kdu_close] caused by Real Time Statistics?

By | Oracle | No Comments

In a recent project we migrated an Oracle database previously running on 12.1.0.2 on an Oracle Database Appliance to an Exadata X8 with DB version 19.7. Shortly after the migration a merge-statement (upsert) failed with an ORA-07445: exception encountered: core dump [kdu_close()+107] [SIGSEGV] [ADDR:0xE0] [PC:0x1276AE6B] [Address not mapped to object] [] The stack looked as follows: kdu_close – updThreePhaseExe – upsexe – opiexe – kpoal8 – opiodr – ttcpip – opitsk – opiino – opiodr…

Read More
Jul 20 2020
Clemens Bleile

Oracle ASH SQL_PLAN_LINE_ID in adaptive plans

By | Oracle | No Comments

There are several methods to find out where time is spent in an execution plan of a query running in an Oracle database. Classical methods like SQL Trace and running a formatter tool like tkprof on the raw trace, or newer methods like SQL Monitor (when the Tuning Pack has been licensed) or running a query with the GATHER_PLAN_STATISTICS-hint (or with statistics_level=all set in the session) and then using DBMS_XPLAN.DISPLAY_CURSOR(format=>’ALLSTATS LAST’). However, what I often…

Read More
Jun 05 2020
Clemens Bleile

DB-Upgrade hangs in SE2 waiting on Streams AQ while gathering statistics on LOGMNR-Tables

By | Oracle | No Comments

A couple of weeks ago I upgraded an Oracle Standard Edition 2 test database from 12.1.0.2 to 12.2.0.1 (with the April 2020 Patch Bundle) on Windows. Recently I upgraded the production database. Both upgrades were done with the Database Upgrade Assistant DBUA. I didn’t use AUTOUPGRADE because I had to upgrade only 1 database and the DBUA handles everything for me (including changing the necessary Windows services and update the timezone file). Both upgrades did…

Read More