[IMAGE]
This tip is brought to you by the International Oracle Users Group (IOUG), a
user-driven organization that empowers Oracle database and development
professionals by delivering the highest quality information, education,
networking and advocacy. It is excerpted from the paper "Oracle applications tuning," by David Welch. Become a member of the IOUG and gain access to
thousands more tips and techniques created for Oracle users by Oracle users.
Part 1 of this tip debated some common beliefs about apps tuning and offered an approach to efficient project management. This part touches on the specifics of v$sqlarea view, Statspack, index counts, CPU and user involvement.
The treasure map
At the Oracle tuning levels, the treasure map is the view v$sqlarea. If I were an IT manager, I'd memorize this view name. And when I run into my DBA in the halls, I'd ask them how many times this week they've queried this view.
Metalink note 235146.1 has sample queries against this view. For example:
More recent 9i versions add the column MODULE that can reveal the Oracle Applications module name.
Statspack
Statspack is still neglected in many large shops. This can be an intimidating report. Don't make the mistake of trying to read all the way through the output and understand everything. Rather the first page should guide you as to which ten percent of the rest of the report you should focus on. Statspack version 9.2 now includes the CPU and Elapsed Time columns. Previously, to sort long running SQL to the top, we had to turn on trace, concatenate the trace files, and run them through tkprof. That won't work at larger shops where a single day of trace can mount to 10gb.
User involvement in the performance engagement
Whoever got this suggestion into "the book" gets credit for common sense ingenuity! Get your users involved in performanc
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
e diagnostics. Put an Oracle Applications benchmark PC on a cart and make it available to them. It's got to be a PC as opposed to a similarly configured notebook, as a notebook will not have the same performance characteristics as a PC with the same specifications. Configure it as follows:
What the benchmark PC doesn't have:
The benchmark PC goes to the desk of users with performance problems. Plug its LAN cable into their PC's wall LAN socket, and have them do their work for a time on the benchmark PC. Then roll the benchmark PC into the computer room and plug it into the segment local to the middle tier, and have the user do more work on it. The benchmark PC eliminates subjectivity on the users part about Oracle Applications performance, and subjectivity on your part as to the users' performance complaints.
Index counts and performance
Back in the 7x days, the Developer's Guide basically said don't put more than four to five indexes on a table. Today, here's the quote:
Reality: Oracle Apps tables can have 30+ canned indexes each. If we can knock a terabyte or more of I/O off required SQL of required high frequency, we get over our concern for high index counts in a hurry and add the index.
CPU
Reduce concurrent manager pools width, but not so far that jobs block. We commonly see scenarios where reducing concurrent manager pool breadth actually increases batch job throughput, when it brings CPU back to less than pegged. There are many jobs that have peer processes that must complete. If the job's pool breadth is too narrow, the needed job might never get attention and the overall job blocks.
We've been involved with Oracle Apps installation teams and trainers that love to open up the concurrent manager pool width, with no view to CPU impact, and it stays that way come production day. Security is often wide open on the training/test environments, and the installers open up the concurrent manager pools in hopes of getting their batch job done earlier. They might not have a view to the CPU impact of doing so and might be running the box pegged.
CPU run queues should be no deeper than twice your CPU count. If CPU is pegged for more than moments a day, something's gotta give. And the first place to look is the concurrent manager pools.
Part 3 will discuss memory, data dictionary fragmentation and other tuning issues.
