"The app is slow in QA, can you take a look?"
"Sure..."
-It turns out it takes 10 seconds to load one key page in the system.
-Telnet to the box, and look at the load average, vmstat and io stat - Everything is ok.
-Telnet to the database, do the same - Everything is ok.
-Turn on P6Spy in the hibernate.proeprties
-Look at the log files - 10 queries are taking over .5 seconds. Most are related to one table.
-It turns out the index for for the primary key of table is missing (huh?). Add it and reload the page, its down to 5 seconds.
-Look at the spy.log again and 4 queries are taking over .5 seconds. Notice some foriegn key relations don't have indexes. (Explain plan could of pinpointed things better, but most of this was easy to see w/o it).
-To see how wide spread this is, I found this excellent script:
select c.OWNER,
c.TABLE_NAME,
c.CONSTRAINT_NAME,
cc.COLUMN_NAME,
r.TABLE_NAME,
rc.COLUMN_NAME,
cc.POSITION
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.CONSTRAINT_TYPE = 'R'
and c.OWNER not in ('SYS','SYSTEM')
and c.R_OWNER = r.OWNER
and c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and c.OWNER = cc.OWNER
and r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and r.OWNER = rc.OWNER
and cc.POSITION = rc.POSITION
order by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION
Adding more missing constraints and the app is blazingly fast again. Babu, I am in your debt.
<< Home