Infrastructure at your Service

All posts by Clemens Bleile

Clemens Bleile
Senior Consultant

Clemens Bleile has more than 24 years of IT experience, thirteen in Oracle Support and eight in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.
Clemens Bleile

sqlplus and its column output

By | Database Administration & Monitoring, Database management | No Comments

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here): Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus. How can you address…

 
Read More
Clemens Bleile

Connect-times to the DB suddenly become very slow using sqlcl

By | Database Administration & Monitoring, Database management, Development & Performance, Oracle | 2 Comments

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and…

 
Read More
Clemens Bleile

CPUs: Cores versus Threads on an Oracle Server

By | Database Administration & Monitoring, Database management | 2 Comments

When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what does a utilization of 50% mean? As I had an ODA X7-M in a test lab available, I thought I’ll…

 
Read More
Clemens Bleile

Running the Oracle Client on a Raspberry Pi

By | Database Administration & Monitoring, Database management, Development & Performance, Oracle | No Comments

What are the possibilities to use a Raspberry Pi computer as an Oracle client? Besides other things I’ll show a possibility in this Blog to run the fat/thick Oracle Client on a Raspberry Pi! REMARK: All examples below were made with an Rasperry Pi 3 and the OS Raspbian, which can be downloaded from https://www.raspberrypi.org/downloads First of all what’s possible with Java and Thin Clients? Running the Java-Programs sqldeveloper or its counterpart in command line…

 
Read More
Clemens Bleile

Transfer redo in async-mode to the Gold/Master copy of the Production DB for ACFS snapshots

By | Database Administration & Monitoring | 2 Comments

If you store your databases on the cluster filesystem ACFS you may use the provided Perl-script gDBClone from OTN to clone databases or create snapshot databases. It is an interesting approach to create clones from the Production DB in minutes regardless of the production DB size. What you do is to create a standby DB from your production DB on a separate cluster and use that standby DB as a Gold/Master copy for ACFS snapshots….

 
Read More
Clemens Bleile

DOAG 2017: avg_row_len with virtual columns

By | Database Administration & Monitoring, Database management, Oracle | No Comments

At the DOAG I attended a session “Top-level DB design for Big Data in ATLAS Experiment at CERN” provided by Gancho Dimitrov. The presentation was actually very interesting. As part of Gancho’s improvement activities to reduce space in a table he stored data in a 16 Bytes raw format (instead of a string representing hex values which requires 36 Bytes) and use virtual columns to actually calculate the real hex-string. So the original value is…

 
Read More
Clemens Bleile

Recommended DB Parameters on the Oracle Database Appliance (ODA)

By | Database Administration & Monitoring, Database management, Hardware & Storage | No Comments

When creating a DB on the ODA using # oakcli create database -db <db-name> a template is used to set a couple of DB parameters like e.g. _datafile_write_errors_crash_instance=FALSE _db_writer_coalesce_area_size=16777216 _disable_interface_checking=TRUE _ENABLE_NUMA_SUPPORT=FALSE _FILE_SIZE_INCREASE_INCREMENT=2143289344 _gc_policy_time=0 _gc_undo_affinity=FALSE db_block_checking=’FULL’ db_block_checksum=’FULL’ db_lost_write_protect=’TYPICAL’ filesystemio_options=’setall’ parallel_adaptive_multi_user=FALSE parallel_execution_message_size=16384 parallel_threads_per_cpu=2 use_large_pages=’ONLY’ In recent projects I saw a couple of DBs running on ODA, which did not have (all) those parameters set, because the DBs were migrated from a non-ODA-platform and the customer took over…

 
Read More
Clemens Bleile

Workaround for bug 19566227/20563128 doing Cross Platform Migration (MOS Note 1389592.1)

By | Database Administration & Monitoring, Database management | One Comment

In a project I have to move an Oracle 11.2.0.4-DB (around 7TB in size) from Sparc Solaris (called Source in the remainder of this Blog) to Linux x86-64 (called Target in the remainder of the Blog). I.e. a platform migration from Big Endian to Little Endian. A good method to do that is described in My Oracle Support Note 1389592.1: 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup Basically I copy the…

 
Read More
Clemens Bleile

Trace files segmented in multiple parts as a workaround for bug 23300142

By | Database Administration & Monitoring | No Comments

Today I visited a customer, who deleted a Data Guard configuration (i.e. a temporary Data Guard setup through the broker was deleted). The LOG_ARCHIVE_DEST_STATE_2 on the primary database was set to DEFER temporarily. That resulted in trace-files with name *tt*.trc to become huge (GBytes after a couple of days). Analysis showed that this was caused by bug 23300142 in 12.1.0.2. See My Oracle Support Note Bug 23300142 – TT background process trace file message: async…

 
Read More
Clemens Bleile

SQL Plan Directives in 12cR2. Behavior is different than in 12cR1.

By | Database Administration & Monitoring, Database management | 10 Comments

During the Frankfurter IT Tage I provided a presentation about SQL Plan Directives and how to reproduce a testcase in a test environment for a plan with used SQL Plan Directives (SPD). In that context I did a couple of tests with 12cR2 (12.2.0.1) and wanted to blog about the change in behavior in that release. Here is my testcase I did initially run in 12.1.0.2: First I created a table with 4 columns: A,…

 
Read More