GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — Coverage Report doesn't run (or takes forever) · Issue #970 · utPLSQL/utPLSQL

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

New issue
Copy link
New issue
Copy link
Closed
#981
Closed
Coverage Report doesn't run (or takes forever)#970
#981
Copy link
Labels
bug
Milestone
v3.1.8

Description

@rafael-trevisan
rafael-trevisan
opened on Jul 7, 2019
Issue body actions

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.

Activity

Next
  1. lwasylow commented on Jul 7, 2019

    @lwasylow
    lwasylow
    on Jul 7, 2019
    Member
    More actions

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

  2. rafael-trevisan commented on Jul 7, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 7, 2019
    Author
    More actions

    Hey, I'll try it.

  3. jgebal commented on Jul 7, 2019

    @jgebal
    jgebal
    on Jul 7, 2019
    Member
    More actions

    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.

  4. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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.

  5. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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 :)

    Reacted by tetardv
  6. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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

  7. pesse commented on Jul 8, 2019

    @pesse
    pesse
    on Jul 8, 2019
    Member
    More actions

    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

  8. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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)
    ...
    ...
    ...
    
  9. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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  
    
  10. rafael-trevisan commented on Jul 8, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 8, 2019
    Author
    More actions

    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.

  11. rafael-trevisan commented on Jul 9, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 9, 2019
    Author
    More actions

    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?

  12. lwasylow commented on Jul 9, 2019

    @lwasylow
    lwasylow
    on Jul 9, 2019
    Member
    More actions

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

  13. rafael-trevisan commented on Jul 9, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 9, 2019
    Author
    More actions

    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.

  14. lwasylow commented on Jul 9, 2019

    @lwasylow
    lwasylow
    on Jul 9, 2019
    Member
    More actions

    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?

  15. rafael-trevisan commented on Jul 9, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 9, 2019
    Author
    More actions

    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?

  16. 10 remaining items

  17. tetardv commented on Jul 12, 2019

    @tetardv
    tetardv
    on Jul 12, 2019
    More actions

    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

  18. PhilippSalvisberg commented on Jul 12, 2019

    @PhilippSalvisberg
    PhilippSalvisberg
    on Jul 12, 2019
    Member
    More actions

    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)?

  19. rafael-trevisan commented on Jul 12, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 12, 2019 via email
    Author
    More actions
    Yes, definitely. I meant as a workaround until 2020 when Oracle replies the SR
    …
    On Jul 12, 2019, at 5:47 AM, Jacek Gębal ***@***.***> wrote: 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. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.
  20. PhilippSalvisberg commented on Jul 12, 2019

    @PhilippSalvisberg
    PhilippSalvisberg
    on Jul 12, 2019
    Member
    More actions

    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.

  21. rafael-trevisan commented on Jul 12, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 12, 2019
    Author
    More actions

    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.

  22. PhilippSalvisberg commented on Jul 12, 2019

    @PhilippSalvisberg
    PhilippSalvisberg
    on Jul 12, 2019
    Member
    More actions

    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.

  23. PhilippSalvisberg commented on Jul 12, 2019

    @PhilippSalvisberg
    PhilippSalvisberg
    on Jul 12, 2019
    Member
    More actions

    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.

  24. rafael-trevisan commented on Jul 14, 2019

    @rafael-trevisan
    rafael-trevisan
    on Jul 14, 2019
    Author
    More actions

    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.

    Reacted by Philipp Salvisberg and tetardv
  25. jgebal commented on Jul 14, 2019

    @jgebal
    jgebal
    on Jul 14, 2019
    Member
    More actions

    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.

  26. PhilippSalvisberg
    added
    bug
    on Jul 16, 2019
  27. tetardv commented on Jul 16, 2019

    @tetardv
    tetardv
    on Jul 16, 2019
    More actions

    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.

  28. jgebal commented on Jul 16, 2019

    @jgebal
    jgebal
    on Jul 16, 2019
    Member
    More actions

    @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?

  29. fabiopan commented on Jul 17, 2019

    @fabiopan
    fabiopan
    on Jul 17, 2019
    More actions

    @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

  30. jgebal
    added this to the v3.1.8 milestone on Jul 17, 2019
  31. PhilippSalvisberg
    closed this as completedin #981on Jul 18, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bug

    Type

    No type

    Projects

    No projects

      Milestone

      • v3.1.8

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions

      You are visitor 01402503 — GeezerVison does not store the pages you look up.
      Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.