GeezerVison is showing github.com — temporary tablespace usage · Issue #1169 · utPLSQL/utPLSQL
temporary tablespace usage #1169DescriptionHi,
I have installed latest utplsql v3.1.11 on a 19.3.0.0.0 for a training initially and then hopefully for real development testing.
The database is not managed by me but I have access to enough system views,
In the schema that the tests will be run there are about 2400+ packages
For a simple test suite, the standard text reporter returns instantly the result.
However the html reporter with coverage is stuck for a long time and then ends with
ORA-01652: unable to extend temp segment by ... in tablespace ...
The database has a 32 GB temp tablespace which is almost all free and I can verify from dba_temp_free_space that
when starting the test all free space is being eaten away until the error message comes out.
I have found that I can limit the scope of coverage to certain packages and run the test
(though 1 minute runtime for a single package of 150 lines seems excessive)
but it is desired to test the whole software in the end (and have coverage for all packages).
The amount of 32 GB seems too big. Furthermore I have no guidance on how to estimate the total necessary temp size in GB
in order to tell the administrators.
So I would like to ask:
- guidance on estimating total temp size needed to have full schema coverage
- if it's possible to alter certain storage parameters (or utplsql options) so that the temp storage used is reduced (and perhaps runtime is reduced too)
Activityjgebal commented on Nov 23, 2021 You should probably consider limiting the packages that are getting reported.
By default, coverage is gathered on entire schema(schemas) under test.
If you want you can list packages that you want to gather coverage on interested in.
If you want to gather coverage on entire schema containing 2400+ packages, consider the lines of code that will be scanned and reported back in HTML report.
select count(*) lines, sum(length(text))/1024/1024 size_MB from dba_source where owner = 'THE_OWNER' and type = 'PACKAGE BODY';
See this section of coverage documentation jgebal commented on Nov 23, 2021 You could also try using different coverage reporter (I suggest Coveralls) and see if you face the same problem.
Also, can you perhaps provide full error stack? It could be valuable to see at which statement it actually failed. dsnz commented on Nov 24, 2021 Hi,
here are your answers and some observations
- from sqlplus, I run a procedure in a <package> that calls ut_run
if ut_run syntax is
ut.run('<package>', ut_coverage_html_reporter());
then after about 5 minutes this is what I receive back exactly
(except for masking package and tablespace names)
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERAGE_HTML_REPORTER", line 35
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
ORA-06512: at "UT3.UT_RUNNER", line 144
ORA-01652: unable to extend temp segment by 16 in tablespace <temp>
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERAGE_HTML_REPORTER", line 35
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
BEGIN <package>.run_html_coverage; END; -- <package> contains ut_run call
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 16 in tablespace <temp>
ORA-06512: at "UT3.UT_RUNNER", line 152
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERAGE_HTML_REPORTER", line 35
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
ORA-06512: at "UT3.UT_RUNNER", line 144
ORA-06512: at "UT3.UT", line 134
ORA-06512: at "UT3.UT", line 488
ORA-06512: at "UT3.UT", line 623
ORA-06512: at "<package>", line 53
ORA-06512: at line 1
if ut_run syntax is
ut.run('<package>', ut_coverage_html_reporter(),
a_include_objects => ut_varchar2_list('<package2>', '<package3>'));
runs fine but in about 1 minute (!)
which is excessive and I guess is related to the number of packages in the schema
can you verify that this is the reason ?
- running with the ut_coveralls_reporter() reporter
fails in about 2-2.5 minutes with the following stack trace
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERALLS_REPORTER", line 89
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
ORA-06512: at "UT3.UT_RUNNER", line 144
ORA-01652: unable to extend temp segment by 16 in tablespace <temp>
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERALLS_REPORTER", line 89
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
BEGIN <package>.run_html_coverage; END;
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 16 in tablespace <temp>
ORA-06512: at "UT3.UT_RUNNER", line 152
ORA-06512: at "UT3.UT_COVERAGE", line 158
ORA-06512: at "UT3.UT_COVERAGE", line 218
ORA-06512: at "UT3.UT_COVERALLS_REPORTER", line 89
ORA-06512: at "UT3.UT_REPORTER_BASE", line 193
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 70
ORA-06512: at "UT3.UT_EVENT_MANAGER", line 80
ORA-06512: at "UT3.UT_RUN", line 66
ORA-06512: at "UT3.UT_SUITE_ITEM", line 49
ORA-06512: at "UT3.UT_RUNNER", line 144
ORA-06512: at "UT3.UT", line 134
ORA-06512: at "UT3.UT", line 488
ORA-06512: at "UT3.UT", line 623
ORA-06512: at "<package>", line 55
ORA-06512: at line 1
- the output from
select count(*) lines, sum(length(text))/1024/1024 size_MB
from dba_source
where owner = '<OWNER>' and type = 'PACKAGE BODY';
is
2,595,931 (lines) and 152 (size_MB) dsnz commented on Nov 24, 2021 update:
I forgot to say and it maybe related that installation was done with session parameter securefile=none
as a workaround for ORA-60019
also running with
ut.run('<package>', ut_coverage_html_reporter(),
a_include_objects => ut_varchar2_list('<package2>', '<package3>'));
takes 6 minutes the first time (ie. after running for full schema or with coveralls)
and therafter it takes 1 minute jgebal commented on Nov 25, 2021 Hi @dsnz
I'll look into details of the error and possible reasons for it.
So in essence there are two separate problems at hand:
- performance run with coverage report generation
- problem with space usage
One more question
Did you upgrade from previous version recently and only now encounter the issue (is it a regression for you) or is it that you started with utPLSQL from version 3.1.11?
One more request
Can you try the new approach and see where do you get the error?
Is it at the time the tests are executed or at the time the coverage report is generated?
declare
l_coverage_run_id raw(32);
begin
l_coverage_run_id := 'A6AA5B7361251CE6E053020011ACA055';
-- l_coverage_run_id := sys_guid;
ut_runner.coverage_start(l_coverage_run_id);
ut.run('<package>');
ut_runner.coverage_stop();
end;
/
--now you can try different coverage reporters passing the persisted ID of coverage run.
select * from table( ut_coverage_html_reporter().get_report(
a_coverage_options => ut_coverage_options(
include_objects => ut_varchar2_list('<package2>', '<package3>'),
coverage_run_id => 'A6AA5B7361251CE6E053020011ACA055'
)
) );Make sure the coverage run is different each time you execute ut.run and want to gather coverage - best to use sys_guid() function. dsnz commented on Nov 25, 2021 Hi @jgebal ,
-
there was no upgrade, this is a fresh start
-
I tried the new approach (let's call it programmatic coverage :D)
and the pl/sql block ends successfully in about 55 sec :) :)
after that I run the select as you said but it fails due to bad argument types for ut_coverage_options
from the documentation it seems the type ut_varchar2_rows should be used instead of ut_varchar2_list
but even using this type I get ORA-06512 at line 36 (previous line is 59) in ut_coverage_options
so I tried without the include_objects option
and it failed with same message of unable to extend temp segment as in previous post
this time I watched more closely how space was consumed in the temp tablespace and I saw that not all was consumed
(perhaps about 5 GB) so I guess the issue is really reaching the max extents jgebal commented on Nov 28, 2021 Thanks @dsnz
I really appreciate your help investigating this issue.
So I assume the 55 seconds is the test run itself (with DBMS_PROFILER & DBMS_PLSQL_COVERAGE enabled.
The challenge is the volume of space consumed to generate the temporary coverage data that is needed.
We would need to investigate if it's the actual data in GTT table that is used or is it the SQL Query running into some odd execution plan.
From the line number where the execution fails, it seems that it's the query itself.
So we would need to understand what is going on in your system that querying data dictionary is causing problems.
To do that, can you check if:
- user running the tests has access to the
DBA_SOURCE view?
- are you running inside a PDB (pluggable database) of a multi-tenant database?
Are you able to capture all active SQL while the statement ...ut_coverage_html_reporter().get_report... is running?
There should be an active recursive SQL starting with something like:
with
trigger_source_offsets as (
select min(s.line) - 1 offset, s.owner, s.name, s.type
...This is the SQL that is failing.
The interesting part is that it's reading the DBA_SOURCE or ALL_SOURCE (depending on user privileges).
It seems that execution plan is not good for some reason.
Is this your local database or a shared DB installed on some server?
Were dictionary statistics gathered? See: https://docs.oracle.com/en/database/oracle/oracle-database/18/spuss/gathering-dictionary-statistics-after-upgrading.html dsnz commented on Nov 29, 2021 Hi,
here are the experiments I did according to your requests (and some more)
- the user running the tests does have access to the DBA_SOURCE view !
- no, not running inside a PDB (pluggable database) of a multi-tenant database !
- failing sql has been captured, and the entire dbms_xplan.display_cursor output is
SQL_ID 2fhps31zjtsf1, child number 0
-------------------------------------
with trigger_source_offsets as ( select min(s.line)
- 1 offset, s.owner, s.name, s.type from dba_source s
where s.type = 'TRIGGER' and s.owner in (
select /*+ cardinality(t 3) */
upper(t.column_value) from table(:l_schema_names) t)
and (lower(s.text) like '%begin%' or lower(s.text) like
'%declare%' or lower(s.text) like '%compound%') group by
s.owner, s.name, s.type ), sources as ( select /*+
cardinality(f 0) */ lower(s.type||'
'||s.owner||'.'||s.name) as full_name, s.owner, s.name, s.type,
s.line - case when s.type = 'TRIGGER' then o.offset else 0 end
as line, s.text from dba_source s
left join trigger_source_offsets o on (s.owner = o.owner
and s.name = o.name and s.type = o.type) where s.type in
('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'F
Plan hash value: 3794220000
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1850K(100)| |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 4060K| 8589M| | 1850K (1)| 00:01:13 |
| 3 | VIEW | | 2646 | 395K| | 639 (1)| 00:00:01 |
| 4 | HASH GROUP BY | | 2646 | 2813K| 3032K| 639 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 2646 | 2813K| | 32 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | | 3 | 6 | | 29 (0)| 00:00:01 |
| 7 | VIEW | INT$DBA_SOURCE | 882 | 936K| | 1 (0)| 00:00:01 |
| 8 | UNION-ALL PARTITION | | | | | | |
|* 9 | FILTER | | | | | | |
| 10 | NESTED LOOPS | | 888 | 139K| | 428 (1)| 00:00:01 |
| 11 | NESTED LOOPS | | 6240 | 139K| | 428 (1)| 00:00:01 |
|* 12 | HASH JOIN | | 32 | 2848 | | 278 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 32 | 2080 | | 277 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| |
| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 32 | 1536 | | 276 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ5 | 89 | | | 203 (0)| 00:00:01 |
| 18 | INDEX FULL SCAN | I_USER2 | 134 | 3216 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_SOURCE1 | 195 | | | 3 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | SOURCE$ | 28 | 2016 | | 6 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 26 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 28 | FILTER | | | | | | |
|* 29 | FILTER | | | | | | |
| 30 | NESTED LOOPS | | 1 | 2115 | | 216 (5)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 87 | | 206 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 63 | | 205 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| |
| 35 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 46 | | 204 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | I_OBJ5 | 1 | | | 203 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | | 1 (0)| 00:00:01 |
|* 38 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 1 | 2028 | | 10 (100)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 43 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 44 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
| 46 | MERGE JOIN SEMI | | 4060K| 7996M| | 1849K (1)| 00:01:13 |
| 47 | SORT JOIN | | 4060K| 7989M| | 1849K (1)| 00:01:13 |
| 48 | MERGE JOIN ANTI NA | | 4060K| 7989M| | 1849K (1)| 00:01:13 |
| 49 | SORT JOIN | | 4060K| 7981M| 20G| 1849K (1)| 00:01:13 |
| 50 | VIEW | INT$DBA_SOURCE | 4060K| 7981M| | 102K (1)| 00:00:04 |
| 51 | UNION-ALL | | | | | | |
|* 52 | FILTER | | | | | | |
| 53 | NESTED LOOPS | | 4089K| 627M| | 102K (1)| 00:00:04 |
| 54 | NESTED LOOPS | | 4089K| 627M| | 102K (1)| 00:00:04 |
|* 55 | HASH JOIN | | 20949 | 1820K| | 4659 (2)| 00:00:01 |
| 56 | TABLE ACCESS FULL | USER$ | 134 | 2278 | | 5 (0)| 00:00:01 |
|* 57 | HASH JOIN | | 20949 | 1472K| | 4654 (2)| 00:00:01 |
| 58 | INDEX FULL SCAN | I_USER2 | 134 | 3216 | | 1 (0)| 00:00:01 |
|* 59 | TABLE ACCESS FULL | OBJ$ | 20949 | 981K| | 4653 (2)| 00:00:01 |
|* 60 | INDEX RANGE SCAN | I_SOURCE1 | 195 | | | 3 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID | SOURCE$ | 195 | 14040 | | 6 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 64 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 66 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 67 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 69 | FILTER | | | | | | |
|* 70 | FILTER | | | | | | |
| 71 | NESTED LOOPS | | 1 | 2115 | | 3796 (1)| 00:00:01 |
| 72 | NESTED LOOPS | | 1 | 87 | | 3786 (1)| 00:00:01 |
| 73 | NESTED LOOPS | | 1 | 70 | | 3785 (1)| 00:00:01 |
| 74 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 46 | | 3784 (1)| 00:00:01 |
|* 75 | INDEX SKIP SCAN | I_OBJ1 | 1 | | | 3783 (1)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | | 1 (0)| 00:00:01 |
| 77 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 78 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| |
|* 79 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 1 | 2028 | | 10 (100)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 82 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 83 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 84 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 85 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 86 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 87 | SORT UNIQUE | | 333 | 666 | | 30 (4)| 00:00:01 |
| 88 | COLLECTION ITERATOR PICKLER FETCH | | 333 | 666 | | 29 (0)| 00:00:01 |
|* 89 | SORT UNIQUE | | 3 | 6 | | 30 (4)| 00:00:01 |
| 90 | COLLECTION ITERATOR PICKLER FETCH | | 3 | 6 | | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LINE"-CASE "TYPE" WHEN 'TRIGGER' THEN "O"."OFFSET" ELSE 0 END >0)
2 - access("OWNER"="O"."OWNER" AND "NAME"="O"."NAME" AND "TYPE"="O"."TYPE")
9 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
12 - access("O"."OWNER#"="U"."USER#")
15 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
17 - access("O"."SPARE3"="U"."USER#")
filter((DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' AND (("O"."TYPE#"=13
AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#"))))
19 - access("O"."OBJ#"="S"."OBJ#")
20 - filter((LOWER("S"."SOURCE") LIKE '%compound%' OR LOWER("S"."SOURCE") LIKE '%declare%' OR LOWER("S"."SOURCE")
LIKE '%begin%'))
21 - filter("TYPE#"=:B1)
22 - access("UE"."USER#"=:B1)
23 - filter("UE"."TYPE#"=:B1)
24 - access("UE"."USER#"=:B1)
26 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
27 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
28 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
29 - filter(NULL IS NOT NULL)
34 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
36 - access("O"."SPARE3"="U"."USER#" AND "O"."TYPE#"=28)
filter("O"."TYPE#"=28)
37 - access("O"."OWNER#"="U"."USER#")
38 - filter(("O"."OBJ#"="S"."JOXFTOBN" AND (LOWER("S"."JOXFTSRC") LIKE '%compound%' OR LOWER("S"."JOXFTSRC") LIKE
'%declare%' OR LOWER("S"."JOXFTSRC") LIKE '%begin%')))
39 - filter("TYPE#"=:B1)
40 - access("UE"."USER#"=:B1)
41 - filter("UE"."TYPE#"=:B1)
42 - access("UE"."USER#"=:B1)
44 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
45 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
52 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
55 - access("O"."SPARE3"="U"."USER#")
57 - access("O"."OWNER#"="U"."USER#")
59 - filter(((("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#")) AND
(DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='FUNCTION' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PACKAGE BODY' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PROCEDURE' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TYPE BODY')))
60 - access("O"."OBJ#"="S"."OBJ#")
62 - filter("TYPE#"=:B1)
63 - access("UE"."USER#"=:B1)
64 - filter("UE"."TYPE#"=:B1)
65 - access("UE"."USER#"=:B1)
67 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
68 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
69 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
70 - filter(NULL IS NOT NULL)
75 - access("O"."TYPE#"=28)
filter("O"."TYPE#"=28)
76 - access("O"."OWNER#"="U"."USER#")
78 - access("O"."SPARE3"="U"."USER#")
79 - filter("O"."OBJ#"="S"."JOXFTOBN")
80 - filter("TYPE#"=:B1)
81 - access("UE"."USER#"=:B1)
82 - filter("UE"."TYPE#"=:B1)
83 - access("UE"."USER#"=:B1)
85 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
86 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
87 - access(INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND
INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
filter((INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))
89 - access("OWNER"=UPPER(VALUE(KOKBF$)))
filter("OWNER"=UPPER(VALUE(KOKBF$)))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - no_parallel / duplicate hint
U - no_parallel
Note
-----
- Degree of Parallelism is 1 because of hint
- I can't know if dictionary statistics were gathered but I gathered them myself before continuing below :)
- after gathering statistics I run again the
ut.run('<package>', ut_coverage_html_reporter());
but it still fails with unable to extend temp segment message
and this time it runs in about 1m 5s, which is a record time ! (though it didn't complete and can't know if it would take more time to successful completion)
there is also a new dbms_xplan.display_cursor for child cursor 1 (after gathering system statistics)
SQL_ID 2fhps31zjtsf1, child number 1
-------------------------------------
with trigger_source_offsets as ( select min(s.line)
- 1 offset, s.owner, s.name, s.type from dba_source s
where s.type = 'TRIGGER' and s.owner in (
select /*+ cardinality(t 3) */
upper(t.column_value) from table(:l_schema_names) t)
and (lower(s.text) like '%begin%' or lower(s.text) like
'%declare%' or lower(s.text) like '%compound%') group by
s.owner, s.name, s.type ), sources as ( select /*+
cardinality(f 0) */ lower(s.type||'
'||s.owner||'.'||s.name) as full_name, s.owner, s.name, s.type,
s.line - case when s.type = 'TRIGGER' then o.offset else 0 end
as line, s.text from dba_source s
left join trigger_source_offsets o on (s.owner = o.owner
and s.name = o.name and s.type = o.type) where s.type in
('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'F
Plan hash value: 3687002882
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 18M(100)| |
|* 1 | FILTER | | | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 31M| 64G| | 18M (1)| 00:12:16 |
| 3 | VIEW | | 2646 | 395K| | 639 (1)| 00:00:01 |
| 4 | HASH GROUP BY | | 2646 | 2813K| 3032K| 639 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 2646 | 2813K| | 32 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | | 3 | 6 | | 29 (0)| 00:00:01 |
| 7 | VIEW | INT$DBA_SOURCE | 882 | 936K| | 1 (0)| 00:00:01 |
| 8 | UNION-ALL PARTITION | | | | | | |
|* 9 | FILTER | | | | | | |
| 10 | NESTED LOOPS | | 888 | 139K| | 428 (1)| 00:00:01 |
| 11 | NESTED LOOPS | | 6240 | 139K| | 428 (1)| 00:00:01 |
|* 12 | HASH JOIN | | 32 | 2848 | | 278 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 32 | 2080 | | 277 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| |
| 16 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 32 | 1536 | | 276 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | I_OBJ5 | 89 | | | 203 (0)| 00:00:01 |
| 18 | INDEX FULL SCAN | I_USER2 | 134 | 3216 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_SOURCE1 | 195 | | | 3 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | SOURCE$ | 28 | 2016 | | 6 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 23 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 26 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 28 | FILTER | | | | | | |
|* 29 | FILTER | | | | | | |
| 30 | NESTED LOOPS | | 1 | 2115 | | 216 (5)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 87 | | 206 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 63 | | 205 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_USER1 | 1 | | | 0 (0)| |
| 35 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 46 | | 204 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | I_OBJ5 | 1 | | | 203 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | | 1 (0)| 00:00:01 |
|* 38 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 1 | 2028 | | 10 (100)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 43 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 44 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
| 46 | MERGE JOIN SEMI | | 31M| 59G| | 18M (1)| 00:12:16 |
| 47 | SORT JOIN | | 31M| 59G| | 18M (1)| 00:12:16 |
| 48 | MERGE JOIN ANTI NA | | 31M| 59G| | 18M (1)| 00:12:16 |
| 49 | SORT JOIN | | 31M| 59G| 158G| 18M (1)| 00:12:16 |
| 50 | VIEW | INT$DBA_SOURCE | 31M| 59G| | 731K (1)| 00:00:29 |
| 51 | UNION-ALL | | | | | | |
|* 52 | FILTER | | | | | | |
|* 53 | HASH JOIN | | 31M| 4812M| | 731K (1)| 00:00:29 |
| 54 | TABLE ACCESS FULL | USER$ | 134 | 2278 | | 5 (0)| 00:00:01 |
|* 55 | HASH JOIN | | 31M| 4304M| | 731K (1)| 00:00:29 |
| 56 | INDEX FULL SCAN | I_USER2 | 134 | 3216 | | 1 (0)| 00:00:01 |
|* 57 | HASH JOIN | | 31M| 3587M| 9416K| 731K (1)| 00:00:29 |
|* 58 | TABLE ACCESS FULL | OBJ$ | 160K| 7527K| | 4654 (2)| 00:00:01 |
| 59 | TABLE ACCESS FULL | SOURCE$ | 89M| 6178M| | 315K (1)| 00:00:13 |
|* 60 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 62 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 65 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 67 | FILTER | | | | | | |
|* 68 | FILTER | | | | | | |
| 69 | NESTED LOOPS | | 1 | 2115 | | 3796 (1)| 00:00:01 |
| 70 | NESTED LOOPS | | 1 | 87 | | 3786 (1)| 00:00:01 |
| 71 | NESTED LOOPS | | 1 | 70 | | 3785 (1)| 00:00:01 |
| 72 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 46 | | 3784 (1)| 00:00:01 |
|* 73 | INDEX SKIP SCAN | I_OBJ1 | 1 | | | 3783 (1)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | I_USER2 | 1 | 24 | | 1 (0)| 00:00:01 |
| 75 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | | 1 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | I_USER# | 1 | | | 0 (0)| |
|* 77 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 1 | 2028 | | 10 (100)| 00:00:01 |
|* 78 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 79 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
|* 80 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | | 2 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | | 1 (0)| 00:00:01 |
| 82 | NESTED LOOPS | | 1 | 31 | | 3 (0)| 00:00:01 |
|* 83 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 84 | INDEX RANGE SCAN | I_OBJ4 | 1 | 11 | | 2 (0)| 00:00:01 |
|* 85 | SORT UNIQUE | | 333 | 666 | | 30 (4)| 00:00:01 |
| 86 | COLLECTION ITERATOR PICKLER FETCH | | 333 | 666 | | 29 (0)| 00:00:01 |
|* 87 | SORT UNIQUE | | 3 | 6 | | 30 (4)| 00:00:01 |
| 88 | COLLECTION ITERATOR PICKLER FETCH | | 3 | 6 | | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LINE"-CASE "TYPE" WHEN 'TRIGGER' THEN "O"."OFFSET" ELSE 0 END >0)
2 - access("OWNER"="O"."OWNER" AND "NAME"="O"."NAME" AND "TYPE"="O"."TYPE")
9 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
12 - access("O"."OWNER#"="U"."USER#")
15 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
17 - access("O"."SPARE3"="U"."USER#")
filter((DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' AND (("O"."TYPE#"=13
AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#"))))
19 - access("O"."OBJ#"="S"."OBJ#")
20 - filter((LOWER("S"."SOURCE") LIKE '%compound%' OR LOWER("S"."SOURCE") LIKE '%declare%' OR LOWER("S"."SOURCE")
LIKE '%begin%'))
21 - filter("TYPE#"=:B1)
22 - access("UE"."USER#"=:B1)
23 - filter("UE"."TYPE#"=:B1)
24 - access("UE"."USER#"=:B1)
26 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
27 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
28 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
29 - filter(NULL IS NOT NULL)
34 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
36 - access("O"."SPARE3"="U"."USER#" AND "O"."TYPE#"=28)
filter("O"."TYPE#"=28)
37 - access("O"."OWNER#"="U"."USER#")
38 - filter(("O"."OBJ#"="S"."JOXFTOBN" AND (LOWER("S"."JOXFTSRC") LIKE '%compound%' OR LOWER("S"."JOXFTSRC") LIKE
'%declare%' OR LOWER("S"."JOXFTSRC") LIKE '%begin%')))
39 - filter("TYPE#"=:B1)
40 - access("UE"."USER#"=:B1)
41 - filter("UE"."TYPE#"=:B1)
42 - access("UE"."USER#"=:B1)
44 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
45 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
52 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
53 - access("O"."SPARE3"="U"."USER#")
55 - access("O"."OWNER#"="U"."USER#")
57 - access("O"."OBJ#"="S"."OBJ#")
58 - filter(((("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#")) AND
(DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='FUNCTION' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PACKAGE BODY' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PROCEDURE' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TYPE BODY')))
60 - filter("TYPE#"=:B1)
61 - access("UE"."USER#"=:B1)
62 - filter("UE"."TYPE#"=:B1)
63 - access("UE"."USER#"=:B1)
65 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
66 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
67 - filter((BITAND("U"."SPARE1",16)=0 OR BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"<>88 AND IS NULL) OR
( IS NOT NULL AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL))))
68 - filter(NULL IS NOT NULL)
73 - access("O"."TYPE#"=28)
filter("O"."TYPE#"=28)
74 - access("O"."OWNER#"="U"."USER#")
76 - access("O"."SPARE3"="U"."USER#")
77 - filter("O"."OBJ#"="S"."JOXFTOBN")
78 - filter("TYPE#"=:B1)
79 - access("UE"."USER#"=:B1)
80 - filter("UE"."TYPE#"=:B1)
81 - access("UE"."USER#"=:B1)
83 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
84 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
85 - access(INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND
INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
filter((INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND
INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))
87 - access("OWNER"=UPPER(VALUE(KOKBF$)))
filter("OWNER"=UPPER(VALUE(KOKBF$)))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
0 - STATEMENT
U - no_parallel / duplicate hint
U - no_parallel
Note
-----
- Degree of Parallelism is 1 because of hint
- statistics feedback used for this statement
- out of curiosity , after the above runs
I run again
ut.run('<package>', ut_coverage_html_reporter(),
a_include_objects => ut_varchar2_list('<package2>', '<package3>'));
and it run successfully in about 5m (!)
and then I run it again
and this time it run in about 1m5s
so clearly something happens when switching to new options
( something is recomputed ? and then cached ? but you know better :) ) jgebal commented on Feb 13, 2022 Is the problem still reproducible after gathering data-dictionary statistics?
Oracle will try to pick the best plan possible (it should learn by mistakes).
If stats gathering didn't help, then we may need to consider rewriting the query somehow. jgebal commented on Feb 13, 2022 Would you be able to try installing utPLSQL referenced feature branch?
I've noticed that in your DB the optimizer struggels with SORT-MERGE-JOIN that is done to satisfy this filter:
where (s.owner, s.name) not in ( select /*+ cardinality(el {skipped_objects_cardinality})*/el.owner, el.name from table(:l_skipped_objects) el )
I've changed the filter to be:
where not exists (
select /*+ cardinality(el {skipped_objects_cardinality})*/ 1
from table(:l_skipped_objects) el
where s.owner = el.owner and s.name = el.name
)
This, plus one more optimization, caused the plan to change
From (before change):
SQL_ID azu5xhknp5c0s, child number 0
-------------------------------------
with trigger_source_offsets as ( select min(s.line) - 1
offset, s.owner, s.name, s.type from dba_source s
where s.type = 'TRIGGER' and s.owner in (
select /*+ cardinality(t 3) */
upper(t.column_value) from
table(sys.odcivarchar2list('PMUSER')) t )
and (lower(s.text) like '%begin%' or lower(s.text) like '%declare%' or
lower(s.text) like '%compound%') group by s.owner, s.name,
s.type ), sources as ( select /*+ cardinality(f
{mappings_cardinality}) */ lower(s.type||'
'||s.owner||'.'||s.name) as full_name, s.owner, s.name, s.type,
s.line original_line, s.line - case when s.type =
'TRIGGER' then o.offset else 0 end as line, s.text
from dba_source s left join trigger_source_offsets o
on (s.owner = o.owner and s.name = o.name and s.type = o.
Plan hash value: 2662781388
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 120K|00:00:02.23 | 16811 | | | |
| 1 | SORT ORDER BY | | 1 | 9283 | 120K|00:00:02.23 | 16811 | 22M| 1737K| 19M (0)|
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 9283 | 120K|00:00:01.07 | 16811 | 1451K| 1451K| 727K (0)|
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 | 3 | 1 |00:00:00.01 | 0 | | | |
|* 4 | FILTER | | 1 | | 236K|00:00:01.13 | 16811 | | | |
|* 5 | HASH JOIN RIGHT OUTER | | 1 | 9283 | 276K|00:00:01.24 | 16811 | 1040K| 1040K| 1315K (0)|
| 6 | VIEW | | 1 | 8 | 741 |00:00:00.07 | 3173 | | | |
| 7 | HASH GROUP BY | | 1 | 8 | 741 |00:00:00.07 | 3173 | 906K| 906K| 1332K (0)|
| 8 | NESTED LOOPS | | 1 | 8 | 1425 |00:00:00.07 | 3173 | | | |
| 9 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 | 3 | 1 |00:00:00.01 | 0 | | | |
| 10 | VIEW | INT$DBA_SOURCE | 1 | 3 | 1425 |00:00:00.07 | 3173 | | | |
| 11 | UNION-ALL PARTITION | | 1 | | 1425 |00:00:00.07 | 3173 | | | |
|* 12 | FILTER | | 1 | | 1425 |00:00:00.07 | 3173 | | | |
| 13 | NESTED LOOPS | | 1 | 30 | 1425 |00:00:00.07 | 3170 | | | |
| 14 | NESTED LOOPS | | 1 | 212 | 20854 |00:00:00.03 | 2797 | | | |
|* 15 | HASH JOIN | | 1 | 4 | 741 |00:00:00.02 | 1530 | 1229K| 1229K| 1263K (0)|
| 16 | NESTED LOOPS | | 1 | 63 | 127 |00:00:00.01 | 3 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 18 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 19 | INDEX FULL SCAN | I_USER2 | 1 | 63 | 127 |00:00:00.01 | 1 | | | |
|* 20 | TABLE ACCESS FULL | OBJ$ | 1 | 36 | 1407 |00:00:00.08 | 1527 | | | |
|* 21 | INDEX RANGE SCAN | I_SOURCE1 | 741 | 53 | 20854 |00:00:00.01 | 1267 | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID | SOURCE$ | 20854 | 8 | 1425 |00:00:00.04 | 373 | | | |
|* 23 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 24 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 25 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 26 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 27 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 28 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
|* 29 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
|* 30 | HASH JOIN | | 0 | 4 | 0 |00:00:00.01 | 0 | 844K| 844K| |
|* 31 | HASH JOIN | | 0 | 4 | 0 |00:00:00.01 | 0 | 900K| 900K| |
| 32 | NESTED LOOPS | | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
| 33 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 35 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
|* 36 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 37 | INDEX FULL SCAN | I_USER2 | 0 | 63 | 0 |00:00:00.01 | 0 | | | |
|* 38 | FIXED TABLE FULL | X$JOXSCD | 0 | 7131 | 0 |00:00:00.01 | 0 | | | |
|* 39 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 40 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 41 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 42 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 43 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 44 | MERGE JOIN ANTI NA | | 1 | 9283 | 276K|00:00:01.04 | 13638 | | | |
| 45 | SORT JOIN | | 1 | 9340 | 276K|00:00:00.11 | 0 | 52M| 2537K| 46M (0)|
| 46 | VIEW | INT$DBA_SOURCE | 1 | 9340 | 276K|00:00:06.94 | 13638 | | | |
| 47 | UNION-ALL | | 1 | | 276K|00:00:06.90 | 13638 | | | |
|* 48 | FILTER | | 1 | | 276K|00:00:06.77 | 13638 | | | |
|* 49 | HASH JOIN | | 1 | 9375 | 276K|00:00:06.67 | 13383 | 1393K| 1393K| 1556K (0)|
| 50 | TABLE ACCESS FULL | USER$ | 1 | 63 | 127 |00:00:00.01 | 9 | | | |
|* 51 | HASH JOIN | | 1 | 9375 | 276K|00:00:00.35 | 13374 | 1557K| 1557K| 1295K (0)|
| 52 | INDEX FULL SCAN | I_USER2 | 1 | 63 | 127 |00:00:00.01 | 1 | | | |
| 53 | NESTED LOOPS | | 1 | 9375 | 276K|00:00:00.46 | 13373 | | | |
| 54 | NESTED LOOPS | | 1 | 9375 | 276K|00:00:00.23 | 7903 | | | |
|* 55 | TABLE ACCESS FULL | OBJ$ | 1 | 176 | 4612 |00:00:00.01 | 1527 | | | |
|* 56 | INDEX RANGE SCAN | I_SOURCE1 | 4612 | 53 | 276K|00:00:00.04 | 6376 | | | |
| 57 | TABLE ACCESS BY INDEX ROWID | SOURCE$ | 276K| 53 | 276K|00:00:00.09 | 5470 | | | |
|* 58 | TABLE ACCESS FULL | USER_EDITIONING$ | 85 | 1 | 0 |00:00:00.01 | 255 | | | |
| 59 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 60 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 61 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 62 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 63 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
|* 64 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 65 | NESTED LOOPS | | 0 | 5 | 0 |00:00:00.01 | 0 | | | |
| 66 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 67 | HASH JOIN | | 0 | 2 | 0 |00:00:00.01 | 0 | 973K| 973K| |
| 68 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
|* 69 | INDEX SKIP SCAN | I_OBJ1 | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
| 70 | INDEX FULL SCAN | I_USER2 | 0 | 63 | 0 |00:00:00.01 | 0 | | | |
| 71 | TABLE ACCESS CLUSTER | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 72 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 73 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 0 | 3 | 0 |00:00:00.01 | 0 | | | |
|* 74 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 75 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 76 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 77 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 78 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 79 | SORT UNIQUE | | 276K| 8168 | 0 |00:00:00.08 | 0 | 2048 | 2048 | 2048 (0)|
| 80 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 | 8168 | 1 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=UPPER(VALUE(KOKBF$)))
4 - filter("LINE"-CASE "TYPE" WHEN 'TRIGGER' THEN "O"."OFFSET" ELSE 0 END >0)
5 - access("OWNER"="O"."OWNER" AND "NAME"="O"."NAME" AND "TYPE"="O"."TYPE")
12 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
15 - access("O"."SPARE3"="U"."USER#" AND "O"."OWNER#"="U"."USER#")
18 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
20 - filter((DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' AND (("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#"))))
21 - access("O"."OBJ#"="S"."OBJ#")
22 - filter((LOWER("S"."SOURCE") LIKE '%compound%' OR LOWER("S"."SOURCE") LIKE '%declare%' OR LOWER("S"."SOURCE") LIKE '%begin%'))
23 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
25 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
26 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
27 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
28 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
29 - filter(NULL IS NOT NULL)
30 - access("O"."OBJ#"="S"."JOXFTOBN")
31 - access("O"."OWNER#"="U"."USER#")
34 - access("U"."NAME"=UPPER(VALUE(KOKBF$)))
36 - access("O"."SPARE3"="U"."USER#" AND "O"."TYPE#"=28)
filter("O"."TYPE#"=28)
38 - filter((LOWER("S"."JOXFTSRC") LIKE '%compound%' OR LOWER("S"."JOXFTSRC") LIKE '%declare%' OR LOWER("S"."JOXFTSRC") LIKE '%begin%'))
39 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
41 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
42 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
43 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
48 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
49 - access("O"."SPARE3"="U"."USER#")
51 - access("O"."OWNER#"="U"."USER#")
55 - filter(((("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#")) AND
(DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='FUNCTION' OR DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PACKAGE BODY' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PROCEDURE' OR DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TYPE BODY')))
56 - access("O"."OBJ#"="S"."OBJ#")
58 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
60 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
61 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
62 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
63 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
64 - filter(NULL IS NOT NULL)
67 - access("O"."OWNER#"="U"."USER#")
69 - access("O"."TYPE#"=28)
filter("O"."TYPE#"=28)
72 - access("O"."SPARE3"="U"."USER#")
73 - filter("O"."OBJ#"="S"."JOXFTOBN")
74 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
76 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
77 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
78 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
79 - access(INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
filter((INTERNAL_FUNCTION("NAME")=SYS_OP_ATG(VALUE(KOKBF$),2,3,2) AND INTERNAL_FUNCTION("OWNER")=SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 1 because of hint
- 4 Sql Plan Directives used for this statement
To (after change):
SQL_ID cxmqs2nu0hza4, child number 0
-------------------------------------
with sources as ( select /*+ cardinality(f
{mappings_cardinality}) */ lower(s.type||'
'||s.owner||'.'||s.name) as full_name, s.owner, s.name, s.type,
s.line original_line, s.line -
case when s.type = 'TRIGGER'
then min(case when lower(s.text) like '%begin%' or
lower(s.text) like '%declare%' or lower(s.text) like '%compound%' then
s.line-1 end) over (partition by s.owner,
s.type, s.name) else 0 end
as line, s.text from dba_source s
where s.type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE',
'FUNCTION', 'TRIGGER') and s.owner in (
select /*+ cardinality(t 3) */
upper(t.column_value) from
table(sys.odcivarchar2list('PMUSER')) t ) ),
coverage_sourc
Plan hash value: 716135486
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 120K|00:00:01.81 | 8460 | | | |
| 1 | SORT ORDER BY | | 1 | 197K| 120K|00:00:01.81 | 8460 | 22M| 1740K| 20M (0)|
|* 2 | HASH JOIN RIGHT ANTI | | 1 | 197K| 120K|00:00:00.86 | 8460 | 1483K| 1483K| 731K (0)|
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 | 333 | 1 |00:00:00.01 | 0 | | | |
|* 4 | VIEW | | 1 | 198K| 120K|00:00:00.81 | 8460 | | | |
| 5 | WINDOW SORT | | 1 | 198K| 122K|00:00:00.70 | 8460 | 15M| 1491K| 14M (0)|
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 198K| 122K|00:00:00.17 | 8460 | 1451K| 1451K| 730K (0)|
| 7 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 1 | 3 | 1 |00:00:00.01 | 0 | | | |
| 8 | VIEW | INT$DBA_SOURCE | 1 | 198K| 276K|00:00:00.41 | 8460 | | | |
| 9 | UNION-ALL | | 1 | | 276K|00:00:00.36 | 8460 | | | |
|* 10 | FILTER | | 1 | | 276K|00:00:00.27 | 8460 | | | |
|* 11 | HASH JOIN | | 1 | 199K| 276K|00:00:00.19 | 8073 | 1210K| 1210K| 1478K (0)|
|* 12 | HASH JOIN | | 1 | 4193 | 4612 |00:00:00.01 | 1537 | 1393K| 1393K| 1562K (0)|
| 13 | TABLE ACCESS FULL | USER$ | 1 | 63 | 127 |00:00:00.01 | 9 | | | |
|* 14 | HASH JOIN | | 1 | 4193 | 4612 |00:00:00.01 | 1528 | 1557K| 1557K| 1293K (0)|
| 15 | INDEX FULL SCAN | I_USER2 | 1 | 63 | 127 |00:00:00.01 | 1 | | | |
|* 16 | TABLE ACCESS FULL | OBJ$ | 1 | 4193 | 4612 |00:00:00.01 | 1527 | | | |
| 17 | TABLE ACCESS FULL | SOURCE$ | 1 | 199K| 508K|00:00:00.04 | 6536 | | | |
|* 18 | TABLE ACCESS FULL | USER_EDITIONING$ | 129 | 1 | 0 |00:00:00.01 | 387 | | | |
| 19 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 21 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 22 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 23 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
|* 24 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 25 | NESTED LOOPS | | 0 | 5 | 0 |00:00:00.01 | 0 | | | |
| 26 | NESTED LOOPS | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 27 | HASH JOIN | | 0 | 2 | 0 |00:00:00.01 | 0 | 973K| 973K| |
| 28 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
|* 29 | INDEX SKIP SCAN | I_OBJ1 | 0 | 4 | 0 |00:00:00.01 | 0 | | | |
| 30 | INDEX FULL SCAN | I_USER2 | 0 | 63 | 0 |00:00:00.01 | 0 | | | |
| 31 | TABLE ACCESS CLUSTER | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 32 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 33 | FIXED TABLE FIXED INDEX | X$JOXSCD (ind:1) | 0 | 3 | 0 |00:00:00.01 | 0 | | | |
|* 34 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 35 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 36 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 37 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 38 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2) AND "NAME"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
4 - filter("LINE">0)
6 - access("OWNER"=UPPER(VALUE(KOKBF$)))
10 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
11 - access("O"."OBJ#"="S"."OBJ#")
12 - access("O"."SPARE3"="U"."USER#")
14 - access("O"."OWNER#"="U"."USER#")
16 - filter(((("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#")) AND
(DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='FUNCTION' OR DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PACKAGE BODY' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='PROCEDURE' OR DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE
BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TRIGGER' OR
DECODE("O"."TYPE#",7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE
BODY',22,'LIBRARY',87,'ASSEMBLY','UNDEFINED')='TYPE BODY')))
18 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
22 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
23 - filter((( IS NULL AND "O"."TYPE#"<>88) OR BITAND("O"."FLAGS",1048576)=1048576 OR BITAND("U"."SPARE1",16)=0 OR
(((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR IS NOT NULL) AND IS NOT NULL)))
24 - filter(NULL IS NOT NULL)
27 - access("O"."OWNER#"="U"."USER#")
29 - access("O"."TYPE#"=28)
filter("O"."TYPE#"=28)
32 - access("O"."SPARE3"="U"."USER#")
33 - filter("O"."OBJ#"="S"."JOXFTOBN")
34 - filter(("TYPE#"=:B1 AND "UE"."USER#"=:B2))
36 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
37 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
38 - filter(("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 1 because of hint
- 4 Sql Plan Directives used for this statement
In your plans, the problem was line:
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
...
| 48 | MERGE JOIN ANTI NA | | 4060K| 7989M| | 1849K (1)| 00:01:13 |
| 49 | SORT JOIN | | 4060K| 7981M| 20G| 1849K (1)| 00:01:13 |
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------
...
| 48 | MERGE JOIN ANTI NA | | 31M| 59G| | 18M (1)| 00:12:16 |
| 49 | SORT JOIN | | 31M| 59G| 158G| 18M (1)| 00:12:16 |
Where you have 20G/158G of temp used to do a SORT JOIN
With the proposed change the SORT-MERGE-ANTI-JOIN should be replaced with more optimal HASH-JOIN-RIGHT-ANTI.
This will prevent the temp consumption for SORT operation.
Hopefully this will solve your issue. jgebal commented on Feb 22, 2022 @dsnz I'm closing this issue for now and consider it resolved.
Please reopen if you still face the issue when running coverage with utPLSQL v3.1.12 (released soon)
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|