Troubleshooting catalogue

This is a list of all articles I’ve written that describe some troubleshooting ideas. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

• Analytic Sorts (Nov 2022): Investigating a performance issue and finding a hole in a gap in an internal optimisation
• Lost Space (Nov 2022): How dbms_space_admin fixed my bitmaps when dba_free_space “lost” some free space.
• PL/SQL Labels (Oct 2022): Using labels as targets for goto, exit, and continue
  • PL/SQL labels (Apr 2017): reducing errors by using “block” labels to qualify pl/sql variable names.
• v$session_longops (Oct 2022): how to interpret the information. It’s not the answer, it’s a clue.
• Tracing tip (Oct 2022): Enabling tracing by process name – 11g and 19c
• Parallel Default (Sept 2022): Why did my query run parallel? A possible reason and a simple demo.
• Hash Joins and functions (July 2022): tracking down why a trim() function in a hash join predicate used 1,800 seconds of CPU.
• Pagination Costs (July 2022): select with offset N next M can change plans as N and/or M vary.
  • Fetch Fetch and FBIs (June 2019): a error of omission (with workaround) in the optimizer’s treatment of FBIs with Fetch First / Top-N (last tested 19.11)
  • Fetch First under the covers (July 2013): How does Oracle transform Fetch First / Top-N before optimising – should be retested for newer versions.
  • Parallel Fetch First under the covers (July 2013): How effective is the optimizer with Fetch First / Top-N when executing in parallel
• LOB Space (July 2022): considerations when comparing dbms_space.space_usage() with sum(dbms_lob.getlength())
• Index Wildcard (July 2022): Solving a problem of a case where a special case fix control had a bad side effect on a cursor_sharing damage-limitation strategy.
• ORA-00054 pt.1 (July 2022): Trouble-shooting with errorstack and ksq trace
  • ORA-00054 pt.2 (July 2022): Trouble-shooting with systemstate trace
• ORA-39857 (June 2022): using a “drop tablespace” problem to describe a generic method for finding out why Oracle is not doing what you thought it would.
• Case Study (June 2022): an example of solving a performance problem by reading an SQL Monitor report
• Adaptive Plans (April 2022): what they can cost and how block then selectively
• PL/SQL Warning (April 2022): an aid to pre-empt trouble-shooting – automatic warnings of pl/sql coding flaws
• Finding SQL (Aug 2021): currently includes ideas on finding “who has been using up all the temporary space”.
• Memory allocation (Aug 2021): SGA target, PGA target, Memory target: where’s the memory going.
• Cleaning the audit trail (July 2021): playing about with hexadecimal pinpoints a bug in dbms_audit_mgmt.clean_audit_trail
• Disappearing NOT NULL constraints (July 2021): the effects of the link between primary keys and not null declarations
• v$resource_limit (March 2021): reference note and simple script
• Dates and formats (Dec 2020): It’s important to use date datatypes, and check for type-conversion threats.
• Java Names upgrade (Dec 2020): how an upgrade from 12c to 19c caused an error in “home-grown” auditing.
• Interval partition serialising (Feb 2020): Why did a parallel insert into an interval partitioned table serialise through one PX process – conjecture and workaround.
• Troubleshooting (August 2019): A generic strategy and specific example of how to re-engineer certain classes of query
• Limited tracing (May 2019): How to enable tracing system wide with a limit on how many times the trace is dumped per session.
• Comparing Execution Plans (Mar 2018): A case study in finding “what changed”.
• Interval Partitioning load threat (Feb 2018): a big insert as select, create as select to load/create an interval partitioned table can produce a MASSIVE sort.
• v$lock efficiency (May 2013): the benefit of collecting stats on individual x$ structures, and how to avoid dbms_stats.gather_fixed_object_stats
• Old Plan_table (Jan 2010): why dbms_xplan.display() might have a note: ‘PLAN_TABLE’ is old version
• PGA Memory (June 2009): checking process memory and process memory detail.
• v$session_longops (June 2007): Link to a paper (now incomplete) discussing what gets reported in v$session_longops.