1. Find which table/objects has more number of changes during the problematic window:
set lines 2000 pages 2000
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
sum(db_block_changes_delta) as maxchages
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('2017_06_23 08','YYYY_MM_DD HH24')
AND to_date('2017_06_23 13','YYYY_MM_DD HH24')
GROUP BY to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dhsso.object_name order by maxchages asc;
SNAP_TIME OBJECT_NAME MAXCHAGES
---------------- ------------------------------ ----------
2017_06_23 10:10 ** USER2: 12880000 929040
2017_06_23 12:20 USER3 945840
2017_06_23 11:50 ** USER2: 12920960 1044608
2017_06_23 12:20 ** USER2: 12548224 1053648
2017_06_23 10:50 USER1 10270048
2017_06_23 09:50 USER1 10482448
2017_06_23 08:50 USER1 12311328
2017_06_23 12:50 USER1 12840416
2017_06_23 08:30 USER1 14257616
2017_06_23 10:30 USER1 14998688
2017_06_23 09:30 USER1 15096224
2017_06_23 08:40 USER1 15209616
2017_06_23 09:40 USER1 16080960
2017_06_23 10:40 USER1 16450688
2017_06_23 12:20 USER1 16478448
2017_06_23 11:10 USER1 27028928
2017_06_23 11:20 USER1 30691664
2017_06_23 12:10 USER1 33638784
2017_06_23 12:00 USER1 35467568
2017_06_23 12:40 USER1 39375520
2017_06_23 11:50 USER1 48477552
2017_06_23 11:30 USER1 48963904
2017_06_23 12:30 USER1 53693696
2017_06_23 11:40 USER1 63538416
2. Once you know the objects ,get the SQL information realted to those objects
SELECT to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text,4000,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 '%USER1%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND begin_interval_time BETWEEN to_date('2017_06_23 08','YYYY_MM_DD HH24')
AND to_date('2017_06_23 13','YYYY_MM_DD HH24')
AND dhss.sql_id = dhst.sql_id;
INSTANCE_NUMBER SQL_ID EXECUTIONS_DELTA ROWS_PROCESSED_DELTA
--------------- ------------- ---------------- --------------------
2017_06_23 11:20
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 0 0
2017_06_23 11:20
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 2356 7619304
2017_06_23 12:40
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 0 0
2017_06_23 10:30
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 10 13
2017_06_23 10:30
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 2317 7493022
2017_06_23 12:40
UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
1 bw9hk94rnvg3z 3790 11601387
set linesize 20000
set pagesize 20000
set long 99999
select sql_id,SQL_FULLTEXT from gv$sql where SQL_ID='&SQL_ID';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
bw9hk94rnvg3z UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
3. Once you know the SQL ID , get the program and userid who is running it and intimate the user to take care of this query / Program.
SELECT instance_number, to_char(sample_time,'yyyy_mm_dd hh24:mi:ss'),
user_id,
program
FROM dba_hist_active_sess_history
WHERE sql_id in ('bw9hk94rnvg3z')
AND snap_id BETWEEN 489306 AND 489320 ;
INSTANCE_NUMBER TO_CHAR(SAMPLE_TIME USER_ID PROGRAM
--------------- ------------------- ---------- ----------------------------------------------------------------
1 2017_06_23 11:10:15 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:17 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:17 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:07 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:20:07 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:57 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:57 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:47 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:47 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:37 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:37 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:27 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:27 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:16 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:16 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:06 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:19:06 124 httpd.worker@joshi.com (TNS V1-V3
1 2017_06_23 11:40:41 280 Golden32.exe
1 2017_06_23 11:40:41 124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:41 124 oracle@localhost (J003)
1 2017_06_23 11:40:41 124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:30 280 Golden32.exe
1 2017_06_23 11:40:30 124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:30 124 oracle@localhost (J003)
1 2017_06_23 11:40:30 124 httpd.worker@joshi.com (TNS V1-V3)
1 2017_06_23 11:40:20 280 Golden32.exe
###
V$ARCHIVED_LOG displays information about the archived logs generated by your database and haven't yet aged out from your control file.
We are going to use this view to generate a report, displaying some useful information about it.
BLOCKS column stores archived log's size in blocks, BLOCK_SIZE stores block size in bytes, so if we multiply them (BLOCKS*BLOCK_SIZE) we get its size in bytes.
ARCHIVED indicates whether the log is archived and DELETED whether RMAN has deleted it.
The following query displays per day the volume in MBytes of archived logs generated, deleted and of those that haven't yet been deleted by RMAN.
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');
1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.
Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values
indicate a session generating lots of redo. The query you can use is:
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;
SID SERIAL# USERNAME PROGRAM BLOCK_CHANGES
---------- ---------- ------------------------------ ------------------------------------------------ -------------
3853 2084 WEB_TEST oracle@localhost (J003) 23880750
302 54420 USER5 Golden32.exe 1695622
3845 58755 USER4 httpd.worker@site.joshin.com (TNS V1-V3) 1131377
3144 35736 USER4 JDBC Connect Client 1085874
2572 4955 USER4 httpd.worker@site15.localhost.com (TNS V1-V3) 941475
1727 8937 USER4 JDBC Connect Client 932579
2415 10553 oracle@localhost (SMON) 410585
1442 23763 USER4 httpd.worker@joshi.com (TNS V1-V3) 357261
1428 2166 USER4 JDBC Connect Client 237139
1728 13101 USER4 httpd.worker@site.joshin.com (TNS V1-V3) 235480
2565 65219 USER4 oracle@localhost (J001) 174404
2) Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).
SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
SID SERIAL# USERNAME PROGRAM USED_UBLK USED_UREC
---------- ---------- ------------------------------ ------------------------------------------------ ---------- ----------
3853 2084 WEB_TEST oracle@localhost (J003) 90 6328
2565 65219 USER4 oracle@localhost (J001) 3 164
586 4151 WILLARDC SQL Developer 1 20
set lines 2000 pages 500
col MODULE for a20
col USERNAME for a15
col EVENT for a30
col MACHINE for a40
select SID,USERNAME,STATUS,PROGRAM,MACHINE,to_char(LOGON_TIME,'dd/mm/yyyy hh24:mi:Ss') LOGON_TIME,SQL_id from v$session where username is not null and SID='&SID';
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ------------------------- ------------------- -------------
3853 WEB_TEST ACTIVE oracle@localhost (J003) localhost 23/06/2017 13:30:16 bw9hk94rnvg3z
cal
SQL> col MACHINE for a40
SQL> /
Enter value for sid: 3853
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
3853 WEB_TEST ACTIVE oracle@localhost (J003) localhost 23/06/2017 13:30:16 bw9hk94rnvg3z
SQL>
SQL> /
Enter value for sid: 302
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
302 USER5 INACTIVE Golden32.exe PCDATA\CTC2372M1 23/06/2017 10:01:23
SQL> /
Enter value for sid: 3845
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
3845 USER4 INACTIVE httpd.worker@site.joshin.com (TNS V1-V3) site17.localhost.com 23/06/2017 11:07:02
SQL> /
Enter value for sid: 3144
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
3144 USER4 INACTIVE JDBC Connect Client site21.localhost.dmz/172.22.1.6 22/06/2017 23:30:00
SQL> /
Enter value for sid: 2572
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
2572 USER4 INACTIVE httpd.worker@site15.localhost.com (TNS V1-V3) site15.localhost.com 23/06/2017 12:33:09
SQL> /
Enter value for sid: 1727
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
1727 USER4 INACTIVE JDBC Connect Client site21.localhost.dmz/172.22.1.6 23/06/2017 02:00:00
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
1442 USER4 ACTIVE oracle@localhost (J002) localhost 23/06/2017 13:45:27 ajs0qvdbt6qs5
SQL> /
Enter value for sid: 1428
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
1428 USER4 INACTIVE JDBC Connect Client site21.localhost.dmz/172.22.1.6 23/06/2017 11:00:00
SQL> /
Enter value for sid: 1728
SID USERNAME STATUS PROGRAM MACHINE LOGON_TIME SQL_ID
---------- --------------- -------- ------------------------------------------------ ---------------------------------------- ------------------- -------------
1728 USER4 INACTIVE httpd.worker@site.joshin.com (TNS V1-V3) site17.localhost.com 23/06/2017 12:43:42
set linesize 20000
set pagesize 20000
set long 99999
select sql_id,SQL_FULLTEXT from gv$sql where SQL_ID='&SQL_ID';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
ajs0qvdbt6qs5 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN PcdGate_3rdPartyDigital.APIBatchSend; :mydate := next_date; IF br
oken THEN :b := 1; ELSE :b := 0; END IF; END;
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
bw9hk94rnvg3z UPDATE USER1 SET WBSTATUS = :B2 , WBSTATUSTYPE = :B1 WHERE WBTRASESID = :B3
###
select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks,
(t.USED_UBLK*8192/1024) KBytes from v$transaction t,
v$session s,
v$sql sql
where t.addr = s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';
No comments:
Post a Comment