Oracle Scratchpad

March 27, 2013

Open Cursors

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:13 pm BST Mar 27,2013

Here’s a little detail that appeared in 11gR2 that may help you answer questions about open cursors. Oracle has added a “cursor type” column to the view v$open_cursor, so you can now see which cursors have been held open because of the pl/sql cursor cache, which have been held by the session cursor cache, and various other reasons why Oracle may take a short-cut when you fire a piece of SQL at it.

The following is the output showing the state of a particular session just after it has started up in SQL*Plus and called a PL/SQL procedure to run a simple count:

March 4, 2013

Duplicate indexes ?

Filed under: Indexing,Oracle,Troubleshooting — Jonathan Lewis @ 5:39 pm BST Mar 4,2013

I don’t think this is likely to happen on a production system (until 12c) – but look what you can do if you try hard enough:

  1  select
  2     index_name, column_name from user_ind_columns
  3  where
  4     table_name = 'T1'
  5  order by
  6*    index_name , column_position
SQL> /

-------------------- --------------------
T1_I1                N1

T1_I2                N1

4 rows selected.

That’s a straight cut-n-paste from an Oracle SQL*Plus session. (You can tell I typed it in real time because I missed the return before the FROM, and couldn’t be bothered to go back and do it again ;) )

February 22, 2013

Deadlock Detection

Filed under: deadlocks,Locks,Oracle,Troubleshooting — Jonathan Lewis @ 6:19 pm BST Feb 22,2013

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):


February 18, 2013

Transactions 2

Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 7:54 am BST Feb 18,2013

Here’s a little follow-on from Friday’s posting. I’ll start it off as a quiz, and follow up tomorrow with an explanation of the results (though someone will probably have given the correct solution by then anyway).

I have a simple heap table t1(id number(6,0), n1 number, v1 varchar2(10), padding varchar2(100)). The primary key is the id column, and the table holds 3,000 rows where id takes the values from 1 to 3,000. There are no other indexes. (I’d show you the code, but I don’t want to make it too easy to run the code, I want you to try to work it out in your heads).


February 15, 2013


Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:07 am BST Feb 15,2013

It’s very easy to get a lot of information from an AWR (or Statspack) report – provided you remember what all the numbers represent. From time to time I find that someone asks me a question about some statistic and my mind goes completely blank about the exact interpretation; but fortunately it’s always possible to cross check because so many of the statistics are cross-linked. Here’s an example of a brief mental block I ran into a few days ago – I thought I knew the answer, but realised that I wasn’t 100% sure that my memory was correct:


January 28, 2013

Losing it

Filed under: Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 6:08 pm BST Jan 28,2013

The example I gave last week showing how a SORT operation in an execution plan might include the work of resolving function calls in your SQL and might, therefore, be reporting much higher resource utilisation than expected reminded me of some problems I’ve had with gaps in execution plans in the past. So I thought I’d give a little demonstration of the way in which the completeness of execution plans can develop over time.


January 25, 2013


Filed under: Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 5:45 pm BST Jan 25,2013

Here’s a little quirk of execution plans that came up recently on the Oak Table network. If you call a function in a query, and do some sorting with the results, where does the work of calling the function get reported in the execution plan if you trace the query or look at the in-memory rowsource execution stats. Let’s take a look at a simple example:


January 11, 2013

Quiz Night

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 6:41 pm BST Jan 11,2013

Warning – this is a catch question, and I haven’t given you enough information to have any idea of the right answer; though, by telling you that I haven’t given you enough information to have any idea of the right answer, you now have some information that might help you to get closer to the right answer.


January 7, 2013

Analysing Statspack 13

Filed under: Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 6:44 pm BST Jan 7,2013

A recent (Jan 2013) post on the OTN database forum reported a performance problem on Oracle (so no AWR), and posted a complete statspack report to one of the public file-sharing sites. It’s been some time since I did a quick run through the highlights of trouble-shooting with statspack, so I’ve picked out a few points from this one to comment on.

As usual, although this specific report is Statspack, the same analysis would have gone into looking at a more modern AWR report, although I will make a couple of comments at the end about the extra material that would have been available by default with the AWR report that would have helped us help the OP.


December 7, 2012

Update Error

Filed under: Execution plans,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 5:10 pm BST Dec 7,2012

When doing updates with statements that use correlated subqueries, it’s important to make sure that your brackets (parentheses) are in the right place. Here are two statements that look fairly similar but have remarkably different results – and sometimes people don’t realise how different the statements are:


November 30, 2012


Filed under: AWR,Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 5:45 pm BST Nov 30,2012

Sometimes you find bugs on MOS (Metalink, OCIS, whatever) that make you feel positively ill. I’ve just been on a customer site where (in passing) they mentioned that one of their historic queries against v$sqlstats now tool just over one second (CPU) in when it had previously taken about 200ms on***. After a little checking it seemed likely that the change was possibly related to the fact that they had increased the size of the SGA significantly, allowing for a much larger shared pool and library cache; however there have been numerous code changes in the shared pool area on the route from 10g to 11g, so I decided to check MOS to see if anyone else had seen a similar problem. I found this:


November 6, 2012


Filed under: Oracle,Troubleshooting,Tuning — Jonathan Lewis @ 5:47 pm BST Nov 6,2012

Possibly an item to file under “philosophy”, but a question came up in the seminar I was presenting today that prompted me to blog (very briefly) about why I manage to be so good at avoiding errors and inventing workarounds to problems. You probably know that you may see an execution plan change when you add a foreign key constraint to a table – but today someone in the class asked me if this would still work if the constraint were to be disabled. The immediate response that sprang to my mind was “surely not” – but the second thought was that I didn’t really know the answer and would have to check; and the third thought was that maybe it wouldn’t if disabled, but what about novalidate; and the fourth thought was whether the setting for query_rewrite_integrity would make a difference; and the fifth thought was to wonder if there were other conditions that mattered.

So hey-ho for the weekend, when I have to set up a test case for a query that changes plan when I add a foreign key constraint – and then try (at least) eight different combinations of circumstances to check what it really takes to make the change happen.

August 30, 2012


Filed under: Bugs,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 5:55 pm BST Aug 30,2012

I’ve given examples in the past of how you can be suprised by Oracle when a mechanism that has “always worked” in the past suddenly just doesn’t work because some unexpected internal coding mechanism hits a boundary condition. One example I gave of this was rebuilding indexes online – where a limitation on the key size of index organized tables made it impossible to do an online rebuild of an index on a heap table because of an ORA-01450 (maximum key length) error that was raised against the (index-organized) journal table that  Oracle creates internally to support the rebuild.

Slightly more recently the topic of the sys_op_map_nonnull() function came up in some comments about the ora_hash function – and I made a passing comment about the function appearing in materialized view refreshes and then never followed it up. Fortunately I got an email from Jared Still and Don Seiler a few days ago which contained a nice short demonstration of the problem so, with their permission, I’ve published it below.

July 25, 2012

Questions – 1

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 5:13 pm BST Jul 25,2012

A recent question on the OTN Database forum:

If the block size of the database is 8K and average row length is 2K and if we  select all column of the table the I/O show that it had read more blocks then compare to specific column of the same table. Why is this?

Secondly if Oracle brings a complete block is the db buffer cache, while reading block from disk, they why there is a difference of block count in two queries. This difference reveals to me when I check the EXPLAIN PLAN for two different queries against the same table but one select all columns and the other one select specific column.

Kindly help me in clearing this confusion.

I can’t see anything subtle and complex in the problem as stated, so why doesn’t the OP give a clear explanation of what’s puzzling them?

February 27, 2012

Geek Stuff

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 5:50 pm BST Feb 27,2012

A recent post on the OTN database forum raises a problem with v$sql_shared_memory:

query to V$SQL_SHARED_MEMORY don’t return rows

please explain why ?

A follow-up posting then describes how the OP picked the view definition from v$fixed_view_definitions and used the text of that query instead of the view itself – and still got no rows returned:

(select /*+use_nl(h,c)*/ c.inst_id,kglnaobj,kglfnobj, kglnahsh, kglobt03, kglobhd6, rtrim(substr(ksmchcom, 1, instr(ksmchcom, ‘:’, 1, 1) – 1)), ltrim(substr(ksmchcom, -(length(ksmchcom) – (instr(ksmchcom, ‘:’, 1, 1))), (length(ksmchcom) – (instr(ksmchcom, ‘:’, 1, 1)) + 1))), ksmchcom, ksmchptr, ksmchsiz, ksmchcls, ksmchtyp, ksmchpar from x$kglcursor c, x$ksmhp h where ksmchds = kglobhd6 and kglhdadr != kglhdpar)

The answer is quite simple – but in two parts.


« Previous PageNext Page »

The Rubric Theme Blog at


Get every new post delivered to your Inbox.

Join 3,454 other followers