[HANA] Statistics server 관련 내용

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/6c8f4daa6e39488d8ea3cdd1a6870536.html

SAP Help Portal

 

---

 

https://launchpad.support.sap.com/#/notes/1991615

https://launchpad.support.sap.com/#/notes/1991615

1991615 - Configuration options for the Embedded Statistics Service

Version 13 from May 29, 2015 in English

 

Symptom

You have activated the Embedded Statistics Service. You now want to configure its behavior.

Reason and Prerequisites

You have activated the Embedded Statistics Service in accordance with SAP Note 1917938.

Solution

Unless otherwise specified, the catalog objects described below are stored in the schema _SYS_STATISTICS.

 

All procedures are listed in the table STATISTICS_OBJECTS with the relevant type and ID. A scheduler regularly calls the procedures of the type "Alert", "Collector", and "SpecialFunction". The system stores their IDs in the table STATISTICS_SCHEDULE. This table also provides information about the interval at which the scheduler regularly and automatically calls each check (type "Alert"), collector (type "Collector"), and special function (type "SpecialFunction").

 

You can use an SQL statement to change this interval. Note the following: If you execute checks/collectors too frequently, this will cause the system load to increase unnecessarily.

(Example)

What to do	SQL Command
Change the frequency of check 17 to 2,000 seconds:	update _SYS_STATISTICS.STATISTICS_SCHEDULE set INTERVALLENGTH=2000 where ID=17
Deactivate collector 5034	update _SYS_STATISTICS.STATISTICS_SCHEDULE set STATUS='Inactive' where ID=5034
Activate check 17	update _SYS_STATISTICS.STATISTICS_SCHEDULE set STATUS='Idle' where ID=17
Display the current "info" threshold value for check 17	select DEFAULT_VALUE, CURRENT_VALUE, UNIT from _SYS_STATISTICS.STATISTICS_ALERT_THRESHOLDS where ALERT_ID=17 and SEVERITY=1 * If the CURRENT_VALUE is zero, the DEFAULT_VALUE is active,   otherwise the CURRENT_VALUE is active.
Change the "info" threshold value for check 17	update _SYS_STATISTICS.STATISTICS_ALERT_THRESHOLDS set CURRENT_VALUE=200000000 where ALERT_ID=17 and SEVERITY=1
Reset the "info" threshold value for check 17	update _SYS_STATISTICS.STATISTICS_ALERT_THRESHOLDS set CURRENT_VALUE=null where ALERT_ID=17 and SEVERITY=1
Change the time for collector data retention	update _SYS_STATISTICS.STATISTICS_SCHEDULE set RETENTION_DAYS_CURRENT = 90 where ID = 5001| * Bear in mind that lengthening the collector data retention time increases   the memory consumption of the Statistics Service. * If you change the data retention time, the change is only visible after a certain   time period. The system deletes old data daily.

- Here, you can also activate or deactivate all checks and collectors individually (column STATUS).
  The normal status is "Idle": The scheduler can execute the check/collector.
  If the scheduler is currently executing a check/collector the check/collector has the status "Scheduled".
  If a check/collector has a problem, the system changes the status to "Disabled" and the scheduler no longer calls
  it.
  As of Revision 93, a check/collector is automatically reset to "Idle" after 1 hour + the interval length.
  In older revisions, its status must be manually set to "Idle" again.

- Some checks may produce an "info" alert. Some of these also have an "info" threshold value that you change using
   SQL.
   The following checks have an "info" threshold value: 17, 21, 28, 29, 39, 40, 44, 45, 54, 55, 56, 58, 60, 61, 64.

 

 

---

 

요약하면

- Oracle의 AWR처럼 statistics server에 저장하여 과거 성능정보들을 확인할 수 있음
  . _sys_statistics.HOST_* 테이블 

  . 참고로 M_* 는 현재 상태를 보여주는 System view라면 HOST_*는 history 정보를 저장하기 위한 도구로 사용

- 이에 대한 각 항목들의 주기 등도 테이블 내의 데이터 값으로 관리됨
  . 위의 Notes에서의 Example 처럼 update 문을 통해서 해당 항목 변경
  . _sys_statistics.statistics_schedule : 스케쥴 관리
  . _sys_statistics.statistics_objects : 항목 관리
  . _sys.statistics.statistics_properties : Property 관리

* 현 설정 현황 조회

select * from   _sys_statistics.statistics_schedule s        , _sys_statistics.statistics_objects o where s.id = o.id    and o.type = 'Collector' order by 1