| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Oracle 8i and poor query performance in some queries
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
 |
 |