¶ Wait events en CPU gebruik tonen in Excel
Dit artikel beschrijft de manier die gebruikt wordt om voor alle NIET PRD databases een overzicht te krijgen van de tijd in seconden per dag besteedt aan wachten t.o.v. seconden dat de database gebruik kan maken van CPU kracht. PRD omgevingen hebben op dit moment geen ‘last’ van grote hoeveelheden wait events maar als dit in de toekomst wel het geval wordt kan dezelfde werkwijze op PRD omgevingen gebruikt worden.
Deze gegevens worden per databaseserver verzameld in een overzicht zoals hieronder te zien is
Daarnaast wordt op basis van dit overzicht een grafiek gemaakt.
Dit is samengebracht in een Excel per databaseserve. Een voorbeeld van deze Excel is te downloaden via link: /waits/af201_all_db_waits.xlsx
¶ gegevens verzamelen met script all_db _waits_incl_cpu.sh
Per databaseserver wordt script all_db _waits_incl_cpu.sh (voorbeeld te vinden via link:/waits/all_db_waits_incl_cpu_10_10.sh) uitgevoerd. In het voorbeeldscript is een begin- en einddatum hard coded gedefinieerd en moet aangepast worden.
-- verander hier eventueel begon- en einddatum
define b_ts = '2023-10-10 00:00:00'
define e_ts = '2023-10-10 23:59:59'Het script verzamelt per instance die ‘actief’ is m.b.v. ps -ef|grep ora_smon wait evets en CPU gebruik.
for DB in $(ps -ef|grep ora_smon|grep -v grep|awk '{printf("%s\n",$NF)}'|cut -d"_" -f3|sort|grep -v ^R)
do
<< HIER worden alle wait events en CPU gebruik per database opgehaald. >>
doneDE output van het script moet ge-redirect worden naar een .csv bestand
./all_db _waits_incl_cpu.sh >> 20231010_all_db _waits_incl_cpu.csv¶ external tabel in oracle aanmaken
De 20231010_all_db _waits_incl_cpu.csv file wordt m.b.v. een oracle external table verwerkt. In dit artikel wordt gebruik gemaakt van directory /data op een willekeurige databaseserver om de external table aan te maken. Kopieer file 20231010_all_db_waits_incl_cpu.csv naar file all_db_waits_incl_cpu.csv. Er wordt vervolgens een oracle directory aangemaakt met lees- en schrijfrechten aan public.
create or replace directory extdata as '/data';
grant read on directory extdata to public;
grant write on directory extdata to public;
create table machine_waits
( host_name varchar2(20),
instance_name varchar2(10),
starttijd varchar2(20),
eindtijd varchar2(20),
seconden number,
duration varchar2(10),
wait_class varchar2(120),
event varchar2(200)
)
organization external
( type oracle_loader
default directory extdata
access parameters
(records delimited by newline
badfile extdata:'bad_%a_%p.bad'
logfile extdata:'log_%a_%p.log'
skip 1
fields terminated by ';'
optionally enclosed by '"'
lrtrim
missing field values are null
reject rows with all null fields
(host_name,instance_name,starttijd,eindtijd,seconden,duration,wait_class,event)
)
location
( 'all_db_waits_incl_cpu.csv')
)
reject limit unlimited
/¶ verwerken .csv file(s)
Tabel machine_waits wordt gebruikt om een nieuwe aan te maken tabel all_db_waits_incl_cpu te vullen.
create table all_db_waits_incl_cpu
(
host_name varchar2(20),
instance_name varchar2(10),
starttijd varchar2(20),
eindtijd varchar2(20),
seconden number,
duration varchar2(10),
wait_class varchar2(120),
event varchar2(200)
);
insert into all_db_waits_incl_cpu select * from machine_waits;
commit;Nadat alle .csv files verzameld en verwerkt zijn bevat tabel all_db_waits_incl_cpu voor alle instances per databaseserver de informatie over wait events en CPU gebruik.
 |
 |
Met onderstaande query is voor databaseserver af201.wolk.care de data voor dag 10-10-2023 op te halen om in de eerder genoemde Excel te ‘plakken’.
with waits as
(
select instance_name,
wait_class,
sum(seconden) AS totaal_seconden
from all_db_waits_incl_cpu
where event not in ('TCP Socket (KGAS)')
and host_name='af201.wolk.care'
and starttijd between to_date('20231010 00:00', 'YYYYMMDD HH24:MI:SS') and to_date('20231010 23:59', 'YYYYMMDD HH24:MI:SS')
group by instance_name, starttijd, host_name, wait_class
)
select instance_name,
sum(case when wait_class = 'Application' then totaal_seconden else 0 end) as "Application",
sum(case when wait_class = 'Commit' then totaal_seconden else 0 end) as "Commit",
sum(case when wait_class = 'Concurrency' then totaal_seconden else 0 end) as "Concurrency",
sum(case when wait_class = 'Configuration' then totaal_seconden else 0 end) as "Configuration",
sum(case when wait_class = 'Network' then totaal_seconden else 0 end) as "Network",
sum(case when wait_class = 'Other' then totaal_seconden else 0 end) as "Other",
sum(case when wait_class = 'System I/O' then totaal_seconden else 0 end) as "System I/O",
sum(case when wait_class = 'User I/O' then totaal_seconden else 0 end) as "User I/O",
sum(case when wait_class = 'ON CPU' then totaal_seconden else 0 end) as "ON CPU"
from waits
group by instance_name
order by instance_name;In SQL Developer kan je alles in het ‘Query Result’ block selecteren en kopiëren.
¶ postgres alternatief
Om lokaal zonder oracle RDBMS hetzelfde te kunnen bereiken is een docker container met PostgreSQL een goed alternatief. Het aanmaken van een PostgreSQL en pgAdmin container wordt o.a. beschreven in https://belowthemalt.com/2021/06/09/run-postgresql-and-pgadmin-in-docker-for-local-development-using-docker-compose/
In PostgreSQL moeten 3 extensies worden ‘geïnstalleerd’ .
create extension postgres_fdw;
create extension file_fdw;
create server srv_file_fdw foreign data wrapper file_fdw;Vervolgens wordt een zgn. foreign table aangemaakt . Dit is het PostgreSQL alternatief voor een oracle external table.
create foreign table if not exists machine_waits_raw
( regel varchar(250) )
server srv_file_fdw
options ( filename '/var/tmp/all_DB_waits_incl_CPU.csv' , format 'csv');Standaard heeft PostgesSQL niet zoiets als badfile , waardoor rijen die niet voldoen aan de ‘beschrijving’ ge-skipped worden. Als dit nodig is moet je pgloader gebruiken, deze keuze heb ik niet gemaakt. In plaats hiervan heb ik een workaround met een view v_all_db_waits_incl_cpu gebruikt. Ook kent PostgreSQL geen varchar2 , waardoor het aanmaken van table all_db_waits_incl_cpu er iets anders uitziet.
create or replace view v_all_db_waits_incl_cpu
as
select
split_part(regel,';', 1) as host_name,
split_part(regel,';', 2) as instance_name,
split_part(regel,';', 3) as starttijd,
split_part(regel,';', 4) as eindtijd,
split_part(regel,';', 5) as seconden,
split_part(regel,';', 6) as duration,
split_part(regel,';', 7) as wait_class,
split_part(regel,';', 8) as event
from machine_waits_raw
where regel like 'a%';
create table all_db_waits_incl_cpu
( host_name varchar(20),
instance_name varchar(10),
starttijd date,
eindtijd date,
seconden int,
duration varchar(10),
wait_class varchar(120),
event varchar(200)
); Het insert statement ‘cast’ de kolommen.
insert into all_db_waits_incl_cpu (host_name, instance_name, starttijd, eindtijd, seconden,duration, wait_class, event)
select regexp_replace(host_name, '\s+$', ''),
regexp_replace(instance_name, '\s+$', ''),
to_timestamp(starttijd, 'DD-MM-YYYY HH24:MI'), to_timestamp(eindtijd, 'DD-MM-YYYY HH24:MI'), cast(seconden as integer),
regexp_replace(duration, '\s+$', ''),
regexp_replace(wait_class, '\s+$', ''),
regexp_replace(event, '\s+$', '')
from v_all_db_waits_incl_cpu;Vervolgens kan onderstaand statement gebruikt worden om de gegevens te verzamelen.
select
instance_name,
sum(case when wait_class = 'Application' THEN totaal_seconden else 0 end) as "Application",
sum(case when wait_class = 'Commit' THEN totaal_seconden else 0 end) as "Commit",
sum(case when wait_class = 'Concurrency' THEN totaal_seconden else 0 end) as "Concurrency",
sum(case when wait_class = 'Configuration' THEN totaal_seconden else 0 end) as "Configuration",
sum(case when wait_class = 'Network' THEN totaal_seconden else 0 end) as "Network",
sum(case when wait_class = 'Other' THEN totaal_seconden else 0 end) as "Other",
sum(case when wait_class = 'System I/O' THEN totaal_seconden else 0 end) AS "System I/O",
sum(case when wait_class = 'User I/O' THEN totaal_seconden else 0 end) aS "User I/O",
sum(case when wait_class = 'ON CPU' THEN totaal_seconden else 0 end) as "ON CPU"
from (
select
instance_name,
wait_class,
sum(seconden) as totaal_seconden
from all_db_waits_incl_cpu
where event not in ('TCP Socket (KGAS)')
and host_name='af202.wolk.care'
and starttijd between '2023-10-10 00:00' and '2023-10-10 23:59'
group by instance_name, starttijd, host_name, wait_class
) as subquery
group by instance_name
order by instance_name;
-- is hetzelfde als
with rgs as
(
select instance_name,
wait_class,
sum(seconden) AS totaal_seconden
from all_db_waits_incl_cpu
where event not in ('TCP Socket (KGAS)')
and host_name='af202.wolk.care'
and starttijd between '2023-10-10 00:00' and '2023-10-10 23:59'
group by instance_name, starttijd, host_name, wait_class
)
select instance_name,
sum(case when wait_class = 'Application' then totaal_seconden else 0 end) as "Application",
sum(case when wait_class = 'Commit' then totaal_seconden else 0 end) as "Commit",
sum(case when wait_class = 'Concurrency' then totaal_seconden else 0 end) as "Concurrency",
sum(case when wait_class = 'Configuration' then totaal_seconden else 0 end) as "Configuration",
sum(case when wait_class = 'Network' then totaal_seconden else 0 end) as "Network",
sum(case when wait_class = 'Other' then totaal_seconden else 0 end) as "Other",
sum(case when wait_class = 'System I/O' then totaal_seconden else 0 end) as "System I/O",
sum(case when wait_class = 'User I/O' then totaal_seconden else 0 end) as "User I/O",
sum(case when wait_class = 'ON CPU' then totaal_seconden else 0 end) as "ON CPU"
from rgs
group by instance_name
order by instance_name;