Pythian Group

I was asked, the other day, to automate the creation of a client’s weekly report, which is a pivot table of some aggregate data generated by a query.

As we know, prior to 11g, Oracle did not have a simple table pivot feature. 11g has changed that, and the pivot clause is certainly useful. It requires, however, an aggregation calculation in the intersection (at least that’s what I got from the documentation). But what if you already have the data to populate in the intersection area? Or, you may no longer have the raw data to aggregate it again. In that case, you are forced to trick it to get an aggregation in.

I’ve written a set of fairly simple PL/SQL code that handles this task in all versions of Oracle that supports associative nested VARRAYs (I have no clue when that started getting supported. It sure works great with 10g).

Here is how my code works: Read the rest of this entry . . .

This has been discussed before by my colleague Paul Moen in his article on Oracle standby recovery rate monitoring, but I recently made a discovery that makes it easier to generate both statistics on log apply performance, and more useful stats too.

First, let me say that this discovery is based on my observations and has not been verified with Oracle Support nor by any insider. If you know one who can confirm this, please ask him or her.

The discovery is a hidden column in the x$kcclh table, which is the underlying table for gv$log_history (and therefore v$log_history). It’s the only column not exposed in the fixed views. Why? I don’t know but it sure would be nice to have it exposed and clearly (officially) defined. The column is x$kcclh.lhtsm. It stores the timestamp of when the log began to be applied. The same timestamp as the one printed in the alert log for the “Media Recovery Log…” message and the same timestamp as the one recorded in the v$dataguard_status.message text column.

The advantage of accessing this information from the x$kcclh table is that you get access to more historical data (the number of days will vary based on your redo log switch rate and the size of the control files and possibly on other factors).

The time difference between two consecutive logs, say 1 and 2, gives us the time spent applying the log 1. At least that’s the theory. Some factors that we can’t really take into account are the log delay set in the log_archive_dest_n parameter on the primary db (it’s an optional setting), and any unexpected events, such as an MRP0 process getting hung or a FAL server having trouble to send a log, etc.

Nevertheless, I feel it is very useful information. Any anomaly can be easily identified as it would come up with an unusual elapse time.

Here’s the query I use to get the log apply elapse time:

Read the rest of this entry . . .

Have you ever wanted to know where exactly in a datafile segments are placed? Have you ever wondered just how fragmented a tablespace is? How about how much space you could reclaim if the last segment of a datafile was moved out of the tablespace? These questions, and many more, can be easily answered with a detailed extent map of the datafiles.

Yes, I know, this subject is a rather old one, and a few different solutions have been provided by several professionals, including the famous Tom Kyte. But none of the answers I found did exactly what I wanted, and therefore, I chose to write my own solution. OEM does provide this, but for a price — the Tablespace Map is part of the Oracle Tuning Pack — and I like the free stuff and the extra flexibility I have using queries.

As all of you know, Oracle has been providing this extent map via the dba_extents view forever (or at least since v6, which is the version I first worked with). The problem is that today’s super-large databases tend to have segments with thousands of extents. These extents are either of fixed size (LMT Uniformed Sized, DMT with pct_increase=0) or variable sizes (DMT, system-managed extent LMTs). In both cases, the dba_extents view provides one entry per extent, but several of these extents may in fact be contiguous extents. To answer the questions I began with, a view of this extent list aggregated by segment is much more useful. Read the rest of this entry . . .

Woohoo, I finally got a bit of spare time to blog — my first blog post ever!

I chose to talk about a technique I used at a client’s site to report the topmost space-wasting objects in an Oracle database. I was looking for a way to detect these objects without having to run some expensive analyze statements or dbms_stats jobs. I found out that I can use the dbms_space package to do this. It worked very well for me and I’m sure lots of DBAs could use this technique too. It is not perfect in all situations as it has some prerequisites that I will list later, but still it does the trick for me.

The dbms_space package has procedures that are very useful for determining how much space is used within db blocks. As far as I know, this information is determined by looking at the tablespace bitmap pages. I haven’t confirmed this yet, but that would explain why it is so much faster than running an analyze or dbms_stats.gather_%_stats.

So, the idea is to generate the space-usage information using dbms_space, store that information into a table, and then run queries on it to report the topmost space-wasters.

Read the rest of this entry . . .