Oracle

Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:

• how to gather fixed object stats for a given X$
• should I gather fixed objects stats on both RAC nodes
• why I can’t gather stats on some X$-tables

At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks

Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 11.1.0.7 database:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_8K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_8K               3        128    1048576        128            3
TEST_8K               3        256    1048576        128            3
TEST_8K               3        384    1048576        128            3
TEST_8K               3        512  130023424      15872            3

4 rows selected.

The answer in this case is simple – here’s what I did just before running my query:

I spent yesterday neatening up a few old articles. For the most part it is a bit of a dull process, but it has to be done every so often.

I was doing a little research for an upcoming presentation on Oracle 12c Adaptive Optimization and I came across a series of posts on Dynamic Sampling by Randolf Geist (one of my favorite Oracle smart guys). I could not find a complete list of the series of posts either through Google or the search function on the site where they were posted. I guessed at a couple of the url’s and got lucky. Thought it might be useful at some point in the future so I wrote them down on my blog (which is where I often keep private notes). But decided it might be useful to others so decided to go ahead and publish it.

Thank you for attending the Exadata Snapper (ExaSnapper) hacking session!

I have split the recording of this session into 3 pieces and uploaded to enkitec.tv. The ExaSnapper v0.7 beta that I demoed is also available now at enkitec.com (registration needed). See the links below.

For quick reference, here’s the syntax of running ExaSnapper – there are two modes, one is the before/after capture (think Tom Kyte’s runstats, but for exadata metrics) and the other is more like a DBA-monitoring mode, where you can just measure a few seconds worth of a long-running query runtime and get the IO and efficiency figures from there.

Here’s an excerpt from the install script documentation section:

By some strange coincidence, the “London Bus” effect perhaps, there have been three posts on the OTN database forum in the last couple of days relating to deadlocks; and they have prompted me to indulge in a little rant about the myth of Oracle and deadlock detection; it’s the one that goes:

“Oracle detects and resolves deadlocks automatically.”

Oracle may detect deadlocks automatically, but it doesn’t resolve them, it simply reports them (by raising error ORA-00060 and rolling back one statement) then leaves the deadlocked sessions stuck until the session that received the report resolves the problem or an external agent resolves the problem.

Consider the following example (which, I have to admit, I wrote without access to a live instance):

This blog entry is about modern Linuxes. In other words RHEL6 equivalents with 2.6.3x kernels and not the ancient RHEL5 with 2.6.18 kernel (wtf?!), which is the most common in enterprises unfortunately. And no, I’m not going to use kernel debuggers or SystemTap scripts here, just plain old “cat /proc/PID/xyz” commands against some useful /proc filesystem entries.

Troubleshooting a “slow” process

Here’s one systematic troubleshooting example I reproduced in my laptop. A DBA was wondering why their find command had been running “much slower”, without returning any results for a while. Knowing the environment, we had a hunch, but I got asked about what would be the systematic approach for troubleshooting this – already ongoing – problem right now.

Luckily the system was running OEL6, so had a pretty new kernel. Actually the 2.6.39 UEK2.

A customer of mine run out of memory due to much server processes of dedicated connected client sessions. As an alternative I tried to explain the options between DEDICATED and SHARED SERVER concepts as an initial attempt/workaround for the client software problems. Looking around on the internet for pictures and or newbie documentation, I found …

Continue reading »

If you already downloaded snapper v4, then better re-download it again as the v4.03 also runs in SQL Developer!

Snapper used to require access to DBMS_LOCK, so it could sleep for X seconds between the “before” and “after” performance data snapshots. Now it is possible to get away without using DBMS_LOCK. Instead you will run Snapper twice, once for taking the “before” snapshot, then run your workload and then run Snapper again for taking the “after” snapshot and print the output.

So, the usual way of running snapper is this:

@snapper4 all 5 1 152

This would take 1 5-second performance snapshot SID 152′s V$ views.

With Snapper4 you can use the old way or just add BEGIN or END keywords to the 1st parameter, like this: