Troubleshooting Oracle session disconnection slowness
I have been recently asked to investigate very interesting issue. In short: AIX 7.1 with Oracle 12.1.0.2. Connect with SQL*Plus from hostX to databaseA running on hostZ, then disconnect – was hanging for around 75 seconds. Connect with SQL*Plus from hostY to databaseA running on…
Issue with the Hint ENABLE_PARALLEL_DML
Performing an ETL with large data sets, it is often a good idea to run DML in parallel. But, in contrast to parallel query or DDL, parallel DML have to be explicitly enabled. You had to issue ALTER SESSION ENABLE PARALLEL DML in the past….
Dynamic Pivot with Polymorphic Table Function?
LiveSQL is great place to start playing with new features. It provides a couple of very helpful demo scripts explaining how polymorphic table functions work. There I found a new script few days ago which uses PTF for dynamic pivot! WOW! According to my subjective…
Polymorphic Table Functions (PTF), Part 4 – Table Semantic PTF
In the first three parts of the series we have seen how a PTF basically works, but we have focused on row-semantic PTF only. In this part we’ll look at the table semantic PTF. As a reminder, the main difference to row-semantic PTF is that…
Fast Recovery Area space usage threshold
Using Fast Recovery Area to let Oracle automatically manage the backup/flashback/recovery related files is very useful, but avoiding problems related with FRA free space shortage requires careful planning. There are several factors that have to be taken into account, apart from the most obvious like…
Data Types of Join Columns
The data type of primary key and foreign key columns has an impact on the query performance. This should be considered in the design decision of keys in a Data Vault schema. In a performance review of a customer project, we detected a lot of…
Redo Transport from Oracle 11.2 to 12.2 and SYS password case-sensitivity problems
Case-sensitive passwords were introduced in Oracle Database 11g, causing some misunderstanding and introducing quite a lot of annoying problems for DBAs, even though it was really required to bring level of Oracle’s security to 21st century standards ;). Since then, even more changes were introduced,…
Polymorphic Table Functions (PTF) , Part 3 – Row Replication
In the third part of the PTF-series we learn how a PTF can change the cardinality of the input data flow: return more or less rows as in the input. We’ll use the same simple table as in the part 2 and our new task…
Online Statistics Gathering for ETL – Part 3
Preparing my session on Online Statistics Gathering for ETL for the DOAG conference, I noticed some points that I didn’t covered in the previous two blog posts. The first point is showing the problem that may arise if indexes are involved. The second one is…
Polymorphic Table Functions (PTF) , Part 2 – More Basics With Some Deep-Dive
In the first part of PTF series we went through a very basic example removing some columns and adding a new column with a constant value. Starting from the same example we’ll do something more meaningful in the second part. How about concatenating the values…
Social Media @trivadis