The query seems efficient - why poor performance?

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Mon, 30 Jun 2014 04:59:32 -0700 (PDT)
Message-ID: <7b9ac689-360d-46f9-b49e-201ed4c88e0e_at_googlegroups.com>



This is Oracle 9.2.0.8 on a Sun SPARC server There is a query (query#1) that gets executed approx 300,000 times per day. Average time per execution = 390 milliseconds.

It seems very slow taking into account that it doesn't do much:
- buffer gets per execution: 4,547

  • disk reads per execution: 0 (to be precise: 1,576 reads for 295,000 executions)
  • rows returned per execution: 6
  • CPU time per execution: 390 milliseconds
  • Elapsed time per execution: 390 milliseconds

This is straightforward join: table A is joined to B, B is joined to C, C is joined to D, D is joined to E. No sub-queries. It returns one column (VARCHAR2(10)) from table D. Table A is accessed through unique index on AccountID (using bind variable), the joins are on columns with unique indexes or indexes with good selectivity. This is conventional SELECT, not SELECT FOR UPDATE. All tables are conventional, no remote tables, collections, index-organized, views, materialized views, V$ or X$ views. No LOB, LONG, RAW etc columns, user-defined types, functions. No DISTINCT, no missing join conditions (this isn't Cartesian join). All joins are inner joins - no outer joins. The plan for this query uses NESTED LOOPS, TABLE ACCESS BY INDEX ROWID, INDEX UNIQUE SCAN, INDEX RANGE SCAN, and CONCATENATION (because of OR condition).

In the same database we have a query (query#2) with these characteristics:
- buffer gets per execution: 171,017

  • disk reads per execution: 13
  • rows returned per execution: 28
  • CPU time per execution: 440 milliseconds
  • Elapsed time per execution: 550 milliseconds This seems reasonable.

I've found Statspack report for the query#1 from year 2012: it was using the same plan and execution time was only 1.7 millisecond. In 2 years the tables have grown (may be by 1/3), but this doesn't explain performance deterioration from 1.7 millisecond to 390 milliseconds.

The server is running with CPU utilization 20% or less. This is physical server - not an LDOM or a zone. Top 5 events show 'db file sequential reads', CPU time, 'db file scattered read', 'log file sync' - nothing unusual. Received on Mon Jun 30 2014 - 13:59:32 CEST

Original text of this message