Hi,
I appreciate that similar questions have been asked before, but I am struggling with the next step if you like.
I want to get all the base tables (recursively) used by a list of views. From various posts here I know how to do this for a single view and that works fine. However, I want to do this for a list of views (e.g. coming from a subquery). I tried various things, but when I get something which works, the performance is dreadful.
My latest attempt is below and will need to mention that I am not an Oracle expert at all, so the SQL I am using is probably not the best anyway.
What I am trying to do here is to get the base tables for all views used on a certain day (hence the sub select from the DBA_HIST_SQL_PLAN. This runs fine for a couple of views, but quickly the performance degrades (and I am not even sure if the logic is 100% correct here). The DISTINCTS are there as I want to process each view only once (and due to the recursive nature duplicates will appear).
SELECT DISTINCT
OWNER
, NAME
, TYPE
, REFERENCED_OWNER
, REFERENCED_NAME
, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
WHERE TYPE = 'VIEW'
AND REFERENCED_TYPE='TABLE'
START WITH (OWNER,NAME) IN
(
SELECT
OBJECT_OWNER
, OBJECT_NAME
FROM DBA_HIST_SQL_PLAN
WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
AND OBJECT_TYPE = 'VIEW'
AND OBJECT_OWNER IS NOT NULL
AND OBJECT_NAME IS NOT NULL
)
CONNECT BY NOCYCLE (OWNER,NAME) = ((PRIOR REFERENCED_OWNER, PRIOR REFERENCED_NAME ))So to summarize what I am trying to achieve is to get a list of view names and all the underlying base tabes used on a certain day on the system. As mentioned, I know how to do this for a single view, but I want to do it for a list of views supplied from another (sub)query.
Any help advice is appreciated or if someone has a ready snippet even better.
UPDATE:
After some digging around I tried a different approach which seems to work much faster, but I have a feeling this is still not 100% correct... Ideally I want to end up with a result set like follows:
<owner of view><view name><owner of referred table><name of referred table>
(Where owner of view and view name, the first 2 columns refer to the view we are looking up. I am not necessary interested in the intermediate results (views), unless we can put them in a separate pair of columns)
This is the code for this new attempt:
WITH usedViews (name, owner, type, rname, rowner, rtype, lvl ) AS
(
SELECT name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
FROM dba_dependencies
WHERE (owner,name) IN
(
SELECT
OBJECT_OWNER
, OBJECT_NAME
FROM DBA_HIST_SQL_PLAN
WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
AND OBJECT_TYPE = 'VIEW'
AND OBJECT_OWNER IS NOT NULL
AND OBJECT_NAME IS NOT NULL
)
AND referenced_type IN ('TABLE','VIEW')
UNION ALL
SELECT a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type, r.lvl + 1
FROM dba_dependencies a,
usedViews r
WHERE r.rname = a.name
AND r.rowner = a.owner
AND r.rtype = a.type
AND a.referenced_type IN ('TABLE','VIEW')
) cycle name, owner, type set cycle_detected to 1 default 0
SELECT DISTINCT
owner
, name
, rowner
, rname
FROM usedViews
WHERE rtype = 'TABLE'
UPDATE 2:
Apologies, for making this a lengthy post (but it might help others), but I think I might have done it, but comments/feedback welcome.
This is the latest version:
WITH usedViews (viewname, viewowner, viewtype, interim_name, interim_owner, interim_type, refname, refowner, reftype, lvl ) AS
(
SELECT name, owner, type, name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
FROM dba_dependencies
WHERE (owner,name) IN
(
SELECT DISTINCT
OBJECT_OWNER
, OBJECT_NAME
FROM DBA_HIST_SQL_PLAN
WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
AND OBJECT_TYPE = 'VIEW'
AND OBJECT_OWNER IS NOT NULL
AND OBJECT_NAME IS NOT NULL
)
AND referenced_type IN ('TABLE','VIEW')
UNION ALL
SELECT r.viewname,r.viewowner,r.viewtype,a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type,r.lvl + 1
FROM dba_dependencies a,
usedViews r
WHERE r.refname = a.name
AND r.refowner = a.owner
AND r.reftype = a.type
AND a.referenced_type IN ('TABLE','VIEW')
)
SELECT DISTINCT
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
FROM usedViews
WHERE RefTYPE = 'TABLE'
GROUP BY
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
ORDER BY
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
Many thanks in advance,
Misha
Funny...as we I was reading this from the top, I was thinking "Recursive WITH seems a more natural approach to this" and voila, we ended up on the same thought as I kept reading :-)
I think your approach is fine. The only thing I was perhaps add, is that DBA_DEPENDENCIES can be a little slow sometimes (its quite a complex dictionary view), so you might get some benefit by materializing that out into its own temporary copy before doing the hard work in the query.
So something like the below:
WITH
tmp_Dep as (
select /*+ materialize */ name, owner, type, referenced_name, referenced_owner, referenced_type
FROM dba_dependencies
where owner not in ( select distinct schema from dba_registry )
),
usedViews (viewname, viewowner, viewtype, interim_name, interim_owner, interim_type, refname, refowner, reftype, lvl ) AS
(
SELECT name, owner, type, name, owner, type, referenced_name, referenced_owner, referenced_type, 1 AS lvl
FROM tmp_Dep
WHERE (owner,name) IN
(
SELECT DISTINCT
OBJECT_OWNER
, OBJECT_NAME
FROM DBA_HIST_SQL_PLAN
WHERE TRUNC(timestamp) BETWEEN TO_DATE('2018-12-16', 'YYYY-MM-DD') AND TO_DATE('2018-12-16', 'YYYY-MM-DD')
AND OBJECT_TYPE = 'VIEW'
AND OBJECT_OWNER IS NOT NULL
AND OBJECT_NAME IS NOT NULL
)
AND referenced_type IN ('TABLE','VIEW')
UNION ALL
SELECT r.viewname,r.viewowner,r.viewtype,a.name, a.owner, a.type, a.referenced_name, a.referenced_owner, a.referenced_type,r.lvl + 1
FROM tmp_Dep a,
usedViews r
WHERE r.refname = a.name
AND r.refowner = a.owner
AND r.reftype = a.type
AND a.referenced_type IN ('TABLE','VIEW')
)
SELECT DISTINCT
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
FROM usedViews
WHERE RefTYPE = 'TABLE'
GROUP BY
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
ORDER BY
viewname
, viewowner
, viewtype
, refname
, refowner
, reftype
I excluded internal stuff using the query to DBA_REGISTRY but you can remove that if you want to retain that stuff.