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.

• Querying LONGs (March 2024): They shouldn’t be there, but sometimes you can’t avoid them
• Missing partitions (March 2024): how interval partitioning can produce misleading plans
• Disabling oradebug (March 2024): it is possible to block any calls to oradebug.
• SQL trace 23c (November 2023): 23c allows you to enable “adaptive” sql trace through dbms_session and dbms_monitor
• Pragma Deprecate (October 2023): using pl/sql warnings to report deprecated internal pl/sql code.
  • PL/SQL Warnings (April 2022): an aid to pre-empt trouble-shooting – automatic warnings of pl/sql coding defects
• Case Study (September 2023): Wrong results from a transformation to a lateral view with merge join
• PDML disabled (July 2023): Why is my insert/update/delete/merge not executing in parallel
• Case Study (June 2023): tweaking an in-line scalar subquery
• Case Study (June 2023): querying a very large GTT – indexes or “materialized views”
• Case Study (Jan 2023): finding information from a minimal amount of data
• Case Study (Jan 2023): how to work out why a query is “going slowly” and how to fix it
• 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
• 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.
• Too Many Parse Errors (Oct 2017): notes on a warning that can appear in the 12.2 alert log.
  • Parse errors (Oct 2017): a warning about the impact of parse errors on the shared pool
• Segmented trace files (Jan 2016): It is possible to set trace parameters to break a trace file into chunks, and only keep the first and most recent chunks.
• v$lock efficiency (May 2013): the benefit of collecting stats on individual x$ structures, and how to avoid dbms_stats.gather_fixed_object_stats
• Double Trouble (May 2010): A strategy for rewriting a query to minimise the work done filtering data by using a “two-pass” approach to critical tables.
  • Manual Optimisation (Apr 2008): An early variation on the “two-pass” theme
  • Manual Optimisation pt.2 (May 2008): a follow-up to part 1.
  • Manual Optimisation pt.3 (Oct 2008): a continuation of part 2.
• 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.
• Variable Performance (Aug 2007): a strategy for investigating if variations in performance are due to overloaded hardware.
• v$session_longops (June 2007): Link to a paper (now incomplete) discussing what gets reported in v$session_longops.
• Optimizer Environment (May 2007): seeing details of the optimizer parameter settings in v$sql_optimzer_env etc. (Updated to 23c).
• Filter Subqueries (Nov 2006): An early (but still relevant) explanation of the random performance impact of Scalar Subquery Caching