Intermediate SQL

How to find SPM baseline by sql_id

Maxym Kharchenko

When you start working with SQL Plan baselines, one of the annoying things that you might find is that the main “baseline” dictionary view dba_sql_plan_baselines does not have sql_id column. SQL> @DESC dba_sql_plan_baselines Name NULL? TYPE [...]

How to freeze ORACLE database by strace

Maxym Kharchenko

Sometimes when you trace things, you can discover some really interesting (and unexpected) stuff. For example, here is a simple way to “freeze” your ORACLE database, which I “discovered” while tracing system calls in LGWR process (ORACLE 11.2.0.2 on Linux 2.6.18 ×64). Here goes … To freeze your ORACLE database start with the following unassuming [...]

ora_cpu.pl tool for Linux

Maxym Kharchenko

A very brief update. One of the smaller (and nicer) part of ORA_MEM package is ora_cpu.pl utility that shows graphically what active db processes are doing at the moment … be it running SQL, accessing db object or waiting for something. As an added benefit, it also shows some important OS statistics, such as process [...]

How to become regular ORACLE user when you do NOT know the password

Maxym Kharchenko

This is a fairly common situation for a DBA: you can always connect as SYSDBA, but sometimes you really need to connect as a regular database user (i.e. to have proper environment for your queries). If you are root in UNIX, this would be extremely easy to do: su - <regular user> Unfortunately, ORACLE does [...]

The troubling global nature of SQL Profiles and SPM Baselines

Maxym Kharchenko

ORACLE SQL Profiles and SPM Baselines can sometimes work in mysterious (and unexpected) ways. In particular, they both act "globally" and "fire" based almost exclusively on the text of SQL query. This has some rather serious side effects that everyone needs to be aware of. This post will explore them.

What is the difference between SQL Profile and SPM Baseline ?

Maxym Kharchenko

ORACLE SQL Profiles and SQL Plan Baselines often look alike and it turns out that they are actually built very similarly. Nevertheless, there are some major differences between them, both in their overall purpose as well as how they actually work and this post explores these differences.

What are SQL Profiles and why do we need them ?

Maxym Kharchenko

New ORACLE feature: SQL Profiles can be used to correct optimizer's behavior in cases where underlying data does not fit optimizer's statistical expectations. It does so by dynamically sampling real data and thus making optimizer estimations much more precise. Unlike dynamic sampling these new precise numbers can be stored in a data dictionary and reused on all subsequent runs of the SQL (thus skipping the expensive dynamic sampling operation). See how the use of SQL Profiles can drastically improve SQL performance and why it makes sense to use them.

Can ORACLE 11g memory_target work with AIX large pages ?

Maxym Kharchenko

One of my readers recently asked me if ORACLE 11g can use memory_target along with AIX large pages … (Thanks Randolf!) At the first blush this seems to be impossible. The main reason, of course, is that memory_target and large pages are used for the purposes that are, in fact, completely opposite! The goal of [...]

How AIX Paging Space works. Part 1: Why your program memory footprint gets bloated sometimes

Maxym Kharchenko

This post talks about how paging space works on modern AIX systems. In particular, the workings of now default "deferred paging space policy" are explained. Check it out if you want to know when and how AIX allocates and deallocated paging space blocks, what is the meaning of svmon "pgsp" field as well as why memory footprint of your program or shared memory segment may be bloated sometimes ...

ORACLE 11g XML alert logs. Surprise: They are actually more convenient to work with

Maxym Kharchenko

ORACLE 11g completely changed the way how the cornerstone of ORACLE status information - instance alert log is stored and maintained. While XML storage might not seem very user friendly at first, with a bit of ingenuity and environment tweaking, XML can become DBAs best friend as it makes it much easier to extract meaningful information from alert (as well as many other) log files.