X-Received: by 10.66.222.9 with SMTP id qi9mr22339526pac.28.1404129573346; Mon, 30 Jun 2014 04:59:33 -0700 (PDT) X-Received: by 10.50.4.36 with SMTP id h4mr587287igh.9.1404129573186; Mon, 30 Jun 2014 04:59:33 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!209.197.12.246.MISMATCH!nx02.iad01.newshosting.com!newshosting.com!69.16.185.113.MISMATCH!peer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!enother.net!news.glorb.com!uq10no19081060igb.0!news-out.google.com!bp9ni0igb.0!nntp.google.com!uq10no19081043igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Mon, 30 Jun 2014 04:59:32 -0700 (PDT) Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=203.13.128.104; posting-account=h2bfjgoAAAB8OxqMUJkSqT-KE-FeFQmL NNTP-Posting-Host: 203.13.128.104 User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <7b9ac689-360d-46f9-b49e-201ed4c88e0e@googlegroups.com> Subject: The query seems efficient - why poor performance? From: vsevolod afanassiev Injection-Date: Mon, 30 Jun 2014 11:59:33 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Received-Bytes: 3320 X-Received-Body-CRC: 2757166209 Xref: news.cambrium.nl 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 =3D 390 milliseconds.=20 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 execu= tions) - 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 i= s joined to D, D is joined to E. No sub-queries. It returns one column (VAR= CHAR2(10)) from table D. Table A is accessed through unique index on Accoun= tID (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-o= rganized, 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, IND= EX UNIQUE SCAN, INDEX RANGE SCAN, and CONCATENATION (because of OR conditi= on). 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 th= e same plan and execution time was only 1.7 millisecond. In 2 years the tab= les have grown (may be by 1/3), but this doesn't explain performance deteri= oration from 1.7 millisecond to 390 milliseconds. The server is running with CPU utilization 20% or less. This is physical se= rver - 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.