Приложение А. Полезные сценарии

§ Какой SQL-оператор выполняет задание (job) и выполняет ли оно что-нибудь вообще:

column status format a10

set feedback off

set serveroutput on

select username, sid, serial#, process, status

from v$session

where username is not null

/

column username format a20

column sql_text format a55 word_wrapped

set serveroutput on size 1000000

declare

x number;

begin

for x in

(select username||'('||sid||', '||serial#||

') ospid = ' || process ||

' program = ' || program username,

to_char(LOGON_TIME, ' Day HH24: MI') logon_time,

to_char(sysdate, ' Day HH24: MI') current_time,

sql_address, LAST_CALL_ET

from v$session

where status = 'ACTIVE'

and rawtohex(sql_address) < > '00'

and username is not null order by last_call_et)

loop

for y in (select max(decode(piece, 0, sql_text, null)) ||

max(decode(piece, 1, sql_text, null)) ||

max(decode(piece, 2, sql_text, null)) ||

max(decode(piece, 3, sql_text, null))

sql_text

from v$sqltext_with_newlines

where address = x.sql_address

and piece < 4)

loop

if (y.sql_text not like '%listener.get_cmd%' and

y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')

then

dbms_output.put_line('--------------------');

dbms_output.put_line(x.username);

dbms_output.put_line(x.logon_time || ' ' ||

x.current_time||

' last et = ' ||

x.LAST_CALL_ET);

dbms_output.put_line(

substr(y.sql_text, 1, 250));

end if;

end loop;

end loop;

end;

/

column username format a15 word_wrapped

column module format a15 word_wrapped

column action format a15 word_wrapped

column client_info format a30 word_wrapped

select username||'('||sid||', '||serial#||')' username,

module,

action,

client_info

from v$session

§ Сравнительный анализ выполнения фиксаций транзакций

set echo on

drop table t;

create table t (x int);

set serveroutput on

declare

l_start number default dbms_utility.get_time;

begin

for i in 1.. 100000

loop

insert into t values (1);

end loop;

commit;

dbms_output.put_line

((dbms_utility.get_time-l_start)/100 || ' секунд');

end;

/

declare

l_start number default dbms_utility.get_time;

begin

for i in 1.. 100000

loop

insert into t values (1);

commit;

end loop;

dbms_output.put_line

((dbms_utility.get_time-l_start)/100 || ' секунд');

end;

/

§ Получение списка файлов для холодного бэкапа:

spool filelist_for_backup

SELECT DECODE(f.name, null,

RTRIM(t.NAME)||' - '||'!! for the temporary datafiles look up in V$TEMPFILE!! ',

RTRIM(t.NAME)||' - '||RTRIM(f.NAME)) " Tablespace + Datafiles"

FROM V$TABLESPACE t, V$DATAFILE f

WHERE t.TS# = f.TS#(+)

ORDER BY t.NAME

/

SELECT NAME " Temporary datafiles" from V$TEMPFILE;

SELECT MEMBER " Redo logs" FROM V$LOGFILE;

SELECT NAME " Control files" FROM V$CONTROLFILE;

spool off

§ Сравнение генерируемой информации в журналах повтора

column value new_value old_value

set echo on

create or replace view redo_size

as

select value

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and v$statname.name = 'redo size';

drop table test1;

select value from redo_size;

create table test1

as select * from all_objects

/

select value-& old_value REDO_GENERATED from redo_size;

drop table test1;

select value from redo_size;

create table test1

NOLOGGING

as select * from all_objects

/

select value-& old_value REDO_GENERATED from redo_size;

§ Свойства пользовательского окружения

select

sys_context('userenv', 'current_user') cur_user,

sys_context('userenv', 'session_user') ses_user,

sys_context('userenv', 'ip_address') ip,

sys_context('userenv', 'network_protocol') prot,

sys_context('userenv', 'proxy_user') prox,

sys_context('userenv', 'db_domain') domain,

sys_context('userenv', 'os_user') os,

sys_context('userenv', 'terminal') terminal,

sys_context('userenv', 'db_name') db,

sys_context('userenv', 'host') host,

sys_context('userenv', 'authentication_type') AUT_type,

sys_context('userenv', 'authentication_data') AUT_data

from dual

§ Среднее время ожидания процессоров-диспетечеров

COLUMN wait_time FORMAT 99999 heading " Average Wait"

COLUMN protocol FORMAT A60 heading " Protocol"

SELECT network protocol,

DECODE (sum(totalq), 0, ' No responses',

TO_CHAR(sum(wait)/sum(totalq)*100, '9.99') ||

' Sec Wait Per Response') wait_time

FROM v$queue q, v$dispatcher d

WHERE q.type = 'DISPATCHER'

AND q.paddr = d.paddr

GROUP BY network;

§ Пользователи и их текущие транзакции

Select s.username, s.machine, s.terminal, s.program,

t.xidusn " Undo segment number",

t.used_ublk " Number of undo blocks used",

t.used_urec " Number of undo records used",

t.status,

t.start_time,

t.start_scnb,

t.log_io,

t.phy_io,

t.cr_get,

t.cr_change

from v$session s, v$transaction t

WHERE addr = taddr

§ Некоторые полезные свойства БД, к которой подключены:

set echo off feedback off pagesize 50 term off linesize 80

column Property_Name format a24

column Property_Value format a40

column Description format a80

spool db_props.txt

select Property_Name,

Property_Value,

Description

from database_properties;

spool off

set feedback 6 pagesize 24 term on

clear columns

§ Наиболее часто используемые SQL-выражения. Поиск по числу выполнений с обращением к кэшу данных.

SET LINESIZE 1200
SET PAGESIZE 20000
SET TRIMSPOOL ON;
SPOOL./bad_sql.log
PROMPT Critical SQL statements with
PROMPT High Buffer Gets to Executions Ratio
SELECT buffer_gets/(executions+1),
executions,
buffer_gets,
sql_text
FROM v$SQLAREA
WHERE buffer_gets/(executions+1) > = 20
ORDER BY 1 DESC;
SPOOL OFF;

§ Состояние объектов БД (valid/invalid)

SELECT DISTINCT (object_type) object, status, COUNT(*)

FROM user_objects

GROUP BY object_type, status;

§ Просмотр привилегий, данных через роли и напрямую

CREATE VIEW user_role_hierarchy
AS
SELECT u2.name granted_role
FROM (SELECT * FROM sys.sysauth$
CONNECT BY PRIOR privilege# = grantee#
START WITH grantee# = uid OR grantee# = 1) sa, sys.user$ u2
WHERE u2.user#=sa.privilege#
UNION ALL SELECT user FROM DUAL
UNION ALL SELECT 'PUBLIC' FROM DUAL
/

GRANT SELECT ON user_role_hierarchy TO PUBLIC;

Теперь с помощью этого представления можно выяснить объектные и системные привилегии для каждого пользователя в системе:

SELECT DISTINCT privilege, owner, table_name
FROM dba_tab_privs
WHERE grantee IN (SELECT * FROM user_role_hierarchy);

select distinct privilege
from dba_sys_privs
where grantee in (select * from user_role_hierarchy);

§ Сводка по блокировкам в системе:

SELECT s.username, s.SID, l.TYPE, l.id1, l.id2, l.lmode, l.request,

p.spid pid

FROM v$lock l, v$session s, v$process p

where s.sid = l.sid

and p.addr = s.paddr

and s.username is not null order by id1, s.sid, request

§ Сессии, ожидающие получения блокировки:

select event, p1, p2, p3 from v$session_wait
where wait_time=0 and event='enqueue';

§ Сессии, ожидающие блокировки для начала транзакции (TX-блокировки):

select * from v$lock where type='TX' and request> 0;

§ Сессии, удерживающие блокировки транзакции (TX-блокировки):

select * from v$lock where type='TX' and lmode> 0;