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:
select sql_text, executions, buffer_gets, disk_reads, rows_processed, sorts, address, first_load_time, HASH_VALUE, module from v$sqlarea where executions > 0 order by reads_per descMore recent 9i versions add the column MODULE that can reveal the Oracle Applications module name.
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 performance 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:
- 750+ MHz
- 256 MB RAM
- Windows 2000 Pro SP-4
- Swap on a segregated logical drive
- Jinitiator--locked down version
- Approved software, such as Office 2003
- Screen saver
- Any sort of tool bars
- Anything resident
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:
Oracle does not limit the number of indexes you can create on a table. However, you should consider the performance benefits of indexes and the needs of your database applications to determine which columns to index.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.
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.