 |
|
Tuning large oracle batch jobs
Oracle Tips by Burleson Consulting |
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 MetaLink 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
metalink 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".
