Oracle tables Statistics

For long-term memory management, you should keep the OS statistics inside Oracle tables for analysis. Because of the transient nature of UNIX statistics, the DBA must have some facility for keeping information for long-term analysis. Otherwise, the UNIX environment changes so quickly that the DBA will not be able to diagnose a UNIX problem. The UNIX script below shows how you can capture vmstat data into an Oracle table.

#!/bin/ksh

The following series of images examines this script.

Here's a basic UNIX shell script that captures `vmstat` data and inserts it into an Oracle 11g table. This example assumes that you have Oracle Database 11g, SQL*Plus installed, and appropriate access to an Oracle schema where you can create tables and insert data.
Step 1: Create the Oracle Table
First, create a table in Oracle to store the `vmstat` data. Connect to your Oracle database and execute the following SQL to create the table:

CREATE TABLE vmstat_data (
    sample_time DATE DEFAULT SYSDATE,
    r           NUMBER,
    b           NUMBER,
    swpd        NUMBER,
    free        NUMBER,
    buff        NUMBER,
    cache       NUMBER,
    si          NUMBER,
    so          NUMBER,
    bi          NUMBER,
    bo          NUMBER,
    in          NUMBER,
    cs          NUMBER,
    us          NUMBER,
    sy          NUMBER,
    id          NUMBER,
    wa          NUMBER
);

Step 2: Create the Shell Script
Create a shell script (e.g., `capture_vmstat.sh`) with the following content:

#!/bin/bash

# Set Oracle environment variables
export ORACLE_SID=your_oracle_sid
export ORACLE_HOME=/path/to/oracle_home
export PATH=$ORACLE_HOME/bin:$PATH

# SQL*Plus connection details
USERNAME="your_username"
PASSWORD="your_password"
DATABASE="your_database"

# Capture vmstat data
vmstat_output=$(vmstat 1 1 | tail -1)

# Parse vmstat output
read -r r b swpd free buff cache si so bi bo in cs us sy id wa <<< "$vmstat_output"

# Insert data into Oracle database
sqlplus -s $USERNAME/$PASSWORD@$DATABASE <<EOF
INSERT INTO vmstat_data (r, b, swpd, free, buff, cache, si, so, bi, bo, in, cs, us, sy, id, wa)
VALUES ($r, $b, $swpd, $free, $buff, $cache, $si, $so, $bi, $bo, $in, $cs, $us, $sy, $id, $wa);
COMMIT;
EXIT;
EOF

Explanation of the Script

1. Oracle Environment Variables: Set ORACLE_SID, ORACLE_HOME, and update the PATH to include the Oracle binaries.
2. SQL*Plus Credentials: Define the Oracle username, password, and database connection.
3. Capture vmstat Data: The vmstat 1 1 | tail -1 command captures a single line of vmstat output after a 1-second delay, providing the current snapshot.
4. Parse vmstat Output: The output is parsed into variables (r, b, swpd, free, etc.) using read.
5. Insert into Oracle: Connect to the Oracle database using SQL*Plus and insert the parsed data into the vmstat_data table. Each vmstat metric is mapped to the appropriate column in the table.
6. Commit Transaction: COMMIT the transaction to ensure data is saved in the table.

Step 3: Run the Script
Make the script executable:

chmod +x capture_vmstat.sh

Run the script:

./capture_vmstat.sh

Automating the Script: To capture data at regular intervals, you can schedule this script in `cron`. For example, to capture data every minute, add the following line to your crontab:

* * * * * /path/to/capture_vmstat.sh

This will capture `vmstat` data every minute and insert it into the Oracle table. Make sure to adjust the interval as needed. The next lesson examines remedies for memory consumption.