Here is the sqlplus scripting I use to check whether row chaining is degrading a query’s performance:
VARIABLE monitored_sid number;
SELECT sid into :monitored_sid
select b.value "table fetch continued rows"
where b.SID = :monitored_sid
and b.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME
WHERE NAME='table fetch continued row');
I create a test script with the problem query and put this code after it to (more...)
Today is the second anniversary of my first post on this blog.
Some time ago I played with a free blog that came with a yahoo email account that my wife and I share but didn’t get very far with that. Finally for this blog I decided to spend a small amount of money to get my own hosting and domain name.
I’m using iPage for the hosting. I’ve been happy with their (more...)
I’m trying to get up to speed on 12c, especially the multitentant features. So, I decided to read the sections of the manuals that related to using multitentant. Here is the name of the manual I was looking at:
Oracle® Database Administrator’s Guide 12c Release 1 (12.1)
It isn’t great reading. It is kind of slow-paced and really more of a reference but since it is a large new feature I (more...)
Saw this blog post about how Cary Millsap learned about Oracle performance tuning and I thought it was interesting:
It is interesting because I’ve learned so much from Cary Millsap and Jeff Holt’s book and it is cool to see how Cary got to the position he is in as a well-known Oracle performance expert. I guess I’ve always found the performance tuning part of my Oracle DBA job the (more...)
In an earlier post about how query high compression eliminated row chaining in tables with more than 255 columns I got question about whether updates to tables with more than 255 columns and query high compression ran slower with this type of compression than on an uncompressed table.
The answer is that, at least in the tests I ran, the updates of a table with more than 255 columns took much longer on a table (more...)
I was on call again last week and again had to do a database recovery using RMAN. I thought I might as well document some lessons learned even though it went quite smoothly.
First, here are the steps I took. I’ve modified the commands and script so that our real usernames, passwords, database names, and host names are obscured, but otherwise it is exactly what I ran.
Environment wise we are on 11. (more...)
We appear to be hitting this bug on our production Exadata RAC system:
Bug 14383007 – Sort runs out of temp space in RAC even when temp space is available
One particular job errors out with ORA-01652 errors on the TEMP tablespace even though there is tons of free space in the sort segments. So, I got the idea of building a work around to have this job only login to the node that (more...)
I’ve been having issues for a long time now with an Exadata RAC database that has user reports experiencing library cache lock waits. The challenge is to figure out what is holding the library cache locks that the queries are waiting on.
My starting point on library cache locks has always been this Oracle support document:
How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)
But it (more...)
I wanted to find out if queries against a particular table were experiencing parse time during the week. We had previously seen issues where queries on the table would be stuck parsing for tens of minutes before they started actually running. I think this has been resolved but I’m looking for some validation from ASH. I know that on Sundays we recompress some subpartitions for the table and this leads to higher (more...)
A coworker of mine cleaned up a bunch of old data to improve performance on an older system of ours and one of the queries started running slower. It looks like the optimizer was choosing a full table scan when an index existed that was much faster. So, I took at look at why it was choosing the full scan and what could be done to get the query to run with the (more...)