Infrastructure at your Service

All Posts By

Franck Pachot

Franck Pachot

TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT

By | Database Administration & Monitoring, Oracle | 4 Comments

By Franck Pachot . When you have a Data Guard configuration, you want the application to connect to the right server, where the primary is, without taking too much time. The default TCP timeout is 1 minute which is too long. When you don’t want to configure a virtual IP address (VIP) you can simply list all the addresses in the client connection string. But then you need to reduce the timeout. A short duration…

Read More
Franck Pachot

ATP vs ADW – the Autonomous Database lockdown profiles

By | Cloud, Oracle | No Comments

By Franck Pachot . The Oracle database has always distinguished two types of workloads: transactional (OLTP) and datawarehouse (VLDB, DWH, DSS, BI, analytics). There is the same idea in the managed Oracle Cloud with two autonomous database services. To show how this is old, here is how they were defined in the Oracle7 Tuning Book: The definition has not changed a lot. But the technology behind DSS/DWH has improved. Now, with In-Memory Column Store, Smart…

Read More
Franck Pachot

MERGE JOIN CARTESIAN: a join method or a join type?

By | Oracle | No Comments

By Franck Pachot . I’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE and USE_MERGE_CARTESIAN hints. I’ll show the execution plan, with SQL Monitoring in text mode. And…

Read More
Franck Pachot

How much free space can be reclaimed from a segment?

By | Oracle | No Comments

By Franck Pachot . You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more,…

Read More
Franck Pachot

Oracle 18c preinstall RPM on RedHat RHEL

By | Oracle | No Comments

By Franck Pachot . The Linux prerequisites for Oracle Database are all documented but using the pre-install rpm makes all things easier. Before 18c, this was easy on Oracle Enterprise Linux (OEL) but not so easy on RedHat (RHEL) where the .rpm had many dependencies on OEL and UEK. Now that 18c is there to download, there’s also the 18c preinstall rpm and the good news is that it can be run also on RHEL…

Read More
Franck Pachot

Release 18.0.0.0.0 Version 18.3.0.0.0 On-Premises binaries

By | Oracle | No Comments

By Franck Pachot . Good news, the latest Patchset for Oracle 12cR2 (which is not named patchset anymore, is actually called release 18c and numbered 18.0.0.0.0) is available for download on OTN. It is great because OTN download does not require access to Support and Software Updates. It is available to anybody under the Free Developer License Terms (basically development, testing, prototyping, and demonstrating for an application that is not in production and for non-commercial…

Read More
Franck Pachot

Installing ZFS on OEL7 UEK4 for Docker storage

By | Database Administration & Monitoring | No Comments

By Franck Pachot . The Oracle Database is fully supported on Docker according that Linux is Red Hat Enterprise Linux 7 or Oracle Enterprise Linux 7 with Unbreakable Enterprise 4. This is documented in MOS Note 2216342.1. Given the size of the Oracle database in GigaBytes even empty, the way it is installed at build with many file updates, and the per-block modifications of the datafiles, a block level copy-on-write filesystem is a must and…

Read More
Franck Pachot

Google Cloud Spanner – no decimal numeric data types

By | Cloud, Database Administration & Monitoring | No Comments

By Franck Pachot . Google Cloud Spanner is a distributed relational database focused on scalability without compromising consistency and integrity. It is available only as a managed service in Google Cloud. Basically, the idea is to keep the scalability advantages of NoSQL database (like Bigtable) but adding transactions, relational tables, SQL, structured data,… as in the relational databases we love for decades. The commercial pitch includes all the NoSQL buzzwords, with the addition of the…

Read More
Franck Pachot

Drilling down the pgSentinel Active Session History

By | Database Administration & Monitoring, Postgres | No Comments

By Franck Pachot . In pgSentinel: the sampling approach for PostgreSQL I mentioned that one of the advantages of the ASH approach is the ability to drill down from an overview of the database activity, down to the details where we can do some tuning. The idea is to always focus on the components which are relevant to our tuning goal: Filter/Group by the relevant dimension to focus on what you want to tune (a…

Read More