Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Coverage Report doesn't run (or takes forever) #970

Closed
rafael-trevisan opened this issue Jul 7, 2019 · 37 comments · Fixed by #981

Comments

@rafael-trevisan
Copy link

commented Jul 7, 2019

The coverage report doesn't run or takes forever.

version: 18.0.0.0.0
compatibility: 18.0.0
ut_version: v3.1.3.2402
Edition: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0
Port String: x86_64/Linux 2.4.xx

Information about client software
SQLDeveloper, SQLPlus, utPLSQL-cli

To Reproduce
./utplsql run ***/***@host:1521/*** -f=ut_coverage_html_reporter -o=coverage.html

Expected behavior
A HTML file with the coverage report

Details of the issue
A couple of "utPLSQL" and "JDBC Thin Client" database sessions get stuck waiting forever for the following wait-events:

  • resmgr:cpu quantum
  • PL/SQL lock timer
  • PX Deq: Execute Reply

The SQL running queries are:

select full_name
       , owner
       , name
       , line
       , to_be_skipped
       , text
from (
  select lower(s.owner || '.' || s.name) as full_name
         , s.owner
         , s.name
         , s.line - coalesce(
    case
      when type != 'TRIGGER' then
        0
    end
  ,(
    select min(t.line) - 1
    from dba_source t
    where t.owner = s.owner
          and t.type = s.type
          and t.name = s.name
          and regexp_like(t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*', 'i')
  )) as line
         , s.text
         , case
    when
                     -- to avoid execution of regexp_like on every line
                     -- first do a rough check for existence of search pattern keyword
     ( lower(s.text) like '%procedure%'
           or lower(s.text) like '%function%'
           or lower(s.text) like '%begin%'
           or lower(s.text) like '%end%'
           or lower(s.text) like '%package%' )
         and regexp_like ( s.text
    , '^([\t ]*(((not)?\s*(overriding|final|instantiable)[\t ]*)*(static|constructor|member)?[\t ]*(procedure|function)|package([\t ]+body)|begin|end([\t ]+\S+)*[ \t]*;))'
    , 'i' ) then
      'Y'
  end as to_be_skipped
  from dba_source s
  where s.owner in (
    select upper(t.column_value)
    from table ( :l_schema_names ) t
  )
        and s.type not in (
    'PACKAGE'
    , 'TYPE'
    , 'JAVA SOURCE'
  )
             --Exclude calls to utPLSQL framework, Unit Test packages and objects from a_exclude_list parameter of coverage reporter
        and ( s.owner
  , s.name ) not in (
    select el.owner
           , el.name
    from table ( :l_skipped_objects ) el
  )
)
where line > 0

and

select column_value as text
from table ( ( :b1 ).get_lines ( :b2
, :b3 ) )

Running the tests works fine. Only the Coverage Report doesn't run.

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jul 7, 2019

Hi did you try 3.1.7? There were improvements in terms of overall performance.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 7, 2019

Hey, I'll try it.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jul 7, 2019

What schema are you running coverage on?
How many code objects (packages /triggers/procedures etc) do you have in that schema?
If the query is stuck I suspect it's due to huge schema or lack of up to date fixed table statistics.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

I'm running on the application schema.

Not many packages/triggers. About 30 packages and 60 triggers. Shouldn't take this long. I remember running the same "Coverage Report" not so long ago and it was fine. The only big change I can recall was changing the Oracle from non-pdb to pdb. Other than that is the same. I will get utPLSQL upgraded in the server this week and will try again.

Table stats seem to be fine too.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

Hi guys, I upgraded utPLSQL to 3.1.7 but it didn't fix the issue.

Whenever I run a Coverage Report it gets stuck.

The findings I have so far are:

  • it seems utPLSQL ends up opening 2 sessions (module utPLSQL) and these 2 sessions run the same query in parallel (px), however, one session gets blocked by the other. When this happens the CPU on the server goes up to 100% and then it never finishes running the report.

I will be troubleshooting this today. If someone has any idea or suggestion they'd be very appreciated :)

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

Another finding here:

The DBA also pointed another related session under the module "JDBC Thin Client". He said this session was waiting due to a dbms_session.lock

Don't know if that helps somehow

@pesse

This comment has been minimized.

Copy link
Member

commented Jul 8, 2019

Hey @rafael-trevisan , which version of utplsql-cli are you using?
You can find out by doing ./utplsql info
It would also be great if you did a run with -d parameter and send me the output:
./utplsql run ***/***@host:1521/*** -f=ut_coverage_html_reporter -o=coverage.html

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

Here you go

ip-192-168-1-87:bin Rafael$ ./utplsql run ***/***@***:1521/*** -d  -f=ut_coverage_html_reporter -o=coverage.html
2019-07-08 12:25:30 [main] INFO  org.utplsql.cli.RunCommand - ########## utPLSQL cli ###########
#                                #
#   utPLSQL-cli 3.1.6.local      #
#   utPLSQL-java-api 3.1.6.411   #
#   Java-Version: 1.8.0_191      #
#   ORACLE_HOME: null            #
#   NLS_LANG: null               #
#                                #
#   Thanks for testing!          #
#                                #
##################################
2019-07-08 12:25:30 [main] INFO  org.utplsql.cli.RunCommand - 
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:oracle:oci8:@***:1521/***
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................3
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................3
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"***"
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
2019-07-08 12:25:30 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
2019-07-08 12:25:30 [main] DEBUG c.z.hikari.util.DriverDataSource - Loaded driver with class name oracle.jdbc.OracleDriver for jdbcUrl=jdbc:oracle:oci8:@***:1521/***
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName.................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:oracle:thin:@***:1521/***
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................3
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................3
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"***"
2019-07-08 12:25:30 [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
2019-07-08 12:25:30 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
2019-07-08 12:25:30 [main] DEBUG c.z.hikari.util.DriverDataSource - Loaded driver with class name oracle.jdbc.OracleDriver for jdbcUrl=jdbc:oracle:thin:@***:1521/***
2019-07-08 12:25:31 [main] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@646d64ab
2019-07-08 12:25:31 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
2019-07-08 12:25:31 [main] INFO  o.u.c.d.TestedDataSourceProvider - Use connection string jdbc:oracle:thin:****/****@***:1521/***
2019-07-08 12:25:31 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=1, active=1, idle=0, waiting=0)
2019-07-08 12:25:31 [main] INFO  org.utplsql.cli.RunCommand - Successfully connected to database. UtPLSQL core: v3.1.7.3096
2019-07-08 12:25:31 [main] INFO  org.utplsql.cli.RunCommand - Oracle-Version: 18.0.0.0.0
2019-07-08 12:25:32 [main] DEBUG c.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Executed rollback on connection oracle.jdbc.driver.T4CConnection@646d64ab due to dirty commit state on close().
2019-07-08 12:25:32 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@115564
2019-07-08 12:25:32 [main] DEBUG org.utplsql.api.reporter.Reporter - Database-reporter initialized, Type: UT_COVERAGE_HTML_REPORTER, ID: 8D31685B819C05C4E053BA0216AC68B7
2019-07-08 12:25:32 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Added connection oracle.jdbc.driver.T4CConnection@7a179d7e
2019-07-08 12:25:32 [HikariPool-1 connection adder] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - After adding stats (total=3, active=0, idle=3, waiting=0)
2019-07-08 12:25:33 [pool-1-thread-1] INFO  org.utplsql.cli.RunTestRunnerTask - Running tests now.
2019-07-08 12:25:33 [pool-1-thread-1] INFO  org.utplsql.cli.RunTestRunnerTask - --------------------------------------
2019-07-08 12:25:33 [pool-1-thread-1] INFO  org.utplsql.api.TestRunner - TestRunner initialized
2019-07-08 12:25:33 [pool-1-thread-1] INFO  org.utplsql.api.TestRunner - Running on utPLSQL v3.1.7.3096
2019-07-08 12:25:33 [pool-1-thread-1] INFO  org.utplsql.api.TestRunner - Initializing reporters
2019-07-08 12:25:34 [pool-1-thread-1] INFO  org.utplsql.api.TestRunner - Running tests
2019-07-08 12:26:01 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=3, active=2, idle=1, waiting=0)
2019-07-08 12:26:31 [HikariPool-1 housekeeper] DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=3, active=2, idle=1, waiting=0)
...
...
...
@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

The session gets stuck waiting for PX and this is the explain plan:

SELECT STATEMENT   Cost = 61
SORT AGGREGATE  
    PX COORDINATOR   
        PX SEND QC (RANDOM) :TQ10000 
            SORT AGGREGATE  
                PX PARTITION LIST ALL  
                    EXTENDED DATA LINK FULL INT$DBA_SOURCE 
VIEW   
    MERGE JOIN ANTI NA  
        SORT JOIN  
            HASH JOIN SEMI  
                PX COORDINATOR   
                    PX SEND QC (RANDOM) :TQ20000 
                        PX PARTITION LIST ALL  
                            EXTENDED DATA LINK FULL INT$DBA_SOURCE 
                COLLECTION ITERATOR PICKLER FETCH  
        SORT UNIQUE  
            COLLECTION ITERATOR PICKLER FETCH  
@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 8, 2019

Another thing here.

If I run within SQLDev the Coverage Report for a single package, it works:

begin
  ut.run(
    'my_schema', ut_coverage_html_reporter(),     
     a_include_objects => ut_varchar2_list('my_package')
  );
end;

However, if I remove the filter for the objects, it gets stuck again with the same wait event in the same query.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 9, 2019

Hi guys,

Some update on this.

The DBA did some investigation, and after commenting out (for only testing purpose) the line 60 of ut3.ut_coverage we got the Coverage Report working here.

The line we commented out is:

and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i')

If we put this line back, the session gets stuck again and never finishes.

Would this be safe to use as a workaround or could that bring potentially problem that we aren't aware?

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jul 9, 2019

I believe it will report incorrectly on lines for triggers. Would be interesting to see what executions plans are selected.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 9, 2019

I will try to get the execution plan here.

Would there be a way to run the Coverage Report only for packages then? I mean, I know there are parameters to filter including/excluding object names, but am not sure about object types.

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jul 9, 2019

I don't think so but that's probably a good enhancements idea.
Do you have an source code of packages in file? If so you can try to use a a_source_file_mappings and if you point on folder with source code that have only packages than it will limit. But that's very depend on how your db project is setup. Maybe @jgebal got some idea?

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 9, 2019

I do have all my source code in files. I tried to use this mapping parameter once but I didn't get much lucky.

My current folder structure is:

./db
    src
        packages
            package1.pks
            package1.pkb
            package2.pks
            package2.pkb
    test
        test_package1.pks
        test_package1.pkb
        test_package2.pks
        test_package2pkb

How would be the a_source_file_mappings with this structure?

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jul 10, 2019

From docs: http://utplsql.org/utPLSQL/v3.1.7/userguide/coverage.html
If its a single owner schema it should be straight forward for BASH e.g.:

utPLSQL-cli/bin/utplsql run test_runner/pass@db_url \
 -p=tests_owner \
 -source_path=src -owner=code_owner \
 -test_path=tests     -owner=tests_owner \
 -f=ut_coverage_html_reporter -o=coverage.html \
 -f=ut_sonar_test_reporter    -o=test_results.xml

In that section of documentation there are couple good examples.

@tetardv

This comment has been minimized.

Copy link

commented Jul 11, 2019

Hello,
Have you been able to workaround this issue ? I am running into it as well, it runs for 2 hours and then timeout. I have tried to use CLI and directly in SQL dev, without success. I have double checks installation process and given rights, increase tablespaces, execute coverage report multiple times so it might finish...

Regards,
Vincent.

@lwasylow

This comment has been minimized.

Copy link
Member

commented Jul 11, 2019

Also in PDB?

@tetardv

This comment has been minimized.

Copy link

commented Jul 11, 2019

Yes.

@tetardv

This comment has been minimized.

Copy link

commented Jul 11, 2019

Well I have tried with your 'utPLSQL-demo-project-develop' and it is working so I do believe the framework do not like big schema.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 11, 2019

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

@rafael-trevisan, I'm not sure what is causing the problem. But your database configuration leads to a parallel query on the data dictionary views. I made some tests and forced parallel executions with various degrees. In my cases this lead to much longer execution times.

Hence, I suggest to try the following based on the example you posted previously.

alter session disable parallel query;
alter session disable parallel dml;
begin
  ut.run(
    'my_schema', ut_coverage_html_reporter()
  );
end;
/
@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 12, 2019

@pesse

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

Currently, you can't make cli set specific session parameters.
This possibility might be added in a future version:
utPLSQL/utPLSQL-cli#103

@jgebal

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

Shouldn't this be fixed at DB itself?
Seems like your database is misconfigured and querying dba_source goes parallel causing performance issiues.
It will be hard for utplsql to address all possible db config challenges.
We could add noparrallel hint to coverage query though I'm not sure if it's right thing to do.
We might fix it for some and break it for others.

@tetardv

This comment has been minimized.

Copy link

commented Jul 12, 2019

hello,
for me disabling parallel query and dml remove the timeout, so now my coverage has been running for more than 6hours now. The database im running into is docker image created from Oracle repo itself, I can make any change I want to since I am administering it even if I am not the dba of my company. But what parameters should I change ?

regards,
vincent

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

Would be helpful to hint these queries that access the data dictionary to not run in parallel?

@rafael-trevisan first of all we have to find the root cause. So, the questions remains. How long did the anonymous PL/SQL block ran in your environment (with disable parallel query and parallel dml)?

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 12, 2019

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

Yes, definitely. I meant as a workaround until 2020 when Oracle replies the SR

@rafael-trevisan and what is the runtime without parallel query/dml? Without this information we do not know if this is a viable workaround.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 12, 2019

Yes, definitely. I meant as a workaround until 2020 when Oracle replies the SR

@rafael-trevisan and what is the runtime without parallel query/dml? Without this information we do not know if this is a viable workaround.

I'll uncomment the line and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i') and will test it. Will share the results here.

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

But what parameters should I change ?

@tetardv I suggest to run

begin
   dbms_stats.gather_dictionary_stats;
end;
/

before trying to run the coverage report again.

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member

commented Jul 12, 2019

I'll uncomment the line and regexp_like( t.text, '[A-Za-z0-9$#_]*(begin|declare|compound).*','i') and will test it. Will share the results here.

But we already know that commenting out this line helps and that it produces a false result. So, I suggest to test it without changing the utPLSQL code. Thanks.

@rafael-trevisan

This comment has been minimized.

Copy link
Author

commented Jul 14, 2019

Hi all, disabling the parallel in the session didn't resolve the issue. It changed indeed the wait event from PX deq something to db file sequential read, but it never finished running the report.

After continuing the investigation it became clear that and regexp_like(... was making the inner query to be very very slow. When we changed the line 60 to be a set of or t.text like '%begin%' (pseudo-code just as example), it ran beautifully.

Interestingly, line 66 seems to have thought about this issue 🤔

Anyways, in the end the DBA changed a bit the query to keep the regex_like but he used the with command to query the dba_source just once and then pointed the outer and inner query to use this "with dataset". This workaround also worked beautifully (even faster than having or like or like or like) and it's the one we decided to keep.

We will post here the code he changed, but I hope this comment helps to understand and explain the issue.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jul 14, 2019

Thanks @rafael-trevisan
I will include that fix (once you publish it).
I also have another improvement for coverage query waiting to be published.
You can see it here

The idea is to separate the more complex query (on Triggers) from the one that is more straightforward.
If your schema doesn't contain triggers, you should see it running a bit faster too.

@tetardv

This comment has been minimized.

Copy link

commented Jul 16, 2019

hello,
Removing the line 'and regexp_like( t.text, '[A-Za-z0-9$#_](begin|declare|compound).','i')' worked for me as well. And @PhilippSalvisberg only created the html file with no input, it find no objects at all.
looking forward to have this issue fix then. Thank you guys.

Regards,
vincent.

@jgebal

This comment has been minimized.

Copy link
Member

commented Jul 16, 2019

@rafael-trevisan

Anyways, in the end the DBA changed a bit the query to keep the regex_like but he used the with command to query the dba_source just once and then pointed the outer and inner query to use this "with dataset". This workaround also worked beautifully (even faster than having or like or like or like) and it's the one we decided to keep.

Can you provide the modified query text?

fabiopan pushed a commit to fabiopan/utPLSQL that referenced this issue Jul 17, 2019
@fabiopan

This comment has been minimized.

Copy link

commented Jul 17, 2019

@rafael-trevisan

Anyways, in the end the DBA changed a bit the query to keep the regex_like but he used the with command to query the dba_source just once and then pointed the outer and inner query to use this "with dataset". This workaround also worked beautifully (even faster than having or like or like or like) and it's the one we decided to keep.

Can you provide the modified query text?

Below is the change we have made.
#980

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
7 participants
You can’t perform that action at this time.