Jak sprawdzić bazę danych Oracle pod kątem długotrwałych zapytań

Moja aplikacja korzystająca z bazy danych Oracle działa wolno lub wygląda na to, że całkowicie się zatrzymała.

Jak mogę się dowiedzieć, które zapytania są najdroższe, abym mógł to zbadać dalej?

Ten pokazuje SQL, który jest obecnie „AKTYWNY”: -

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

To pokazuje zamki. Czasami sprawy idą wolno, ale dzieje się tak, ponieważ jest zablokowany, czekając na blokadę:

select
  object_name, 
  object_type, 
  session_id, 
  type,         -- Type or system/user lock
  lmode,        -- lock mode in which session holds lock
  request, 
  block, 
  ctime         -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

Jest to dobre rozwiązanie do wyszukiwania długich operacji (np. Pełne skanowanie tabeli). Jeśli dzieje się tak z powodu wielu krótkich operacji, nic się nie pojawi.

COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/

Spróbuj tego, a otrzymasz zapytania trwające obecnie dłużej niż 60 sekund. Zauważ, że drukuje wiele wierszy na uruchomione zapytanie, jeśli SQL ma wiele wierszy. Spójrz na sid, numer seryjny, aby zobaczyć, co należy do siebie.

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece

v $ session_longops

Jeśli szukasz sofar! = Totalwork, zobaczysz te, które nie zostały ukończone, ale wpisy nie zostaną usunięte po zakończeniu operacji, więc możesz tam również zobaczyć dużo historii.

Step 1:Execute the query

column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session a, v$process b, v$transaction c,
v$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time
/   

Step 2: desc v$session

Step 3:select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)

Step 4: select sql_text from v$sqltext where address='XXXXXXXX';

Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;

Możesz wygenerować raport AWR (automatyczne repozytorium obciążenia) z bazy danych.

Uruchom z wiersza poleceń SQL * Plus:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Przeczytaj dokument dotyczący sposobu generowania i rozumienia raportu AWR. Zapewnia pełny obraz wydajności bazy danych i problemów z zasobami. Gdy już zapoznamy się z raportem AWR, pomocne będzie znalezienie Top SQL, który zużywa zasoby.

Ponadto w interfejsie użytkownika 12C EM Express możemy wygenerować AWR.

Możesz sprawdzić szczegóły długo działających zapytań, takich jak% ukończonych i pozostały czas, używając poniższego zapytania:

 SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR, 
 TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" 
 FROM V$SESSION_LONGOPS 
 WHERE OPNAME NOT LIKE '%aggregate%' 
       AND TOTALWORK != 0 
       AND SOFAR <> TOTALWORK;

Pełną listę kroków rozwiązywania problemów można znaleźć tutaj: Rozwiązywanie problemów z długimi sesjami

select sq.PARSING_SCHEMA_NAME, sq.LAST_LOAD_TIME, sq.ELAPSED_TIME, sq.ROWS_PROCESSED, ltrim(sq.sql_text), sq.SQL_FULLTEXT
  from v$sql sq, v$session se
 order by sq.ELAPSED_TIME desc, sq.LAST_LOAD_TIME desc;

Możesz użyć widoku v $ sql_monitor, aby znaleźć zapytania działające dłużej niż 5 sekund. Może to być dostępne tylko w wersjach Enterprise Oracle. Na przykład to zapytanie zidentyfikuje wolno działające zapytania z mojej usługi TEST_APP:

select to_char(sql_exec_start, 'dd-Mon hh24:mi'), (elapsed_time / 1000000) run_time,
       cpu_time, sql_id, sql_text 
from   v$sql_monitor
where  service_name = 'TEST_APP'
order  by 1 desc;

Zwróć uwagę, że czas elapsed_time jest wyrażony w mikrosekundach, więc / 1000000, aby uzyskać coś bardziej czytelnego