The entire process of Oracle large page configuration in Linux environment

# grep PageTables /proc/meminfo
PageTables:      1244880 kB

$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

#!/bin/bash
#
# hugepages_setting.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6' | '3.8' | '3.10' | '4.1' | '4.14' ) echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

$ chmod u+x hugepages_setting.sh

$ ./hugepages_setting.sh 
Recommended setting: vm.nr_hugepages = 305

vm.nr_hugepages=306

# sysctl -p

$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:     306
HugePages_Free:      306
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

* soft memlock 626688
* hard memlock 626688

SQL> show parameter target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     16
pga_aggregate_target                 big integer 200M
sga_target                           big integer 600M

$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:     306
HugePages_Free:       98
HugePages_Rsvd:       93
HugePages_Surp:        0
Hugepagesize:       2048 kB

****************** Large Pages Information *****************
 
Total Shared Global Region in Large Pages = 602 MB (100%)
 
Large Pages used by this instance: 301 (602 MB)
Large Pages unused system wide = 5 (10 MB) (alloc incr 4096 KB)
Large Pages configured system wide = 306 (612 MB)
Large Page size = 2048 KB
***********************************************************

ALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

****************** Large Pages Information *****************
Parameter use_large_pages = ONLY
 
Total Shared Global Region in Large Pages = 602 MB (100%)
 
Large Pages used by this instance: 301 (602 MB)
Large Pages unused system wide = 5 (10 MB) (alloc incr 4096 KB)
Large Pages configured system wide = 306 (612 MB)
Large Page size = 2048 KB
***********************************************************

SQL> STARTUP
ORA-27137: unable to allocate large pages to create a shared memory segment
Linux-x86_64 Error: 12: Cannot allocate memory
SQL> 

****************** Large Pages Information *****************
Parameter use_large_pages = ONLY
 
Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB
 
ERROR:
  Failed to allocate shared global region with large pages, unix errno = 12.
  Aborting Instance startup.
  ORA-27137: unable to allocate Large Pages to create a shared memory segment
 
ACTION:
  Total Shared Global Region size is 608 MB. Increase the number of
  unused large pages to atleast 304 (608 MB) to allocate 100% Shared Global
  Region with Large Pages.
***********************************************************

#!/bin/bash
 
# Oracle Large Page Memory Performance Test - Results Analysis Script 
# Check parametersif [ -z "$1" ] || [ -z "$2" ]; then
    echo "Usage: $0 <Non-large page test result directory> <Large page test result directory>"
    exit 1
fi
 
# Define constantsNON_HUGEPAGES_DIR=$1
HUGEPAGES_DIR=$2
ANALYSIS_DIR="$HOME/hugepages_analysis_$(date +%Y%m%d_%H%M%S)"
RESULT_FILE="$ANALYSIS_DIR/comparison_results.txt"
 
# Create an analysis directorymkdir -p $ANALYSIS_DIR
 
# Record the result functionrecord_result() {
    echo "$1" &gt;&gt; $RESULT_FILE
    echo "$1"
}
 
#Analyze the test resultsanalyze_test_results() {
    local test_type=$1
    local non_hugepages_files=$(find $NON_HUGEPAGES_DIR -name "${test_type}*.txt" | sort)
    local hugepages_files=$(find $HUGEPAGES_DIR -name "${test_type}*.txt" | sort)
    
    record_result "=== Test Type: $test_type ==="
    record_result ""
    
    local non_hugepages_total_time=0
    local non_hugepages_count=0
    local hugepages_total_time=0
    local hugepages_count=0
    
    # Calculate the average time for non-large page tests    record_result "Non-big page test results:"
    for file in $non_hugepages_files; do
        # Extract real time        real_time=$(grep "real" "$file" | tail -1 | awk '{print $2}')
        # Convert to seconds        minutes=$(echo $real_time | cut -d'm' -f1)
        seconds=$(echo $real_time | cut -d'm' -f2 | cut -d's' -f1)
        total_seconds=$(echo "scale=2; $minutes * 60 + $seconds" | bc)
        
        record_result "  document: $(basename $file), Execution time: ${total_seconds}Second"
        non_hugepages_total_time=$(echo "scale=2; $non_hugepages_total_time + $total_seconds" | bc)
        ((non_hugepages_count++))
    done
    
    if [ $non_hugepages_count -gt 0 ]; then
        non_hugepages_avg_time=$(echo "scale=2; $non_hugepages_total_time / $non_hugepages_count" | bc)
        record_result "  平均Execution time: ${non_hugepages_avg_time}Second"
    else
        record_result "  Non-big page test results were found"
        non_hugepages_avg_time=0
    fi
    
    record_result ""
    
    # Calculate the average time for large page tests    record_result "Large page test results:"
    for file in $hugepages_files; do
        # Extract real time        real_time=$(grep "real" "$file" | tail -1 | awk '{print $2}')
        # Convert to seconds        minutes=$(echo $real_time | cut -d'm' -f1)
        seconds=$(echo $real_time | cut -d'm' -f2 | cut -d's' -f1)
        total_seconds=$(echo "scale=2; $minutes * 60 + $seconds" | bc)
        
        record_result "  document: $(basename $file), Execution time: ${total_seconds}Second"
        hugepages_total_time=$(echo "scale=2; $hugepages_total_time + $total_seconds" | bc)
        ((hugepages_count++))
    done
    
    if [ $hugepages_count -gt 0 ]; then
        hugepages_avg_time=$(echo "scale=2; $hugepages_total_time / $hugepages_count" | bc)
        record_result "  平均Execution time: ${hugepages_avg_time}Second"
    else
        record_result "  未找到Large page test results"
        hugepages_avg_time=0
    fi
    
    record_result ""
    
    # Computing performance improvement percentage    if [ $non_hugepages_avg_time -gt 0 ] &amp;&amp; [ $hugepages_avg_time -gt 0 ]; then
        improvement=$(echo "scale=2; (1 - $hugepages_avg_time / $non_hugepages_avg_time) * 100" | bc)
        record_result "Performance improvement: ${improvement}%"
    else
        record_result "无法计算Performance improvement百分比"
    fi
    
    record_result "========================================"
    record_result ""
}
 
# Main functionrecord_result "Oracle large page memory performance test comparison analysis"
record_result "Test date: $(date)"
record_result "非Large page test results目录: $NON_HUGEPAGES_DIR"
record_result "Large page test results目录: $HUGEPAGES_DIR"
record_result "----------------------------------------"
record_result ""
 
# Analyze various test resultsanalyze_test_results "full_table_scan"
analyze_test_results "index_scan"
analyze_test_results "dml_operations"
 
# Generate chart dataif [ -f "$RESULT_FILE" ]; then
    # Extract performance improvement data    grep "Performance improvement" "$RESULT_FILE" &gt; "$ANALYSIS_DIR/improvement_data.txt"
    
    # Create a simple performance comparison chart    cat &gt; "$ANALYSIS_DIR/performance_comparison_chart.csv" &lt;&lt;EOF
Test Type,Average time for non-large pages(Second),Large page average time(Second),Performance improvement(%)
Full table scan,$(grep "Full table scan" "$RESULT_FILE" | awk '{print $5}'),$(grep "Full table scan" "$RESULT_FILE" | awk '{print $12}'),$(grep "Full table scan" "$RESULT_FILE" | awk '{print $15}')
Index Scan,$(grep "Index Scan" "$RESULT_FILE" | awk '{print $5}'),$(grep "Index Scan" "$RESULT_FILE" | awk '{print $12}'),$(grep "Index Scan" "$RESULT_FILE" | awk '{print $15}')
DMLoperate,$(grep "DML Operation" "$RESULT_FILE" | awk '{print $5}'),$(grep "DML Operation" "$RESULT_FILE" | awk '{print $12}'),$(grep "DML Operation" "$RESULT_FILE" | awk '{print $15}')
EOF
    
    record_result "The analysis is completed, the result is saved in: $RESULT_FILE"
    record_result "The chart data is saved in: $ANALYSIS_DIR/performance_comparison_chart.csv"
fi    

#!/bin/bash
 
# Oracle Large Page Memory Performance Test - Test Execution Script 
# Check whether it is an oracle userif [ "$(id -un)" != "oracle" ]; then
    echo "Please use oracle user to execute this script"
    exit 1
fi
 
# Define constantsORACLE_SID="TESTDB"
ORACLE_HOME="/u01/app/oracle/product/19.3.0/dbhome_1"
TEST_USER="testuser"
TEST_PASS="testuser"
TEST_RUNS=5
TEST_RESULTS_DIR="$HOME/hugepages_test_results"
LOG_FILE="$TEST_RESULTS_DIR/performance_test_$(date +%Y%m%d_%H%M%S).log"
 
# Create a result directorymkdir -p $TEST_RESULTS_DIR
 
# Logging functionlog() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a $LOG_FILE
}
 
# Function: Execute SQL testsrun_sql_test() {
    local test_name=$1
    local sql_statement=$2
    local test_file="$TEST_RESULTS_DIR/${test_name}_$(date +%Y%m%d_%H%M%S).txt"
    
    log "Start the test: $test_name"
    
    for ((i=1; i<=$TEST_RUNS; i++)); do
        log "Test Run #$i"
        
        # Execute SQL and record time        { time $ORACLE_HOME/bin/sqlplus -S $TEST_USER/$TEST_PASS <<EOF
SET TIMING ON
SET SERVEROUTPUT ON
$sql_statement
EXIT;
EOF
        ; } 2>> $test_file
        
        # Sleep for 2 seconds        sleep 2
    done
    
    log "Complete the test: $test_name"
}
 
# Function: Execute benchmark testsrun_benchmark_tests() {
    log "Start the benchmark test..."
    
    # Test 1: Full table scan    run_sql_test "full_table_scan" "SELECT COUNT(*) FROM test_table;"
    
    # Test 2: Index Scan    run_sql_test "index_scan" "SELECT * FROM test_table WHERE created_date > SYSDATE - 100;"
    
    # Test 3: DML Operation    run_sql_test "dml_operations" "
DECLARE
    v_cnt NUMBER;
BEGIN
    -- Update operation
    UPDATE test_table SET data = data || '_UPDATED' WHERE MOD(id, 10) = 0;
    COMMIT;
    
    -- Delete operation
    DELETE FROM test_table WHERE id > 990000;
    COMMIT;
    
    -- Insert operation
    FOR i IN 990001..1000000 LOOP
        INSERT INTO test_table VALUES (i, 'NEW TEST DATA ' || TO_CHAR(i), SYSDATE);
    END LOOP;
    COMMIT;
    
    -- Rollback test
    SAVEPOINT sp1;
    UPDATE test_table SET data = data || '_TEMP' WHERE MOD(id, 5) = 0;
    ROLLBACK TO sp1;
    
    -- Statistics of row count
    SELECT COUNT(*) INTO v_cnt FROM test_table;
    DBMS_OUTPUT.PUT_LINE('Total number of rows: ' || v_cnt);
END;
/
"
    
    log "Benchmark test completion"
}
 
# Function: Collect AWR Reportscollect_awr_report() {
    log "Collect AWR Reports..."
    
    local snapshot_id_start=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT MAX(snap_id) FROM dba_hist_snapshot;
EXIT;
EOF
)
    
    # Wait for 10 minutes to collect performance data    log "Wait for 10 minutes to collect performance data..."
    sleep 600
    
    local snapshot_id_end=$($ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT MAX(snap_id) FROM dba_hist_snapshot;
EXIT;
EOF
)
    
    local awr_file="$TEST_RESULTS_DIR/awr_report_$(date +%Y%m%d_%H%M%S).html"
    
    # Create an AWR report    $ORACLE_HOME/bin/sqlplus -S / as sysdba <<EOF
SET SERVEROUTPUT ON
SPOOL $awr_file
@?/rdbms/admin/ html
$snapshot_id_start
$snapshot_id_end
7
SPOOL OFF
EXIT;
EOF
    
    log "AWRReport generated: $awr_file"
}
 
# Main functioncase "$1" in
    run-tests)
        log "=== Start performance testing ==="
        run_benchmark_tests
        log "=== Performance test completed ==="
        ;;
    collect-awr)
        log "=== Start collectingAWRReport ==="
        collect_awr_report
        log "=== AWRReportcollect完成 ==="
        ;;
    *)
        echo "usage: $0 [run-tests|collect-awr]"
        echo "  run-tests     - Perform benchmark tests"
        echo "  collect-awr   - collectAWR性能Report"
        exit 1
        ;;
esac    

#!/bin/bash
 
# Oracle Large Page Memory Performance Test - Environment Preparation Script 
# Check whether it is a root userif [ "$(id -u)" -ne 0 ]; then
    echo "Please userootThe user executes this script"
    exit 1
fi
 
# Define constantsSGA_SIZE_GB=8
HUGEPAGE_SIZE_MB=2
HUGEPAGE_COUNT=$((SGA_SIZE_GB * 1024 / HUGEPAGE_SIZE_MB))
ORACLE_SID="TESTDB"
ORACLE_HOME="/u01/app/oracle/product/19.3.0/dbhome_1"
 
# Function: Configure large page memoryconfigure_hugepages() {
    echo "Configure large page memory..."
    # Revise    cat >> /etc/ <<EOF
# Configure large page memory for Oracle performance testingvm.nr_hugepages = $HUGEPAGE_COUNT
vm.transparent_hugepage.enabled = never
vm.transparent_hugepage.khugepaged = never
EOF
    
    # Make the configuration effective    sysctl -p
    
    # Configure large page mount points    if ! grep -q "hugetlbfs" /etc/fstab; then
        echo "none /dev/hugepages hugetlbfs defaults 0 0" >> /etc/fstab
        mkdir -p /dev/hugepages
        mount -a
    fi
    
    echo "Large page memory configuration completed"
}
 
# Function: Disable large page memorydisable_hugepages() {
    echo "Disable large page memory..."
    # Revise    sed -i '/vm.nr_hugepages/d' /etc/
    sed -i '/vm.transparent_hugepage/d' /etc/
    
    # Add disabled configuration    cat >> /etc/ <<EOF
# Disable large page memoryvm.nr_hugepages = 0
vm.transparent_hugepage.enabled = always
vm.transparent_hugepage.khugepaged = always
EOF
    
    # Make the configuration effective    sysctl -p
    
    # Uninstall large page mount point    if grep -q "hugetlbfs" /etc/fstab; then
        umount /dev/hugepages 2>/dev/null
        sed -i '/hugetlbfs/d' /etc/fstab
    fi
    
    echo "Large page memory disabled"
}
 
# Function: Configure Oracle to use large pagesconfigure_oracle_hugepages() {
    echo "Configure Oracle to use large page memory..."
    su - oracle -c "$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
ALTER SYSTEM SET sga_target=${SGA_SIZE_GB}G SCOPE=SPFILE;
ALTER SYSTEM SET use_large_pages=TRUE SCOPE=SPFILE;
EXIT;
EOF"
    
    echo "OracleLarge page configuration completed，Please restart the database to make the configuration take effect"
}
 
# Function: Configure Oracle not to use large pagesconfigure_oracle_no_hugepages() {
    echo "Configure Oracle not to use large page memory..."
    su - oracle -c "$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
ALTER SYSTEM SET sga_target=${SGA_SIZE_GB}G SCOPE=SPFILE;
ALTER SYSTEM SET use_large_pages=FALSE SCOPE=SPFILE;
EXIT;
EOF"
    
    echo "OracleLarge page configuration disabled，Please restart the database to make the configuration take effect"
}
 
# Function: Create test users and tablescreate_test_objects() {
    echo "Create test users and tables..."
    su - oracle -c "$ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
CREATE USER testuser IDENTIFIED BY testuser DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, DBA TO testuser;
EXIT;
EOF"
    
    # Create a test table    su - oracle -c "$ORACLE_HOME/bin/sqlplus testuser/testuser <<EOF
CREATE TABLE test_table (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(4000),
    created_date DATE
);
-- insert100Ten thousand test data
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO test_table VALUES (i, 'TEST DATA ' || TO_CHAR(i), SYSDATE);
        IF MOD(i, 10000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    COMMIT;
END;
/
-- Create an index
CREATE INDEX idx_test_table ON test_table(created_date);
EXIT;
EOF"
    
    echo "Test object creation is completed"
}
 
# Main functioncase "$1" in
    enable-hugepages)
        configure_hugepages
        configure_oracle_hugepages
        ;;
    disable-hugepages)
        disable_hugepages
        configure_oracle_no_hugepages
        ;;
    create-test-data)
        create_test_objects
        ;;
    *)
        echo "Usage: $0 [enable-hugepages|disable-hugepages|create-test-data]"
        echo " enable-hugepages - Configure and enable large page memory"
        echo "disable-hugepages - Disable large page memory"
        echo "create-test-data - Create test data"
        exit 1
        ;;
esac    

# Oracle large page memory performance test experimental steps 
## 1. Test environment preparation### 1. System and database configurationEnsure that the test environment meets the following conditions：
- Linuxoperating system（recommendOracle Linux 7orRed Hat Enterprise Linux 7Versions above）
- Oracledatabase（12cVersions above）
- Enough memory（At least16GB）
- 测试database实例
### 2. Install the test scriptCopy the three script files provided to the test server：
-  - Environment preparation script
-  - Test execution script
-  - Results analysis script
 
Ensure that the script has execution permissions：chmod +x   
 
## 2. Perform tests### 1. Disable large page memory and perform testsfirst，byroot用户执行by下命令禁用大页内存并配置OracleDon't use large pages：./ disable-hugepages
Restart the system to make the configuration take effect：shutdown -r now
After the system restarts，byoracle用户连接database并启动database：sqlplus / as sysdba
STARTUP
Create test data：./ create-test-data
Perform benchmarks and collect results：mkdir -p ~/non_hugepages_results
./ run-tests > ~/non_hugepages_results/test_log_$(date +%Y%m%d).txt
./ collect-awr > ~/non_hugepages_results/awr_log_$(date +%Y%m%d).txt
### 2. Enable large page memory and perform testsbyroot用户执行by下命令启用大页内存并配置OracleUse large pages：./ enable-hugepages
Restart the system to make the configuration take effect：shutdown -r now
After the system restarts，byoracle用户连接database并启动database：sqlplus / as sysdba
STARTUP
Perform benchmarks and collect results：mkdir -p ~/hugepages_results
./ run-tests > ~/hugepages_results/test_log_$(date +%Y%m%d).txt
./ collect-awr > ~/hugepages_results/awr_log_$(date +%Y%m%d).txt
 
## 3. Analyze the test resultsbyoracle用户执行Results analysis script：./ ~/non_hugepages_results ~/hugepages_results
The analysis script will generate detailed test comparison results and chart data，View the result file：cat ~/hugepages_analysis_*/comparison_results.txt
 
## 4. Interpretation of experimental resultsAnalyze the data in the result file，重点关注by下指标：
1. Various tests（Full table scan、Index Scan、DMLoperate）Comparison of execution time when large pages are enabled and large pages are not enabled
2. Percent performance improvement
3. AWRMemory usage reported、CPUKey indicators such as usage rate
 
Normally，After enabling large page memory，Oracledatabase的性能会有明显提升，尤其是在内存访问密集型operate中。
 
## 5. Things to note1. During the test，Ensure the system load is stable，Avoid other applications interfering with test results
2. Close all unnecessary services and processes before testing
3. 建议进行多次测试by确保结果的可靠性
4. After the test is completed，可by根据实际情况决定是否在生产环境中启用大页内存配置