Troubleshooting

Here’s an odd little bug (I think) in the optimizer that showed itself in a thread on the OTN database forum some time ago. The optimizer can choose an index which is NOT the cheapest index for satisfying a query against a single table. Here’s the demo – which I’ve run against 11.1.0.6 using an 8KB block size, ASSM and system allocated extent sizes:

I finally finished my first Exadata performance troubleshooting article.

This explains one bug I did hit when stress testing an Exadata v2 box, which caused smart scan to go very slow – and how I troubleshooted it:

• Troubleshooting Exadata v2 Smart Scan Performance

Thanks to my secret startup company I’ve been way too busy to write anything serious lately, but apparently staying up until 6am helped this time! :-) Anyway, maybe next weekend I can repeat this and write Part 2 in the Exadata troubleshooting series ;-)

Enjoy! Comments are welcome to this blog entry as I haven’t figured out a good way to enable comments in the google sites page I’m using…

From time to time people run into problems with UNDO tablespaces that have grown much larger than expected (perhaps due to a rogue process doing far too much work) and refuse to shrink. The workaround is to create a new undo tablespace and switch the instance to use it – but even this simple procedure [...]

In case you don’t follow the link to Martin Widlake’s blog (see right) very often, he’s done a couple of recent posts on dba_tab_modifications that are worth reading. (And I’ve just discovered the ‘gutter=”false”;’ option for the ‘sourcecode’ tag in one of the comments on Martin’s blog – and that’s also a  helpful feature.) Filed [...]

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]

From time to time I’ve warned people that subquery factoring should be used with a little care if all you’re trying to do is make a query more readable by extracting parts of the SQL into “factored subqueries” (or Common Table Expressions – CTEs – if you want to use the ANSI term for them). [...]

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] 
{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

If you want to be good at trouble-shooting one of the most important skills you have to learn is how to examine the evidence. Here’s a section of a trace file that was published recently on the OTN database forum. There’s an interesting detail that you might pick out – how long will it take you [...]

Do you think you know how Oracle records access to migrated or chained rows ? You may need to check your theories. Here’s a little demonstration that may amuse you. It uses an 8KB block size, a locally managed tablespaces (LMT) with uniform extent sizes of 1MB, and freelist management rather than automatic segment space [...]

A recent question on OTN gave the following information: I’m having problem with my database (Oracle 8.1.7.2.0 on Solaris 9), which contains more than 1 row with a same value on a field that has uniqueness constraint. Could you please help tell me how to fix this? Here is the log from sqlplus. When I [...]