Hello
Im working with (sometimes against) Oracle 8i 8.1.5, and stumbled upon one of my databases being extremely slow.
The database contains ONE table "Actions", that contains the following fields:
- SalesManID NUMBER
- GroupID NUMBER
- Answer NUMBER
There is an unique index on all fields.
The table contains 2 million records.
Everything works fine this way, but when i want to pivot the data i get serious performance problems, which seems odd to me:
I want to transmute the data into something like this:
Group1 Group2 Group3
Salesman1 1 2 1
Salesman2 3 1 3
Salesman3 5 2 1
For this i've created a query like this:
select salesmanID,
min(decode(groupID, 1, answer)) Group1,
min(decode(groupID, 2, answer)) Group2,
min(decode(groupID, 3, answer)) Group3,
min(decode(groupID, 4, answer)) Group4,
min(decode(groupID, 5, answer)) Group5
From Actions
Group By
salesmanID
On Oracle 8i 8.1.5, this query takes around 10 minutes, and gives the following statistics:
Statistics
--------------------------
----------
----------
----------
--
2965 recursive calls
9354 db block gets
27361 consistent gets
704280 physical reads
55316 redo size
1111 bytes sent via SQL*Net to client
1387 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
1 sorts (disk)
1 rows processed
What worries me is the 704280 physical reads from the harddisk!
The Oracle database is setup with 1.5 GB RAM. The following parameters from my INIT.ORA file:
-- INIT.ORA parameters:
db_file_multiblock_read_co
unt = 32
db_block_buffers = 480000
shared_pool_size = 15728640
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 59 # INITIAL
parallel_max_servers = 5 # SMALL
log_buffer = 65536 # INITIAL
max_dump_file_size = 10240 # limit trace file size to 5M each
global_names = true
oracle_trace_collection_na
me = ""
db_block_size = 2048
job_queue_processes = 2
job_queue_interval = 10
open_links = 4
distributed_transactions = 500
compatible = 8.1.0
---
A similar query on Microsoft SQL Server 2000, took around 45 seconds.
It seems to me that there is a bottleneck (database setup problem???) that I should fix as soon as possible.
Anyone have any ideas?
Thanks in advance
/mtoft
