Rozliczenie obciążenia serwera Oracle

Ostatnio w jednej z firm dostałem zagadnienie do przemyślenia…
Odkąd spółki należące do jednej korporacji zaczęły rozliczać się między sobą i działami za korzystanie z usług powstał problem jak X spółek pracujących na jednej bazie, jednym serwerze obciążyć sprawiedliwie za zużywane zasoby ….

Intuicyjnie, zacząłem się zastanawiać, czy informacje tego typu są dostępne gdzieś w bazie…

Chwila namysłu i stwierdzenie, że to co nawet znajdziemy w ASH (Active Session History) nie da nam odpowiedzi na nurtujące nas pytania …

Jak najlepiej zatem podejść do tematu ? Dla mnie odpowiedź jest zawsze jedna: “Najprościej jak się da !”

Problem rozbity na części pierwsze wygląda następująco:
1. Podzielić użytkowników według przynależności do firm
2. Wybrać statystykę mówiącą o “zużywaniu” serwera przez sesję
3. Stworzyć mechanizmy zbierające i agregujące dane
4. Zaobserwować czy samo monitorowanie nie obciąża zbytnio serwera
5. Zaprezentować dane w sposób wygodny graficznie

Ad 1.
Sprawa wydaje się trywialna. Utwórzmy zatem struktury odpowiadające zagadnieniu

 CREATE TABLESPACE WORKLOAD datafile '' size 2G ; CREATE USER WORKLOAD_MON identified by workload default tablespace WORKLOAD quota unlimited on WORKLOAD; -- Dodanie potrzebnych uprawnienień Grant connect,resource,select any dictionary, manage scheduler,scheduler_admin to WORKLOAD_MON; -- Utworzenie struktur tabel -- Tabela z próbkami dla każdej sesji aktywnej CREATE TABLE SESS_BY_USER_STAT_DET_DAILY (DAY DATE, SID NUMBER, VALUE NUMBER, USERNAME VARCHAR2(100), RESOURCE_CONSUMER_GROUP VARCHAR2(100)); -- Zagregowana tabela z wartościami dziennymi wybranej statystyki dla każdego z użytkowników CREATE TABLE CPU_BY_RSRCGRP_USER_DAY (RESOURCE_CONSUMER_GROUP VARCHAR2(100),USERNAME VARCHAR2(100),DAY DATE, VALUE NUMBER); -- Tabela z nazwami firm CREATE TABLE CORPORATIONS(ID NUMBER,CORPORATION VARCHAR2(1000), constraint PK_CORP PRIMARY KEY(ID,CORPORATION)); ALTER TABLE CORPORATIONS ADD CONSTRAINT U_CORP UNIQUE (ID); -- Tabela przydzielająca użytkowników do poszczególnych spółek CREATE TABLE USER_CORPORATION (USERNAME VARCHAR2(100), ID NUMBER, CONSTRAINT FK_UC FOREIGN KEY(ID) REFERENCES CORPORATIONS(ID), CONSTRAINT PK_UC PRIMARY KEY (USERNAME,ID)) ;

-- Tabela z danymi historycznymi
 CREATE TABLE CPU_BY_RSRCGRP_USER_HIST (RESOURCE_CONSUMER_GROUP VARCHAR2(100),USERNAME VARCHAR2(100),DAY DATE, VALUE NUMBER);

-- Indeksy przydatne przy pobieraniu interesujących nas danych oraz agregacjach
 Create index sbusdd_day_char on SESS_BY_USER_STAT_DET_DAILY(TO_CHAR(DAY,'YYYY-MM-DD')) ONLINE;
 Create index sbusdd_day_char_date on SESS_BY_USER_STAT_DET_DAILY(TO_DATE(TO_CHAR(DAY,'YYYY-MM-DD'),'YYYY-MM-DD')) ONLINE;
 Create index sbusdd_sid_user on SESS_BY_USER_STAT_DET_DAILY(SID,USERNAME) ONLINE;
 Create index CBRUD_USER on CPU_BY_RSRCGRP_USER_DAY(USERNAME);
 Create index CBRUD_DAY on CPU_BY_RSRCGRP_USER_DAY(DAY);
 Create index UC_USER on USER_CORPORATION(USERNAME);
 Create index UC_CORP on USER_CORPORATION(ID);

 Ad. 2
Tutaj sprawa może być dyskusyjna ..
Ja wybrałem statystykę sesji “CPU used by this session”. Dlaczego ? Czy wyobrażamy sobie jakąkolwiek operację, bez udziału CPU ? Owszem dla przykładu “db file scaterred read” jest operacją I/O, jednak nawet wtedy “coś” musi te dane przeprocesować, wczytać do cache bazy …
Poza tym, chodzi nam głównie o procentowe rozliczenie udziału w obciążeniu, aby w sposób przybliżony wykazać podział a nie policzyć w jakim stopniu serwer jest obciążony.
Mówiąc trochę więcej o tej statystyce warto wspomnieć o tym kiedy jest naliczana dla poszczególnych sesji. Wyrażana jest w dziesiątych częściach milisekundy, a naliczana jest przy każdym zakończeniu wywołania użytkownika. Wiążą się z tym pewne niedogodności. Jeśli spojrzymy na raport AWR sumaryczny czas CPU used by this session może okazać się większy od całkowitego czasu procesora dla wybranego np. godzinnego okresu. Różnica ta jest dośc prosta do wytłumaczenia, jeśli pamiętami o tym, że user call mógł rozpocząć się przed naszym pierwszym snapshot’em, trwać długo a zakończyć tuż przed końcaem snapshot’u drugiego między którymi rozpatrujemy raport AWR. Podobnie jeśli user call jest krótszy od 1/10 milisekundy , statystyka ta nie zostanie naliczona.

Mimo wszystkich neidogodności, wybór ten wydał mi się najrozsądniejszy.

Ad. 3

Poniżej przedstawiłem implementacje mechanizmów zbierających dane z komentarzami. Sama idea jest prosta. CO 20 sekund zbieramy informacje dla każdej sesji odnośnie wybranej przez nas statystyki. Wrzucamy wartości do tabeli szczegółów, uaktualniając wiersz jeśli takowa sesja użytkownika już istnieje, lub dodając nowy, jeśli jest to nowa sesja z nowym użytkownikiem.
Raz dziennie agregujemy dane. Mamy możliwość przerzucania danych do tabel historycznych oraz agregacji za dowolny dzień lub ze wszystkich danych z tabeli szczegółów.

 -- Procedura zbierająca dane szczegółowe dla sesji CREATE OR REPLACE PROCEDURE GATHER_CPU_STATS_BY_SESSION AS Cursor sessStat is (select TO_CHAR(SYSDATE,'YYYY-MM-DD') DAY, st.SID,st.VALUE,sess.USERNAME,sess.RESOURCE_CONSUMER_GROUP from v$session sess, v$sesstat st,v$statname sn where sn.NAME='CPU used by this session' and st.STATISTIC#=sn.STATISTIC# and st.SID=sess.SID and sess.USERNAME is not null);

rSessStat sessStat%ROWTYPE;
 isRegisteredSess NUMBER;

BEGIN
 FOR rSessStat in sessStat LOOP
 BEGIN
 Select 1 into isRegisteredSess from SESS_BY_USER_STAT_DET_DAILY where SID=rSessStat.SID and USERNAME=rSessStat.USERNAME;
 EXCEPTION WHEN NO_DATA_FOUND THEN isRegisteredSess := 0;
 END;
 IF isRegisteredSess = 0 THEN
 Insert into SESS_BY_USER_STAT_DET_DAILY values(TO_DATE(rSessStat.DAY,'YYYY-MM-DD'),rSessStat.SID,rSessStat.VALUE,rSessStat.USERNAME,rSessStat.RESOURCE_CONSUMER_GROUP);
 ELSE
 Update SESS_BY_USER_STAT_DET_DAILY set VALUE=rSessStat.VALUE where SID=rSessStat.SID and USERNAME=rSessStat.USERNAME;
 END IF;
 END LOOP;
 commit;

END;

-- Procedura agregująca dane za zadany dzień

CREATE OR REPLACE PROCEDURE AGGREGATE_CPU_STATS_BY_DAY(F_DAY DATE DEFAULT SYSDATE) AS
 BEGIN
 Insert into
 CPU_BY_RSRCGRP_USER_DAY
 select
 RESOURCE_CONSUMER_GROUP, USERNAME,
 TO_DATE(TO_CHAR(F_DAY-1,'YYYY-MM-DD') || '00:00:00','YYYY-MM-DD HH24:MI:SS'),
 sum(VALUE)
 from
 sess_by_user_stat_det_daily
 where DAY between
 TO_DATE(TO_CHAR(F_DAY-1,'YYYY-MM-DD') || '00:00:00','YYYY-MM-DD HH24:MI:SS') and
 TO_DATE(TO_CHAR(F_DAY,'YYYY-MM-DD') || '00:00:00','YYYY-MM-DD HH24:MI:SS') GROUP BY RESOURCE_CONSUMER_GROUP, USERNAME;
 --Delete from sess_by_user_stat_det_daily where DAY <= SYSDATE - 365;
 commit;
 end;

-- Procedura agregująca wszystkie dane dostępne w tabeli szczegółów

CREATE OR REPLACE PROCEDURE AGGREGATE_CPU_STATS_BY_DAY_ALL AS
 BEGIN
 insert into CPU_BY_RSRCGRP_USER_HIST(RESOURCE_CONSUMER_GROUP,USERNAME,DAY, VALUE) Select * from CPU_BY_RSRCGRP_USER_DAY;
 delete from CPU_BY_RSRCGRP_USER_DAY;
 Insert into
 CPU_BY_RSRCGRP_USER_DAY
 Select
 RESOURCE_CONSUMER_GROUP, USERNAME,
 TO_DATE(TO_CHAR(DAY,'YYYY-MM-DD'),'YYYY-MM-DD'),SUM(VALUE)
 from
 sess_by_user_stat_det_daily
 where
 TO_DATE(TO_CHAR(DAY,'YYYY-MM-DD'),'YYYY-MM-DD') < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
 group by
 TO_DATE(TO_CHAR(DAY,'YYYY-MM-DD'),'YYYY-MM-DD'),RESOURCE_CONSUMER_GROUP,USERNAME
 order by 3,1,2;
 commit;
 end;

-- Widok podający dane w "czystej" formie

CREATE OR REPLACE FORCE VIEW "CORP_BY_DAY_STATS" ("CORPORATION", "DAY", "PERCENTCPU")
 AS
 SELECT det.CORPORATION,
 det.DAY,
 ROUND(((det.VALUE/sm.VALUE) * 100)) PercentCPU
 FROM
 (Select t1.DAY,NVL(corp.corporation,'OTHER') CORPORATION, t1.VALUE from (
 SELECT NVL(uc.ID,-1) ID,
 cpubd.DAY,
 SUM(cpubd.VALUE) VALUE
 FROM USER_CORPORATION uc RIGHT JOIN
 CPU_BY_RSRCGRP_USER_DAY cpubd
 ON uc.USERNAME=cpubd.USERNAME
 GROUP BY uc.ID,
 cpubd.DAY) t1 LEFT join CORPORATIONS corp on t1.ID=corp.ID
 ) det,
 (SELECT DAY,SUM(VALUE) VALUE FROM CPU_BY_RSRCGRP_USER_DAY GROUP BY DAY
 ) sm
 WHERE det.DAY=sm.DAY;

-- Schedule dla Job'a zbierającego dane dla sesji

begin
 dbms_scheduler.create_schedule(
 schedule_name=>'GATHER_CPU_STAT_SCHEDULE',
 start_date => SYSTIMESTAMP,
 repeat_interval => 'freq=SECONDLY; interval=20',
 end_date => NULL,
 comments => 'Schedule for GATHER_CPU_STATS_BY_SESSION every 30 seconds');
 end;

-- Definicja programu dla Job'a
 begin
 dbms_scheduler.create_program(
 program_name => 'GATHER_CPU_STAT_PROG',
 program_type => 'STORED_PROCEDURE',
 program_action => 'GATHER_CPU_STATS_BY_SESSION',
 enabled => true,
 COMMENTS => 'Program for GATHER_CPU_STATS_BY_SESSION');
 end;

-- Utworzenie Job'a zbierajacego dane z zadanym schedul'em oraz programem
 begin
 dbms_scheduler.create_job(
 job_name => 'GATHER_CPU_STAT_JOB',
 program_name => 'GATHER_CPU_STAT_PROG',
 schedule_name => 'GATHER_CPU_STAT_SCHEDULE',
 enabled => TRUE,
 comments => 'JOB FOR GATHER_CPU_STATS_BY_SESSION defined with program GATHER_CPU_STAT_PROG and schedule GATHER_CPU_STAT_SCHEDULE');
 end;

-- Schedule dla codziennej agregacji danych
 begin
 dbms_scheduler.create_schedule(
 schedule_name=>'AGGREGATE_CPU_STATS_SCHEDULE',
 start_date => to_timestamp_tz('2012-04-24 00:15','YYYY-MM-DD HH24:MI'),
 repeat_interval => 'freq=DAILY; byhour=00;byminute=15',
 end_date => NULL,
 comments => 'Schedule for GATHER_CPU_STATS_BY_SESSION every 30 seconds');
 end;

--Definicja programu dla agregacji
 begin
 dbms_scheduler.create_program(
 program_name => 'AGGREGATE_CPU_STATS_PROG',
 program_type => 'STORED_PROCEDURE',
 program_action => 'AGGREGATE_CPU_STATS_BY_DAY',
 enabled => true,
 COMMENTS => 'Program for Aggregating sess_by_user_stat_det_daily');
 end;

-- Utworzenie Job'a agregującego z zadanym schedule'm oraz programem
 begin
 dbms_scheduler.create_job(
 job_name => 'AGGREGATE_CPU_STATS_JOB',
 program_name => 'AGGREGATE_CPU_STATS_PROG',
 schedule_name => 'AGGREGATE_CPU_STATS_SCHEDULE',
 enabled => TRUE,
 comments => 'JOB FOR AGGREGATE_CPU_STATS_BY_DAY defined with program AGGREGATE_CPU_STATS_PROG and schedule AGGREGATE_CPU_STATS_SCHEDULE');
 end;

AD. 4

Musicie mi wierzyć, że wykonałem odpowiednie testy czy nie obciążam serwera 🙂 Zostawiam to inwencji każdego z was 🙂

AD. 5

Graficzna prezentacja jest oczywiście dowolna … Ja wybrałem formę przez WWW.Załączam pliki , może ktoś zechce skorzystać z gotowego rozwiązania m jak również zrzuty ekranu.

generateImages.sh – uruchamiana z crontab generacja wykresów
drawPie.pl – główna generacja wykresów z wewnątrz generateImages.sh
getPieData.sh – z wewnątrz drawPie.pl – pobiera dane do wykresów
getPieDataSummary.sh – z wewnątrz drawPie.pl – pobiera dane do wykresów sumarycznych
regenerateImages.sh – generuje od nowa wykresy z wszystkich dostępnych danych dla każdego dnia
redrawPie.pl – potrzebny dla regenerateImages.sh

generacja wykresów dla strony