The query seems efficient - why poor performance?
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.