Oracle Scratchpad

June 7, 2011

Audit Excess

Filed under: audit,Bugs,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:18 pm BST Jun 7,2011

So you’ve decided you want to audit a particular table in your database and think that Oracle’s built in audit command will do what you want. You discover two options that seem to be relevant:

audit all on t1 by access;
audit all on t1 by session;

To check the audit state of any object in your schema you then run a simple query – with a few SQL*Plus formatting commands – to see something like the following:

(more…)

April 20, 2011

Evidence

Filed under: Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:12 pm BST Apr 20,2011

Here’s a nice example on the OTN database forum of Dom Brooks looking at the evidence.

  • The query is slow – what does the trace say.
  • There’s “row source execution” line that says we get 71,288 rows before doing a hash unique drops it to 3,429 rows.
  • There’s a statement (upper case, bind variables as :Bn) in the trace file that has been executed 71,288 times
  • A very large fraction of the trace file time is in the secondary statement
  • There’s a user-defined function call in the original select list, before a ‘select distinct’.

Conclusion: the code should probably do a “distinct” in an inline view before calling the function, reducing the number of calls to the function from 71,288 to 3,429.

Footnote: There may be other efficiency steps to consider – I’m always a little suspicious of a query that uses “distinct”: possibly it’s hiding an error in logic, possibly it should be rewritten with an existence subquery somewhere, but sometimes it really is the best strategy. There’s are some unusual statistics names coming from autotrace in the OP’s system – I wonder if he’s installed one of Tanel Poder’s special library hacks.

December 9, 2010

Geek Stuff – 2

Filed under: trace files,Troubleshooting — Jonathan Lewis @ 6:50 pm GMT Dec 9,2010

This is a post specially for Junping Zhang, who has been waiting patiently for a follow-up to my geek post about find the system-level setting for parameter use_stored_outlines. His question was: “Is it also possible to find the session-level setting ?”

The answer is yes – but it’s harder, version specific, requires a little preparation, and only allows you to see the setting for your own session.
(more…)

April 30, 2010

10053 viewer

Filed under: CBO,Execution plans,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 7:49 pm BST Apr 30,2010

I’ve been trying to find a way to post an executable for several weeks because I’ve been sent a simple viewer for 10053 trace files written by Hans-Peter Sloot of Atos Origin and Robert van der Ende. They wrote this viewer because trace files from event 10053 can be enormous, and scrolling back and fore through them to cross reference the interesting bits can be extremely tedious. Their “tree-viewer” allows you to see all the important headings and expand only the detail you’re interested in.
(more…)

March 30, 2010

heap block compress

Filed under: Infrastructure,trace files — Jonathan Lewis @ 7:24 pm BST Mar 30,2010

In a recent note showing how an index could become much larger than the underlying table because of the different ways that Oracle handles deletion from table and index blocks, I pointed out that Oracle would have to pick a moment to replace rows marked for deletion with a stub, or place-holder, showing where the row had been but reserving the rowid in case a process rolled back the delete. (This tied back to a note I had written showing that the row directory in a table block could become much larger than you might think possible.)

My closing question asked when Oracle would do this replacement, and how you could monitor it. This note is a partial answer to that question.

(more…)

March 7, 2010

Treedump – 2

Filed under: Indexing,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 6:32 pm GMT Mar 7,2010

In an earlier article about investigating the state of an index in detail I supplied a piece of SQL that would analyse an index (no, not using the Analyze command) and summarise the number of entries in each leaf block that currently held any entries at all. Here’s a sample of the type of output it produced:

(more…)

December 28, 2009

Short Sorts

Filed under: Infrastructure,Oracle,Performance,sorting,trace files,Tuning — Jonathan Lewis @ 7:29 pm GMT Dec 28,2009

I posted a little holiday quiz – timed to appear just before midnight (GMT) on 24th December – that asked about the number of rows sorted and the memory used for queries like:

select sortcode
from
        (
        select sortcode
        from   t1
        order by
                sortcode
        )
where
        rownum <= 10
;

The number and variety of the responses was gratifying. It’s always interesting to see how many important little details appear as people start to tackle even fairly straight-forward questions like this.

(more…)

December 16, 2009

Adaptive Optimisation ?

Filed under: CBO,Execution plans,trace files — Jonathan Lewis @ 11:53 pm GMT Dec 16,2009

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.

Since it’s running on 11.2.0.1 your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t include any bind variables.

Gregory emailed me about this one, and  it was the thing that finally persuaded me to tear down a laptop and install 64-bit OEL with 11.2 – and I ran his test case and got the same results. (If you build his sample schema, you’ll need about 1GB of free space).

(more…)

September 7, 2009

Analytic Agony

Filed under: Infrastructure,Oracle,Performance,sorting,trace files,Troubleshooting — Jonathan Lewis @ 5:30 pm BST Sep 7,2009

When I wrote Practical Oracle 8i, version 8.1.5 was the most recent version of Oracle but version 8.1.6  came out just before I finished writing – and the only thing in 8.1.6 I thought important enough to add to the book was a section on Analytic Functions because they were the best new coding feature in the product.

Since then I’ve always warned people to be a little careful about how they use analytic functions because of the amount of sorting they can introduce. My suggestion has always been to crunch “large” volumes of data down to “small” volumes of data before applying any analytic functions to add “intelligence” to the intermediate result.
(more…)

August 17, 2009

treedump

Filed under: Indexing,Infrastructure,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 5:31 pm BST Aug 17,2009

Here’s a mechanism for examining indexes in some detail if you think that something odd may be going on inside them. It’s a feature that I first decribed in Practical Oracle 8i, although the book doesn’t mention a problem with it that I subsequently discovered that means I always test it carefully on a small index before I use it on a large one.  Here’s a demonstration – cut from an SQL*Plus session on 10.2.0.3:

(more…)

July 24, 2009

IQ2 – Answers

Filed under: Index Explosion,Indexing,Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 9:04 pm BST Jul 24,2009

I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do that in postings.

Question 1: I’ve created a table and index with initrans 4, then inserted one row into the table. How many ITL (interested transaction list – see glossary) entries will there be in the first block of the index when you dump it.

Answer 1: The table block will show 4 entries in the ITL, obeying your setting for initrans, but the index block will show only two entries – unless you’re using Oracle 8i or earlier (Basically indexes tend to ignore the setting for initrans  except when you rebuild an index, or create it on existing data.):
(more…)

June 7, 2009

PGA leaks

Filed under: Infrastructure,trace files,Troubleshooting — Jonathan Lewis @ 7:53 pm BST Jun 7,2009

Here’s a simple script that I created a short time ago while investigating a memory problem on a client site. The purpose of writing the script was, as always, to strip the client’s code back to a bare minimum in an attempt to work out the root cause of a problem. (Warning: if you want to run this script, your Oracle shadow process will grab about 1GB of PGA RAM )

(more…)

May 14, 2009

Consistent Gets

Filed under: Execution plans,Performance,trace files,Troubleshooting — Jonathan Lewis @ 8:13 am BST May 14,2009

Today’s little quiz – just for fun, but prompted by a few comments in this posting on OTN: (more…)

March 22, 2009

Block size – again

Filed under: Block Size,Infrastructure,Performance,trace files,Troubleshooting,undo — Jonathan Lewis @ 7:09 pm GMT Mar 22,2009

Here’s a little oddity that I came across at a client site recently.

The client called me in because they were having problems with Oracle error “ORA-01555: snapshot too old” appearing in a particular task after a few thousand seconds (typically 5,000 to 6,000) even though they had set the undo_retention to 14,400 seconds and had a huge undo tablespace running with autoextend enabled on the data files.

(more…)

January 4, 2009

Index rebuild – again

Filed under: Index Rebuilds,Indexing,Infrastructure,trace files — Jonathan Lewis @ 7:10 pm GMT Jan 4,2009

Someone posted a very pertinent question about blocks splits and index rebuilds on the OTN Database forum yesterday covering a detail of the cost/benefit equation that I don’t think I’ve mentioned before.

Since I answered the question on the forum I’ve posted a link here to make it available to a wider audience. 

(This is a strategy I may adopt more frequently in the future – there’s a lot of useful material of mine all over the internet, and I really ought to make sure I don’t spend time repeating myself when  simple pointer would do).

[Updated May 2009: The forum item has since gone missing – so maybe linking to notes I’ve written in public groups is not so smart after all]. Fortunately I happen to have been saving copies of the thread as it progressed, and the little point that I wanted to highlight was just the following:

“When you create or rebuild an index Oracle does not honour the pctfree setting in the branch blocks. Since the branch blocks are effectively 100% packed, the very first leaf block split in each branch block (except the last one) will almost certainly result in a branch block split. This means a single row insert into an 8KB block could result in 16KB+ of redo for the leaf block plus 16KB+ for the branch block split.”

[Further reading on rebuilding indexes]

« Previous PageNext Page »

Website Powered by WordPress.com.