|
PL/SQL Получение списка таблиц из запроса
#39346609
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Новосибирск
Сообщения: 1 759
|
|
КомпМаг,
explain plan+dba_objects в данной задаче имеет ряд ограничений.
Например, не ясно, какой результат нужно получить, если сработал join elimination.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
SQL> def l_sqlstr="select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x"
SQL>
SQL> create table parent(x int primary key);
Table created.
SQL>
SQL> create view vw_parent
2 as
3 select * from parent;
View created.
SQL>
SQL> create table child(x int, c int,
2 constraint child_parent_fk foreign key(x) references parent);
Table created.
SQL>
SQL> create materialized view mv_child
2 build immediate
3 disable query rewrite
4 as
5 select count(c.x) x_count
6 from vw_parent p,
7 child c
8 where p.x = c.x;
Materialized view created.
SQL>
SQL> explain plan set statement_id = 'Q' for &l_sqlstr.;
old 1: explain plan set statement_id = 'Q' for &l_sqlstr.
new 1: explain plan set statement_id = 'Q' for select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x
Explained.
SQL>
SQL> select listagg(full_table_name, ', ') within group(order by full_table_name) tbl_list
2 from (select distinct o.owner || '.' ||
3 decode(o.object_type,
4 'INDEX',
5 (select table_name
6 from dba_indexes i
7 where i.owner = o.owner
8 and i.index_name = o.object_name),
9 o.object_name) full_table_name
10 from dba_objects o
11 where (owner, object_name) in
12 (select distinct p.object_owner, p.object_name
13 from plan_table p
14 where p.statement_id = 'Q'
15 and p.object_name is not null)
16 and o.data_object_id is not null);
TBL_LIST
----------------------------------------------------------------------------------------------------------------------------------
TC.CHILD
1 row selected.
SQL>
SQL> select plan_table_output from table(dbms_xplan.display( statement_id=> 'Q', format=> 'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3223756890
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| CHILD |
------------------------------------
9 rows selected.
Можно было бы раскручивать зависимости по DBA_DEPENDENCIES:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
SQL> create or replace procedure p
2 is
3 cursor csr is
4 &l_sqlstr.;
5 begin
6 null;
7 end;
8 /
old 4: &l_sqlstr.;
new 4: select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x;
Procedure created.
SQL>
SQL> col referenced_owner for a20
SQL> col referenced_name for a20
SQL> col referenced_type for a20
SQL> select referenced_owner, referenced_name, referenced_type
2 from dba_dependencies
3 where owner = user
4 and name = 'P'
5 and type = 'PROCEDURE'
6 and referenced_type not in ('PACKAGE');
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE
-------------------- -------------------- --------------------
TC VW_PARENT VIEW
TC CHILD TABLE
Но тут возникают нюансы, например, с materialized view, которые учитываются в runtime:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
SQL> alter materialized view mv_child enable query rewrite;
Materialized view altered.
SQL>
SQL>
SQL> explain plan set statement_id = 'Q' for &l_sqlstr.;
old 1: explain plan set statement_id = 'Q' for &l_sqlstr.
new 1: explain plan set statement_id = 'Q' for select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x
Explained.
SQL> select * from table(dbms_xplan.display( statement_id=> 'Q', format=> 'basic'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3139081009
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_CHILD |
-------------------------------------------------
8 rows selected.
Для учета можно использовать v$object_dependency/v$db_object_cache:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
SQL> col owner for a20
SQL> col name for a30
SQL> col type for a20
SQL> select doc.owner, doc.name, doc.type
2 from v$sqlarea sqla,
3 v$object_dependency od,
4 v$db_object_cache doc
5 where sqla.sql_text = q'#explain plan set statement_id = 'Q' for &l_sqlstr.#'
6 and od.from_hash = sqla.hash_value
7 and doc.hash_value = od.to_hash
8 and doc.type in ('TABLE', 'VIEW');
old 5: where sqla.sql_text = q'#explain plan set statement_id = 'Q' for &l_sqlstr.#'
new 5: where sqla.sql_text = q'#explain plan set statement_id = 'Q' for select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x#'
OWNER NAME TYPE
-------------------- ------------------------------ --------------------
TC MV_CHILD TABLE
TC PARENT TABLE
TC VW_PARENT VIEW
TC CHILD TABLE
4 rows selected.
SQL>
SQL> with deps(owner, name, type, hash_value) as (
2 select doc.owner, doc.name, doc.type, doc.hash_value
3 from v$sqlarea sqla,
4 v$object_dependency od,
5 v$db_object_cache doc
6 where 1=1
7 and sqla.sql_text = q'#explain plan set statement_id = 'Q' for &l_sqlstr.#'
8 and od.from_hash = sqla.hash_value
9 and doc.hash_value = od.to_hash
10 union all
11 select doc.owner, doc.name, doc.type, doc.hash_value
12 from deps d,
13 v$object_dependency od,
14 v$db_object_cache doc
15 where od.from_hash = d.hash_value
16 and doc.hash_value = od.to_hash)
17 select distinct *
18 from deps
19 where type in ('TABLE', 'VIEW')
20 /
old 7: and sqla.sql_text = q'#explain plan set statement_id = 'Q' for &l_sqlstr.#'
new 7: and sqla.sql_text = q'#explain plan set statement_id = 'Q' for select /*+ rewrite(c mv_child)*/count(c.x) from vw_parent p, child c where p.x = c.x#'
OWNER NAME TYPE HASH_VALUE
-------------------- ------------------------------ -------------------- ----------
TC CHILD TABLE 1841841891
TC VW_PARENT VIEW 3317479009
TC MV_CHILD TABLE 155164606
TC PARENT TABLE 2405718569
4 rows selected.
|
|
|
