Oracle ideas

cardinality и фильтрация через коллекцию, подсказка оптимизатору на использование индекса.

create table test(
id_pok number not null,
val number,
constraint uk_test unique(id_pok)
);

insert into test
select rownum as id_pok,
round(dbms_random.value(0,100)) as val
from dual
connect by rownum<=1000000

commit;

create or replace type type_n is table of number;

begin
dbms_stats.gather_table_stats(user,tabname => 'test', cascade => true);
end;

--slr:1800
select sum(t.val)
from test t
where t.id_pok in (select column_value from table(type_n(10,20,30,40,50)) f)

SELECT STATEMENT, GOAL = ALL_ROWS
SORT AGGREGATE
HASH JOIN RIGHT SEMI
COLLECTION ITERATOR CONSTRUCTOR FETCH
TABLE ACCESS FULL TEST

--slr:17
select sum(t.val)
from test t
where t.id_pok in (select /*+ cardinality(f,5) */ column_value from table(type_n(10,20,30,40,50)) f )

SELECT STATEMENT, GOAL = ALL_ROWS
SORT AGGREGATE
NESTED LOOPS
NESTED LOOPS
SORT UNIQUE
COLLECTION ITERATOR CONSTRUCTOR FETCH
INDEX UNIQUE SCAN UK_TEST
TABLE ACCESS BY INDEX ROWID TEST

Генерация csv в clob, парсинг

1.Создание таблицы

create table TEST_JDBC_BLOB
(
img_id VARCHAR2(32),
image CLOB
)

2. Пополнение случайными данными
declare
src clob;
dest clob;
begin
SELECT image INTO dest
FROM TEST_JDBC_BLOB
WHERE img_id = 'mockdata.csv' FOR UPDATE;

for i in 1..5000 loop

select to_clob(
to_char(i)||';'||
(case when round(dbms_random.value(1,7))!=7 then dbms_random.string('x',round(dbms_random.value(1,10))) end)||';'||
(case when round(dbms_random.value(1,7))!=7 then dbms_random.string('x',round(dbms_random.value(1,10))) end)||';'||
(case when round(dbms_random.value(1,7))!=7 then dbms_random.string('x',round(dbms_random.value(1,10))) end)||';'||
(case when round(dbms_random.value(1,7))!=7 then dbms_random.string('x',round(dbms_random.value(1,10))) end)||';'||
(case when round(dbms_random.value(1,7))!=7 then dbms_random.string('x',round(dbms_random.value(1,10))) end)||';'||
(case when round(dbms_random.value(1,7))!=7 then round(dbms_random.value(1,1000)) end)||chr(10)
)
into src
from dual;
DBMS_LOB.APPEND(dest, src);
end loop;
COMMIT;
end;

3. чтение csv
select x.*
from test_jdbc_blob t
, xmltable('/a/b'
passing xmlparse(document
'<a><c>' ||
replace(
replace(
dbms_xmlgen.convert(t.image)
, ';'
, '</c><c>'
)
, chr(10)
, '</c><c>'
) ||
'</c></a>'
wellformed
)
columns col1 varchar2(4000) path 'c[1]'
, col2 varchar2(4000) path 'c[2]'
, col3 varchar2(4000) path 'c[3]'
, col4 varchar2(4000) path 'c[4]'
, col5 varchar2(4000) path 'c[5]'
, col6 varchar2(4000) path 'c[6]'
, col7 varchar2(4000) path 'c[7]'
) x
where t.img_id = 'mockdata.csv'

4. Генерируем 200Мб CSV. Контроль размера.

select round(s.bytes/1024/1024) as SizeCsVMb
from
user_segments s
where s.segment_name=(select l.segment_name
from user_lobs l
where l.table_name='TEST_JDBC_BLOB')

5. Количество записей и время полного чтения через ctas.

create table delit_test_numrows_time as
select x.*
from test_jdbc_blob t
, xmltable('/a/b'
passing xmlparse(document
'<a><c>' ||
replace(
replace(
dbms_xmlgen.convert(t.image)
, ';'
, '</c><c>'
)
, chr(10)
, '</c><c>'
) ||
'</c></a>'
wellformed
)
columns col1 varchar2(4000) path 'c[1]'
, col2 varchar2(4000) path 'c[2]'
, col3 varchar2(4000) path 'c[3]'
, col4 varchar2(4000) path 'c[4]'
, col5 varchar2(4000) path 'c[5]'
, col6 varchar2(4000) path 'c[6]'
, col7 varchar2(4000) path 'c[7]'
) x

where t.img_id = 'mockdata.csv'

Проверка будет ли использоваться индекс, без создания

1) Разные индексы на одинаковых данных. Влияние распределения данных.
2) Виртуальный индекс.

1)
clear;

drop table t1;
drop table t2;
drop table src;

create table t1(
id number,
pok number,
val number
);

create table t2(
id number,
pok number,
val number
);

create table src(
id number,
pok number,
val number
);

insert into src
select rownum as id,
round(dbms_random.value(1,100)) as pok,
round(dbms_random.value(1,100000)) as val
from dual
connect by rownum <=100000;

commit;

insert into t1
select * from src order by pok;

insert into t2
select * from src order by dbms_random.value(0,1);

commit;

create index it1 on t1(pok);

create index it2 on t2(pok);

begin
dbms_stats.gather_index_stats(user,indname => 'IT1');
dbms_stats.gather_index_stats(user,indname => 'IT2');
end;
/

-------------

select *

from

user_indexes i

where i.INDEX_NAME in ('IT1','IT2')

INDEX_NAME AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

IT1 2 295

IT2 240 24047

-------------

Разный план, на одинаковых таблицах и полностью одинаковых данных, толкьо по разному распределенных.

select sum(1),sum(val) from t1 where t1.pok=5 Index range scan

select sum(1),sum(val) from t2 where t2.pok=5 Table full scan

2) Удалчяем индекс, смотрим план, создаём вирутальный индекс, смотирм план.

-----------------

clear;

set autotrace on explain;

set serveroutput on;

drop index it1;

EXPLAIN PLAN FOR (select sum(1),sum(val) from t1 where t1.pok=5);

SELECT * FROM table(dbms_xplan.display);

create index it1 on t1(pok) nosegment;

alter session set "_use_nosegment_indexes" = true;

EXPLAIN PLAN FOR (select sum(1),sum(val) from t1 where t1.pok=5);

SELECT * FROM table(dbms_xplan.display);

---------------

Вывод:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as dbadmin@NSSER_HARD

Cannot SET AUTOTRACE

Index dropped

Explained

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 13 (8)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | TABLE ACCESS FULL| T1 | 1556 | 40456 | 13 (8)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."POK"=5)
Note
-----
- dynamic sampling used for this statement (level=2)

18 rows selected

Index created

Session altered

Explained

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3461075613
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (0)| 00:00
| 1 | SORT AGGREGATE | | 1 | 26 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1556 | 40456 | 5 (0)| 00:00
|* 3 | INDEX RANGE SCAN | IT1 | 441 | | 1 (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."POK"=5)
Note
-----
- dynamic sampling used for this statement (level=2)

library cache pin When you drop or recomile object

Search holder:

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
sys.x_$kglpn pn,
v$session ses,
sys.x_$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc

XSD XML

1) Регистрация схемы из файла в directory

BEGIN
DBMS_XMLSCHEMA.deleteSchema('RIGF.xsd',4);

DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'RIGF.xsd',
SCHEMADOC => bfilename('XMLLOAD','RIGF.xsd'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => TRUE
);

commit;
END;

SELECT *
FROM ALL_XML_SCHEMAS s
where s.owner='EGRIP'

begin
dbms_xmlschema.compileSchema(schemaURL => 'RIGF.xsd');
end;

2) Создание таблицы на основе корневого элемента xsd, пополнение из clob

CREATE TABLE po_tab (id NUMBER, xmlcol XMLType)
XMLType COLUMN xmlcol
XMLSCHEMA "RIGF.xsd"
ELEMENT "ROOTEL";

begin
for ds in (select * from xml_files where id=50) loop
INSERT INTO po_tab VALUES(ds.id, XMLType(ds.xml_file));
end loop;
end;

3) Создание ресурса,

--[1]
BEGIN
--DBMS_XMLSCHEMA.deleteSchema('RIGF.xsd',4);
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'RIGF.xsd',
SCHEMADOC => bfilename('XMLLOAD','RIGF.xsd'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => TRUE
);
commit;
END;

==========================================

--[2]
DROP TABLE egrip_xml;

CREATE TABLE egrip_xml(id NUMBER,
nyear NUMBER,
file_name VARCHAR2(128),
xmlcol XMLType)
XMLType COLUMN xmlcol STORE AS CLOB
XMLSCHEMA "RIGF.xsd"
ELEMENT "EGRIP";

/*
create sequence S_EGRIP
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
*/

--[3] задание имен файлов
insert into egrip_xml(id,nyear,file_name)
select
rownum as id,
to_number(substr(t.filename,12,4)) as nyear,
t.filename
from
(
SELECT 'EGRIP_FULL_2016-01-02_71710.XML' as FILENAME FROM DUAL UNION ALL
SELECT 'EGRIP_FULL_2016-01-02_71803.XML' as FILENAME FROM DUAL
) t

--[4] загрузка файлов xml из директории
DECLARE
src_loc BFILE;
src_offset NUMBER := 1;
dst_offset NUMBER := 1;
src_osin NUMBER;
dst_osin NUMBER;
bytes_rd NUMBER;
bytes_wt NUMBER;
w number;
xml_data clob;
BEGIN

for ds in (select fl.* from egrip_xml fl)
loop
src_loc:= bfilename('XMLLOAD',ds.file_name);

DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.fileopen (src_loc, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (xml_data, src_loc, DBMS_LOB.getlength (src_loc));
DBMS_LOB.fileclose (src_loc);

update egrip_xml t
set t.xmlcol = XMLType(xml_data)
where t.id = ds.id;

COMMIT;
end loop;
END;

--[5]
/*
Сравнение быстродействия!
*/

--[На прямую]
select distinct
--t.ID,
extractValue(ds_svip.COLUMN_VALUE,'/СвИП/@ИННФЛ') as INNFL,
extractValue(ds_svip.COLUMN_VALUE,'/СвИП/СвФЛ/ФИОРус/@Фамилия') as SURNAME
from (
------------------------------------------------------------------------------------
select f.id,
XMLType(f.xml_file) as xmlcol
from xml_files f
------------------------------------------------------------------------------------
) t,
table(XMLSequence(t.xmlcol.extract('/EGRIP/СвИП'))) ds_svip,
table(XMLSequence(ds_svip.extract('/СвИП/СвОКВЭД'))) ds_svip_okved,
table(XMLSequence(ds_svip_okved.extract('/СвОКВЭД/СвОКВЭДДоп'))) ds_svip_okved_dop
where
extractValue(ds_svip_okved_dop.COLUMN_VALUE,'/СвОКВЭДДоп/@НаимОКВЭД')='УПРАВЛЕНИЕ ФИНАНСОВЫМИ РЫНКАМИ'

--[через схему]
select distinct
--t.ID,
extractValue(ds_svip.COLUMN_VALUE,'/СвИП/@ИННФЛ') as INNFL,
extractValue(ds_svip.COLUMN_VALUE,'/СвИП/СвФЛ/ФИОРус/@Фамилия') as SURNAME
from (
------------------------------------------------------------------------------------
select f.*
from egrip_xml f
------------------------------------------------------------------------------------
) t,
table(XMLSequence(t.xmlcol.extract('/EGRIP/СвИП'))) ds_svip,
table(XMLSequence(ds_svip.extract('/СвИП/СвОКВЭД'))) ds_svip_okved,
table(XMLSequence(ds_svip_okved.extract('/СвОКВЭД/СвОКВЭДДоп'))) ds_svip_okved_dop
where
extractValue(ds_svip_okved_dop.COLUMN_VALUE,'/СвОКВЭДДоп/@НаимОКВЭД')='УПРАВЛЕНИЕ ФИНАНСОВЫМИ РЫНКАМИ'

Пример парсинга XML

select
t.ID,
--ds_ip.COLUMN_VALUE as egrip_ip_xml,
extractValue(ds_ip.COLUMN_VALUE,'/IP/@IDDOK') as IP_IDDOK,
extractValue(ds_ip.COLUMN_VALUE,'/IP/@IDIP') as IP_IDIP,
extractValue(ds_ip.COLUMN_VALUE,'/IP/@OGRNIP') as IP_OGRNIP,
extractValue(ds_ip.COLUMN_VALUE,'/IP/@INN') as IP_INN,
--
extractValue(ds_ip_okved.COLUMN_VALUE,'/OKVED/@KOD_OKVED') as KOD_OKVED,
extractValue(ds_ip_okved.COLUMN_VALUE,'/OKVED/@MAIN') as MAIN,
extractValue(ds_ip_okved.COLUMN_VALUE,'/OKVED/@NAME') as NAME
--
from (
------------------------------------------------------------------------------------
select ID,
xmltype(replace(t.XML_FILE,'xmlns="http://granit.ru/integration"','')) as XML
from list_of_xml_for_load t
where t.id in (
1
-- ,2
-- ,586 -- 65 okveds
-- ,585 -- 10 okveds
)
------------------------------------------------------------------------------------
) t,
table(XMLSequence(t.xml.extract('/EGRIP_IP_DATA/IP'))) ds_ip
,table(XMLSequence(ds_ip.COLUMN_VALUE.extract('/IP/OKVED'))) ds_ip_okved

Загрузка XML как CLOB из директории

DECLARE
src_loc BFILE;
dst_loc CLOB;
src_offset NUMBER := 1;
dst_offset NUMBER := 1;
src_osin NUMBER;
dst_osin NUMBER;
bytes_rd NUMBER;
bytes_wt NUMBER;
w number;
xml_data clob;
BEGIN

for ds in (select fl.* from list_of_xml_for_load fl where fl.file_name in ('RIV_M_77066_151021_280.xml','RIV_M_77066_151118_16.xml'))
loop
dst_loc := ds.xml_file;
src_loc:= bfilename('XMLLOAD',ds.file_name);

DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.fileopen (src_loc, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (xml_data, src_loc, DBMS_LOB.getlength (src_loc));
DBMS_LOB.fileclose (src_loc);

update list_of_xml_for_load t
set t.xml_file=xml_data
where t.id = ds.id;

COMMIT;
end loop;

END ;

------------------------------------
лист файлов BAT

@echo off
for /r %%i in (*.xml) do (
rem Выводим имя файла
echo "SELECT "%%i" as FILENAME FROM DUAL UNION ALL " >> cmd.txt
)

Базовый пример получения имен полей запроса

DECLARE
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
cols DBMS_SQL.desc_tab;
ncols PLS_INTEGER;
BEGIN
DBMS_SQL.PARSE
(cur, '
SELECT
(select q.n_order from tmp_sort_report_pok q where q.id = fd.id_pok ) as n_order_pok
,nvl(msk_analytics.get_number(ID_COL_6965),0) as sort_val,fd.id_pok,
fd.id_oiv,
fd.id_okrug,
fd.id_region,
fd.id_complex,
-- fd.id_gp,
fd.id_expense,
fd.id_customer,
r.ID_COL_121,r.ID_COL_132,r.ID_COL_158,r.ID_COL_2201,r.ID_COL_2282,r.ID_COL_3961,r.ID_COL_3962,r.ID_COL_3966,r.ID_COL_406,r.ID_COL_4081,r.ID_COL_4186,r.ID_COL_444,r.ID_COL_5440,r.ID_COL_5525,r.ID_COL_5678,r.ID_COL_6025,r.ID_COL_6041,r.ID_COL_6220,r.ID_COL_6282,r.ID_COL_6460,r.ID_COL_6544,r.ID_COL_6584,r.ID_COL_6585,r.ID_COL_6683,r.ID_COL_6684,r.ID_COL_6724,r.ID_COL_6725,r.ID_COL_6726,r.ID_COL_6764,r.ID_COL_6904,r.ID_COL_6905,r.ID_COL_6906,r.ID_COL_6964,r.ID_COL_6965,r.ID_COL_7046,r.ID_COL_7047,r.ID_COL_7048,r.ID_COL_7058,r.ID_COL_7065,r.ID_COL_7089,r.ID_COL_7264,r.ID_COL_7332,r.ID_COL_7404,r.ID_COL_7409,r.ID_COL_7424,r.ID_COL_7425,r.ID_COL_7426,r.ID_COL_7427,r.ID_COL_7428,r.ID_COL_7429,r.ID_COL_7430,r.ID_COL_7431,r.ID_COL_7432,r.ID_COL_7433,r.ID_COL_7435,r.ID_COL_7436,r.ID_COL_7453,r.ID_COL_7534
FROM R_5618_20150501_15_1 r, /* отчет-дата-бюджет-версия расчета */
iot_rep_fix_dims fd
WHERE
r.id_row = fd.id_row
and fd.ID_GP in (select to_number(FILTER_VALUE) from REP_FILTERS where WEB_IDENTIFIER = ''d03f75814107ac5b6ce84f9035979a3c'' and DIMENSION_NAME = ''ID_GP'')
and fd.ID_OIV in (select to_number(FILTER_VALUE) from REP_FILTERS where WEB_IDENTIFIER = ''d03f75814107ac5b6ce84f9035979a3c'' and DIMENSION_NAME = ''ID_OIV'')
and fd.ID_POK in (select to_number(FILTER_VALUE) from REP_FILTERS where WEB_IDENTIFIER = ''d03f75814107ac5b6ce84f9035979a3c'' and DIMENSION_NAME = ''ID_POK'')

and
id_okrug = 0 and
id_region = 0 and
(
(id_oiv in (100500,100501,100600,100601) and id_complex = -1) or
(id_oiv not in (100500,100501,100600,100601) and id_complex != -1)
)
order by 1,2 asc

', DBMS_SQL.NATIVE);

DBMS_SQL.DESCRIBE_COLUMNS (cur, ncols, cols);
FOR colind IN 1 .. ncols
LOOP
DBMS_OUTPUT.PUT_LINE (cols(colind).col_name);
END LOOP;
DBMS_SQL.CLOSE_CURSOR (cur);
END;

N_ORDER_POK
SORT_VAL
ID_POK
ID_OIV
ID_OKRUG
ID_REGION
ID_COMPLEX
ID_EXPENSE
ID_CUSTOMER
ID_COL_121
ID_COL_132
ID_COL_158
ID_COL_2201

ID_COL_2282

БЛОКИРОВКИ РАЗНЫЕ

Взято от сюда

	select sid,blocking_session,username,sql_id,event,machine,osuser,program,last_call_et from v$session where blocking_session > 0;

	select * from dba_blockers
	select * from dba_waiters

	-- Find what the blocking session is doing
	select sid,blocking_session,username,sql_id,event,state,machine,osuser,program,last_call_et from v$session where sid=746 ;

	-- Find the blocked objects
	select owner,object_name,object_type from dba_objects where object_id in (select object_id from v$locked_object where session_id=271 and locked_mode =3);


	-- Friendly query for who is blocking who
	-- Mostly for versions before v$session had blocking_session column
	select s1.inst_id,s2.inst_id,s1.username \|\| '@' \|\| s1.machine
	\|\| ' ( SID=' \|\| s1.sid \|\| ' ) is blocking '
	\|\| s2.username \|\| '@' \|\| s2.machine \|\| ' ( SID=' \|\| s2.sid \|\| ' ) ' AS blocking_status
	from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
	where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
	and l1.BLOCK=1 and l2.request > 0
	and l1.id1 = l2.id1
	and l2.id2 = l2.id2
	order by s1.inst_id;


	-- find blocking sessions that were blocking for more than 15 minutes + objects and sql
	select s.SID,p.SPID,s.machine,s.username,CTIME/60 as minutes_locking, do.object_name as locked_object, q.sql_text
	from v$lock l
	join v$session s on l.sid=s.sid
	join v$process p on p.addr = s.paddr
	join v$locked_object lo on l.SID = lo.SESSION_ID
	join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID
	join v$sqlarea q on s.sql_hash_value = q.hash_value and s.sql_address = q.address
	where block=1 and ctime/60>15

	-- Check who is blocking who in RAC
	SELECT DECODE(request,0,'Holder: ','Waiter: ') \|\| sid sess, id1, id2, lmode, request, type
	FROM gv$lock
	WHERE (id1, id2, type) IN (
	SELECT id1, id2, type FROM gv$lock WHERE request>0)
	ORDER BY id1, request;

	-- Check who is blocking who in RAC, including objects
	SELECT DECODE(request,0,'Holder: ','Waiter: ') \|\| gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
	FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
	join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
	join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID
	WHERE (id1, id2, gv$lock.type) IN (
	SELECT id1, id2, type FROM gv$lock WHERE request>0)
	ORDER BY id1, request;




	-- Who is blocking who, with some decoding
	select sn.USERNAME,
	m.SID,
	sn.SERIAL#,
	m.TYPE,
	decode(LMODE,
	0, 'None',
	1, 'Null',
	2, 'Row-S (SS)',
	3, 'Row-X (SX)',
	4, 'Share',
	5, 'S/Row-X (SSX)',
	6, 'Exclusive') lock_type,
	decode(REQUEST,
	0, 'None',
	1, 'Null',
	2, 'Row-S (SS)',
	3, 'Row-X (SX)',
	4, 'Share',
	5, 'S/Row-X (SSX)',
	6, 'Exclusive') lock_requested,
	m.ID1,
	m.ID2,
	t.SQL_TEXT
	from v$session sn,
	v$lock m ,
	v$sqltext t
	where t.ADDRESS = sn.SQL_ADDRESS
	and t.HASH_VALUE = sn.SQL_HASH_VALUE
	and ((sn.SID = m.SID and m.REQUEST != 0)
	or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
	(select s.ID1, s.ID2
	from v$lock S
	where REQUEST != 0
	and s.ID1 = m.ID1
	and s.ID2 = m.ID2)))
	order by sn.USERNAME, sn.SID, t.PIECE

	-- Who is blocking who, with some decoding
	select OS_USER_NAME os_user,
	PROCESS os_pid,
	ORACLE_USERNAME oracle_user,
	l.SID oracle_id,
	decode(TYPE,
	'MR', 'Media Recovery',
	'RT', 'Redo Thread',
	'UN', 'User Name',
	'TX', 'Transaction',
	'TM', 'DML',
	'UL', 'PL/SQL User Lock',
	'DX', 'Distributed Xaction',
	'CF', 'Control File',
	'IS', 'Instance State',
	'FS', 'File Set',
	'IR', 'Instance Recovery',
	'ST', 'Disk Space Transaction',
	'TS', 'Temp Segment',
	'IV', 'Library Cache Invalidation',
	'LS', 'Log Start or Switch',
	'RW', 'Row Wait',
	'SQ', 'Sequence Number',
	'TE', 'Extend Table',
	'TT', 'Temp Table', type) lock_type,
	decode(LMODE,
	0, 'None',
	1, 'Null',
	2, 'Row-S (SS)',
	3, 'Row-X (SX)',
	4, 'Share',
	5, 'S/Row-X (SSX)',
	6, 'Exclusive', lmode) lock_held,
	decode(REQUEST,
	0, 'None',
	1, 'Null',
	2, 'Row-S (SS)',
	3, 'Row-X (SX)',
	4, 'Share',
	5, 'S/Row-X (SSX)',
	6, 'Exclusive', request) lock_requested,
	decode(BLOCK,
	0, 'Not Blocking',
	1, 'Blocking',
	2, 'Global', block) status,
	OWNER,
	OBJECT_NAME
	from v$locked_object lo,
	dba_objects do,
	v$lock l
	where lo.OBJECT_ID = do.OBJECT_ID
	AND l.SID = lo.SESSION_ID
	and block=1