AWR, Snapshot and Baseline for Report

ใน Database จะใช้ AWR (Automatic Workload repository) สำหรับรวบรวมการประมวลผลต่างๆและเก็บสถิติประสิทธิภาพการใช้งาน  แล้วจึงนำมาแสดงสถานะและประสิทธิภาพของ Database ในรูปแบบของ report เพื่อนำไปการตรวจปัญหาต่างๆและ tuning Database ด้วยตนเอง

AWR เก็บรวบรวมข้อมูลดังนี้
- Object Statistics (สถิติการ access และ usage ของ Database segments)
- Time Model Statistics (V$SYS_TIME_MODEL และ V$SESS_TIME_MODEL)
- Some of the System and Session Statistics (V$SYSSTAT และ V$SESSTAT)
- ASH (Active Session History) Statistics
- High load generating SQL Statements

Components ต่างๆของ AWR
- Automatic Database Diagnostic Monitor
- Undo Advisor
- SQL Tuning Advisor
- Segment Advisor

Snapshots

- การ snapshot ในแต่ละอัน คือ เริ่ม snapshot (BEGIN_INTERVAL_TIME) และทำการเก็บค่าต่างๆ และจบการ snapshot (END_INTERVAL_TIME)

                     SNAPSHOT1             SNAPSHOT2                 SNAPSHOT3

เริ่มSnapshot SNAPSHOT_INTERVAL จบSnapshot --> เริ่มSnapshot SNAPSHOT_INTERVAL จบSnapshot --> เริ่มSnapshot SNAPSHOT_INTERVAL จบSnapshot

- ถ้าช่วงระยะห่างของ start ถึง end time ของการ snapshot มีค่าน้อยจะเก็บรายละเอียดได้มากกว่ามีค่ามาก เพราะค่าน้อยจะเกิดการ snapshot บ่อยกว่า จึงมีข้อมูลเก็บไว้หลากหลายกว่า ทำให้วิเคราะห์ได้ละเอียดกว่า แต่การ snapshot จำนวนมาก ก็จะต้องการพื้นที่และกระทบกับการทำงานของระบบมากกว่า

 การพิจารณาว่าจะกำหนดช่วงให้ห่างกันเท่าไร ให้ดูจากถ้าช่วงไหนมีปัญหาบ่อย ก็ให้กำหนดช่วงสั้นๆ เพื่อให้หาสาเหตุได้ง่ายขึ้น แต่พอแก้ไขปัญหาแล้วระบบทำงานปกติ ก็ให้กำหนดช่วงยาวๆแทน

   เช่น

    ตัวอย่าง

จำนวน session ในแต่ละช่วงเวลา

2020/01/01 00:00 = 1

2020/01/01 01:00 = 10

2020/01/01 02:00 = 20

...

....

.....

2020/01/01 22:00 = 220

2020/01/01 23:00 = 230

2020/01/02 00:00 = 240

 

กรณี snap ทุกๆ 1 ชม

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME  

---------- ------------------- -------------------

24  2020/01/01 23:00:00 2020/01/02 00:00:00

23  2020/01/01 22:00:00 2020/01/01 23:00:00

22  2020/01/01 21:00:00 2020/01/01 22:00:00

....                                       

......                                     

........ 

5   2020/01/01 02:00:00 2020/01/01 05:00:00

4   2020/01/01 02:00:00 2020/01/01 04:00:00

3   2020/01/01 02:00:00 2020/01/01 03:00:00

2   2020/01/01 01:00:00 2020/01/01 02:00:00

1   2020/01/01 00:00:00 2020/01/01 01:00:00

กรณี snap ทุกๆ 6 ชม

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME  

---------- ------------------- -------------------

4   2020/01/01 18:00:00 2020/01/02 00:00:00

3   2020/01/01 12:00:00 2020/01/01 18:00:00

2   2020/01/01 06:00:00 2020/01/01 12:00:00

1   2020/01/01 00:00:00 2020/01/01 06:00:00

ตัวอย่าง 1 ต้องการหาการเปลี่ยนแปลงจำนวน sessions ในช่วงเวลา 2020/01/01 01:00:00 - 2020/01/01 05:00:00 กรณี snap ทุกๆ 1 ชม SNAP_ID 1 มีจำนวน session Begin ถึง End = 1 ถึง 10 SNAP_ID 2 มีจำนวน session Begin ถึง End = 10 ถึง 20 SNAP_ID 3 มีจำนวน session Begin ถึง End = 20 ถึง 30 SNAP_ID 4 มีจำนวน session Begin ถึง End = 30 ถึง 40 SNAP_ID 5 มีจำนวน session Begin ถึง End = 40 ถึง 50 กรณี snap ทุกๆ 6 ชม SNAP_ID 1 มีจำนวน session Begin ถึง End = 1 ถึง 60 SNAP_ID 2 มีจำนวน session Begin ถึง End = 60 ถึง 120 จะเห็นว่า snap ทุกๆ 1 ชม แสดง report (จะใช้ค่า End) = 10 ถึง 50 ได้ค่าที่ตรงกับความเป็นจริงมากกว่า snap ทุกๆ 6 ชม แสดง report (จะใช้ค่า End) = 60 ถึง 120 ตัวอย่าง 2 ต้องการหาการเปลี่ยนแปลงจำนวน sessions ในช่วงเวลา 2020/01/01 02:00:00 - 2020/01/01 03:00:00 กรณี snap ทุกๆ 1 ชม SNAP_ID 2 มีจำนวน session Begin ถึง End = 10 ถึง 20 SNAP_ID 3 มีจำนวน session Begin ถึง End = 20 ถึง 30 กรณี snap ทุกๆ 6 ชม มี SNAP_ID = 1 SNAP_ID 1 มีจำนวน session Begin ถึง End = 1 ถึง 60 SNAP_ID 2 มีจำนวน session Begin ถึง End = 60 ถึง 120 จะเห็นว่า snap ทุกๆ 1 ชม แสดง report (จะใช้ค่า End) = 20 ถึง 30 ได้ค่าที่ตรงกับความเป็นจริงมากกว่า snap ทุกๆ 6 ชม แสดง report (จะใช้ค่า End) = 60 ถึง 120

- การเกิดใหม่ของ Snapshot หรือ SNAP_ID ตัวใหม่ ใน DBA_HIST_SNAPSHOT เมื่อ snapshot เริ่มต้นทำงาน คือ เกิด BEGIN_INTERVAL_TIME โดยจะยังไม่มีข้อมูลใน DBA_HIST_SNAPSHOT จนกว่าจะจบการทำงาน คือ เกิด END_INTERVAL_TIME หรือเรียกว่า จบการ snapshot

- ค่า default คือ ทำการ snapshot ทุกๆ 1 ชม (INTERVAL)  และเก็บรักษา snapshot เอาไว้ 8 วัน (RETENTION)

- สำหรับการออก report

  การกำหนดช่วงของ snapshot จะแสดง report นับจาก snap ID เริ่มต้น ถึง snap ID สิ้นสุด โดยใช้ END_INTERVAL_TIME แสดงว่าถ้าต้องการกำหนด snapshot ในช่วงเวลาใดๆต้องดูจากระยะ END_INTERVAL_TIME

  เช่น

 DBA_HIST_SNAPSHOT

    SNAP_ID       DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME  

 ---------- ---------- --------------- ------------------- -------------------

        86 1256122414               1 2020/09/10 14:00:54 2020/09/10 15:00:28

        85 1256122414               1 2020/09/10 13:00:25 2020/09/10 14:00:54

        84 1256122414               1 2020/09/10 12:00:51 2020/09/10 13:00:25

        83 1256122414               1 2020/09/10 10:35:05 2020/09/10 12:00:51

        82 1256122414               1 2020/09/09 18:00:04 2020/09/10 10:35:05

        81 1256122414               1 2020/09/09 17:00:32 2020/09/09 18:00:04

        80 1256122414               1 2020/09/09 16:27:05 2020/09/09 17:00:32

  ต้องการ report ช่วงเวลา 2020/09/09 17:00 ถึง 2020/09/10 15:00 จะต้องเลือก start snapshot = 80 และ end snapshot = 86

  โดย report จะแสดง Begin Snap = 17:00:32 และ End Snap = 15:00:28

RETENTION หรือ RETENTION_INTERVAL (ระยะการเก็บรักษา)

- คือ ช่วงเวลาหน่วยนาทีในการเก็บรักษา history เอาไว้

- ระบุค่าต่างๆ

ค่าเป็นตัวเลข ค่าที่ระบุต้องอยู่ในช่วง MIN_RETENTION (1 วัน) ถึง MAX_RETENTION (100 ปี)

ค่า 0 คือ Snapshot จะเก็บรักษาไว้ตลอดไป ค่าที่ระบบกำหนดส่วนมากจะใช้เป็นการตั้งค่าการเก็บข้อมูล

ค่า NULL คือ Snapshot ค่าเดิมจะถูกรักษาไว้

INTERVAL หรือ  SNAPSHOT_INTERVAL (ระยะ snapshot)

- คือ ช่วงระยะห่างของเวลาเริ่มต้นและสิ้นสุดของแต่ล่ะ Snapshot หน่วยนาที หรือเรียกว่า ในแต่ละ snapshot จะเริ่มเก็บจนหยุดเก็บใช้เวลาเท่าไร

- ระบุค่าต่างๆ

ค่าเป็นตัวเลข ค่าที่ระบุต้องอยู่ในช่วง MIN_INTERVAL (10 นาที) ถึง MAX_INTERVAL (1 ปี)

ค่า 0 คือ ทำการ Snapshot แบบ auto โดยการทำงาน Snapshot ในแบบ manual จะถูกปิดใช้งานการรวบรวมสถิติทั้งหมด ค่าที่ระบบกำหนดส่วนมากจะใช้เป็นการตั้งค่าการเก็บข้อมูล

ค่า NULL คือ Snapshot ค่าปัจจุบันจะถูกรักษาไว้

TOPNSQL

- ระบุค่าเป็นตัวเลข 

- คือ จำนวนสูงสุดของ SQL ที่จะ flush ข้อมูลของแต่ละ  criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, และ Version Count)

- จะไม่กระทบกับ statistics และ flush level แต่จะแทนที่ค่า system default ของ AWR SQL collection

- การตั้งค่าจะมีดังนี้

ค่าเป็นตัวเลข กำหนดได้ต่ำสุด 30 และสูงสุด 50000

NULL จะคงการตั้งค่าปัจจุบันไว้

- ระบุค่าเป็น varchar(2)

- การตั้งค่าจะมีดังนี้

MAXIMUM ทำการ capture SQL ใน cursor cache แบบสมบูรณ์

ค่าเป็นตัวเลข กำหนดได้ต่ำสุด 30 และสูงสุด 50000

DEFAULT กำหนดค่าแบบ default คือ top 30 สำหรับ statistics level แบบ TYPICAL และ top 100 สำหรับ statistics level แบบ ALL

NULL จะคงการตั้งค่าปัจจุบันไว้nt will keep the current setting.

- Criteria 14 อัน ใน AWR report และ Oracle AWR จะ capture ใน top-n-SQL ของแต่ละ criteria จาก http://www.dba-oracle.com/t_awr_automatic_snapshot_settings_modify.htm
1.Elapsed Time (ms)
2.CPU Time (ms)
3.Executions
4.Buffer Gets
5.Disk Reads
6.Parse Calls
7.Rows
8.User I/O Wait Time (ms)
9.Cluster Wait Time (ms)
10.Application Wait Time (ms)
11.Concurrency Wait Time (ms)
12.Invalidations
13.Version Count
14.Sharable Mem(KB)

แสดงเวลาของการ snapshot
- BEGIN_INTERVAL_TIME คือ เวลาที่เริ่ม snapshot
- END_INTERVAL_TIME คือ เวลาที่จบ snapshot
- BEGIN_INTERVAL_TIME ถึง END_INTERVAL_TIME ระยะเวลาจะห่างกันตามค่า SNAPSHOT_INTERVAL

คำสั่ง modify snapshot settings
dbms_workload_repository.modify_snapshot_settings(
   retention   in  number    default null,
   interval    in  number    default null,
   topnsql     in  number    default null,
   dbid        in  number    default null);

คำสั่ง taken snapshot
exec dbms_workload_repository.create_snapshot;

คำสั่ง removed snapshot
begin
  dbms_workload_repository.drop_snapshot_range (
    low_snap_id  => 22,
    high_snap_id => 32);
end;
/ 

แสดงค่าจำนวน วัน ชม นาที วินาที ของ SNAPSHOT_INTERVAL และ RETENTION_INTERVAL

ตัวอย่าง ทำการ snapshot ทุกๆ 1 ชม และเก็บรักษา snapshot เอาไว้ 8 วัน

รูปแบบของ SNAP_INTERVAL และ RETENTION คือ +วัน ชม:นาที:วินาที
SQL> select dbid,snap_interval,retention,topnsql from dba_hist_wr_control;
      DBID SNAP_INTERVAL         RETENTION            TOPNSQL 
---------- --------------------- -------------------- ----------
1550490185 +00 01:00:00.000000   +08 00:00:00.000000  DEFAULT 
หรือ
SNAPSHOT_INTERVAL_SEC และ RETENTION_INTERVAL_SEC หน่วยคือ seconds
set linesize 1000
SQL> select
(' Total='||trunc(wrcon.snapshot_interval_sec/60/60/24) || 'Day '
||to_char(trunc(MOD(wrcon.snapshot_interval_sec/60/60,24)),'fm9900')||':'||to_char(trunc(MOD(wrcon.snapshot_interval_sec,3600)/60),'fm00')||':'||to_char(MOD(MOD(wrcon.snapshot_interval_sec,3600),60),'fm00')
)snapshot_interval
,(' Total='||trunc(wrcon.retention_interval_sec/60/60/24) || 'Day '
||to_char(trunc(MOD(wrcon.retention_interval_sec/60/60,24)),'fm9900')||':'||to_char(trunc(MOD(wrcon.retention_interval_sec,3600)/60),'fm00')||':'||to_char(MOD(MOD(wrcon.retention_interval_sec,3600),60),'fm00')
)retention_interval
from
(select (extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval))*60 snapshot_interval_sec
,(extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention))*60 retention_interval_sec
from dba_hist_wr_control)wrcon;
SNAPSHOT_INTERVAL         RETENTION_INTERVAL                                           
------------------------- ----------------------
Total=0Day 01:00:00       Total=8Day 00:00:00

แสดงค่าจำนวน นาที ของ SNAPSHOT_INTERVAL และ RETENTION_INTERVAL (เป็นค่าที่ set ใน parameter)
SQL> select extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval
from dba_hist_wr_control;
SNAPSHOT_INTERVAL RETENTION_INTERVAL
----------------- ------------------
               60              11520

แสดง detail snapshot ทั้งหมดที่มีอยู่
SQL> select sn.snap_id,sn.dbid,sn.instance_number
--,sn.startup_time
,to_char(sn.begin_interval_time,'yyyy/mm/dd hh24:mi:ss')begin_interval_time
,to_char(sn.end_interval_time,'yyyy/mm/dd hh24:mi:ss')end_interval_time
from dba_hist_snapshot sn
where to_char(sn.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') >= '2018/07/05 00:00:00' and to_char(sn.end_interval_time,'yyyy/mm/dd hh24:mi:ss') <= '2018/07/05 06:00:00'
order by sn.begin_interval_time desc,sn.end_interval_time desc
;
   SNAP_ID       DBID INSTANCE_NUMBER STARTUP_TIME                BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ---------- --------------- --------------------------- ------------------- -------------------
      6551 1550490185               1 6/7/2018 5:25:17.000 PM     2018/07/05 04:00:08 2018/07/05 05:00:39
      6550 1550490185               1 6/7/2018 5:25:17.000 PM     2018/07/05 03:00:44 2018/07/05 04:00:08
      6549 1550490185               1 6/7/2018 5:25:17.000 PM     2018/07/05 02:00:07 2018/07/05 03:00:44
      6548 1550490185               1 6/7/2018 5:25:17.000 PM     2018/07/05 01:00:45 2018/07/05 02:00:07
      6547 1550490185               1 6/7/2018 5:25:17.000 PM     2018/07/05 00:00:38 2018/07/05 01:00:45

แสดง summary snapshot ทั้งหมดที่มีอยู่
SQL> select min(sn.snap_id)min_snap_id,max(sn.snap_id)max_snap_id
,min(to_char(sn.begin_interval_time,'yyyy/mm/dd hh24:mi:ss'))min_begin_interval_time
--,max(to_char(sn.begin_interval_time,'yyyy/mm/dd hh24:mi:ss'))max_begin_interval_time
--,min(to_char(sn.end_interval_time,'yyyy/mm/dd hh24:mi:ss'))min_end_interval_time
,max(to_char(sn.end_interval_time,'yyyy/mm/dd hh24:mi:ss'))max_end_interval_time
,count(sn.snap_id)count_snap
from dba_hist_snapshot sn
where to_char(sn.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') >= '2018/07/05 00:30:00' and to_char(sn.end_interval_time,'yyyy/mm/dd hh24:mi:ss') <= '2018/07/05 03:30:00'
;
MIN_SNAP_ID MAX_SNAP_ID MIN_BEGIN_INTERVAL_TIME MAX_END_INTERVAL_TIME COUNT_SNAP
----------- ----------- ----------------------- --------------------- ----------
      42606       42610 2018/07/05 00:30:20     2018/07/05 03:00:27           10

Snapshots settings

Set STATISTICS_LEVEL เป็นการกำหนดจำนวนของ SQL statements ที่จะทำการ captured ดูเพิ่มเติมจาก Document "ORACLE_TUNING_Statistics Level.txt"
SQL> alter system set statistics_level=typical;
หรือ
SQL> alter system set statistics_level=all;

Modify snapshot settings
ตัวอย่าง snapshot ทุกๆ 10 นาที และเก็บ history เป็นเวลา 3 ปี
SQL> execute dbms_workload_repository.modify_snapshot_settings (interval => 10,retention => 1576800);
หรือ กำหนดค่าเหมือนค่า default คือ snapshot ทุกๆชม. และเก็บ history เป็นเวลา 7 วัน
SQL> execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080);

Baselines
- เป็นการสร้าง snapshot ในช่วงเวลาใดๆก็ได้ที่ต้องการใช้เป็นตัวนำไปเปรียบเทียบกับตัวอื่นๆ
   เช่น ช่วงเวลา 08.00 น. ระบบจะทำงานช้ามาก จึงนำช่วงนี้มาสร้างเป็น baseline ชื่อ SYSTEM_CRITICAL เพื่อนำไปเทียบกับ snapshot อื่นๆที่ทำงานช้า ว่ามี metrics แตกต่างจาก SYSTEM_CRITICAL อย่างไร
         ช่วงเวลา 12.00 น. ระบบจะทำงานเป็นปกติ จึงนำช่วงนี้มาสร้างเป็น baseline ชื่อ SYSTEM_NORMAL เพื่อนำไปเทียบกับ snapshot อื่นๆที่ทำงานช้า ว่ามี metrics แตกต่างจาก SYSTEM_NORMAL อย่างไร

แสดง Baseline
set linesize 1000
SQL> select bl.dbid,bl.baseline_id,bl.baseline_name,bl.baseline_type
,bl.start_snap_id,bl.end_snap_id
,bl.start_snap_time,bl.end_snap_time
,bl.moving_window_size
from dba_hist_baseline bl;
      DBID BASELINE_ID BASELINE_NAME                 BASELINE_TYPE START_SNAP_ID END_SNAP_ID START_SNAP_TIME                    END_SNAP_TIME                    MOVING_WINDOW_SIZE
---------- ----------- ----------------------------- ------------- ------------- ----------- ---------------------------------- -------------------------------- ------------------
1550490185           1 SYSTEM_NORMAL                 STATIC                 6000        6010 6/12/2018 6:00:21.094 AM           6/12/2018 4:00:49.560 PM                         
1550490185           2 SYSTEM_CRITICAL               STATIC                 5993        5999 6/11/2018 11:00:59.064 PM          6/12/2018 5:00:20.522 AM                         
1550490185           0 SYSTEM_MOVING_WINDOW          MOVING_WINDOW          6006        6197 6/12/2018 12:00:52.653 PM          6/20/2018 11:00:10.618 AM                         8

Create baseline
SQL> begin
  dbms_workload_repository.create_baseline (
    start_snap_id => 210,
    end_snap_id   => 220,
    baseline_name => 'mybaseline_batch_workingday');
end;
/

Delete baseline
SQL> begin
  dbms_workload_repository.drop_baseline (
    baseline_name => 'mybaseline_batch_workingday',
    cascade       => FALSE); -- Deletes associated snapshots if TRUE.
end;
/

การสร้าง AWR Report ด้วย AWR Scripts

AWR Report
--> sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 12:06:52 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt.  Default value is 'html'.

'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report

Enter value for report_type: html
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual

Type Specified:  กำหนดประเภทรายงาน --> html

old   1: select '&&report_type' report_type_def from dual
new   1: select 'html' report_type_def from dual

old   1: select '&&view_loc' view_loc_def from dual
new   1: select 'AWR_PDB' view_loc_def from dual

Current Instance
~~~~~~~~~~~~~~~~
DB Id        DB Name       Inst Num      Instance     Container Name
-------------- -------------- -------------- -------------- --------------
 1186862925 DBTEST1      1 dbtest1      dbtest1

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num DB Name      Instance   Host
------------ ---------- ---------    ----------   ------
* 1186862925  1 DBTEST1      dbtest1   oradb12c.loc

Using 1186862925 for database Id
Using        1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: กำหนดจำนวนวัน --> 5

Listing the last 5 days of Completed Snapshots
Instance     DB Name   Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
dbtest1      DBTEST1 429  21 Jan 2019 21:04   1

430  21 Jan 2019 21:15   1

431  21 Jan 2019 21:30   1

432  21 Jan 2019 21:45   1

....

......

...........

452  22 Jan 2019 02:45   1

453  22 Jan 2019 03:00   1

454  22 Jan 2019 03:15   1

455  22 Jan 2019 03:30   1

....

......

...........

489  22 Jan 2019 12:00   1

490  22 Jan 2019 12:15   1

491  22 Jan 2019 12:30   1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: กำหนดSnapIDเริ่มต้น --> 429
Begin Snapshot Id specified: 429

Enter value for end_snap: กำหนดSnapIDสิ้นสุด --> 489
End   Snapshot Id specified: 489

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_429_489.html. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: กำหนดReportName --> awr_20190121to20190122

Using the report name awr_20190121to20190122
.......
...........
...............

End of Report

Report written to awr_20190121to20190122

จะได้ AWR Report file
--> ls -lrt
total 884
-rw-r--r--. 1 oracle oinstall 902197 Jan 22 12:08 awr_20190121to20190122.lst

Transfer awr_20190121to20190122.lst ไปที่ Local PC และแก้ไข file จาก .lst เป็น .html
และ open ด้วย Browser Tool จะสามารถดู AWR ได้

Compare Two AWR Reports

--> sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 12:40:29 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual

Type Specified: กำหนดประเภทรายงาน --> html

old   1: select '&&report_type' report_type_def from dual
new   1: select 'html' report_type_def from dual

old   1: select '&&view_loc' view_loc_def from dual
new   1: select 'AWR_PDB' view_loc_def from dual

Current Instance
~~~~~~~~~~~~~~~~
old   1: select (case when '&view_loc' = 'AWR_PDB'
new   1: select (case when 'AWR_PDB' = 'AWR_PDB'

old   1: select &default_dbid   dbid
new   1: select 1186862925     dbid
old   2:      , &default_dbid   dbid2
new   2:      , 1186862925     dbid2

   DB Id       DB Id DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 1186862925  1186862925 DBTEST1      1      1 dbtest1

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num DB Name      Instance   Host
------------ ---------- ---------    ----------   ------
* 1186862925  1 DBTEST1      dbtest1   oradb12c.loc

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1186862925 for Database Id for the first pair of snapshots
Using        1 for Instance Number for the first pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days: กำหนดจำนวนวันของFirst --> 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name   Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
dbtest1      DBTEST1 429  21 Jan 2019 21:04   1

          430  21 Jan 2019 21:15   1

431  21 Jan 2019 21:30   1

432  21 Jan 2019 21:45   1

....

......

...........

452  22 Jan 2019 02:45   1

453  22 Jan 2019 03:00   1

454  22 Jan 2019 03:15   1

455  22 Jan 2019 03:30   1

....

......

...........

489  22 Jan 2019 12:00   1

490  22 Jan 2019 12:15   1

491  22 Jan 2019 12:30   1

Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: กำหนดSnapIDเริ่มต้นของFirst --> 429
First Begin Snapshot Id specified: 429

Enter value for end_snap: กำหนดSnapIDสิ้นสุดของFirst --> 440
First End   Snapshot Id specified: 440

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num DB Name      Instance   Host
------------ ---------- ---------    ----------   ------
* 1186862925  1 DBTEST1      dbtest1   oradb12c.loc

Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 1186862925 for Database Id for the second pair of snapshots
Using        1 for Instance Number for the second pair of snapshots

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing without
specifying a number lists all completed snapshots.

Enter value for num_days2: กำหนดจำนวนวันของSecond --> 2

Listing the last 2 days of Completed Snapshots

                                429  21 Jan 2019 21:04   1

430  21 Jan 2019 21:15   1

431  21 Jan 2019 21:30   1

432  21 Jan 2019 21:45   1

....

......

...........

452  22 Jan 2019 02:45   1

453  22 Jan 2019 03:00   1

454  22 Jan 2019 03:15   1

455  22 Jan 2019 03:30   1

....

......

...........

489  22 Jan 2019 12:00   1

490  22 Jan 2019 12:15   1

491  22 Jan 2019 12:30   1

Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: กำหนดSnapIDเริ่มต้นของSecond -->  441
Second Begin Snapshot Id specified: 441

Enter value for end_snap2: กำหนดSnapIDสิ้นสุดของSecond --> 491
Second End   Snapshot Id specified: 491

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrdiff_1_429_1_441.html  To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name: กำหนดReportName --> awr_20190121compare20190122

Using the report name awr_20190121compare20190122
.......
.............
.................


Report written to awr_20190121compare20190122

จะได้ AWR Report file
--> ls -lrt
total 1192
-rw-r--r--. 1 oracle oinstall 1217225 Jan 22 12:42 awr_20190121compare20190122.lst

Transfer awr_20190121compare20190122.lst to Local PC และแก้ไข file จาก .lst เป็น .html
และ open ด้วย Browser Tool จะสามารถดู AWR ได้

การสร้าง AWR Report ด้วย SQL Developer

เปิดใช้งานเครื่องมือสำหรับ DBA
Menu View > DBA

จะเห็นหน้าจอ DBA และไปที่ Menu Performance > AWR
จะเห็นรูปแบบรายงานดังนี้
AWR Report Viewer สำหรับออกรายงาน AWR อันเดียว
Difference Report Viewer สำหรับออกรายงาน AWR สองอัน แล้วนำมาเปรียบเทียบกัน
SQL Report Viewer สำหรับออกรายงาน SQL อันเดียว

การกำหนดช่วงของ snapshot จะแสดง report นับจาก snap ID เริ่มต้น ถึง snap ID สิ้นสุด โดยใช้ END_INTERVAL_TIME

Solve connection time taking long with Inbound Connect Timeout of SQLNET

การแก้ปัญหาเกี่ยวกับการเชื่อมต่อ Database ของ Client ที่ใช้ระยะเวลานาน โดยที่ยังไม่สามารถ authenticate connection ได้

เกี่ยวกับ SQLNET.INBOUND_CONNECT_TIMEOUT

- หน่วยวินาที default = 60

- สำหรับเมื่อ client มีการขอเชื่อมต่อกับเครือข่ายหรือเรียกว่ามีการสร้าง connection request แล้ว แต่ยังไม่สามารถ authenticate connection หรือ

 ไคลเอนต์ไม่สามารถสร้างการเชื่อมต่อ establish a connection และรับรองความถูกต้อง complete authentication ให้เสร็จสิ้นภายในเวลาที่กำหนด นั่นคือ connection ยังไม่ complete

   จนหมดเวลาที่กำหนดใน SQLNET.INBOUND_CONNECT_TIMEOUT แล้ว  database server จะตัดการเชื่อมต่อ เรียกว่า connection time taking long

- ใช้ป้องกัน listener และ Database Server จากการโจมตีของ Denial-of-Service attack ที่ส่ง connection มาจำนวนมากแต่ไม่ถูกใช้หรือไม่มีการปิดจาก client

  โดยตรวจสอบจาก sqlnet.log ว่ามาจาก IP ที่แปลกปลอมหรือไม่

- แสดง error ที่ sqlnet.log file คือ ORA-12170: TNS:Connect timeout occurred

- แสดง error ที่ Client คือ ORA-12547: TNS:lost contact หรือ ORA-12637: Packet gets failed

กำหนดค่า SQLNET.INBOUND_CONNECT_TIMEOUT

--> lsnrctl
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CSPDDB001)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 60
The command completed successfully

LSNRCTL> set inbound_connect_timeout 120
LSNRCTL> show inbound_connect_timeout
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CSPDDB001)(PORT=1521)))
LISTENER parameter "inbound_connect_timeout" set to 120
The command completed successfully

ตรวจสอบ SQLNET.INBOUND_CONNECT_TIMEOUT ด้วยการเชื่อมต่อเครื่อง Database ผ่าน port 1521 เพื่อให้เกิดการสร้าง connection request

ที่ Local ดู log
--> tail -f /oracle/diag/tnslsnr/oradb12c/listener1/alert/log.xml

ที่ Client ทดสอบสร้าง connection request ด้วย telnet และปล่อยทิ้งไว้
--> telnet 192.168.1.128 1521

ที่ Local ดู process ผ่าน netstat จะพบว่า listener จับกับ server process ของ client นั้นๆ เพราะ listener ตัวนี้กำหนดให้จับ client process ที่ติดต่อผ่าน port 1521
จะได้ PID = 26181
--> netstat -anp | grep 192.168.1.1
...
....
.....     
tcp        0      0 ::ffff:192.168.1.128:1521   ::ffff:192.168.1.1:9494     ESTABLISHED 26181/tnslsnr

ที่ Local ดู process จะพบ listener ชื่อ listener1
--> ps -ef|grep 26181
oracle    26181      1  0 Nov25 ?        00:00:38 /oracle/product/12.2.0/dbhome_1/bin/tnslsnr listener1 -inherit

ที่ Local ดู log จะพบ error นี้เมื่อเกินเวลาที่กำหนด
TNS-12525: TNS:listener has not received client's request in time allowed
หรือ TNS-12535: TNS:operation timed out
--> tail -f /oracle/diag/tnslsnr/oradb12c/listener1/alert/log.xml
...
....
.....
 type='UNKNOWN' level='16' host_id='oradb12c.localdomain'
 host_addr='192.168.1.128' pid='26181'>
 TNS-12525: TNS:listener has not received client's request in time allowed
 TNS-12535: TNS:operation timed out
  TNS-12606: TNS: Application timeout occurred

Dead Connection Detection (CDC) with Expire Time of SQLNET

เมื่อ client เชื่อมต่อกับ Database แล้ว แต่เกิดปัญหาบางอย่างทำให้ client และ server processes ถูก terminate connection เป็นการเชื่อมต่อในระดับ OS
กระบวนการนี้  process ยังคง running บน server และ session ใน Database อาจจะยังไม่ถูก terminate

Dead Connection Detection

จาก http://www.nazmulhuda.info/dead-connection-detection-resource-limits-v-session-v-process-and-os-processes

- เมื่อ client เชื่อมต่อกับ Database แล้ว แต่เกิดปัญหาบางอย่างทำให้ client และ server processes ถูก terminate connection เป็นการเชื่อมต่อในระดับ OS

- กระบวนการนี้  process ยังคง running บน server และ session ใน Database อาจจะยังไม่ถูก terminate

  โดย session จะมี status คือ INACTIVE ใน V$SESSION

  และ process จะเรียกว่า Shadow Process คือ process ที่ไม่ได้ใช้งาน

- ขั้นตอนการทำงาน

1.หากไคลเอนต์ไม่มี respond กลับไปยัง DCD probe packet

2.กระบวนการฝั่งเซิร์ฟเวอร์ถูกทำเครื่องหมายว่าเป็นการเชื่อมต่อที่ไม่ทำงาน (marked as a dead connection)

    3.PMON ทำการ clean up Database processes และ resources

4.client OS processes ถูก terminate

- ตัวอย่างเหตุการณ์ 

- ขณะที่เครื่องของ user เชื่อมต่อ Database โดยเชื่อมต่อเฉยๆ หรือ run query จบแล้ว แต่ยังไม่จบ transaction 

- เกิดเหตุการณ์  CDC

- ถ้า user มีการ reboot หรือปิดเครื่อง โดยไม่ log off หรือ disconnect จาก Database

- ถ้ามีปัญหาจาก network ระหว่าง client กับ server เช่น การ disable ที่ Network Adapter อันที่กำลังใช้ connect เครื่อง Database

- ไม่เกิดเหตุการณ์  CDC

- ถ้าใช้ tool SSH ต่อ OS และเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการ close tool SSH เลย โดยไม่ disconnect จาก SQLPlus ก่อน จะทำให้ transaction ทำการ rollback

- ถ้าเชื่อมต่อ Database ด้วย SQL Developer แล้วทำการ kill process ของ SQL Developer เลย โดยไม่ disconnect ก่อน จะทำให้ transaction ทำการ rollback

- ขณะที่เครื่องของ user เชื่อมต่อ Database โดยกำลัง run query อยู่

- เกิดเหตุการณ์  CDC

- ถ้า user มีการ reboot หรือปิดเครื่อง โดยไม่ log off หรือ disconnect จาก Database

- ถ้ามีปัญหาจาก network ระหว่าง client กับ server เช่น การ disable ที่ Network Adapter อันที่กำลังใช้ connect เครื่อง Database

- ถ้าใช้ tool SSH ต่อ OS และเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการ close tool SSH เลย โดยไม่ disconnect จาก SQLPlus ก่อน

- ถ้าเชื่อมต่อ Database ด้วย SQL Developer แล้วทำการ kill process ของ SQL Developer เลย โดยไม่ disconnect ก่อน

- ถ้าเชื่อมต่อ Database ด้วย SQLPlus แล้วทำการปิดโปรแกรมทันที โดยไม่ disconnect ก่อน

- ไม่เกิดเหตุการณ์  CDC

- จากเหตุการณ์ต่างๆที่ทำให้เกิดเหตุการณ์  CDC ถ้า query ที่ active อยู่ เป็นลักษณะการหยุดรอด้วยการใช้คำสั่ง dbms_lock.sleep(...) (บางครั้งเกิด CDC ได้)

Database Resource Limits

- สามารถจัดการ Database Resource Limits ด้วยวิธีดังนี้

- กำหนด RESOURCE_LIMIT = TRUE ใน  parameter file (spfile หรือ pfile) เมื่อทำการ startup Database

- สร้าง user profile เพื่อใช้กำหนด resource limit และกำหนดให้กับ user ที่ต้องการ

- PMON สามารถจัดการ session ได้เพียงระดับ Database สามารถดูที่ V$SESSION แต่ไม่สามารถจัดการ process ระดับ OS ดูที่ V$PROCESS

      สังเกตุว่า SQLNET ยังคง send packet ได้ตามปกติจนกว่า session จะ logged off

                 เกิดปัญหาบางอย่างตามมา เช่น ระบบปฏิบัติการอาจถูกใช้ resource แบบสิ้นเปลืองให้กับ abandoned processes (โปรเซสที่ถูกละทิ้ง) ที่ไม่ได้ใช้งานแล้ว

      ทำให้ V$PROCESS ยังมี process นั้นอยู่ แต่ใน V$SESSION ไม่มี session นั้นทำงานด้วย

                 วิธีแก้ไขคือต้อง cleanup OS process ด้วย

- ขั้นตอนการทำงาน

ตัวอย่าง IDLE_TIME

1. เมื่อ resource ถูกใช้เกินที่กำหนดใน IDLE_TIME

2. PMON จะ mark status = SNIPED ดูที่ V$SESSION 

3. PMON ทำการ clean up database resources ของ session

4. PMON ทำการ kill session

Resource Manager

- สามารถจัดการ kill session ที่มีการใช้งานเกินค่าที่กำหนดได้

- PMON สามารถจัดการ session ได้เพียงระดับ Database เหมือน Database Resource Limits

- ขั้นตอนการทำงาน

ตัวอย่าง MAX_IDLE_TIME 

1. เมื่อ session มีสถานะ INACTIVE เกินที่กำหนดใน MAX_IDLE_TIME

2. PMON จะ mark status = KILLED ดูที่ V$SESSION 

3. PMON ทำการ clean up database resources ของ session

4. PMON ทำการ kill session

SQLNET

- สามารถจัดการ CDC ได้ โดยกำหนดระยะเวลา ด้วย parameter เหล่านี้

SQLNET.SEND_TIMEOUT

SQLNET.RECV_TIMEOUT

SQLNET.EXPIRE_TIME 

Firewall Configuration ในส่วนของ TCP keep-alive

- สามารถจัดการ CDC ได้ โดยกำหนดระยะเวลา ด้วย parameter เหล่านี้

  ใน Linux

tcp_keepalive_time

tcp_keepalive_intvl

tcp_keepalive_probes

Calculate Number of Transactions

การนับจำนวน transactions ใน Database

มีวิธีดังนี้

- วิธีหาจำนวน transactions จาก V$SYSSTAT

- วิธีหาจำนวน transactions จาก History Snapshot

- วิธีหาจำนวน transactions จาก Load profile section ของ Statspack หรือ AWR

วิธีหาจำนวน transactions จาก V$SYSSTAT

อธิบาย

- V$SYSSTAT ใช้สำหรับ system statistics

- จะเก็บจำนวน transactions ตั้งแต่เริ่มต้น startup Database ถ้า restart คือ เริ่มนับ 0 ใหม่

หาจำนวน transactions

SQL> select sum(s.value) transactions

from v$sysstat s ,v$instance i 

where s.name in ('user commits','transaction rollbacks');

TRANSACTIONS

----------------------

11

หาจำนวน transactions ต่อ second

จาก https://community.oracle.com/thread/3725596

sysdate - startup_time คือ จำนวนวัน นับจาก startup Database ถึงปัจจุบัน

86400 * จำนวนวัน คือ แปลงจำนวนวันเป็นวินาที โดย 86400 คือ จำนวนวินาทีใน 1 วัน

SQL> select round(sum(s.value / (86400 * (sysdate - startup_time))),3) "transactions/sec" 

from v$sysstat s ,v$instance i

where s.name in ('user commits','transaction rollbacks');

transactions/sec

----------------

            .064

หาจำนวน transactions โดยการกำหนดจุดเริ่มต้นและจุดสุดท้าย ในการนับจำนวน transactions ที่ต่างกัน

จาก http://ba6.us/?q=book/export/html/26

Query กำหนดให้เป็นค่าจำนวน transaction เริ่มต้น

select sysdate, sum(value) 

into begindate, beginval

from v$sysstat 

where name in ('user commits','user_rollbacks');

SYSDATE  SUM(VALUE)

------------------ -----------

20191128 16:50:00          11

Query กำหนดให้เป็นค่าจำนวน transaction สุดท้าย

select sysdate, sum(value) 

into enddate, endval

from v$sysstat

where name in ('user commits','user_rollbacks');

SYSDATE SUM(VALUE)

----------------- -------------

20191128 16:55:00          11

dbms_lock.sleep(จำนวนวินาที) ใช้กำหนดระยะเวลาห่างของ transactions เริ่มต้น และ transactions สุดท้าย หน่วยวินาที

endval - beginval คือ ผลต่างหรือที่เพิ่มเข้ามาใหม่ของจำนวน transactions ในช่วงเวลาที่กำหนด (เริ่มต้นถึงครั้งสุดท้าย)

diff transactions คือ ผลต่างของจำนวน transactions เริ่มต้นกับสุดท้าย

diff seconds คือ ผลต่างของเวลาเริ่มต้นกับสุดท้าย

diff transactions per second คือ ผลต่างของจำนวน transactions เริ่มต้นกับสุดท้าย ต่อ second

SQL> set serveroutput on

declare

    begindate date;

    enddate date;

    beginval number;

    endval number;

begin

    select sysdate, sum(value) 

    into begindate, beginval

    from v$sysstat 

    where name in ('user commits','user_rollbacks');

    dbms_lock.sleep(3000);

    select sysdate, sum(value) 

    into enddate, endval

    from v$sysstat

    where name in ('user commits','user_rollbacks');

    dbms_output.put_line((endval - beginval) || ' diff transactions');

    

    dbms_output.put_line(((enddate - begindate) * 86400) || ' diff seconds');

    dbms_output.put_line((endval - beginval) / ((enddate - begindate) * 86400) || ' diff transactions per second');

end;

/

0 diff transactions

300 diff seconds

0 diff transactions per second

วิธีหาจำนวน transactions จาก History Snapshot

อธิบาย

- DBA_HIST_SYSSTAT ใช้สำหรับ แสดง information ของ historical system statistics

- DBA_HIST_SNAPSHOT ใช้สำหรับ แสดง information เกี่ยวกับ snapshots ใน Workload Repository

หาจำนวน transactions ต่อ second ในแต่ละช่วงเวลา

จาก https://grepora.com/2016/05/25/oracle-tps-evaluating-transaction-per-second/

ช่วงเวลาใช้ snap time คือ BEGIN_INTERVAL_TIME ถึง BEGIN_INTERVAL_TIME ย้อนหลัง

SQL> with 

hist_snaps as 

(select instance_number,

    snap_id,

    round(begin_interval_time,'MI') datetime,

    (begin_interval_time + 0 - lag(begin_interval_time + 0) over (partition by dbid, instance_number order by snap_id)) 

* 86400 diff_time

    from dba_hist_snapshot

), 

hist_stats as 

(select dbid,

    instance_number,

    snap_id,

    stat_name,

    value - lag(value) over (partition by dbid,instance_number,stat_name order by snap_id)delta_value

    from dba_hist_sysstat

    where stat_name in ('user commits', 'user rollbacks')

)

select to_char(datetime,'yyyymmdd hh24:mi:ss')datetime,

round (sum (delta_value) / 3600, 4) "transactions/sec"

from hist_snaps sn, hist_stats st

where st.instance_number = sn.instance_number

and st.snap_id = sn.snap_id

and diff_time is not null

group by datetime

order by 1 desc;

DATETIME          transactions/sec

----------------- ----------------

20191128 22:01:00            .2481

20191128 21:46:00            .0058

20191128 21:31:00            .0039

20191128 21:16:00            .0033

20191128 21:01:00            .0039

หาจำนวน transactions ต่อ second หรือ minute หรือ hour หรือ day ในแต่ละช่วงเวลา

จาก https://dbaclass.com/article/find-user-commits-per-minute-oracle-database/

ช่วงเวลาใช้ snap time คือ BEGIN_INTERVAL_TIME ถึง END_INTERVAL_TIME

VALUE คือ จำนวน transactions ในช่วงเวลา snap time นั้นๆ

VALUE_PREVOUS คือ จำนวน transactions ในช่วงเวลา snap time ก่อนหน้าช่วงนั้นๆ

VALUE_DIFF คือ VALUE - VALUE_PREVOUS

  ถ้าค่าเป็นบวก แสดงว่า จำนวน transactions ในช่วงเวลา snap time นั้นๆ มีค่ามากกว่า snap time ก่อนหน้าช่วงนั้นๆ

  ถ้าค่าเป็นลบ แสดงว่า จำนวน transactions ในช่วงเวลา snap time นั้นๆ มีค่าน้อยกว่า snap time ก่อนหน้าช่วงนั้นๆ

STAT_PER_SEC คือ จำนวน transactions ต่อ seconds ในช่วงเวลา snap time   

STAT_PER_MIN คือ จำนวน transactions ต่อ minutes ในช่วงเวลา snap time

STAT_PER_HOURS คือ จำนวน transactions ต่อ hours ในช่วงเวลา snap time

STAT_PER_DAY คือ จำนวน transactions ต่อ days ในช่วงเวลา snap time

col stat_name for a20

col value_diff for 9999,999,999

col stat_per_min for 9999,999,999

set lines 200 pages 1500 long 99999999

col begin_interval_time for a30

col end_interval_time for a30

set linesize 1000

set pagesize 40

set pause on

SQL> select hsys.snap_id,

       hsnap.begin_interval_time,

       hsnap.end_interval_time,

           hsys.stat_name,

           hsys.value,

           lag(hsys.value,1,0) over (order by hsys.snap_id) as "value_prevous",

           hsys.value - lag(hsys.value,1,0) over (order by hsys.snap_id) as "value_diff",

           extract(second from (hsnap.end_interval_time - hsnap.begin_interval_time))sec_diff,

           extract(minute from (hsnap.end_interval_time - hsnap.begin_interval_time))min_diff,

           extract(hour from (hsnap.end_interval_time - hsnap.begin_interval_time))hr_diff,

           extract(day from (hsnap.end_interval_time - hsnap.begin_interval_time))day_diff,

           round

           (

            (hsys.value - lag(hsys.value,1,0) over (order by hsys.snap_id)) /

            round

            (   abs

                (extract(hour from (hsnap.end_interval_time - hsnap.begin_interval_time))*60*60 +

                 extract(minute from (hsnap.end_interval_time - hsnap.begin_interval_time))*60 +

                 extract(second from (hsnap.end_interval_time - hsnap.begin_interval_time)) +

                 extract(day from (hsnap.end_interval_time - hsnap.begin_interval_time))*24*60*60

                )

                ,4

            )

            ,4

           )"stat_per_sec",

           round

           (

            (hsys.value - lag(hsys.value,1,0) over (order by hsys.snap_id)) /

            round

            (   abs

                (extract(hour from (hsnap.end_interval_time - hsnap.begin_interval_time))*60 +

                 extract(minute from (hsnap.end_interval_time - hsnap.begin_interval_time)) +

                 extract(second from (hsnap.end_interval_time - hsnap.begin_interval_time))/60 +

                 extract(day from (hsnap.end_interval_time - hsnap.begin_interval_time))*24*60

                )

                ,4

            )

            ,4

           )"stat_per_min",

           round

           (

            (hsys.value - lag(hsys.value,1,0) over (order by hsys.snap_id)) /

            round

            (   abs

                (extract(hour from (hsnap.end_interval_time - hsnap.begin_interval_time))+

                 extract(minute from (hsnap.end_interval_time - hsnap.begin_interval_time))/60 +

                 extract(second from (hsnap.end_interval_time - hsnap.begin_interval_time))/60/60 +

                 extract(day from (hsnap.end_interval_time - hsnap.begin_interval_time))*24

                )

                ,4

            )

            ,4

           )"stat_per_hr",

           round

           (

            (hsys.value - lag(hsys.value,1,0) over (order by hsys.snap_id)) /

            round

            (   abs

                (extract(hour from (hsnap.end_interval_time - hsnap.begin_interval_time))/24+

                 extract(minute from (hsnap.end_interval_time - hsnap.begin_interval_time))/60/24 +

                 extract(second from (hsnap.end_interval_time - hsnap.begin_interval_time))/60/60/24 +

                 extract(day from (hsnap.end_interval_time - hsnap.begin_interval_time))

                )

                ,4

            )

            ,4

           )"stat_per_day"

from dba_hist_sysstat hsys, dba_hist_snapshot hsnap

where hsys.snap_id = hsnap.snap_id

and hsnap.instance_number in (select instance_number from v$instance)

and hsnap.instance_number = hsys.instance_number

and hsys.stat_name in ('user commits','user rollbacks')

order by 1 desc;

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              STAT_NAME       

---------- ------------------------------ ------------------------------ --------------------

      1597 11/28/2019 10:00:50.865 PM     11/28/2019 10:15:06.272 PM     user commits        

      1597 11/28/2019 10:00:50.865 PM     11/28/2019 10:15:06.272 PM     user rollbacks      

      1596 11/28/2019 9:45:45.151 PM      11/28/2019 10:00:50.865 PM     user commits        

      1596 11/28/2019 9:45:45.151 PM      11/28/2019 10:00:50.865 PM     user rollbacks      

      VALUE value_prevous    value_diff   SEC_DIFF   MIN_DIFF    HR_DIFF   DAY_DIFF 

 ---------- ------------- ------------- ---------- ---------- ---------- ---------- 

       1156            26         1,130     15.407         14          0          0 

         26           264          -238     15.407         14          0          0 

        264            25           239      5.714         15          0          0 

         25           243          -218      5.714         15          0          0 

stat_per_sec  stat_per_min stat_per_hr stat_per_day

------------ ------------- ----------- ------------

       1.321            79   4755.8923   114141.414

     -0.2782           -17  -1001.6835   -24040.404

       .2639            16    949.9205   22761.9048

     -0.2407           -14   -866.4547   -20761.905

วิธีหาจำนวน transactions จาก Load profile section ของ Statspack หรือ AWR

จาก https://timurakhmadeev.wordpress.com/2012/02/21/load-profile/

อธิบาย

- V$SYSMETRIC ใช้สำหรับแสดง system metric ที่เก็บจากช่วงเวลาปัจจุบันสุด โดย long duration คือ 60 second และ short duration คือ 15 second

- ในช่วงเวลา 15 หรือ 60 second ที่ผ่านมา

หาจำนวน transactions ต่อ second

col short_name  format a20              heading 'Load Profile'

col per_sec     format 999,999,999.9    heading 'Per Second'

col per_tx      format 999,999,999.9    heading 'Per Transaction'

set colsep '   '

SQL> select lpad(short_name, 20, ' ') short_name

     , per_sec

     , per_tx from

    (select short_name

          , max(decode(typ, 1, value)) per_sec

          , max(decode(typ, 2, value)) per_tx

          , max(m_rank) m_rank 

        from

        (select /*+ use_hash(s) */

                m.short_name

              , s.value * coeff value

              , typ

              , m_rank

           from v$sysmetric s,

               (select 'Database Time Per Sec'                      metric_name, 'DB Time' short_name, .01 coeff, 1 typ, 1 m_rank from dual union all

                select 'CPU Usage Per Sec'                          metric_name, 'DB CPU' short_name, .01 coeff, 1 typ, 2 m_rank from dual union all

                select 'Redo Generated Per Sec'                     metric_name, 'Redo size' short_name, 1 coeff, 1 typ, 3 m_rank from dual union all

                select 'Logical Reads Per Sec'                      metric_name, 'Logical reads' short_name, 1 coeff, 1 typ, 4 m_rank from dual union all

                select 'DB Block Changes Per Sec'                   metric_name, 'Block changes' short_name, 1 coeff, 1 typ, 5 m_rank from dual union all

                select 'Physical Reads Per Sec'                     metric_name, 'Physical reads' short_name, 1 coeff, 1 typ, 6 m_rank from dual union all

                select 'Physical Writes Per Sec'                    metric_name, 'Physical writes' short_name, 1 coeff, 1 typ, 7 m_rank from dual union all

                select 'User Calls Per Sec'                         metric_name, 'User calls' short_name, 1 coeff, 1 typ, 8 m_rank from dual union all

                select 'Total Parse Count Per Sec'                  metric_name, 'Parses' short_name, 1 coeff, 1 typ, 9 m_rank from dual union all

                select 'Hard Parse Count Per Sec'                   metric_name, 'Hard Parses' short_name, 1 coeff, 1 typ, 10 m_rank from dual union all

                select 'Logons Per Sec'                             metric_name, 'Logons' short_name, 1 coeff, 1 typ, 11 m_rank from dual union all

                select 'Executions Per Sec'                         metric_name, 'Executes' short_name, 1 coeff, 1 typ, 12 m_rank from dual union all

                select 'User Rollbacks Per Sec'                     metric_name, 'Rollbacks' short_name, 1 coeff, 1 typ, 13 m_rank from dual union all

                select 'User Transaction Per Sec'                   metric_name, 'Transactions' short_name, 1 coeff, 1 typ, 14 m_rank from dual union all

                select 'User Rollback UndoRec Applied Per Sec'      metric_name, 'Applied urec' short_name, 1 coeff, 1 typ, 15 m_rank from dual union all

                select 'Redo Generated Per Txn'                     metric_name, 'Redo size' short_name, 1 coeff, 2 typ, 3 m_rank from dual union all

                select 'Logical Reads Per Txn'                      metric_name, 'Logical reads' short_name, 1 coeff, 2 typ, 4 m_rank from dual union all

                select 'DB Block Changes Per Txn'                   metric_name, 'Block changes' short_name, 1 coeff, 2 typ, 5 m_rank from dual union all

                select 'Physical Reads Per Txn'                     metric_name, 'Physical reads' short_name, 1 coeff, 2 typ, 6 m_rank from dual union all

                select 'Physical Writes Per Txn'                    metric_name, 'Physical writes' short_name, 1 coeff, 2 typ, 7 m_rank from dual union all

                select 'User Calls Per Txn'                         metric_name, 'User calls' short_name, 1 coeff, 2 typ, 8 m_rank from dual union all

                select 'Total Parse Count Per Txn'                  metric_name, 'Parses' short_name, 1 coeff, 2 typ, 9 m_rank from dual union all

                select 'Hard Parse Count Per Txn'                   metric_name, 'Hard Parses' short_name, 1 coeff, 2 typ, 10 m_rank from dual union all

                select 'Logons Per Txn'                             metric_name, 'Logons' short_name, 1 coeff, 2 typ, 11 m_rank from dual union all

                select 'Executions Per Txn'                         metric_name, 'Executes' short_name, 1 coeff, 2 typ, 12 m_rank from dual union all

                select 'User Rollbacks Per Txn'                     metric_name, 'Rollbacks' short_name, 1 coeff, 2 typ, 13 m_rank from dual union all

                select 'User Transaction Per Txn'                   metric_name, 'Transactions' short_name, 1 coeff, 2 typ, 14 m_rank from dual union all

                select 'User Rollback Undo Records Applied Per Txn' metric_name, 'Applied urec' short_name, 1 coeff, 2 typ, 15 m_rank from dual) m

          where m.metric_name = s.metric_name

            and s.intsize_csec > 5000

            and s.intsize_csec < 7000

)

      group by short_name

)

 order by m_rank;

Load Profile                  Per Second     Per Transaction

--------------------   -----------------   -----------------

             DB Time               .0068                    

              DB CPU               .0067                    

           Redo size         40,574.6338         43,530.7857

       Logical reads          1,035.7357          1,111.1964

       Block changes            312.5999            335.3750

      Physical reads             24.6172             26.4107

     Physical writes              4.6272              4.9643

          User calls               .3995               .4286

              Parses             45.5226             48.8393

         Hard Parses              6.7410              7.2321

              Logons               .0666               .0714

            Executes            143.5253            153.9821

           Rollbacks               .3995                    

        Transactions               .9321                    

        Applied urec              0.0000              0.0000

Connecting to a Hung Database

เมื่อ Database เกิดอาการ hang และเราไม่สามารถ login เพื่อเข้าไปตรวจสอบหรือแก้ไข
จะมีวิธีการที่แนะนำดังนี้ (เรียงตามลำดับผลกระทบจากน้อยไปมาก)
- วิธีที่ 1 เข้า SQLPlus ด้วย option Prelim และ  เก็บ diagnostic information ต่างๆ เพื่อวิเคราะห์ problem
   หลังจากได้ diagnostic information ต่างๆแล้ว สามารถใช้วิธีที่ 2 หรือ 3 หรือ 4 เพื่อให้ Database เริ่มทำงานใหม่
- วิธีที่ 2 เข้า SQLPlus ด้วย option Prelim และ shutdown abort
- วิธีที่ 3 Kill Oracle background process ทั้งหมด ของ Database ที่ต้องการ
- วิธีที่ 4 Restart Server

ตัวอย่าง เหตุการณ์ทดสอบ

Database ชื่อ dbtest1

Session ที่ต้องการสังเกตุ
SQL> begin
  loop
    null;
  end loop;
end;
/

พบ process ใช้งาน %CPU = 99.4
--> top
   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                       
 31006 oracle    20   0 1346m  58m  53m R 99.4  3.1   2:26.03 oracle_31006_db                                                                 
 23328 oracle    20   0 1328m  44m  41m S  0.4  2.4   0:27.50 ora_mmnl_dbtest                                                                 
 23302 oracle    20   0 1330m  34m  28m S  0.2  1.8   0:43.55 ora_dia0_dbtest                                                                 
 23364 oracle    20   0 1350m  25m  21m S  0.2  1.3   0:00.76 ora_arc3_dbtest                                                                 
 26573 root      20   0 99.7m 3640 2652 S  0.2  0.2   0:01.21 sshd       

--> ps -eo user,pid,ppid,cmd |grep 31006
oracle    31006      1 oracledbtest1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    32065  31824 grep 31006

และตอนนี้ไม่สามารถ login เข้า SQLPlus เพราะ Database hung

วิธีที่ 1

--> export ORACLE_SID=dbtest1
--> sqlplus -prelim / as sysdba

ตัวอย่าง การใช้ oradebug ตรวจสอบ process PID = 31006 ว่าตอนนี้กำลังใช้ query อะไรทำงานอยู่หรือไม่

Debug PID ที่ต้องการตรวจสอบ
SQL> oradebug setospid 31006

แสดง trace file
SQL> oradebug tracefile_name
/oracle/diag/rdbms/dbtest1/dbtest1/trace/dbtest1_ora_31006.trc

แสดง SQL ที่ process PID = 31006 กำลังใช้งานอยู่
SQL> oradebug current_sql
begin
  loop
    null;
  end loop;
end;

Close trace
SQL> oradebug close_trace

อ่าน trace file ด้วยคำสั่งของ OS
--> vi /oracle/diag/rdbms/dbtest1/dbtest1/trace/dbtest1_ora_31006.trc
Trace file /oracle/diag/rdbms/dbtest1/dbtest1/trace/dbtest1_ora_31452.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /oracle/product/12.2.0/dbhome_1
System name:    Linux
Node name:      oradb12c.localdomain
Release:        2.6.32-573.el6.x86_64
Version:        #1 SMP Thu Jul 23 15:44:03 UTC 2015
Machine:        x86_64
Instance name: dbtest1
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 31452, image: oracle@oradb12c.localdomain (TNS V1-V3)

*** 2019-11-26T04:30:42.158025+07:00
*** SESSION ID:(68.10681) 2019-11-26T04:30:42.158049+07:00
*** CLIENT ID:() 2019-11-26T04:30:42.158054+07:00
*** SERVICE NAME:(SYS$USERS) 2019-11-26T04:30:42.158059+07:00
*** MODULE NAME:(sqlplus@oradb12c.localdomain (TNS V1-V3)) 2019-11-26T04:30:42.158064+07:00
*** ACTION NAME:() 2019-11-26T04:30:42.158068+07:00
*** CLIENT DRIVER:(SQL*PLUS) 2019-11-26T04:30:42.158072+07:00

Received ORADEBUG command (#2) 'current_sql' from process '31226'

*** 2019-11-26T04:30:42.158105+07:00
Finished processing ORADEBUG command (#2) 'current_sql'
...
....
.....

:q

สามารถดู option การใช้งานอื่นๆของ oradebug ได้จาก เปิด help
SQL> oradebug help

วิธีที่ 2

--> export ORACLE_SID=dbtest1
--> sqlplus -prelim / as sysdba

สั่ง Shutdown abort
SQL> shutdown abort

วิธีที่ 3

แสดง PID ของ process ทั้งหมด ตามชื่อ Database
GREP -V GREP เป็นคำสั่งสำหรับเอา process จากคำสั่ง GREP ออก
--> ps -ef | grep dbtest1 | grep -v grep | awk '{print $2}'
33738
33740
33746
....
......
........

Kill PID ของ process ทั้งหมด ตามชื่อ Database
--> kill -9 `ps -ef | grep dbtest1 | grep -v grep | awk '{print $2}'`

Remove RAM segment ที่ใช้งานอยุ่
--> ipcs -pmb

วิธีที่ 4

ใช้คำสั่ง shutdown หรือ restart OS

คำสั่ง shutdown
--> init 0

คำสั่ง restart
--> init 6