Re: The query seems efficient - why poor performance?

On Monday, June 30, 2014 5:59:32 AM UTC-6, vsevolod afanassiev wrote: Comments embedded.
> This is Oracle 9.2.0.8 on a Sun SPARC server
>

You do realize this is ancient Oracle technology as you're now 5 major releases behind the curve?

> There is a query (query#1) that gets executed approx 300,000 times per day.
>
> Average time per execution = 390 milliseconds.
>

Not an unreasonable or excessively long duration.

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

That the plan hasn't changed leads me to believe that the statistics are fairly current and reasonably accurate. Have you run the query while generating a 10053 trace to see what numbers the optimizer is dealing with?  

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

Since this is an older release it doesn't tend to favor hash joins over nested loop joins; have you thought of modifying the query to incorporate the /*+ use_hash(<tablename>) */ hint to 'nudge' the optimizer into foregoing the nested loop path?

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

Different data distribution over time can affect how a query performs. Yes, it's an index access path but if the clustering factor has changed considerably from when the query ran in 1.7 milliseconds that could increase the overall execution time. Granted it's not a massive difference since we're still in the millisecond range (versus millisecond execution to execution times in the 1 to 10 second range) it still seems to be enough to warrant investigating the current clustering factor. I doubt you have any reports or statistics from 2 years ago when the run time was 'reasonable' so a comparison to those values is not possible however you can, if you understand the clustering factor, decide if the current value is reasonable for the data.  

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

Which pretty much excludes any CPU saturation issues; you might check for memory problems or I/O latency increases since memory can fail and disks do wear out over time.

David Fitzjarrell Received on Tue Jul 01 2014 - 16:31:08 CEST