Re: Oracle 8i and poor query performance in some queries

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:3F954516.E9127BD9_at_remove_spam.peasland.com...
> Typically, when applications running against a database perform well for
> a time period and then *all of a sudden* stop working as well, then it
> is a scalability issue. The application/database wasn't designed to
> scale well. Most users don't recognize that this performance issue was
> getting worse over time because the the performance differences were not
> that significant. In the "classic" chart of performance, one sees a
> "knee in the curve" where the performance degrades quite a bit. This is
> where poor performance really gets noticed. So the trick now is to see
> what the bottlenecks are and resolve those bottleneck. I'd start by
> setting up Statspack and taking snapshots of system performance at
> certain intervals. The Statspack report can show you the tasks that the
> database spent the most time waiting to complete as well as the worst
> performing SQL statements. Start there.
>
> HTH,
> Brian
>
> Heikki Siltala wrote:
> >
> > Hello Oracle community,
> >
> > I am investigating performance problems in 8i Enterprise Edition
> > database. The database has been running for over two years without
> > performance problems. Now some users have started to complain that
> > some queries have started to take too long. I have traced the problem
> > and there seems to be no obvious reason to it. Spotlight (3rd party
> > monitoring tool) shows that the server executes lots of disk io
> > (mostly multi-block read, some single-block read) even if the query is
> > a small one. Our database uses cost based optimizer. It runs on HP-UX
> > 11 64-bit 2 CPU server (PA-RISC 440 MHz) with 1 GB RAM. Data is stored
> > in filesystem files so no raw devices are used. Database block size is
> > 8 kB. I will provide a test case to demonstrate how slow some queries
> > are.
> >
> > Created two tables and put test data into them. Both tables are in the
> > same locally managed tablespace where allocation unit is uniform 1 MB.
> > Table a1 has 15 columns and 35294 rows. Table a2 has 14 columns and
> > 83134 rows. Tables have no indexes. Analyzed both tables using
> > "analyze table compute statistcs", analyze of a1 took 4 seconds,
> > analyze of a2 took 8 seconds. Table a1 is the master table and has a
> > column "id". Each row has its own value for id so the result of
> > "select count(distinct id) from a1;" is 35294. Table a2 is the detail
> > table so every id in a2 is found in a1 and for every id in a1 there is
> > 1 to n rows in table a2.
> >
> > Launched a simple test query "select count(*) from a1 where id not in
> > (select id from a2);". I might think that this should be finished in
> > matter of some seconds. But it does not... Well, finally the query
> > finishes and provides a correct result (0). But it took 33 minutes 37
> > seconds to execute!
> >
> > Explain plan in Spotlight shows first TABLE ACCESS FULL for A1 (cost
> > 109, rows 1765, bytes 7060) then TABLE ACCESS FULL for A2 (cost 191,
> > rows 4157, bytes 16628) then FILTER (cost 0, rows 0, bytes 0), then
> > SORT AGGREGATE (cost 0, rows 1, bytes 4) and finally SELECT STATEMENT
> > (cost 109, rows 1, bytes 4).
> >
> > While running and tracking the query with Spotlight it shows about 3
> > 500 physical IOs per second and about 5 000 consistent IOs per second.
> > Multi-block read dominates the wait events although some single-block
> > read waits and latch waits do occur. Totals wait time are about 170ms
> > per second. Spotlight's "Session statistics"-section shows following
> > rates per second: table scan rows gotten 550 000, session logical
> > reads 5 500, physical reads 5 150, db block gets 140. Here are the
> > session total statistics after the query finishes: table scan rows
> > gotten 1 088 769 810, session logical reads 10 911 422, physical reads
> > 9 537 106, db block gets 424 047.
> >
> > I say there is something wrong, but what? Broken disk? Broken server?
> > Database bug? Configuration problem? All comments are appreciated. I
> > have already tried these with no help: database restart, server
> > restart, moving the tables to another tablespace which is on different
> > disk, switching to rule-based optimizer (analyze table delete
> > statistics & alter session optimizer_mode).
> >
> > Also took a session trace of the sql and processed it with tkprof,
> > here is the output:
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 1 0.00 0.00 0 0 0
> > 0
> > Execute 1 0.00 0.00 0 0 0
> > 0
> > Fetch 2 1983.76 2036.49 9535809 10485528 423540
> > 1
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 4 1983.76 2036.49 9535809 10485528 423540
> > 1
> >
> > Misses in library cache during parse: 1
> > Optimizer goal: CHOOSE
> > Parsing user id: 136
> >
> > Rows Row Source Operation
> > ------- ---------------------------------------------------
> > 1 SORT AGGREGATE
> > 0 FILTER
> > 35295 TABLE ACCESS FULL A1
> > 35294 TABLE ACCESS FULL A2
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"

Brian is right. Your also looking in the wrong place. First thing you need to do is the following:

1. How many users are using the system concurrently vs. how many in the past? This will lead you towards the possibility that its a scaling issue. You have to design and code your queries a certain way to add additional users concurrently. Namely you need bind variables.
2. Find the EXACT queries that are running slow. Test those. Do they run slow? If they dont run slow in isolation, test them during the day when other people are on.
3. Most of the stats you are looking at are useless. This spotlight tool isnt giving you much useful information.
4. Learn how to analyze query plans.