| 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 -> Re: Oracle 8i and poor query performance in some queries
I think I should be sleeping by now but let's check the indexing first. Created btree indexes to both tables on column "id". Then analyzed the tables again and lauched the original query "select count(*) from a1 where id not in (select id from a2);". What has changed is that Spotlight no longer reports high physical IO rate. Actually, there are no physical IOs while the query executes. Spotlight shows a data flow of 1400 blocks per second between buffer cache and server process. Cache hit ratio is 100%. Well let's not rely on Spotlight but instead look at the real execution time: 39 minutes 25 seconds. And the query seems to have started to take lots of CPU power: It gets 99% of the time from one CPU. Here is the tkprof output now:
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.01 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 2 2345.71 2364.51 0 3290041 12
1
total 4 2345.71 2364.52 0 3290041 12
1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 136
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 FILTER
I would interpret this so that the indexing has changed the execution plan but the SQL is still very inefficient. What seems strange to me is that now the query takes even longer than before.
Checked also the original tables in the production environment. Column "id" is indexed in both tables and the indexes are valid. And there is a foreign key relationship between the tables. What is strange that there seems to be no statistics on the original tables nor on the indexes. We do have a routine that should analyze the database regularly to keep the statistics fresh. My guess is that it is no longer working and so the database performance is degrading. I "manually" analyzed the original tables and lauched the original query on them. Earlier it took from 30 minutes up to 45 minutes there. Now it takes about one second there! Here is the tkprof output.
call count cpu elapsed disk query current
rows
Parse 2 0.04 0.02 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 4 1.40 1.71 0 141326 8
2
total 8 1.44 1.73 0 141326 8
2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 136
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 FILTER
I would interpret this so that because there are indexes AND foreign key relationship between the tables the optimizer finds a very effective execution plan. Tried to prove this with the test tables. Created the pk-fk relationship on them also. Then reanalyzed the tables. Yep, it executes now as fast as in the production environment.
I checked our automatic analyze routine and found out that it has been facing a bug in DBMS_STATS.GATHER_DATABASE_STATS described in MetaLink. So it has not been working and our database is missing statistics! This explains the slowdown. But there is even more than that: the two tables from our production environment that we have been playing with are a special case. Because of another bug, the can not be analyzed. If CBO is utilized on them, some Discoverer queries will no longer run on them. So even if the analyze routine had been working, there would be no statistics on these two tables, since the analyze of them is disabled in the routine to prevent CBO on them.
Thank you for the assistance and tips. Now I have at least three thing
to do:
1) fix the automatic analyze routine
2) once again notify the users that it DOES count how they write their
queries
3) start taking regular snapshots on the performance
-- HeikkiReceived on Wed Oct 22 2003 - 19:28:43 CDT
 |
 |