Oracle Scratchpad

February 1, 2022

Troubleshooting catalogue

Filed under: Oracle,Troubleshooting — Jonathan Lewis @ 11:31 am GMT Feb 1,2022

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-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.

1 Comment »

  1. […] Troubleshooting catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — February 1, 2022 @ 11:32 am GMT Feb 1,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: