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 "Predictable 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. Thi Part 2 touched on the specifics of v$sqlarea view, Statspack, index counts, CPU and user involvement. This concluding part covers memory, data dictionary fragmentation, the FND_STATS histogram and other miscellaneous tuning considerations.
Are you swapping? Cut Oracle memory allocation back as far as you need to. Any amount of reparsing and un-buffered disk I/O performs better than swap. On a box dedicated to Oracle Database Server, O/S and Oracle memory footprint should be below 75% of system memory on average. Peaks at 100% should be momentary only.
This wonderful parameter is often neglected in 9i environments. It is light years more efficient in memory allocation than its predecessor parms SORT_AREA_SIZE and HASH_AREA_SIZE, as it is capable of providing 20m to 100m or more of memory to a sort if needed, without having to provide that much to each sort as a base. (You should leave entries for SORT_AREA_SIZE and HASH_AREA_SIZE in the pfile because PGA_AGGREGATE_TARGET must be disabled during RMAN recovery).
PGA_AGGREGATE_TARGET is just that -- a "target," not a "limit." It can frequently allocate twice the memory of the target. Plan on allowing the PGA_AGGREGATE_TARGET high water mark to hit 20% of the system memory of your box. There are new 9.2 v$ views and OEM which will tell you exactly where your system-wide PGA threshold is.
See Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management.
And, by the way, if you want to save on PGA memory, init parm DB_FILES is NOT to be set to CREATE <DATABASE..CONTROLFILE>'s MAXDATAFILES, as more PGA memory is allocated for higher values.
Data dictionary fragmentation
Data dictionary fragmentation must be monitored closely during early production!
SELECT OWNER, EXTENTS, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE EXTENTS > 100 ORDER BY EXTENTS DESCNormally you would run that query eliminating system schemas such as SYS, SYSTEM, DBSNMP, OUTLN, TRACIN, MDSYS, ORDSYS, CTXSYS, & etc. But not when you're running Oracle Apps.
We've seen numerous Oracle Applications object in SYSTEM with between 3K to 20K extents each. Massive recursive SQL against those objects comes at a price. Once your data dictionary is fragmented, a full database reorg will be very painful to configure, test and execute. Adjusting data dictionary object storage parameters is indeed supported but highly discouraged. We don't recommend trying this at home without supervision.
Where a re-org is inadvisable or too painful, you will get some relief by changing the SYSTEM tablespace to locally managed. We've been watching this feature for bugs since it was introduced with 9.2 base release and it has been amazingly clean. Warning! All other tablespaces must be locally managed first. Note:175434.1 Create/Upgrade/Migrate a Database to Have a Locally Managed SYSTEM Tablespace.
If you need to reorg, understand the Oracle Applications DB Reorg published Metalink notes are painfully incomplete despite their bleeding edge revision dates. Just one example: Apps modules that place stray objects in system schemas such as CTXSYS.
The FND_STATS histogram deficiency
A default FND_STATS run gives you what I call "half a histogram."
SELECT COLUMN_NAME, NUM_BUCKETS FROM DBA_TAB_COLUMNS...Check this when you run into queries with sub-optimal access paths. If the result is "1," check the cardinality of the column by running ANALYZE then querying DBA_TAB_COLUMNS NUM_DISTINCT. The fix:
ANALYZE TABLE... SIZE n;...where n is a number larger than the value of NUM_DISTINCT column. Notice the resulting value of NUM_BUCKETS. Compare the access paths and performance/throughput. If you have an improvement, place the ANALYZE command in a post-processing script to FND_STATS. Put that in a script for the problem table. This probably applies to 25% of all Oracle Applications SQL tuning we do. See Addressing Common Issues With Third-Party Applications by Rory Reynoldson in Bricks October, 2003.
Miscellaneous tuning issues
Check for migrated rows.
SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES...;Beware that DMBS_STATS and FND_STATS don't load various "human convenience" statistics into the data dictionary, such as CHAIN_CNT, because the optimizer doesn't care about them. Don't be fooled into thinking Oracle populated the column and your migrated row count is zero. If you want to see those, you must use the ANALYZE command. The CHAIN_CNT column comprehends both chained rows and migrated rows. To better estimate whether the problem is migrated rows, consider the average row length. If the row length is large and even approaching the block size, you probably have a row migration problem. Are you writing your own code? Do yourself and everybody a favor and don't make transactional calls to the Order Import interface.
SEGMENT_SPACE_MANAGEMENT AUTOMATIC: This new 9i feature eliminates PCT_USED and FREELISTS, and can be about three times more performance efficient than the contents of the best manually configured tablespace. But tablespaces cannot be altered to SEGMENT_SPACE_MANAGEMENT AUTOMATIC after they are created. Make sure your initial apps database has every tablespace created this way (doesn't apply to SYSTEM, UNDO and TEMP).
I/O write times: Your red-line ought to be 20ms average even on older direct-attached un-cached drives, never peaking over 1,000 ms. Be aware that with asynch-I/O enabled operating systems, write times reported by Oracle v$filestat, Statspack and utl<b..e>stat are unreliable. You must use operating system utilities such as sar, vmstat and iostat to get this measurement. There is nothing Oracle can do to circumvent this, and they have various published Metalink notes that document the limitation.
And while we're talking about async-I/O, an Oracle/Unix pair that are capable of asynch-I/O and configured to use it can yield 400% better I/O performance, all other factors equal. If you're considering Linux, this alone would be worth giving strong consideration to RedHat as opposed UnitedLinux, which wasn't asynch-I/O capable to our knowledge at time of this writing. And you must run Oracle Database Server 9i on Linux to get the feature.
Proving and documenting engagement progress
In a scaled Oracle Applications environment, it might seem difficult to document overall progress in a performance engagement. We have trended Oracle Instance I/O reduction over a multi-week period by running a Perl script over Statspack output, and graphed it on a spreadsheet. When the graph shows a steady reduction to say 25% lower than the starting point, then you ask management some questions with the graph in-hand: During the graphed period, have they:
- Experienced a decline in business?
- Sent 1/4 of their staff on extended vacation?
- Put restrictions on batch and report activity?
Note 216205.1 Database Initialization Parameters and Configuration for Oracle Applications 11i already documents 10g specific database initialization parameters. Oracle is on an apparent fast track to certify Oracle Apps with the new db release earlier than has been traditional in the past. Database 10g has deprecated the Rule-based Optimizer. But as of 11.5.8, a significant number of Oracle App's 25K stored procedures have RULE hints. So unless Database 10g has a back door for RULE hints (I haven't checked as of the time of this writing), plan on a longer than average shake-out period for Oracle Apps on Database 10g).
There is no way any vendor's ERP can perform adequately out of the box in scaled, complex enterprises. I'm not here to make excuses for ERP vendors for shoddy releases. But the ERP market demands that the ERP packages do everything with all the modules integrated and do so on a ton of hardware platforms. Add to that their inability to foresee every shop's unique data sets and growth trends, and the ERP vendors have a close to impossible task preemptively designing performance into their suites.
Oracle Applications Performance Tuning is not predominantly a top-down activity, although there are a ton of preventative measures that can be taken. When working on performance, document everything. It means the gains are understood and repeatable. It also provides configuration management and post-install steps for the next maintenance release.
Oracle Applications can deliver excellent performance in scaled environments on appropriate hardware, given a blend of prevention and performance maintenance.