read-only standby performance
I set up a standby database to use as for reporting/warm backup, and all works fine when I open it read-only for reporting, apart from the performance of the report queries. A reporting join query like
select a,b,c,d from x,y,z
where <4 conditions>
group by .. order by ..
takes less than a half a second on the primary and 25+ secs on the standby.
I would appreciate some tips on how best to investigate the source of the slowness.
This is Oracle 8.1.6 Enterprise on Solaris 7. During the query 'top' shows near-100% CPU usage and a little (1-5%) wait-for-IO.
I would normally use statspack to investigate what is slow, but it doesnt work on a read-only db..
I thought initially it was to do with slowness of disk-based sorting, so I created a second 'reports' user with a locally-managed temporary tablespace for that user, (but still doing queries on the first user's schema -would that use the first or the second user's temp space?) , but it showed no speed improvement. In addition:
SELECT name,value from v$sysstat
where name like 'sort%';
shows no disk sorts:
NAME VALUE
--------------- ----------
sorts (memory) 1830
sorts (disk) 0
sorts (rows) 454709
but that may be normal for a read-only db (?)
Any tips gratefully received. -please bear in mind that I am a Unix sysadmin with fairly rudimentary oracle knowledge.. Pls let me know if you need details of init.ora settings or whatever.
thanks
Robin