Use shell scripts to view database load

Use shell scripts to view database load

In normal times, you may feel confused when viewing database problems. If no one gives feedback on the problem, you will not take the initiative to find the problem direction. awr, ash searches for relevant information from history records when the problem occurs. I have compiled the following scripts to display the database load information for the current day, be able to understand the busy status of the database.
 
Let's look at a simple example. For example, I want to check the database load from AM to AM.

The script showdbtime. sh displays a comprehensive value of the database load during the specified time period. For example, from to (60 minutes), if dbtime is 120 minutes, the displayed load is 200%.

Replace Linux Shell Parameters

Shell for parameters

Pass Linux/Unix Shell parameters to SQL scripts

Introduction to parameter passing methods in Shell scripts

PASS command line parameters through Shell scripts
 

BEGIN_TIME END_TIME ELAPSED_TIME DBTIME WORKLOAD_PER
-----------------------------------------------------------------------------------------------------------------
201 ** 21-MAY-14 06.07.33.893 PM 201 ** 21-MAY-14 07.07.33.893 PM 60 120 200%

The script content is as follows:

Sqlplus-s $ DB_CONN_STR @ $ SH_DB_SID <EOF

Set linesize 200
Col begin_time format a35
Col end_time format a35
Col elapsed_time format 99999999.999
Col workload_per format a20
SELECT
Begin_time, end_time,
Elapsed_time,
Dbtime,
Trunc (dbtime/decode (elapsed_time, 100, elapsed_time), 2) * | '%' workload_per
From
(
Select
B. SNAP_ID | '**' | B. END_INTERVAL_TIME begin_time,
E. SNAP_ID | '**' | E. END_INTERVAL_TIME end_time,
EXTRACT (day from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 1440 +
EXTRACT (hour from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) * 60 +
EXTRACT (minute from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME) +
EXTRACT (second from e. END_INTERVAL_TIME-B. END_INTERVAL_TIME)/60 elapsed_time,
Db_time.dbtime
FROM DBA_HIST_SNAPSHOT B, DBA_HIST_SNAPSHOT E,
(
SELECT B. snap_id begin_snap, e. snap_id end_snap,
Round (sum (e. value )-
Sum (B. value)/1000000/60, 2) dbtime
FROM DBA_HIST_SYS_TIME_MODEL e, DBA_HIST_SYS_TIME_MODEL B,
(
Select min (snap_id) begin_snap, max (snap_id) end_snap from DBA_HIST_SNAPSHOT where begin_interval_time> sysdate-1
And EXTRACT (hour from END_INTERVAL_TIME) between $1 and $2
) Temp_snap
WHERE
E. STAT_NAME = 'db time'
And B. snap_id = temp_snap.begin_snap
And e. snap_id = temp_snap.end_snap
AND B. STAT_NAME = 'db time'
Group by e. snap_id, B. snap_id
) Db_time
WHERE B. begin_interval_time> sysdate-1
And EXTRACT (hour from e. END_INTERVAL_TIME) between $1 and $2
And B. snap_id = db_time.begin_snap
And e. snap_id = db_time.end_snap
)
/

EOF
Exit

For more details, please continue to read the highlights on the next page:

1 2 Next Page