Archive | March, 2012

A problem with dbms_stats.copy_table_stats

26 Mar

Last week, when I was tuning a SQL statement at a 10.2.0.4 database, I came across a rather interesting issue.

The SQL was performing poorly because of an inefficient plan (wrong join order), and it was relatively straighforward to determine the reason for that: a table partition appeared empty on the plan, while in fact it contained a few hundred thousand records. The table was range-partitioned on a timestamp column (1 partition = 1 day).

As the development team explained to me, they were relying on a rather sophisticated maintenance procedure that was looking over a previous period to chose a suitable partition as the source, and then copied its stats into a newly created partition before it was filled with data to prevent it from being analyzed as empty. Continue reading →

SQL tuning: real-life example

22 Mar

An example of tuning — nothing special, but it does illustrate several aspects of tuning work, so I thought I’d make a blog post out of it.

Yesterday I as contacted by a development team working with a 10.2.0.4 database. Their complaint was the traditional “the system is slow”. Despite very unspecific symptoms, they were very specific about the time when it all started. I didn’t have access to AWR on that database, so instead I looked at top wait events in DBA_HIST_SYSTEM_EVENT for recent snapshots, nothing interesting. The top wait event was db file sequential read (with db file scattered read as a very distant second). Since that particular group was working with just one user in the database, I looked at ASH data for this user — same thing, just a bunch of db file seqential reads. Continue reading →

Tags: , , ,

Dynamic tracing of Oracle logical I/O: part 2. Dtrace LIO v2 is released.

20 Mar

Reblogged from Alexander Anokhin:

What's new in DTrace LIO?
Jump to introduction: Dynamic tracing of Oracle logical I/O

1. The list of supported functions performing logical I/O is extended:
Note: some function names below are my suggestions.

Consistent gets:

  • kcbgtcr - Kernel Cache Buffer Get Consistent Read. This is general entry point for consistent read.
  • kcbldrget - Kernel Cache Buffer Load Direct-Read Get. The function performing direct-path read.

Read more… 639 more words

A new version of a nifty tool to dig into internals of logical IO by Alexander Anokhin and Andrey Nikolaev

Reading AWR reports: case study

14 Mar

In my previous post I described some sections that are typically useful when interpreting AWR data. However, sometimes the answer comes from an unexpected source. For example, the workload profile section of the report contains key information for understanding what the database looks like, but it seldom gives a direct answer to the problem (except for maybe excessive parsing and excessive commits). But recently I came across a case when this section was enough to identify the root cause of a non-trivial issue: Continue reading →

Tags: , ,

Follow

Get every new post delivered to your Inbox.

Join 72 other followers