用途:根据id查找sql语句的资源使用情况_orasqlid
#!/bin/bash
sql_id=$1
echo "\n=============`date`===================\n"
if [ "$LOGNAME" = "oracle" ]; then
SQLPLUS_CMD="/ as sysdba";
else
SQLPLUS_CMD="/";
fi
sqlplus -s "$SQLPLUS_CMD" << EOF
set lin 2000;
col sql_id for a14;
col owner for a5;
col ob for a2;
col FIRST_LOAD_TIME for a8;
col OPTIMIZER_MODE for a4;
col load_ver for a8;
col ver_cnt for a8;
col sorts for a10;
col loads for a10;
col parse_cal for a9;
col exes for a6;
select
a.SQL_ID,
a.PARSING_SCHEMA_NAME owner,
a.FIRST_LOAD_TIME,
a.IS_OBSOLETE ob,
to_char(EXECUTIONS) exes,
to_char(a.VERSION_COUNT) ver_cnt,
to_char(a.LOADED_VERSIONS) load_ver ,
to_char(a.LOADS) loads,
to_char(a.PARSE_CALLS) parse_cal,
to_char(a.SORTS) sorts,
a.DISK_READS,
a.DIRECT_WRITES,
a.OPTIMIZER_MODE,
a.OPTIMIZER_COST,
a.ROWS_PROCESSED,
to_char(a.RUNTIME_MEM/1024,'999999.999')||' Kb' RUNTIME_MEM ,
a.BUFFER_GETS
from v\$sqlarea a
where sql_id='${sql_id}'
/
EOF