Skip to content

Commit

Permalink
Fixes #16 - Viewers for "Identifiers", "Uses" and "Table Usages" are …
Browse files Browse the repository at this point in the history 
…slow for PDB connections

querying all_source/db_source for a single line is much slower in a multitenant environment, replaced all correlated queries with conventional join, materialized a reasonable rowset before
  • Loading branch information
@PhilippSalvisberg
PhilippSalvisberg committed Oct 4, 2017
1 parent ab27b77 commit b6a2f6e
Show file tree
Hide file tree
Showing 5 changed files with 218 additions and 126 deletions.
30 changes: 19 additions & 11 deletions database/utils/view/plscope_identifiers.sql
View file
Original file line number Diff line number Diff line change
Expand Up @@ -16,6 +16,18 @@

CREATE OR REPLACE VIEW plscope_identifiers AS
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM dba_source
WHERE owner LIKE nvl(sys_context('PLSCOPE', 'OWNER'), USER)
AND type LIKE nvl(sys_context('PLSCOPE', 'OBJECT_TYPE'), '%')
AND name LIKE nvl(sys_context('PLSCOPE', 'OBJECT_NAME'), '%')
),
base_ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -135,16 +147,7 @@ WITH
refs.owner AS ref_owner,
refs.object_type AS ref_object_type,
refs.object_name AS ref_object_name,
(
-- this correlated subquery will be evaluated only,
-- if the column TEXT is selected
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM dba_source src
WHERE src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
) AS text,
regexp_replace(src.text, chr(10)||'+$', null) AS text, -- remove trailing new line character
CASE
WHEN tree.name_path LIKE '%:%' AND tree.usage != 'EXECUTE' THEN
-- ensure that this is really a child of a statement
Expand Down Expand Up @@ -215,4 +218,9 @@ WITH
FROM tree
LEFT JOIN dba_identifiers refs
ON refs.signature = tree.signature
AND refs.usage = 'DECLARATION';
AND refs.usage = 'DECLARATION'
LEFT JOIN src
ON src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line;
28 changes: 18 additions & 10 deletions database/utils/view/plscope_naming.sql
View file
Original file line number Diff line number Diff line change
Expand Up @@ -45,6 +45,18 @@ WITH
END;
*
*/
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM dba_source
WHERE owner LIKE nvl(sys_context('PLSCOPE', 'OWNER'), USER)
AND type LIKE nvl(sys_context('PLSCOPE', 'OBJECT_TYPE'), '%')
AND name LIKE nvl(sys_context('PLSCOPE', 'OBJECT_NAME'), '%')
),
ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -99,16 +111,7 @@ WITH
PARTITION BY tree.owner, tree.object_name, tree.object_type
ORDER BY tree.line, tree.col, tree.path_len
) AS procedure_name,
(
-- this correlated subquery will be evaluated only,
-- if the column TEXT is selected
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.dba_source src
WHERE src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
) AS text,
regexp_replace(src.text, chr(10)||'+$', null) AS text, -- remove trailing new line character
tree.usage,
tree.type,
tree.name,
Expand All @@ -121,6 +124,11 @@ WITH
tree.parent_line,
tree.parent_col
FROM tree
LEFT JOIN src
ON src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
WHERE tree.object_type IN ('FUNCTION', 'PROCEDURE', 'TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
),
checked AS (
Expand Down
2 changes: 1 addition & 1 deletion sqldev/pom.xml
View file
Original file line number Diff line number Diff line change
Expand Up @@ -5,7 +5,7 @@
<!-- The Basics -->
<groupId>com.salvis</groupId>
<artifactId>com.salvis.plscope.sqldev</artifactId>
<version>0.6.0-SNAPSHOT</version>
<version>0.5.1</version>
<packaging>bundle</packaging>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
Expand Down
180 changes: 116 additions & 64 deletions sqldev/src/main/resources/com/salvis/plscope/sqldev/editor/plscope-utils-viewers.xml
View file
Original file line number Diff line number Diff line change
Expand Up @@ -19,6 +19,17 @@
<query minversion="12.2">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
base_ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -131,21 +142,30 @@ WITH
END AS "Used?",
tree.line AS "Line",
tree.col AS "Col",
(
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
) AS "Text"
regexp_replace(src.text, chr(10)||'+$', null) AS "Text" -- remove trailing new line character
FROM tree
LEFT JOIN src
ON src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
ORDER BY length(tree.object_type), tree.line, tree.col, tree.path_len
]]></sql>
</query>
<query minversion="11.1">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -231,15 +251,13 @@ WITH
END AS "Used?",
tree.line AS "Line",
tree.col AS "Col",
(
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
) AS "Text"
regexp_replace(src.text, chr(10)||'+$', null) AS "Text" -- remove trailing new line character
FROM tree
LEFT JOIN src
ON src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
ORDER BY length(tree.object_type), tree.line, tree.col, tree.path_len
]]></sql>
</query>
Expand Down Expand Up @@ -303,8 +321,19 @@ SELECT CASE
<query minversion="11.1">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
ids AS (
SELECT /*+materialize */
SELECT /*+ materialize */
owner,
object_type,
object_name,
Expand Down Expand Up @@ -351,17 +380,15 @@ SELECT CASE
ids.name AS "Name",
ids.line AS "Line",
ids.col aS "Col",
(
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = ids.owner
AND src.type = ids.object_type
AND src.name = ids.object_name
AND src.line = ids.line
) AS "Text"
regexp_replace(src.text, chr(10)||'+$', null) AS "Text" -- remove trailing new line character
from ids
JOIN sys.all_identifiers refs
ON ids.signature = refs.signature
LEFT JOIN src
ON src.owner = ids.owner
AND src.type = ids.object_type
AND src.name = ids.object_name
AND src.line = ids.line
LEFT JOIN sys.all_synonyms s
ON s.owner = refs.owner
AND s.synonym_name = refs.object_name
Expand Down Expand Up @@ -396,6 +423,17 @@ SELECT CASE
<query minversion="11.1">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
refs AS (
SELECT /*+materialize */
signature
Expand All @@ -421,17 +459,15 @@ SELECT CASE
ids.name AS "Name",
ids.line AS "Line",
ids.col aS "Col",
(
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = ids.owner
AND src.type = ids.object_type
AND src.name = ids.object_name
AND src.line = ids.line
) AS "Text"
regexp_replace(src.text, chr(10)||'+$', null) AS "Text" -- remove trailing new line character
FROM refs
JOIN sys.all_identifiers ids
ON ids.signature = refs.signature
LEFT JOIN src
ON src.owner = ids.owner
AND src.type = ids.object_type
AND src.name = ids.object_name
AND src.line = ids.line
WHERE NOT (
ids.owner = :OBJECT_OWNER
AND ids.object_type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
Expand All @@ -454,6 +490,17 @@ SELECT CASE
<query minversion="12.2">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
base_ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -552,16 +599,7 @@ WITH
refs.owner AS ref_owner,
refs.object_type AS ref_object_type,
refs.object_name AS ref_object_name,
(
-- this correlated subquery will be evaluated only,
-- if the column TEXT is selected
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
) AS text,
regexp_replace(src.text, chr(10)||'+$', null) AS text, -- remove trailing new line character
CASE
WHEN tree.name_path LIKE '%:%' AND tree.usage != 'EXECUTE' THEN
-- ensure that this is really a child of a statement
Expand All @@ -582,6 +620,11 @@ WITH
LEFT JOIN sys.all_identifiers refs
ON refs.signature = tree.signature
AND refs.usage = 'DECLARATION'
LEFT JOIN src
ON src.owner = tree.owner
AND src.type = tree.object_type
AND src.name = tree.object_name
AND src.line = tree.line
),
dep AS (
SELECT owner AS owner,
Expand Down Expand Up @@ -692,6 +735,17 @@ SELECT CASE
<query minversion="12.2">
<sql><![CDATA[
WITH
src AS (
SELECT /*+ materialize */
owner,
type,
name,
line,
text
FROM sys.all_source
WHERE type IN (upper(replace(:OBJECT_TYPE,'plscope-utils-')), upper(replace(:OBJECT_TYPE,'plscope-utils-')) || ' BODY')
AND name = :OBJECT_NAME
),
base_ids AS (
SELECT owner,
name,
Expand Down Expand Up @@ -970,32 +1024,30 @@ WITH
FROM missing_cols
)
SELECT CASE
WHEN object_type IN ("FUNCTION", "PACKAGE", "PACKAGE BODY", "PROCEDURE", "TRIGGER", "TYPE", "TYPE BODY") THEN
"SQLDEV:LINK:" || owner || ":" || object_type || ":" || object_name || ":" ||
to_char(line,"FM0000009") || ":" || to_char(col,"FM0000009") || ":" || "Source" ||
WHEN base_cols.object_type IN ("FUNCTION", "PACKAGE", "PACKAGE BODY", "PROCEDURE", "TRIGGER", "TYPE", "TYPE BODY") THEN
"SQLDEV:LINK:" || base_cols.owner || ":" || base_cols.object_type || ":" || base_cols.object_name || ":" ||
to_char(base_cols.line,"FM0000009") || ":" || to_char(base_cols.col,"FM0000009") || ":" || "Source" ||
':oracle.dbtools.raptor.controls.grid.DefaultDrillLink'
ELSE
NULL
END AS "Link",
procedure_name AS "Procedure name",
operation AS "Operation",
direct_dependency AS "Direct dep?",
ref_owner "Owner",
ref_object_type AS "Object type",
ref_object_name AS "Object name",
column_name AS "Column name",
line AS "Line",
col AS "Col",
(
SELECT regexp_replace(src.text, chr(10)||'+$', null) -- remove trailing new line character
FROM sys.all_source src
WHERE src.owner = base_cols.owner
AND src.type = base_cols.object_type
AND src.name = base_cols.object_name
AND src.line = base_cols.line
) AS "Text"
base_cols.procedure_name AS "Procedure name",
base_cols.operation AS "Operation",
base_cols.direct_dependency AS "Direct dep?",
base_cols.ref_owner "Owner",
base_cols.ref_object_type AS "Object type",
base_cols.ref_object_name AS "Object name",
base_cols.column_name AS "Column name",
base_cols.line AS "Line",
base_cols.col AS "Col",
regexp_replace(src.text, chr(10)||'+$', null) AS "Text" -- remove trailing new line character
FROM base_cols
ORDER BY length(object_type), line, col, path_len
LEFT JOIN src
ON src.owner = base_cols.owner
AND src.type = base_cols.object_type
AND src.name = base_cols.object_name
AND src.line = base_cols.line
ORDER BY length(base_cols.object_type), base_cols.line, base_cols.col, base_cols.path_len
]]></sql>
</query>
</queries>
Expand Down
Loading

0 comments on commit b6a2f6e

Please sign in to comment.