Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Tuning large oracle batch jobs

Oracle Database Tips by Donald Burleson


Many Oracle shops are bi-modal, processing online transactions during the work day and switching to "batch mode" at night:

  • Day Mode - Fast, online transactions, favors index access.  Thousands of concurrent sessions.
     
  • Night Mode - Multi-hour offline jobs, favoring full-scan access.  Includes creating large reports, rebuilding materialized views and aggregating rollup data.  Less than a dozen concurrent sessions.

Let's examine how we can reconfigure Oracle for batch mode processing, re-setting some default values for faster performance for smaller numbers of concurrent sessions.

Oracle in a batch world

Batch jobs have different needs than OLTP transactions, and some Oracle shops will reconfigure their instances to optimize Oracle for the load characteristics.  For example, an OLTP database may need a large db_cache_size value to support thousands of concurrent transactions, while a batch job (doing parallel full-table scans) does not need a large db_cache_size.  Conversely, OLTP jobs may not need large RAM areas for sorting ad hash joins, while batch jobs many benefit greatly from additional RAM, set via the pga_aggregate_target  parameter.

Applying server resources to batch jobs

There are many approaches to reconfiguring Oracle when you have a small handful of large jobs and no online users:

  • High RAM - Reallocate RAM into pga_aggregate_target
     

  • High CPU - Use parallel query and parallel DML to speed-up jobs (at the expense of higher CPU usage).
     

  • Speed-up sorts - Use RAM-Disk for TEMP tablespace, or post-sort with a tool such as syncsort.

Note:  The built-in governors in pga_aggregate_target can limit sort areas to 100m, and brave DBA's can consider adjusting hidden parameters, for blistering performance.  Laurent Schneider notes in Oracle MOSC that overriding the PGA defaults made a large batch processes run more than 8x faster, but it can also cause sporadic ORA-04030 errors.:

"alter system set pga_aggregate_target=6G;
alter system set "_pga_max_size"=2000000000;

...and I gave the query some hints "NOREWRITE FULL USE_HASH ORDERED".

As a result, it boosted my query performance from 12 hours to 1.5 hour."  

Note: Schneider also noted that using OPQ was a preferred alternative to large RAM regions, with similar performance improvements and no ORA-4030 errors.

PGA usage Note:  There are other tricks for overcoming the built-in governor for PGA usage.  Oracle has a 5% limit for any individual process, and by using parallel DML any single batch job can consume 30% of the PGA without touching any of the undocumented parameters.  Oracle author Laurent Schneider noted:

?I finally opted for a more maintainable solution.  No more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 CPU server.   As discussed in MOSC thread 460157.996, a supported way to increase the maximum PGA memory per single SQL query is to increase the degree of parallelism."

Adjusting the hidden parameters is not a panacea, and you must carefully test then, working with Oracle technical support.  See my notes on PGA management here, and remember that adjusting the hidden parameters can cause serious errors, plus it's not supported by Oracle.  See my notes on PGA sizing here:

Sample instance reconfiguration scripts

The UNIX scripts given below can be used to reconfigure the SGA between the OLTP and DSS without stopping the instance.  The example assumes an isolated Oracle server with 8 gigabytes of RAM, with 10 percent of RAM reserved for UNIX overhead, leaving 7.2 gigabytes for Oracle and Oracle connections.  The scripts are intended either for HP-UX or Solaris and accept the $ORACLE_SID as an argument.

This script could be run at 6:00 p.m. each evening in order to reconfigure Oracle for the memory-intensive batch tasks.

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus 's /nologin<<!
connect system/manager as sysdba;
alter system set db_cache_size  =1500m;
alter system set shared_pool_size  =500m;
alter system set pga_aggregate_target  =4000m;
exit
!

The script below will be run at 6:00 a.m. each morning to reconfigure Oracle for the OLTP usage during the day.

#!/bin/ksh

# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

$ORACLE_HOME/bin/sqlplus 's /nologin<<!
connect system/manager as sysdba;

alter system set db_cache_size  =4000m;
alter system set shared_pool_size  =500m;
alter system set pga_aggregate_target =1500m;

exit
!

Scheduling batch jobs in Oracle

Oracle 10g has developed a great job scheduling interface using the 10g OEM and the dbms_scheduler package.  For complete details on scheduling batch jobs in Oracle, see Dr. Tim Hall's book "Oracle Job Scheduling".

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.