Có quá nhiều thông tin, kiến thức bạn cần tổng hợp phân tích?
Tôi sẽ chia sẻ cho các bạn các câu lệnh SQL thực sự cần thiết nhất giành cho việc monitor CSDL Oracle, các câu lệnh này đã được áp dụng THÀNH CÔNG, HIỆU QUẢ CAO trên các DB "very huge" tại các tập đoàn công nghệ tại Việt Nam. Đây là TINH HOA của nhiều thế hệ qua gần 15 năm, từ khi Oracle chưa thực sự phổ biến tại Việt Nam.
Khi áp dụng thành thạo các câu lệnh đó (nhưng để hiểu được hết thì phải mất thời gian, đảm bảo DB của các bạn dù lớn, nhỏ, online 24/7 đều có thể "cân" được hết🦹♂️🦹♂️, dựa vào các câu lệnh này các bạn hãy tự xây dựng các công cụ tự giám sát qua email, SMS (auto db) cho mình nhé 👇:
(Note: Hãy lưu lại cho mình và áp dụng NGAY trước khi nó không còn được public nhé)
(Note: Hãy lưu lại cho mình và áp dụng NGAY trước khi nó không còn được public nhé)
/**************************ACTIVE, LOCK **************************/
SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id/*,S.PREV_EXEC_START*/, s.logon_time, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE
S.STATUS = 'ACTIVE' AND
S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
and s.username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE','GGADMIN')
--AND username in 'PAYMENT'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
and lower(ss.sql_text) not like lower('%***%')
--and lower(ss.sql_text) like lower('%SUBSCRIBER_REGISTER')
--and s.sid=5923
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id
--order by S.PREV_EXEC_START;
;
select /* 2.Total_ActiveSessioins*/ USERNAME,count(*) as "Tong Active Session" from gv$session where status='ACTIVE' group by USERNAME order by count(*) desc;
select /* 3.Total_Session*/ username,status, count(*) "Tong Session" from gv$session group by username,status order by count(*) desc;
Select /*4.Blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME
From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' order by inst_id;
select distinct inst_id,''''|| blocking_session ||''',' from (Select /*4.Blocking_session*/ inst_id,blocking_session, sid, serial#, sql_id, wait_class, seconds_in_wait, username,STATUS,SCHEMANAME,OSUSER,MACHINE,PROGRAM,TYPE,LOGON_TIME
From gv$session where blocking_session is not NULL and type not like 'BACKGROUND' )
order by inst_id;
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id
--AND B.inst_id = 1
--and a.status='INACTIVE'
--and A.USERNAME LIKE 'TRIEUNV%'
--AND A.USERNAME not in ('SYS','GGATE','GOLDENGATE')
--AND a.program LIKE '%rman%'
AND sql_id in ('3nr64fnzfa84z')
--and machine like '%ADMIN%'
--and a.event in ('library cache lock','library cache load lock','library cache: mutex X','cursor: pin S wait on X','library cache pin','library cache lock','library cache: mutex X','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention','enq: TX - allocate ITL entry','enq: TX - index contention','enq: SQ - contention','enq: TX - row lock contention','PL/SQL lock timer')
--and round(to_number(sysdate-a.prev_exec_start)*1440) >30
and type='USER'
order by a.inst_id;
SELECT /*5.SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
and b.inst_id=2
AND a.sid in (
'4162')
and type='USER'
order by inst_id;
select /*6.SQL_Detail*/ sql_id,sql_fulltext from gv$sql where sql_id in ('184b2tbutgm99');
select distinct sql_ID,
24*60*60*(sysdate - sql_exec_start) seconds_running
FROM v$active_session_history
where sample_time = (select max(sample_time)
from v$active_session_history
where sample_time < to_date('16-09-2021 10:00',
'dd-mm-yyyy hh24:mi'))
and sql_id is not null
order by 24*60*60*(sysdate - sql_exec_start) desc;
select sql_id,sql_fulltext,loaded_versions,executions,loads,invalidations,parse_calls from gv$sql where inst_id=4 and sql_id='16fh6ft7g4jn9S';
SELECT *
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE s.type != 'BACKGROUND' AND S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND AND S.TYPE = 'USER'
AND s.sql_id <> (select sql_id from v$session where sid=(select sid from v$mystat where rownum=1)) and username NOT in ('SYS')
and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') IN ('direct path read','db file scattered read','PX Deq Credit: send blkd') ;
SELECT *
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE s.type != 'BACKGROUND' AND S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND AND S.TYPE = 'USER' and s.username not in ('SYS','SYSMAN')
and s.event in ('library cache lock','gc buffer busy acquire')
and s.username NOT in ('SYS');
select * from DBA_SCHEDULER_RUNNING_JOBS;
--exec DBMS_SCHEDULER.stop_JOB (job_name => 'SYS.REBUILD_IDX_GGSN',force=>true);
select * from DBA_JOBS_RUNNING;
/**************************SUM *********************************************************/
select /* count , status*/ username,status, count(*) from gv$session group by username,status order by count(*) desc;
select /* Active theo user*/ USERNAME,count(*) from gv$session where status='ACTIVE' group by USERNAME order by count(*) desc;
select status, count(*) from gv$session group by status order by status;
select count(*) from gv$session ;
select USERNAME,count(*) from gv$session group by USERNAME order by count(*) desc;
select machine,count(*) from v$session group by machine order by count(*) desc;
select inst_id, count(*) from gv$session group by inst_id;
select /*Thong ke theo status*/ username,status,count(*) from gv$session where username like 'APP_OWNER%' group by username,status order by count(*) desc;
select /*Thong ke theo inst_id*/ inst_id,count(*), username from gv$session where username like 'APP_OWNER%' group by inst_id, username order by username;
select /* user theo machine */ machine,count(*), username from gv$session where username like 'APP_OWNER%' group by machine, username order by username;
/**************************KILL *********************************************************/
-- Xac dinh process tu inst_id, status, username, sql_id, machine, event,
SELECT /*username*/ 'kill -9 ' || SPID A ,a.INST_ID,A.SID,A.SQL_ID, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND, A.EVENT
FROM gv$session a, gv$process b
WHERE b.ADDR = a.paddr
AND a.inst_id=b.inst_id
--AND B.inst_id = 1
--and a.status='INACTIVE'
--and A.USERNAME LIKE 'BINHTV%'
--AND A.USERNAME not in ('SYS','GGATE','GOLDENGATE')
--AND a.program LIKE '%rman%'
--AND sql_id in ('gtsw86x47z0au')
and machine like '%crcsrv02%'
and a.event in ('library cache lock','library cache load lock','library cache: mutex X','cursor: pin S wait on X','library cache pin','library cache lock','library cache: mutex X','gc buffer busy acquire','enq: TS - contention','enq: TX - row lock contention','enq: TM - contention','db file parallel read','row cache lock','enq: DX - contention','enq: US - contention','enq: TX - allocate ITL entry','enq: TX - index contention','enq: SQ - contention','enq: TX - row lock contention','PL/SQL lock timer')
--and round(to_number(sysdate-a.prev_exec_start)*1440) >30
and type='USER'
order by a.inst_id;
SELECT /*SID*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=2
AND a.sid in (
13562
)
and type='USER'
and machine not like '%BINHTV%' --and user not like 'SYS'
order by inst_id;
SELECT /*call package*/ 'kill -9 ' || spid a,'alter system kill session ' || '''' || a.sid || ',' || a.SERIAL# || '@' || a.inst_id||'''' || ' immediate;', a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,a.prev_exec_start,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=4
AND (b.inst_id, a.sid) in (
(select /*+ parallel(8) */ inst_id, sid from gv$access where object like '%PKG_TEST%')
)
and type='USER'
and a.machine not like '%BINHTV%' ;
select *--'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ' immediate;'
select /*+ parallel(8) */ inst_id, sid from gv$access where object like '%TAB1%';
SELECT /*lock table*/ 'kill -9 ' || spid a, a.INST_ID,A.SQL_ID,A.SID, A.SERIAL#, a.USERNAME, a.STATUS,A.SCHEMANAME,a.OSUSER,A.MACHINE,A.PROGRAM,A.TYPE,A.LOGON_TIME,BACKGROUND
FROM gv$session a, gv$process b
WHERE b.addr = a.paddr
AND a.inst_id=b.inst_id
--and b.inst_id=3
AND (b.inst_id, a.sid) in
(SELECT /*+ parallel(8)*/ s.inst_id,s.sid
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name=upper('TAB1'))
--and type='USER'
--ORDER BY username, session_id;
select /*+ parallel(8) */ sid||','from gv$access where upper(object) like upper('TAB1')
--and inst_id=1;
SELECT v.sid,v.serial#,V.INST_ID,l.ORACLE_USERNAME ora_user, o.object_name, o.object_type,
DECODE(l.locked_mode,0, 'None',1, 'Null', 2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share', 5, 'S/Row-X (SSX)',6, 'Exclusive', TO_CHAR(l.locked_mode)) lock_mode,
o.status, to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, gv$session v
WHERE o.object_id = l.object_id and l.SESSION_ID=v.sid order by 2,3;
-- Xac dinh user dang chay cau lenh SQL nao
select p.INST_ID, 'kill -9 '||P.SPID SPID, s.SID, s.username su, substr(sa.sql_text,1,540) SQL_TEXT
from gv$process p,gv$session s,gv$sqlarea sa
where p.addr=s.paddr and p.INST_ID=s.INST_ID and s.username is not null and s.sql_address=sa.address(+) and s.sql_hash_value=sa.hash_value(+)
and s.username=upper('APP_OWNER')
and type='USER'
order by INST_ID, SID;
--alter system kill session '1066,21548,@1' immediate; /*SID, Serial#*/
--alter system kill session '1921,46696' immediate;
select *--'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ' immediate;'
from gv$session
where event = 'enq: TX - row lock contention'
or event like '%lock contention%';
select 'alter system kill session ' || '''' || sid || ',' || SERIAL# || '''' || ';',TIME_REMAINING+ELAPSED_SECONDS,TIME_REMAINING,TARGET
from v$session_longops a where TIME_REMAINING>0
order by TIME_REMAINING+ELAPSED_SECONDS;
/***************************************************** SQL DETAIL **************************/
select sql_id,sql_fulltext from gv$sql where sql_id in ('67bm8d2ah3xhk');
SELECT /* Tim cau lenh sql */ b.inst_id,b.sid, a.SQL_TEXT, b.username, b.machine, b.blocking_session, B.TYPE
FROM gV$SQLAREA a,gV$SESSION b
WHERE a.ADDRESS = b.SQL_ADDRESS
AND upper(SQL_TEXT) LIKE '%SHOP%';
select machine,username,count(*) from gv$session where sql_id='67bm8d2ah3xhk'
group by machine,username order by count(*) desc;
declare
begin
--SQLs with elapsed time more then 1 hour 155429 155430
SELECT *
FROM dba_hist_snapshot where end_interval_time>=to_date('16/09/2021 09:00:00','dd/mm/yyyy hh24:mi:ss')
and end_interval_time <=to_date('16/09/2021 10:00:00','dd/mm/yyyy hh24:mi:ss')
order by end_interval_time;
--155429 155430
SELECT min(snap_id), max(snap_id)
FROM dba_hist_snapshot where end_interval_time>=to_date('16/09/2021 09:00:00','dd/mm/yyyy hh24:mi:ss')
and end_interval_time <=to_date('16/09/2021 10:00:00','dd/mm/yyyy hh24:mi:ss')
order by end_interval_time;
SELECT sql_id,
text,
elapsed_time,
CPU_TIME,
EXECUTIONS,
PX_SERVERS,
DISK_READ_BYTES,
DISK_WRITE_BYTES,
IO_INTERCONNECT_BYTES,
OFFLOAD_ELIGIBLE_BYTES,
CELL_SMART_SCAN_ONLY_BYTES,
FLASH_CACHE_READS,
ROWS_PROCESSED
--AVG_PX_SERVER
FROM (SELECT x.sql_id,
SUBSTR ( dhst.sql_text, 1, 4000) text,
ROUND ( x.elapsed_time / 1000000,0) elapsed_time,
ROUND ( x.cpu_time / 1000000,0) CPU_TIME,
--ROUND ( x.elapsed_time / 1000000, 3) elapsed_time,
--ROUND ( x.cpu_time / 1000000, 3) cpu_time_sec,
x.executions_delta EXECUTIONS,
ROUND (X.DISK_READ_BYTES/1048576,0) DISK_READ_BYTES,
ROUND (X.DISK_WRITE_BYTES/1048576,0) DISK_WRITE_BYTES,
ROUND (X.IO_INTERCONNECT_BYTES/1048576,0) IO_INTERCONNECT_BYTES,
ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES,
X.FLASH_CACHE_READS FLASH_CACHE_READS,
ROUND (X.cell_smart_scan_only_BYTES/1048576,0) CELL_SMART_SCAN_ONLY_BYTES,
(x.ROWS_PROCESSED) ROWS_PROCESSED,
(X.PX_SERVERS) PX_SERVERS,
--ROUND(X.PX_SERVERS/X.executions_delta,0) AVG_PX_SERVER,
row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn
FROM dba_hist_sqltext dhst,
(SELECT dhss.sql_id sql_id,
SUM (dhss.cpu_time_delta) cpu_time,
SUM (dhss.elapsed_time_delta) elapsed_time,
SUM (dhss.executions_delta) executions_delta,
SUM (dhss.PHYSICAL_READ_BYTES_DELTA) DISK_READ_BYTES,
SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA) DISK_WRITE_BYTES,
SUM (dhss.IO_INTERCONNECT_BYTES_DELTA) IO_INTERCONNECT_BYTES,
SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA) OFFLOAD_ELIGIBLE_BYTES,
SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS,
SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA) cell_smart_scan_only_BYTES,
SUM (dhss.ROWS_PROCESSED_DELTA) ROWS_PROCESSED,
SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS
FROM dba_hist_sqlstat dhss
WHERE dhss.snap_id IN
(SELECT distinct snap_id
FROM dba_hist_snapshot
WHERE SNAP_ID >= 155429 AND SNAP_ID<= 155430)
--comment BELOW line if want to include current executions.
--AND dhss.executions_delta > 0
and dhss.instance_number=1
GROUP BY dhss.sql_id) x
WHERE x.sql_id = dhst.sql_id
AND ROUND ( x.elapsed_time / 1000000, 3) > 3600
)
WHERE rn = 1 ORDER BY ELAPSED_TIME DESC;S
--WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"
select wait_class, count(*) cnt from dba_hist_active_sess_history
WHERE SNAP_ID >= 155429 AND SNAP_ID<= 155430
--and instance_number=1
group by wait_class_id, wait_class
order by 2 desc;
-- Top 40 Objects by Physical Read
SELECT * FROM (
SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS,
DHSS.INSTANCE_NUMBER AS INST,
SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ,
SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ,
SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT,
SUM(DHSS.ITL_WAITS_DELTA) ITL_WT,
SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT
from dba_hist_seg_stat DHSS, DBA_OBJECTS DO
WHERE DHSS.SNAP_ID >= 155429 AND DHSS.SNAP_ID<= 155430
AND DHSS.OBJ#=DO.OBJECT_ID
and DHSS.INSTANCE_NUMBER=1
group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
order BY PHY_READ DESC
) WHERE ROWNUM <=40;
end;
/*************************************************** BACKUP ******************************/
select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,2) RATIO , input_bytes_per_sec_display, output_bytes_per_sec_display
from v$rman_backup_job_details
where trunc(end_time)>=trunc(sysdate-120)
order by end_time desc;
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR != TOTALWORK ;
/**************************Analyze, Gather *********************************************************/
select /* partition*/ table_owner,table_name,partition_name, tablespace_name,last_analyzed, num_rows
from dba_tab_partitions
where
table_owner='APP_OWNER' and
last_analyzed > sysdate-90
order by last_analyzed desc;
select owner, table_name, tablespace_name,num_rows, last_analyzed, partitioned
from dba_tables
where
owner='APP_OWNER' and
last_analyzed > sysdate-30
order by last_analyzed desc;
select owner, table_name, partition_name, subpartition_name, num_rows, last_analyzed, stale_stats from dba_tab_statistics
where
--owner='APP_OWNER' and
stale_stats='YES'
order by last_analyzed desc;
-- Partition APP_OWNER
-- CHECK GATHER TABLE
select /* partition*/ table_owner,table_name,partition_name,last_analyzed, num_rows
from dba_tab_partitions
where table_owner in ('APP_OWNER') and
table_name NOT LIKE '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%$%' and table_name not like '%TEST%'
and table_name in ('TAB1','TAB2'')
and (length(partition_name)=12 and to_date(substr(partition_name,5,8),'YYYYMMDD')> trunc(sysdate)-31 and to_date(substr(partition_name,5,8),'YYYYMMDD')< trunc(sysdate))
--and (((length(partition_name) = 12 AND to_date(substr(partition_name,5,8),'YYYYMMDD')<trunc(sysdate)AND to_date(substr(partition_name,5,8),'YYYYMMDD')>trunc(sysdate)-15))
--or (length(partition_name) =10 AND to_date(substr(partition_name,5,6),'YYYYMM')<trunc(sysdate-1) and to_date(substr(partition_name,5,6),'YYYYMM')>trunc(sysdate-62))
--or (length(partition_name) =8 AND to_date(substr(partition_name,5,4),'YYYY')<=trunc(sysdate-1) and to_date(substr(partition_name,5,6),'YYYYMM')>=trunc(sysdate-365))
--)
--AND to_date(substr(partition_name,5,6),'YYYYMM')>=add_months(trunc(sysdate,'month'),-1)))
--and partition_name like '%2021%'
--and last_analyzed<=trunc(sysdate+1) and last_analyzed>=trunc(sysdate-3)
and (last_analyzed is null)
--AND NVL (last_analyzed, trunc(SYSDATE) - 15) < trunc(sysdate)-1
--AND NVL(last_analyzed, SYSDATE) >= trunc(sysdate)-7
order by
--last_analyzed desc,
table_owner, table_name, partition_name desc;
SELECT /* Non-partition */ owner,table_name, last_analyzed,num_rows
from dba_tab_statistics a
where owner in 'APP_OWNER' and
table_name not like 'XXX%' and table_name not like 'TMP%' and (stale_stats is null or stale_stats = 'YES') and object_type = 'TABLE'
--and table_name in ('TAB3','TAB4')
--and (last_analyzed is not null)
and last_analyzed<=trunc(sysdate+1) and last_analyzed>=trunc(sysdate-1)
and not exists (select 1 from dba_tab_statistics where owner = a.owner and table_name = a.table_name and object_type = 'PARTITION' and rownum < 2)
order by last_analyzed desc,1,2 ;
select /* Non-partition*/ owner, table_name,last_analyzed, num_rows
from dba_tables
where
owner in ('APP_OWNER')
and table_name NOT LIKE '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%$%' and table_name not like '%TEST%'
and partitioned='NO'
and (last_analyzed is not null)
--last_analyzed<sysdate and last_analyzed>=trunc(sysdate)
order by last_analyzed desc;
-- CHECK GATHER INDEX
select /* index partitition */ index_owner,index_name,partition_name,last_analyzed, num_rows
from dba_ind_partitions
where
index_owner in ('APP_OWNER')
--and last_analyzed<sysdate and last_analyzed>=trunc(sysdate)
--and (last_analyzed is null or num_rows is null)
and last_analyzed is not null
and partition_name like '%2021%'
order by index_owner, index_name, partition_name desc;
select /* index non-partitition */ owner, index_name,last_analyzed, num_rows from dba_indexes
where
owner in ('APP_OWNER')
and last_analyzed is not null
--and last_analyzed<sysdate and last_analyzed>=trunc(sysdate)
--and (last_analyzed is null or num_rows is null)
order by last_analyzed desc;
select * from binhtv.dbamf_log_jobs where event_date >=sysdate-1 order by event_date desc;
-- GATHER TABLE PARTITION
--select count(1) from (
SELECT /* GATHER TABLE PARTITION*/ table_owner, table_name, partition_name, last_analyzed,num_rows,
'begin
dbms_stats.gather_table_stats
(ownname=>''' || TABLE_OWNER || ''',
tabname=>''' || table_name || ''',
partname=>''' || partition_name || ''',
Granularity => ''PARTITION'',
estimate_percent => 10,
method_opt => ''FOR ALL COLUMNS SIZE AUTO'',
cascade=>true,
degree=>10,
No_Invalidate => FALSE);
end;
'
script
FROM dba_tab_partitions
WHERE
table_owner='APP_OWNER'
and table_name not like '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%TEST%' and table_name not like '%$%'
and table_name in ('TAB1','TAB2')
AND (last_analyzed is null or num_rows is null)
and ((length(partition_name) = 12 AND to_date(substr(partition_name,5,8),'YYYYMMDD')<trunc(sysdate))
-- AND to_date(substr(partition_name,5,8),'YYYYMMDD')>trunc(sysdate)-60
-- )
or (length(partition_name) =10 AND to_date(substr(partition_name,5,6),'YYYYMM')<trunc(sysdate))
-- AND to_date(substr(partition_name,5,6),'YYYYMM')>=add_months(trunc(sysdate,'month'),-2)
-- )
)
and partition_name like '%2021%'
ORDER BY partition_name desc, table_name, partition_name
--)
;
SELECT /* GATHER TABLE NON-PARTITION */ owner,table_name,last_analyzed,num_rows,
'begin
dbms_stats.gather_table_stats
(ownname => '''||owner || ''',
tabname => '''|| table_name|| ''',
cascade => true,
estimate_percent => 10,
degree => 10);
end; '
script
FROM dba_tables
WHERE owner='APP_OWNER'
and table_name not like '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%TEST%' and table_name not like '%$%'
and partitioned = 'NO'
AND (last_analyzed is null or num_rows =0)
ORDER BY last_analyzed,table_name;
--GATHER TABLE PARTITION AUTO
select /* GATHER TABLE PARTITION AUTO */ table_owner, table_name, max(partition_name),
'begin
dbms_stats.gather_table_stats
(ownname=>''' || TABLE_OWNER || ''',
tabname=>''' || table_name || ''',
partname=>''' || max(partition_name) || ''',
Granularity => ''APPROX_GlobAL AND PARTITION'',
estimate_percent => 10,
method_opt => ''FOR ALL COLUMNS SIZE AUTO'',
cascade=>true,
degree=>10,
No_Invalidate => FALSE);
end;
'
script
FROM dba_tab_partitions
WHERE
table_owner='APP_OWNER'
and table_name not like '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%TEST%' and table_name not like '%$%'
--AND (last_analyzed is null or num_rows is null)
AND (last_analyzed is null)
and ((length(partition_name) = 12 AND to_date(substr(partition_name,5,8),'YYYYMMDD')<= trunc(sysdate))
or (length(partition_name) =10 AND to_date(substr(partition_name,5,6),'YYYYMM')<=trunc(sysdate))
)
--and partition_name like '%2021%'
group by table_owner, table_name
ORDER BY table_owner, table_name;
-- GATHER TABLE STALE
SELECT /* GATHER TABLE PARTITION STABLE */ owner,table_name, partition_name, last_analyzed,num_rows,stale_stats,
'begin dbms_stats.gather_table_stats(ownname =>'''||owner||''',tabname =>'''||table_name||''',partname'||'=>'''|| PARTITION_NAME
|| ''',granularity=>''partition'',cascade=> TRUE,force=>TRUE,estimate_percent=>10,'
|| 'method_opt=>''FOR ALL COLUMNS SIZE AUTO'',degree => 8); end;'
script
FROM dba_tab_statistics
where
owner='APP_OWNER'
and table_name not like '%XXX%' and table_name not like '%TMP%' and table_name not like '%TEMP%' and table_name not like '%TEST%' and table_name not like '%$%'
and table_name in ('TAB1','TAB2')
and ((length(partition_name) = 12 AND to_date(substr(partition_name,5,8),'YYYYMMDD')< trunc(sysdate) and to_date(substr(partition_name,5,8),'YYYYMMDD')>= trunc(sysdate)-30)
or (length(partition_name) =10 AND to_date(substr(partition_name,5,6),'YYYYMM')<trunc(sysdate)and to_date(substr(partition_name,5,8),'YYYYMMDD')>= trunc(sysdate)-30) )
-- and table_name='TAB3'
--and num_rows<10000000
and partition_name is not null
--and partition_name like '%2021%'
and (stale_stats='YES' or stale_stats is null)
order by partition_name desc, owner, table_name;
SELECT /* GATHER TABLE NON-PARTITION STALE */ owner,table_name, partition_name, last_analyzed,stale_stats,
'begin
dbms_stats.gather_table_stats
(ownname=>''' || OWNER || ''',
tabname=>''' || table_name || ''',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ''FOR ALL COLUMNS SIZE AUTO'',
cascade=>true,
degree=>10);
end;' script
from dba_tab_statistics a
where owner = 'APP_OWNER'
and table_name not like 'XXX%' and table_name not like 'TMP%' and (stale_stats is null or stale_stats = 'YES') and object_type = 'TABLE'
--and table_name in ('TAB3','TAB4')
and not exists (select 1 from dba_tab_statistics where owner = a.owner and table_name = a.table_name and object_type = 'PARTITION' and rownum < 2)
and (stale_stats='YES' or stale_stats is null)
order by 1,2 desc;
/***************************************************** LOCK BANG, PKG ****************************************************************/
--Table
SELECT c.owner, c.object_name, c.object_type, b.SID,b.SQL_ID, b.serial#, b.status,b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id
AND a.object_id = c.object_id
--and lower(object_name) like lower('%TAB1%');
SELECT /* lock table */ s.inst_id,s.sid, s.serial#,s.sql_id,username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser, s.machine,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD,
decode(l.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',l.type) lock_type
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
and object_name like upper('%TAB2')
--and username like upper('APP_OWNER')
ORDER BY username, session_id;
-- Cac session dang truy cap vao object theo owner --> De kill
select /*+ parallel(8) */ distinct owner from gv$access where lower(object) like lower('%b4_close_cycle%');
/**************************STORAGE *********************************************************/
--ASM
select group_number,type, name, round(total_mb/1024,2) "Total_GB", round(usable_file_mb/1024) "Usable_file_GB" from v$asm_diskgroup order by usable_file_mb desc;
select * from gv$asm_diskgroup;
select * from V$ASM_DISKGROUP_STAT;
select name group_number, os_mb, total_mb, free_mb, path,header_status,mount_status,mode_status,state,create_date, mount_date from gv$asm_disk
--where name='FS1SATA2'
order by name, group_number;
-- DB Size all
Select
( select round(sum(bytes)/1024/1024/1024,2) data_size from dba_data_files ) +
( select round(nvl(sum(bytes),0)/1024/1024/1024,2) temp_size from dba_temp_files ) +
( select round(sum(bytes)/1024/1024/1024,2) redo_size from sys.v_$log ) +
( select round(sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024,2) controlfile_size from v$controlfile) "SizeDB in GB"
from
dual;
-- DB Size theo DF 18TB (20/10/20) 16TB(21/10/20, 15/02/22 24TB)
select round(sum(bytes)/1024/1024/1024) "SizeDB_DF" from dba_data_files
order by "SizeDB_DF" desc;
-- DB Size theo segments: 12TB (20/10/20) 13TB (21/10) 15.7TB (15/02)
select round(sum(bytes)/1024/1024/1024) "SizeDB_Segments" from dba_segments
order by "SizeDB_Segments" desc;
-- Size theo owner
select owner,round(sum(bytes)/1024/1024/1024) "GB" from dba_segments
--where owner='APP_OWNER'
group by owner
order by "GB" desc;
-- Size theo segment (table, index)
select owner, segment_name,round(sum(bytes)/1024/1024/1024) "GB" from dba_segments
--where owner='SYS'
group by owner, segment_name
order by "GB" desc;
-- Check chi tiet
select owner, segment_name,tablespace_name,round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where owner='SYS'
group by owner, segment_name, tablespace_name
order by "GB" desc;
-- LOB
select c.*, d.tablespace_name from (select a.owner, b.segment_name, a."GB",b.table_name,b.column_name from (select owner, segment_Name, round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments
where
--owner in ('APP_OWNER') and
segment_type like '%LOB%'
--and tablespace_name='DATA_LOB'
group by owner, segment_name
order by owner, segment_name) a,
(select owner, table_name, column_name,segment_name from dba_lobs
where table_name not like '%$'
--and owner in ('APP_OWNER')
group by owner, table_name,column_name, segment_name) b
where a.owner=b.owner and a.segment_Name=b.segment_name /*and a.gb>10*/) c, (select distinct owner, segment_name, tablespace_name from dba_segments) d
where c.owner=d.owner and c.segment_name=d.segment_name
order by c."GB" desc, d.tablespace_name,c.owner;
--Size TBS Read Only
select round(sum(bytes)/1024/1024/1024) "TBS_RO_DF_GB" from dba_data_files where tablespace_name in
(select tablespace_name from dba_tablespaces where
status='READ ONLY');
select tablespace_name from dba_tablespaces where
status='READ ONLY'
select round(sum(bytes)/1024/1024/1024) "TBS_RO_SEGMENTS_GB" from dba_segments where tablespace_name in (select name from v$tablespace
where ts# in (select ts# from v$datafile where enabled='READ ONLY'));
-- Check Tablespace free fix size
select b.tablespace_name, tbs_size SizeGb, a.free_space FreeGb
from (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,0) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes)/1024/1024/1024,0) as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
and (a.tablespace_name like '%202008%')
order by tablespace_name ;
-- Check Tablespace Free
SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage", ROUND
(a.bytes_alloc / 1024 / 1024) "Size MB", ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)) "Used MB", ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free", ROUND (maxbytes / 1048576) "Max MB",
ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) "%Used of Max"
FROM ( SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
--and a.tablespace_name in ('UNDOTBS1','UNDOTBS1')
and ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) >95
order by "%Used of Max" desc;
/**************************ARCHIVED LOG *********************************************************/
-- Theo doi archived log sinh ra
select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,2) "Archived Log GB" from V$ARCHIVED_LOG
where trunc(completion_time) >= trunc(sysdate-90)
--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
and dest_id=1
group by trunc(completion_time)
order by trunc(completion_time) desc;
select
to_char(COMPLETION_TIME,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'00',1,0)),'999') "00h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'01',1,0)),'999') "01h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'02',1,0)),'999') "02h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'03',1,0)),'999') "03h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'04',1,0)),'999') "04h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'05',1,0)),'999') "05h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'06',1,0)),'999') "06h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'07',1,0)),'999') "07h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'08',1,0)),'999') "08h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'09',1,0)),'999') "09h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'10',1,0)),'999') "10h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'11',1,0)),'999') "11h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'12',1,0)),'999') "12h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'13',1,0)),'999') "13h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'14',1,0)),'999') "14h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'15',1,0)),'999') "15h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'16',1,0)),'999') "16h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'17',1,0)),'999') "17h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'18',1,0)),'999') "18h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'19',1,0)),'999') "19h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'20',1,0)),'999') "20h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'21',1,0)),'999') "21h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'22',1,0)),'999') "22h",
to_char(sum(decode(substr(to_char(COMPLETION_TIME,'HH24'),1,2),'23',1,0)),'999') "23h",
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024,0)||' GB' "Total GB in a day",COUNT(*) "Total switch log in a day"
from v$archived_log
where to_date(COMPLETION_TIME) > sysdate-31
group by to_char(COMPLETION_TIME,'YYYY-MM-DD')
order by day desc;
-- Archived log sinh ra theo gio
select to_char(next_time,'YYYY-MM-DD hh24') Hour, round(sum(size_in_byte)/1024/1024,2) as size_in_mb, count(*) log_switch from (
select thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE as size_in_byte, next_time
from v$archived_log where name is not null group by thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE, next_time)
group by to_char(next_time,'YYYY-MM-DD hh24') order by 1 desc;
--check high archivelog gen issue
--Kiểm tra archive log gen theo ngày giờ
col day for a12
set lines 1000
set pages 999
col "00" for a3
col "01" for a3
col "02" for a3
col "03" for a3
col "04" for a3
col "05" for a3
col "06" for a3
col "07" for a3
col "08" for a3
col "09" for a3
col "10" for a3
col "11" for a3
col "12" for a3
col "13" for a3
col "14" for a3
col "15" for a3
col "16" for a4
col "17" for a3
col "18" for a4
col "19" for a3
col "20" for a3
col "21" for a3
col "22" for a3
col "23" for a3
SELECT
to_char(first_time,'DD-MON-YYYY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23",
count(*) Tot
from
v$log_history
WHERE first_time > sysdate -7
GROUP by
to_char(first_time,'DD-MON-YYYY'),trunc(first_time) order by trunc(first_time);
--tìm object thay đổi nhiều nhất -> sinh nhiều log nhất
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI:SS') snap_time,
dhsso.object_name,sum(db_block_changes_delta) as maxchanges
FROM dba_hist_seg_stat dhss,dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj# AND dhss.dataobj# = dhsso.dataobj#
AND begin_interval_time BETWEEN to_date('2022-03-29 08:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2022-03-29 11:00:00','YYYY-MM-DD HH24:MI:SS')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI:SS'),dhsso.object_name order by maxchanges desc;
--tìm những query tác động lên object trên
col SQL_TEXT for a60
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text,5000,1),
dhss.instance_number,
dhss.sql_id,executions_delta,rows_processed_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%<tên bảng>%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2022-03-29 08:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2022-03-29 11:00:00','YYYY-MM-DD HH24:MI:SS')
AND dhss.sql_id = dhst.sql_id;
--Tìm user chạy query trên
SELECT to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),user_id,program
FROM dba_hist_active_sess_history WHERE sql_id in ('9kmswdfbxrr0p','71hw4a5wkuw8v','0jkhpvp1chchn')
AND to_char(sample_time,'YYYY-MM-DD HH24:MI:SS') between '2022-03-29 08:00:00' and '2022-03-29 11:00:00' order by 1;
-- Phan vung chua archived log
select * from v$parameter
where name like '%log_archive%';
-- Archived hien tai da apply chua hay da bi xoa archived log chua?
select * from v$archived_log where applied='NO'
order by sequence#,thread#;
/**************************DISTRIBIUTED TRANSACTION *********************************************************/
---///////ORA-01591: lock held by in-doubt distributed transaction 10.1.10741505, xy ly tren sqlplus
select * from sys.pending_trans$;
select * from DBA_2PC_PENDING;
select * from DBA_2PC_NEIGHBORS;
743.30.1421878
1896.9.233248
2780.32.722288
3127.12.110519
commit force '3127.12.110519'
--rollback force '75.1.3697342'
execute dbms_transaction.purge_lost_db_entry('3127.12.110519');
commit;
SELECT local_tran_id, state
FROM DBA_2PC_PENDING
where (retry_time-fail_time)*24*60>1.5;
KILL LOCK DBLINK
declare
CURSOR c1
IS
SELECT local_tran_id, state
FROM DBA_2PC_PENDING
where (retry_time-fail_time)*24*60>1.5; --waiting for longer 4 min
BEGIN
/*FOR r1 IN c1
LOOP
dbms_output.put_line('1');
EXECUTE IMMEDIATE 'rollback force '''
|| r1.local_tran_id
|| '''';
commit;
end loop;*/
FOR r2 IN c1
LOOP
if r2.state in ('committed') then
EXECUTE IMMEDIATE 'begin
dbms_transaction.purge_lost_db_entry('''
|| r2.local_tran_id
|| ''');
commit;
end;';
commit;
elsif r2.state='prepared' then
EXECUTE IMMEDIATE 'rollback force '''
|| r2.local_tran_id
|| '''';
commit;
EXECUTE IMMEDIATE 'begin
dbms_transaction.purge_lost_db_entry('''
|| r2.local_tran_id
|| ''');
commit;
end;';
commit;
else
EXECUTE IMMEDIATE 'begin
dbms_transaction.purge_lost_db_entry('''
|| r2.local_tran_id
|| ''');
commit;
end;';
commit;
end if;
end loop;
END;
/**************************OBJECT INVALID, Index UNUSABLE ***********************************************************/
select 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects
where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE') and status like 'INVALID'and OWNER in ('APP_OWNER')
UNION ALL
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' from dba_objects
where object_type in ('PACKAGE BODY') and status like 'INVALID' and OWNER in ('APP_OWNER') ;
-- Index UNUSABLE
select * from dba_indexes where status='UNUSABLE';
select owner,segment_name, round(sum(bytes)/1024/1024,2) "MB" from dba_segments where (owner,segment_name)
in (select owner, index_name from dba_indexes where status='UNUSABLE')
group by owner, segment_name;
select * from dba_ind_partitions where status='UNUSABLE'
--and index_name like '%%';
/**************************OTHERS *********************************************************/
--DB, Instance
select * from gv$instance;
select * from gv$database;
select * from v$parameter
where name like '%process%';
--Index
select * from dba_ind_partitions where status='UNUSABLE' and index_owner not in ('SYS','SYSTEM') order by index_owner, index_name;
select * from dba_indexes where status!='VALID' and owner not in ('SYS','SYSTEM') and partitioned!='YES' order by owner, index_name;
-- Index parallel
select * from dba_indexes where degree>1 order by 2;
-- Table parallel
select * from dba_tables where degree > '1' ;
-- Check IO
SELECT host_name,
db_name,
instance_name,
ROUND (SUM (last_15_mins) / 1024 / 1024) IO_MB_LAST_15_MINS,
SYSDATE
FROM ( SELECT inst.host_name,
db.name AS db_name,
inst.instance_name,
sm.metric_name,
ROUND (AVG (sm.VALUE), 0) last_15_mins
FROM GV$SYSMETRIC_HISTORY sm,
gv$instance inst,
(SELECT name FROM v$database) db
WHERE sm.inst_id = inst.inst_id
AND sm.metric_name IN ('Physical Read Total Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Redo Generated Per Sec')
AND sm.begin_time >= SYSDATE - 15 / (24 * 60)
GROUP BY inst.host_name,
db.name,
inst.instance_name,
sm.inst_id,
sm.metric_name)
GROUP BY host_name, db_name, instance_name
ORDER BY 1;
select * from DBA_SCHEDULER_RUNNING_JOBS;
/**************************999 *********************************************************/
select * from ddl_log where trunc(ddl_date) =to_date('31/05/2018','dd/mm/yyyy')
and object_name='PKG_TEST';
/***** BINHTV *****/
select * from binhtv.tc_dba_action_log order by action_date desc;
select * from BINHTV.tc_monitor where event_datetime>sysdate-2 order by event_datetime desc;
select rowid, a.* from sys.table_Ip a
where username like 'APP_OWNER%'
--and ip_address like '10.10.10.10'
order by 1;
select * from dba_db_links
order by 1,2,3;
select owner,job_name,job_creator,program_owner,program_name,job_type,job_action ,start_date,repeat_interval,state,enabled,last_start_date,last_run_duration,next_run_date from dba_scheduler_jobs;
select * from dba_jobs_running;
select * from DBA_SCHEDULER_RUNNING_JOBS;
----Các câu SQL check hệ thống.
--Check xem máy nào đang lockdata
select c.owner, c.object_name, c.object_type, b.sid, b.serial#,
'ALTER SYSTEM KILL SESSION ''' || b.sid || ', ' || b.serial# || ''';' AS KILL_COMMAND,
b.status,
b.osuser,
b.machine
from
v$locked_object a , v$session b, dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
--Check câu lệnh SQL chiếm nhiều thời gian
select * FROM gv$sqlarea;
SELECT inst_id, sql_id,module,parsing_schema_name username,
sql_fulltext sql,
optimizer_cost cost,executions,
ROUND (elapsed_time / executions / 1000000,2) AS "avg_time/exc(s)"
FROM gv$sqlarea
WHERE optimizer_cost > 100
AND last_load_time >= TRUNC (SYSDATE)
AND executions > 1
AND elapsed_time / executions/1000000 > 1
AND parsing_schema_name NOT LIKE '%SYS%'
ORDER BY optimizer_cost DESC;
--Tìm tác vụ đang chạy mãi không xong
SELECT
S.SID, S.SERIAL#,
'ALTER SYSTEM KILL SESSION ''' || S.SID || ', ' || S.SERIAL# || '@'||inst_id||''';' AS KILL_COMMAND,
Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID;
--Tìm các SID đang xử dụng bao nhiêu % CPU
SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM
v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;
=============================
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì, phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
[VIP] Bí quyết Giám sát mọi cơ sở dữ liệu Oracle Database, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
