<!-- --><style type="text/css">@import url(https://www.blogger.com/static/v1/v-css/navbar/3334278262-classic.css); div.b-mobile {display:none;} </style> </head><body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d7256432\x26blogName\x3dThe+Frustrated+Programmer\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLACK\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://frustratedprogrammer.blogspot.com/search\x26blogLocale\x3den_US\x26v\x3d2\x26homepageUrl\x3dhttp://frustratedprogrammer.blogspot.com/\x26vt\x3d5012862196962223429', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>
| Friday, July 16, 2004

"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.