ack in 2000 while taking a walk in downtown Houston, I was approached by a couple of Astros baseball fans who asked me to join them in testing the plumbing system in the newly constructed Houston Astros Stadium. Inside the stadium, about 1,000 other Astros fans participated. All we had to do was flush the urinals at once. The team treated us to pizza and soda for taking part in this testa very practical way to load/stress test the stadium but also expensive.
While the Astros team had no problem getting so many people to participate in their load test effort, testing your software the same way is unfathomable. It is not cost effective to do an exact production simulation load test on a pre-production or test environment, which has created a market for numerous software tools that perform simulated load tests. All the testing tools basically run automated tests, during which you can collect other vital operating system information to calculate your software's performance capacity.
Because development teams seldom address load test issues during development, initial load test runs are prone to crashing. Lots of load testing time can be saved if problems such as open cursors and overly used CPU-intensive queries are addressed during the development phase itself. This article walks through how to analyze load test data and do capacity planning for your enterprise Java applications while they are in development.
If you leave database-related processing cursors such as statements and results sets open (without explicit closing instructions), they will eventually max out and throw the Max Open Cursors Exceeded exception. So while doing load tests, use this simple query to watch for open cursors:
select s.SQL_TEXT , count(*) from v$sql s, v$open_cursor oc
where oc.hash_value = s.hash_value
group by s.SQL_TEXT having count(*) > 1
The query will tell you which query cursors are left open. If the cursor for a particular query is not closed, the count corresponding to the query will constantly increase. Once you've identified the query, it is easy to fix the code.
CPU and Memory-Intensive Queries
The V$sqlarea view has many useful columns for identifying CPU-intensive and memory-intensive queries. The CPU_TIME column specifies the accumulated microseconds of CPU time that your SQL uses for parsing, executing, and fetching. The RUNTIME_MEM column specifies the fixed amount of memory required during the execution of a cursor. Queries using clauses such as
tend to demand more CPU time and runtime memory. During low loads these queries do not have much of an effect, but as loads increase they can have a cumulative effect. While there is no workaround for these queries, if the architecture group knows about them beforehand, they can minimize such queries in your application.