SQL Response Time

From: Brian <brian_at_databaseknowledge.com>
Date: 21 Jun 2005 21:58:49 -0700
Message-ID: <1119416329.555100.170100@o13g2000cwo.googlegroups.com>

I have a small database (about 4 gig total) that generates web pages through mod_plsql. The DB gets hit by 30 users 10 hours a day pretty regularly.

The db is 10g running on a Dell 3gig 2meg Zeon hyper-threaded intel processor, with 512M ram and a 6 disk raid 10. The application has very good response time, however some screens are just a tiny bit sluggish-hardly anything to complain.

Just for kicks I brought the ram up to 3 gigs, increased the SGA from 300M to 1.3G, increased open_cursors from 100 to 500 and session_cached_cursors from 0 to 350.

Also I set optimizer_index_caching from 0 to 40 and optimizer_index_cost_adj to 20. I watch the buffer cache and everything is in memory.

Now the application (which is totally 100% pl sql) runs really super fast. Every web page comes back without even the slightest hesitation.

What I don't understand is why OEM now reports 'SQL Response' time increasing from about 30 to 150. Is this because of the larger buffer cache?

Brian Received on Tue Jun 21 2005 - 23:58:49 CDT