응용프로그램이 사용한 CPU 시간
select substr (appl_info.auth_id,1,8) as authid
,cast (appl.agent_id as integer) as agentid
,substr (appl_name,1,20) as
appl_name
,cast (appl.agent_usr_cpu_time_s as integer) as user_cpu
,cast (appl.agent_sys_cpu_time_s as integer) as sys_cpu
,timestampdiff (2, char (current timestamp - appl.uow_start_time)) as elapsed_time
,cast (appl_idle_time as integer) idle_time
,appl.rows_read
,appl.rows_written
from table (snapshot_appl ('DBNAME', -2)) as appl,
table (snapshot_appl_info ('DBNAME', -2)) as appl_info
where appl.agent_id = appl_info.agent_id
and appl_info.appl_id <> '%MON_APPL_ID'
and uow_stop_time is NULL
order by user_cpu desc, agentid with ur;
응용프로그램 목록 확인
select substr(appl_info.auth_id,1,8) as authid
,cast (appl.agent_id as integer) as agentid
,substr (appl_name,1,20) as
appl_name
,substr (appl_info.appl_id,1,30) applid
,case appl_info.appl_status
when 2 then 'Connection
Completed'
when 4 then 'Executing'
when 5 then 'UOW Wait'
when 9 then 'Lock Wait'
when 24 then 'Complie'
when 26 then 'Pending remote request'
else substr(char(appl_info.appl_status),1,10)
end
as status
,cast (appl.num_agents as integer) num_agents
,substr (appl_info.db_name,1,8) dbname
,cast (appl_info.client_pid as integer) client_pid
from table(snapshot_appl ('DBNAME', -2)) as appl,
table (snapshot_appl_info ('DBNAME', -2)) as appl_info
where appl.agent_id = appl_info.agent_id
and appl_info.appl_id <> '%MON_APPL_ID'
order by appl.num_agents desc, appl_name, agentid;
**응용프로그램이 처리한 행의 수 à실행안됨
select substr(appl_info.auth_id,1,8) as authid
,cast (appl.agent_id as integer) as agentid
,substr (appl_name,1,20) as
appl_name
,timestampdiff (2, shar (current timestamp - uow_start_time))
as elapsed_time
,cast (appl.rows_selected as integer) as rows_select
,cast (appl.rows_inserted as integer) as rows_insert
,cast (appl.rows_updated as integer) as rows_update
,cast (appl.rows_deleted as integer) as rows_delete
,cast (appl.rows_read as nteger) rows_read
,cast (appl.rows_written as nteger) rows_written
from table (snapshot_appl('DBNAME', -2)) as appl,
table (snapshot_appl_info('DBNAME', -2)) as appl_info
where appl.agent_id = appl_info.agent_id
and appl_info.appl_id <> '%MON_APPL_ID'
and uow_stop_time is null
order by elapsed_time desc, agentid with ur;
응용 프로그램 별 잠금
select substr (appl_info.auth_id,1,8) as authid
,cast (appl.agent_id as integer) as agentid,
substr (appl_info.appl_name,1,14) as appl_name,
case appl_info.appl_status
when 2 then 'Connection
Completed'
when 3 then 'Executing'
when 4 then 'UOW Wait'
when 5 then 'Lock Wait'
when 9 then 'Complie'
when 24 then 'Pending remote
request'
when 26 then 'Decoupled'
else substr(char(appl_info.appl_status),1,10)
end
as status,
cast (appl.lock_waits as integer) as lock_waits,
cast (appl.locks_held as integer) as lock_held,
cast (appl.lock_escals as integer ) as escals,
cast (appl.x_lock_escals as integer ) as x_escals,
cast (appl.deadlocks as integer ) as deadlock,
cast (appl.locks_waiting as integer ) as locks_waiting
from table (snapshot_appl('DBNAME', -2)) as appl,
table (snapshot_appl_info('DBNAME', -2)) as appl_info
where appl.agent_id = appl_info.agent_id
and appl_info.appl_id <> '$MON_APPL_ID'
order by agentid;
**파티션 별 잠금
select substr (appl_info.AUTH_ID,1,10) as auth_id,
cast (lock.agent_id as integer) as agentid,
substr (appl_info.appl_name,1,16) as appl_name,
substr (lock.tablespace_name,1,15) as tbsname,
substr (lock.table_name,1,18) as tabname,
lock.lock_object_type
when 1 then 'Table'
when 2 then 'Row'
else substr (char(lock.lock_object_type),1,4)
end
as type,
cast ( lock.lock_object_name as integer ) as lockobjname,
case
cast (lock.lock_mode as smallint)
when 3 then 'S'
when 5 then 'X'
when 9 then 'NS'
else substr(char(lock.lock_mode),1,4)
end
as mode,
case
lock.lock_status
when 1 then 'G'
when 2 then 'C'
else
cast(lock.lockstatus as char)
end
as status,
cast (lock.lock_escalation as integer) as escal
from table(snapshot_lock('DBNAME', -2)) as lock,
table(snapshot_appl_info('DBNAME', -2)) as appl_info
where lock.agent_id = appl_info.agent_id
and appl_info.appl_id <> '$MON_APPL_ID'
order by agentid, tbsname, tabname;
테이블 별 잠금
select substr(appl_info.AUTH_ID,1,10) as
auth_id,
cast (lock.agent_id as integer) as agentid,
substr(appl_info.appl_name,1,16) as appl_name,
substr(lock.tablespace_name,1,15) as tbsname,
sunstr(lock.table_name,1,18) as tabname,
case
lock.lock_object_type
when 1 then 'Table'
when 2 then 'Row' as type ,
cast (lock.lock_object_name as integer) as lockobjname,
case
cast (lock.lock_mode as smallint)
when 3 then 'S'
when 5 then 'X'
when 8 then 'U'
when 9 then 'NS'
else substr(char(lock.lock_mode)1,4)
end
as mode,
case
lock.lock_status
when 1 then 'G'
when 2 then 'C'
else
cast (lock.lock_status as char)
end
as status,
cast (lock.lock_escalation as integer) as escal
from table (snapshot_lock('DBNAME', -2)) as lock,
table (snapshot_appl_info('DBNAME', -2)) as appl_info
where lock.agent_id = appl_info.agent_id
and
lock.table_name = ucase('$TABNAME')
and appl_info.appl_id <> '$MON_APPL_ID'
order by agentid, tbsname, tabname, type;
잠금 대기 에이전트
select
cast (lockwait.agent_id as integer) as wait_agent,
substr (appl_info.appl_name,1,10) as wait_appl,
substr (lockwait.table_schema,1,18) as tbschema,
substr (lockwait.table_name,1,18) as tabname,
case lockwait.lock_object_type
when 1 then 'Table'
when 2 then 'Row'
else substr(char(lockwait.lock_object_type),1,4)
end
as lock_type
, case cast (lockwait.lock_mode_requested as smallint)
when 3 then 'S'
when 5 then 'X'
else substr(char(lockwait.LOCK_MODE_REQUESTED),1,4)
end
as lock_mode
, cast (partition_number as smallint) partition
, cast (lockwait.agent_id_holding_lk as integer) as hold_agent
, substr (appl_info2.appl_name,1,10) as hold_appl
, timestampdiff (2, char (current timestamp -
lockwait.LOCK_WAIT_START_TIME)) as wait_time_s
from
table (snapshot_lockwait('DBNAME', -2)) as lockwait,
table (snapshot_appl_info('DBNAME', -2)) as appl_info1,
table (snapshot_appl_info('DBNAME', -2)) as appl_info2,
where
lockwait.agent_id = appl_info1.agent_id
and lockwait.agent_id_holding_lk = appl_info2.agent_id
order by wait_agent, lock_type, hold_agent;
잠금 대기 정적 SQL문
select
cast (lockwait.agent_id as integer) wait_agent
, substr (appl_info.appl_name,1,10) wait_appl
, substr (statement.creator,1,8) pkg_schema
, substr (statement.package_name,1,8) package
, cast ( statement.section_number as smallint ) section
, substr (cat_statement.text,1,63) SQL
, cast (lockwait.agent_id_holding_lk as integer) hold_agent
from table (snapshot_lockwait('DBNAME', -2)) lockwait
, table (snapshot_appl_info('DBNAME', -2)) appl_info
, table (snapshot_statement('DBNAME', -2)) statement
, syscat.statement cat_statements
where
lockwait.agent_id = appl_info.agent_id and
lockwait.agent_id = statement.agent_id and
statement.stmt_text is
null and
cat_statements.pkgname = upper(statement.package_name) and
cat_statements.sectno = statement.section_number;
잠금 대기 동적 SQL문
select
cast (lockwait.agent_id as integer) wait_agent
, substr (appl_info.appl_name,1,10) wait_appl
, cast (NULL
as char(8)) pkg_schema
, cast (NULL
as char(8)) package
, cast (NULL as smallint) section
, substr (statement.stmt_text,1,63) SQL
, cast (lockwait.agent_id_holding_lk as integer) hold_agent
from table (snapshot_lockwait ('DBNAME', -2)) lockwait
, table (snapshot_appl_info ('DBNAME', -2)) appl_info
, table (snapshot_statement ('DBNAME', -2)) statement
where
lockwait.agent_id = appl_info.agent_id and
lockwait.agent_id = statement.agent_id and
statement.stmt_text is
not null
order by wait_agent, hold_agent;
잠금 보유 에이전트의 정적SQL문
select
cast (lockwait.agent_id_holding_lk as integer) as hold_agent
, substr (appl_info.appl_name,1,10) as hold_appl
, substr (statement.creator,1,8) pkg_schema
, substr (statement.package_name,1,8) package
, cast (statement.section_number as smallint) last_section
, cast (cat_statement.secrion as smallint) section
, substr (cat_statements.text,1,61) last_SQL
from
table (snapshot_lockwait ('DBNAME', -2)) as lockwait
, table (snapshot_appl_info('DBNAME', -2)) as appl_info
, table (snapshot_statement('DBNAME', -2)) as statement
, syscat.statement cat_statements
where
lockwait.agent_id_holding_lk = appl_info.agent_id and
lockwait.agent_id_holding_lk = statement.agent_id and
cat_statement.pkgschema = upper(statement.creator) and
cat_statement.package = upper(statement.package_name) and
cat_statement.section <= statement.section_number
order by hold_agent, section;
잠금 보유 에이전트의 동적SQL문
select distinct
cast (lockwait.agent_id_holding_lk as integer) as hold_agent
, substr(appl_info.appl_name,1,10) as hold_appl
, cast (NULL
as char(8)) pkg_schema
, cast (NULL
as char(8)) package
, cast (NULL as smallint) last_section
, cast (NULL
as smallint) section
, cast (substr(statement.stmt_text,1,61) as char(61)) SQL
from
table (snapshot_lockwait('DBNAME', -2)) as lockwait
, table (snapshot_appl_info('DBNAME', -2)) as appl_info
, table (snapshot_statement('DBNAME', -2)) as statement
where
lockwait.agent_id_holding_lk = appl_info.agent_id and
lockwait.agent_id_holding_lk = statement.agent_id and
statement.stmt_text is
not null
order by hold_agent, last_section;
응용프로그램별 로그 사용량
select substr(appl_info.auth_id,1,8) as authid
, cast (appl.agent_id as integer) as agent
, substr (appl_info.appl_name,1,20) appl_name
, appl.uow_log_space_used
, cast (appl.agent_usr_cpu_time_s as integer) user_cpu
, timestampdiff (2, char( current timestamp - appl.uow_start_time))
as elapsed_time
, appl.rows_read
, appl.rows_written
from table (snapshot_appl('DBNAME', -2)) as appl,
table (snapshot_appl_info('DBNAME', -2)) as appl_info
where appl.agent_id = appl_info.agent_id
and appl_info.appl_id <> '$MON_APPL_ID'
order by uow_log_space_used desc, rows_written desc, agentid;
데이터베이스별 로그 사용량
select
total_log_used
, total_log_available
, tot_log_used_top
, cast (sec_logs_allocated as integer) sec_logs_allocated
, sec_log_used_top
, cast (appl_id_oldest_xact as integer) oldest_tx_agent
, substr (appl_info.appl_name,1,12) appl_name
from table (snapshot_database('DBNAME', -2)) as database,
table (snapshot_appl_info('DBNAME', -2)) as appl_info
where database.appl_id_oldest_xact = appl_info.agent_id
and appl_info.appl_id <> '$MON_APPL_ID';
테이블 스페이스 사용량
select substr (tbs_cfg.tablespace_name,1,20) tablespace
, cast (tbs_cfg.tablespace_state as smallint) as state
, case tbs_cfg.tablespace_type
when 1 then 'SMS'
else 'DMS'
end
type
, cast (page_size as integer) page_size
, (total_pages * page_size) / 1024 /1024 as total_size_mb
, (used_pages * page_size) / 1024 /1024 as used_size_mb
, (free_pages * page_size) / 1024 /1024 as free_size_mb
, case tablespace_type
when 1 then 100
else dec ((tbs_cfg.used_pages * 100.00) / tbs_cfg.total_pages,5,2)
end
as ratio
from table (snapshot_tbs_cfg('DBNAME', -2)) as tbs_cfg
order by tablespace;
테이블 스페이스 적중률
select substr (tbs.tablespace_name,1,20) as tablespace
, cast (tbs.pool_data_l_reads as integer) pool_data_l_reads
, cast (tbs.pool_data_p_reads as integer) pool_data_p_reads
, case tbs.pool_data_l_reads
when 0 then null
else dec(((tbs.pool_data_l_reads - tbs.pool_data_p_reads)
* 100.00 / tbs.pool_data_l_reads) ,5,2)
end data_hit_ratio
, cast (tbs.pool_index_l_reads as integer) pool_index_l_reads
, cast (tbs.pool_index_p_reads as integer) pool_index_p_reads
, case tbs.pool_index_l_reads
when 0 then null
else dec(((tbs.pool_index_l_reads - tbs.pool_index_p_reads)
* 100.00 / tbs.pool_index_l_reads) ,5,2)
end index_hit_ratio
from table(snapshot_tbs('DBNAME', -2)) as tbs
order by tablespace;
.gif)
