delete archivelog until time 'sysdate - 7'; archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1758.12751.1145022793 thread=1 sequence=1758 RMAN-08138: warning: archived log not deleted - must create more backups archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1759.13482.1145040079 thread=1 sequence=1759 RMAN-08138: warning: archived log not deleted - must create more backups archived log file name=+RECO01/xxxxxxxxxx/ARCHIVELOG/2023_08_16/thread_1_seq_1760.10421.1145057009 thread=1 sequence=1760 check CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK; use force delete noprompt force archivelog until time 'sysdate - 7';
Friday, 1 September 2023
RMAN-08138: warning: archived log not deleted - must create more backups
Thursday, 24 August 2023
OPW-00010: Could not create the password file. This resource has a Password File.
OPW-00010: Could not create the password file. This resource has a Password File.
orapwd file='+DATA/orapwibrac' ENTRIES=10 DBUNIQUENAME=ibrac Enter password for SYS: OPW-00010: Could not create the password file. This resource has a Password File. [grid@irac01 ~]$ srvctl config database -d ibrac Database unique name: ibrac Database name: Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1 Oracle user: oracle Spfile: +data/IBRAC/PARAMETERFILE/spfile.4018.1145682969 --- as password file already in this config Password file: Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: DATA Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: oinstall OSOPER group: oinstall Database instances: ibrac1,ibrac2 Configured nodes: ibrac01,ibrac02 CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed [oracle@ibrac01:~] $ modify without password file srvctl modify database -d ibrac -pwfile[oracle@irac01:~] $srvctl config database -d ibrac Database unique name: ibrac Database name: Oracle home: /u01/app/oracle/product/12.2.0/dbhome_1 Oracle user: oracle Spfile: +data/IBRAC/PARAMETERFILE/spfile.4018.1145682969 Password file: <<<<<<< now no password file !!! Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: Disk Groups: DATA Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: oinstall OSOPER group: oinstall Database instances: ibrac1,ibrac2 Configured nodes: ibrac01,ibrac02 CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed run again!!! orapwd file='+DATA/orapwibrac' ENTRIES=10 DBUNIQUENAME=ibrac Enter password for SYS: ASMCMD [+] > ls -l +DATA/IBRAC/PASSWORD/ Type Redund Striped Time Sys Name PASSWORD UNPROT COARSE AUG 24 08:00:00 Y pwdibrac.4019.1145693377 ASMCMD [+] > srvctl modify database -d ibrac -pwfile +DATA/IBRAC/PASSWORD/pwdibrac.4019.1145693377 SQL> alter user sys identified by sys ; <<< change the password again :) User altered. SQL>
Wednesday, 23 August 2023
tfactl – Diagnostic data Collection for troubleshooting the issue of GI, ASM, and RAC
tfactl – Diagnostic data Collection for troubleshooting the issue of GI, ASM, and RAC
locate file locate tfactl /u01/app/19.0.0/grid/bin/tfactl /u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl /u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.bat.tmpl /u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.pl /u01/app/19.0.0/grid/suptools/tfa/release/tfa_home/bin/tfactl.tmpl GI_HOME/tfa/bin/tfactl diagcollect -from "MMM/dd/yyyy hh:mm:ss" -to "MMM/dd/yyyy hh:mm:ss" /u01/app/19.0.0/grid/bin/tfactl diagcollect -from "Aug/23/2023 07:00:00" -to "Aug/23/2023 08:00:00" /u01/app/19.0.0/grid/bin/tfactl diagcollect -from "Aug/23/2023 07:00:00" -to "Aug/23/2023 08:00:00" WARNING - TFA Software is older than 180 days. Please consider upgrading TFA to the latest version. Collecting data for all nodes Scanning files from Aug/23/2023 07:00:00 to Aug/23/2023 08:00:00 Collection Id : 20230823083407ibrac01 Detailed Logging at : /u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all/diagcollect_20230823083407_ibrac01.log 2023/08/23 08:34:12 EDT : NOTE : Any file or directory name containing the string .com will be renamed to replace .com with dotcom 2023/08/23 08:34:12 EDT : Collection Name : tfa_Wed_Aug_23_08_34_08_EDT_2023.zip 2023/08/23 08:34:12 EDT : Collecting diagnostics from hosts : [ibrac02, ibrac01] 2023/08/23 08:34:12 EDT : Scanning of files for Collection in progress... 2023/08/23 08:34:12 EDT : Collecting additional diagnostic information... 2023/08/23 08:36:07 EDT : Getting list of files satisfying time range [08/23/2023 07:00:00 EDT, 08/23/2023 08:00:00 EDT] 2023/08/23 08:37:20 EDT : Completed collection of additional diagnostic information... 2023/08/23 08:39:53 EDT : Collecting ADR incident files... 2023/08/23 08:39:53 EDT : Completed Local Collection 2023/08/23 08:39:53 EDT : Remote Collection in Progress... .-------------------------------------------------------------------. | Collection Summary | +---------+------------------------------------------+-------+------+ | Host | Status | Size | Time | +---------+------------------------------------------+-------+------+ | irac02 | Failed Unable to connect to Node ibrac02 | | | | irac01 | Completed | 191MB | 341s | '---------+------------------------------------------+-------+------' Logs are being collected to: /u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all /u01/app/grid/tfa/repository/collection_Wed_Aug_23_08_34_08_EDT_2023_node_all/irac01.tfa_Wed_Aug_23_08_34_08_EDT_2023.zip
Tuesday, 22 August 2023
ORA-17627: ORA-12543: TNS:destination host unreachable ORA-17629: Cannot connect to the remote database server
[oracle@oracentd19c admin]$ rman target sys/sys@orclprim auxiliary sys/sys@orcldstdy|tee stdy.txt
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 22 06:13:15 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1672814384)
connected to auxiliary database: ORCLS (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 22-08-2023 06:13:21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=36 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/u01/app/oracle/product/19c/db_1/dbs/orapworcls' ;
}
executing Memory Script
Starting backup at 22-08-2023 06:13:22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/22/2023 06:13:23
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/22/2023 06:13:23
ORA-17627: ORA-12543: TNS:destination host unreachable
ORA-17629: Cannot connect to the remote database server
RMAN>
from prim to standby
from prod !!!
[oracle@oracent19c admin]$ tnsping orcldstdy
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2023 06:14:45
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.56)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=orcls)))
TNS-12543: TNS:destination host unreachable
Check the firewall !!!!!
on standby
[root@oracentd19c ~]# firewall-cmd --state
running
[root@oracentd19c ~]# systemctl stop firewalld or add rule for 1521
[root@oracentd19c ~]# firewall-cmd --state
not running
[root@oracentd19c ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@oracentd19c ~]# firewall-cmd --state
not running
from prim now working !!
[oracle@oracent19c ~]$ tnsping orcldstdy
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-AUG-2023 06:18:44
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19c/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.56)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=orcls)))
OK (20 msec)
[oracle@oracent19c ~]$
Sunday, 20 August 2023
RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon denied
RMAN-03015: error occurred in stored script Memory Script RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon deniedRMAN> duplicate target database to 'orcld' from active database nofilenamecheck; Starting Duplicate Db at 20-AUG-23 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=38 device type=DISK contents of Memory Script: { sql clone "create spfile from memory"; } executing Memory Script sql statement: create spfile from memory contents of Memory Script: { shutdown clone immediate; startup clone nomount; } executing Memory Script Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 6710885896 bytes Fixed Size 9149960 bytes Variable Size 1174405120 bytes Database Buffers 5519704064 bytes Redo Buffers 7626752 bytes contents of Memory Script: { sql clone "alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile"; sql clone "alter system set db_unique_name = ''orcld'' comment= ''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone from service 'orcl3' primary controlfile; alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''orcld'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 6710885896 bytes Fixed Size 9149960 bytes Variable Size 1174405120 bytes Database Buffers 5519704064 bytes Redo Buffers 7626752 bytes Starting restore at 20-AUG-23 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=35 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 08/20/2023 19:58:01 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-06136: Oracle error from auxiliary database: ORA-17629: Cannot connect to the remote database server ORA-17627: ORA-01017: invalid username/password; logon denied ORA-17629: Cannot connect to the remote database server**************************************************************[oracle@oracentd19c dbs]$ ps -ef|grep -i smon oracle 19234 1 0 20:21 ? 00:00:00 ora_smon_orcldcp orapworcl orapworcld
Monday, 14 August 2023
Create Pluggable Database(PDB) in Oracle 12c
Create Pluggable Database(PDB) in Oracle 12c..
Create Pluggable Database(PDB) in Oracle 12c
if required!!!
alter system set "_exclude_seed_cdb_view"=FALSE scope=both;
System altered.
select distinct substr(name, 1, instr(name, '/',-1)) PATH
from (
select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER name from v$logfile
union all
select name from v$tempfile
) order by 1;
take seed database file path
set linesize 400
col datafile_name for a90
col container for a20
select hxfil file#, d.name datafile_name , c.name container, fhsta fh_status, fhscn fh_scn, fhrba_seq fh_seq , fhafs fh_abs from x$kcvfh x, v$datafile d, v$containers c
where d.file# = x.hxfil
and d.con_id = c.con_id
and c.name='PDB$SEED'
order by c.name,fhscn;
FILE# DATAFILE_NAME CONTAINER FH_STATUS FH_SCN FH_SEQ FH_ABS
---------- ------------------------------------------------------------------------------------------ -------------------- ---------- -------------------- ---------- --------------------
14 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/system.359.1133670139 PDB$SEED 40960 52755005 2043 0
15 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/sysaux.1322.1133670139 PDB$SEED 32768 52755005 2043 0
20 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/pdb9_undotbs.4153.1133670139 PDB$SEED 32768 52755005 2043 0
17 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/users.1419.1133670139 PDB$SEED 32768 52755005 2043 0
16 +DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/undotbs1.1320.1133670139 PDB$SEED 32768 52755005 2043 0
for temp file alter session set container=PDB$SEED ;
select NAME from V$TEMPFILE;
NAME
------------------------------------------------------------------------------------------
+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127
ww
create pluggable database anujy admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');
Pluggable database created.
create pluggable database anujz admin user vihaan identified by vihaan123
default tablespace anujy datafile '+DATA' size 250m autoextend on
file_name_convert=('+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/DATAFILE/','+DATA','+DATA/VIHCDBD8/ABBFB3354CCC218FE05324F56A40A88A/TEMPFILE/temp.4195.1133671127','+DATA');
Pluggable database created.
Saturday, 12 August 2023
TOP SQL reporting
TOP SQL reporting
TOP SQL reporting
---- mon_topsql.sql
https://github.com/vishaldesai/Oracle_Scripts/blob/master/Exadata/mon_topsql.sql
-- Author: Tanel Poder ( http://blog.tanelpoder.com | tanel@tanelpoder.com )
SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF
COL pct FOR A10 JUST RIGHT
COL cpu_pct FOR 999.9
COL io_pct FOR 999.9
col OWNER for a20
col OBJECT_NAME for a20
col PROCEDURE_NAME for a20
BREAK ON day SKIP 1
DEF days=1
PROMPT Displaying daily top SQL for last &days days...
WITH ash AS (
SELECT
day
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds
, cpu_seconds
, LPAD(TRIM(TO_CHAR(RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100, '999.9'))||'%', 10) pct
, RATIO_TO_REPORT(total_seconds) OVER (PARTITION BY day) * 100 pct_num
FROM (
SELECT
TO_CHAR(sample_time, 'YYYY-MM-DD') day
, sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, SUM(10) total_seconds
, SUM(CASE WHEN wait_class = 'User I/O' THEN 10 ELSE 0 END) io_seconds
, SUM(CASE WHEN wait_class IS NULL THEN 10 ELSE 0 END) cpu_seconds
FROM
dba_hist_active_sess_history a
, dba_procedures p
WHERE
a.plsql_entry_object_id = p.object_id (+)
AND a.plsql_entry_subprogram_id = p.subprogram_id (+)
AND sample_time > SYSDATE - &days
AND session_type != 'BACKGROUND' -- ignore for now
GROUP BY
sql_id
, sql_plan_hash_value
, p.owner
, p.object_name
, p.procedure_name
, TO_CHAR(sample_time, 'YYYY-MM-DD')
)
)
, sqlstat AS (
SELECT /*+ MATERIALIZE */
TO_CHAR(begin_interval_time, 'YYYY-MM-DD') day
, sql_id
, plan_hash_value
, SUM(executions_delta) executions
, SUM(rows_processed_delta) rows_processed
, SUM(disk_reads_delta) blocks_read
, SUM(disk_reads_delta)*8/1024 mb_read
, SUM(buffer_gets_delta) buffer_gets
, SUM(iowait_delta)/1000000 awr_iowait_seconds
, SUM(cpu_time_delta)/1000000 awr_cpu_seconds
, SUM(elapsed_time_delta)/1000000 awr_elapsed_seconds
FROM
dba_hist_snapshot
NATURAL JOIN
dba_hist_sqlstat
WHERE
begin_interval_time > SYSDATE - &days
GROUP BY
TO_CHAR(begin_interval_time, 'YYYY-MM-DD')
, sql_id
, plan_hash_value
)
SELECT /*+ MONITOR */
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value plan_hash
, ROUND(total_seconds / 3600,1) total_hours
, total_seconds
, executions
, ROUND(total_seconds / NULLIF(executions,0),2) seconds_per_exec
, io_pct
, cpu_pct
, mb_read
, ROUND(mb_read / NULLIF(executions,0),2) mb_per_exec
, buffer_gets
, ROUND(buffer_gets / NULLIF(executions,0),2) bufget_per_exec
, CASE WHEN sql_id IS NOT NULL THEN
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('''||sql_id||''','||CASE WHEN sql_plan_hash_value = 0 THEN 'NULL' ELSE TO_CHAR(sql_plan_hash_value) END||', format=>''ADVANCED''));'
END extract_plan_from_awr
FROM (
SELECT
day
, pct
, owner
, object_name
, procedure_name
, sql_id
, sql_plan_hash_value
, total_seconds
, io_seconds/total_seconds*100 io_pct
, cpu_seconds/total_seconds*100 cpu_pct
, (SELECT executions FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) executions
, (SELECT mb_read FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) mb_read
, (SELECT buffer_gets FROM sqlstat s WHERE ash.sql_id = s.sql_id AND ash.sql_plan_hash_value = s.plan_hash_value AND ash.day = s.day) buffer_gets
FROM
ash
WHERE
ash.pct_num >= 1
)
ORDER BY
day DESC
, total_seconds DESC
/
DAY PCT OWNER OBJECT_NAME PROCEDURE_NAME SQL_ID PLAN_HASH TOTAL_HOURS TOTAL_SECONDS EXECUTIONS SECONDS_PER_EXEC IO_PCT CPU_PCT MB_READ MB_PER_EXEC BUFFER_GETS BUFGET_PER_EXEC EXTRACT_PLAN_FROM_AWR
---------- ---------- -------------------- -------------------- -------------------- ------------- ---------- ----------- ------------- ---------- ---------------- ------ ------- ---------- ----------- ----------- --------------- --------------------------------------------------------------------------------------------------------------------------
2023-08-12 78.6% ctyc1af1abg5u 200298931 91 327510 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
4.4% 0 5.1 18340 22.3 72.7
4.2% 3xjw1ncw5vh27 2487977020 4.9 17500 88.3 11.7 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('3xjw1ncw5vh27',2487977020, format=>'ADVANCED'));
4.2% c7ngymcfjtb1c 1658157256 4.8 17450 43.8 56.2 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('c7ngymcfjtb1c',1658157256, format=>'ADVANCED'));
2023-08-11 55.1% ctyc1af1abg5u 200298931 55.9 201130 .0 100.0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
8.7% 1zuyhdtsmmx5s 3008400300 8.8 31680
define sql_id='ctyc1af1abg5u'
SET TERMOUT OFF pagesize 5000 tab off verify off linesize 999 trimspool on trimout on null ""
SET TERMOUT ON
COL exec_per_sec FOR 99999990
COL ela_ms_per_sec FOR 99999990
COL rows_per_sec FOR 99999990
COL lios_per_sec FOR 99999990
COL blkrd_per_sec FOR 99999990
COL cpu_ms_per_sec FOR 99999990
COL iow_ms_per_sec FOR 99999990
COL clw_ms_per_sec FOR 99999990
COL apw_ms_per_sec FOR 99999990
COL ccw_ms_per_sec FOR 99999990
SELECT
CAST(begin_interval_time AS DATE) begin_interval_time
,st.con_id
, sql_id
, plan_hash_value
, ROUND(SUM(executions_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) exec_per_sec
, ROUND(SUM(elapsed_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ela_ms_per_sec
, ROUND(SUM(rows_processed_delta) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) rows_per_sec
, ROUND(SUM(buffer_gets_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) lios_per_sec
, ROUND(SUM(disk_reads_delta ) / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) blkrd_per_sec
, ROUND(SUM(cpu_time_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) cpu_ms_per_sec
, ROUND(SUM(iowait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) iow_ms_per_sec
, ROUND(SUM(clwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) clw_ms_per_sec
, ROUND(SUM(apwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) apw_ms_per_sec
, ROUND(SUM(ccwait_delta ) / 1000 / ((CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400), 1) ccw_ms_per_sec
FROM
dba_hist_snapshot sn
, dba_hist_sqlstat st
WHERE
sn.snap_id = st.snap_id
AND sn.dbid = st.dbid
AND sn.instance_number = st.instance_number
AND sql_id = '&sql_id'
--AND plan_hash_value LIKE '2'
--AND begin_interval_time >= 3
--AND end_interval_time <= 4
-- AND begin_interval_time > sysdate -1
GROUP BY
CAST(begin_interval_time AS DATE)
,st.con_id
, CAST(end_interval_time AS DATE)
, sql_id
, plan_hash_value
ORDER BY
begin_interval_time
, sql_id
, plan_hash_value
/
SET LINESIZE 200 PAGESIZE 200 TRIMSPOOL ON
COLUMN username FORMAT a20
COLUMN total_WaitTime FORMAT 999,999.99 HEADING "WAIT_SEC"
COLUMN comment FORMAT a30
COLUMN inst_id FORMAT 9999
COLUMN sid FORMAT 999999
col kill for a17
col EVENT for a20
SELECT t.*,
CASE
WHEN ROWNUM <= 5 THEN 'Highest wait: ' || t.username || ' with ' || t.total_WaitTime || ' seconds'
ELSE 'Waits by user last 15 minutes'
END AS "comment"
FROM (
SELECT
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
--s.inst_id,
-- s.sid,
s.con_id,
s.username,
SUM(ash.wait_time + ash.time_waited) / 1000000 AS total_WaitTime,
s.sql_id,
s.event
FROM gv$active_session_history ash
JOIN gv$session s ON ash.session_id = s.sid AND ash.inst_id = s.inst_id
WHERE 1=1
-- ash.sample_time > (SYSDATE - 1/96) -- Last 15 minutes
and ash.sample_time > SYSDATE - INTERVAL '15' MINUTE
AND s.username IS NOT NULL
GROUP BY ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''',s.con_id, s.username,s.sql_id,s.event
ORDER BY 4 DESC
) t
WHERE ROWNUM < 20;
from https://www.pythian.com/blog/recent-spike-report-from-vactive_session_history-ash
AVG — the average “load” (active sessions) over a 5-minute interval. This should help you spot a problem when you scroll through the results.
TOT — total “load” (active sessions) for that sample time. RAC users: each RAC node will have its own sample time, within 1 second of each other, but not exactly spot-on. So, even if you have sessions waiting on the same event, they will not be grouped together. I kind of like it this way, for now.
SAMPLE_TIME — self-explanatory
CNT — the number of active sessions waiting on the same event and query
EVENT — the event been waited on
SQL_TEXT — self-explanatory, except when empty which means either not found in shared pool or not available in ASH
SQL_ID — if you need to find the SQL
CHD — the child number being executed
COLUMN event FORMAT a25 TRUNCATED
COLUMN "SUBSTR(SQ.SQL_TEXT,1)" FORMAT a40 HEADING "SQL_TEXT" TRUNCATED
col SAMPLE_TIME for a25
-- Metric Columns
COLUMN "sec p" FORMAT a15
COLUMN "disk p" FORMAT 999,999
COLUMN "gets p" FORMAT 9,999,999
COLUMN "rows p" FORMAT 999,999
COLUMN "cpu p" FORMAT 99.999
COLUMN exec FORMAT 999,999,999
COLUMN open FORMAT 999
COLUMN e FORMAT 999
col SQL_TEXT for a50
set linesize 300 pagesize 300
SELECT
ROUND(AVG(MAX(cnt_tot)) OVER (ORDER BY sample_time RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW)) AS avg,
MAX(cnt_tot) AS tot,
sample_time,
MAX(cnt) AS cnt,
event,
SUBSTR(sq.sql_text, 1, 100) as sql_text, -- Truncated for display
ash.sql_id,
ash.sql_child_number AS chd,
TO_CHAR(ROUND(SUM(elapsed_time) / NULLIF(SUM(executions), 0) / 1000000, 6), '9,990.999999') AS "sec p",
ROUND(SUM(disk_reads) / NULLIF(SUM(executions), 0), 0) AS "disk p",
ROUND(SUM(buffer_gets) / NULLIF(SUM(executions), 0), 0) AS "gets p",
ROUND(SUM(rows_processed) / NULLIF(SUM(executions), 0), 0) AS "rows p",
ROUND(SUM(cpu_time) / 1000000 / NULLIF(SUM(executions), 0), 3) AS "cpu p",
SUM(executions) AS exec,
SUM(users_opening) AS open,
SUM(users_executing) AS e
FROM (
SELECT SUM(COUNT(*)) OVER (PARTITION BY sample_time) AS cnt_tot,
COUNT(*) AS cnt,
sample_time,
event,
sql_id,
sql_child_number
FROM gv$active_session_history
WHERE sample_time > SYSDATE - INTERVAL '1' HOUR
GROUP BY event, sql_id, sql_child_number, sample_time
HAVING COUNT(*) >= 10
) ash
LEFT JOIN gv$sql sq ON ash.sql_id = sq.sql_id
AND ash.sql_child_number = sq.child_number
GROUP BY event, sql_text, ash.sql_id, ash.sql_child_number, ash.sample_time
ORDER BY sample_time DESC;
connnect to pdbs
col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('ctyc1af1abg5u',200298931, format=>'ADVANCED'));
Subscribe to:
Posts (Atom)
