Re: The query seems efficient - why poor performance?

On Monday, June 30, 2014 1:59:32 PM UTC+2, vsevolod afanassiev wrote:
> 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.

Vsevolod (is that your first name?),

You are talking about poor performance - how are end users experiencing this? Are there any complaints, is there a real problem with the system from an end-user perspective? 390 milliseconds doesn't seem too bad for me...

Matthias Received on Fri Jul 04 2014 - 11:48:58 CEST