HugePages and Oracle Database 11g Automatic Memory Management

When you create a 11g database using Database Configuration Assistant (DBCA), Automatic Memory Management (AMM) is enabled by default. On a Linux system, if AMM is enabled, SGA memory will be allocated by creating files under /dev/shm so HugePages will not be used. Unfortunately AMM is absolutely incompatible with HugePages.

If you want to use HugePages, both MEMORY_TARGET / MEMORY_MAX_TARGET initialization parameters should be unset for the database instance.

Setting Nonstandard/Multiple Block Sizes

A block size of 8 KB is optimal for most systems. On the other hand, OLTP systems can use smaller block sizes (such as 4K) and DSS systems can use larger block sizes (such as 16K, 32K).

Guidelines for database block size:

1) You need to set Oracle Block size equal to or a multiple of the operating system block size.

2) If your rows are small and you use a large block size, when you fetch a block you may get lots of rows and waste your buffer cache. It may also cause block contention.

3) If your rows are large then you may need a large block size to prevent “chaining”.

4) Larger Oracle block sizes may give better index balancing with more rows in each branch.

Export Fails with EXP-00024

While you’re trying to export schemas with original export (exp) utility, if export fails with “EXP-00024: Export views not installed, please notify your DBA” message, problem is probably about forgetting to update the catalog metadata which is used by export tool:

You can run the following script to create export catalogs but it’s not enough:

How to Stop the Job Scheduler

You can prevent the Job Scheduler from running at startup by

This method will not prevent the use of DBMS_SCHEDULER.RUN_JOB. If someone use DBMS_SCHEDULER.RUN_JOB, the job will execute regardless of the setting of the SCHEDULER_DISABLED attribute

If you want to also stop the jobs which is created by DMBS_JOBS, you need to issue:

Installing and Configuring StatsPack

Statspack is a performance tuning package provided by Oracle. It’s free to use and a reliable tuning tool since Oracle 8i. You can install it on Oracle 10g and 11g if you don’t have license for tuning pack. Oracle recommend installing the statspack package into a different tablespace than system tablespaces. “TOOLS” tablespace of Oracle 9i is  because I’ll install it to . If you use Oracle 10g+, you need to create this tablespace.

During the installation, the user PERFSTAT will be created. This user will own all objects of the STATSPACK package. The installation SQL script will prompt for the PERFSTAT user’s password and default and temporary tablespaces.

You need to check spcpkg.lis for any errors. You can test your statspack install by issuing:

You can create spauto.sql script to automatically schedule an hourly data collection for statspack:

If you want to remove statspack, run spdrop: