Ok. We need to dig out the cache mechanism for annotations then.

I like the idea of configurable name pattern to reduce scanning work

We can't really do a skip-scan, as we build suite hierarchy and execute setup from parent suite even when you explicitly call child suite.
Cache seems the only reasonable option here.
I already had a PoC of cache some time back (January or so) just need to update it and refactor existing code to use it.
This could be part of 3.1.0 release.
20 minutes is not acceptable for scanning.

Are you interested in contributing?

Not sure of how cache can help here. If you talk about plsql tables, then it's suitable for the second run in the same session.
Jenkins, for example, creates new session for every build, such a cache won't work here.
May be result cache can help... other solutions look more complex
But the simpliest solution is a prefix with assumption that all the test packages use the same naming convention. Again, it will be used for large objects only and probably it is not so often used feature and may be complex solution does not makes sense here

Hi,

Have we confirmed that this is the all_source scan? I've seen slow access on some versions and experienced some Oracle bugs in this area.

What version are we talking about?

Do we have instrumentation that helps diagnose these types of issues? We should add.

Once confirmed that the issue is as reported we should capture an execution plan for the query and take things from there.

Adding a new cache adds more complexity with synchronisation. We should try and avoid this if possible.

Definitely worth generating a large test suite to reproduce in this project - even if this not run continuously.

Cheers

Hi!

Version 3.0.3 which is about to be released soon, uses dba_ views if they are accessible which significantly speeds up the parsing.

As you said even if we use cache there will be the "first" execution which will still take 20 mins. Not an option... I'd measure the influence of dba views first.

Only the first execution ever would take 20 minutes.
All others would benefit from cache and only package spec compiled after last run would need scanning.

Anyway. 3.0.3 had some performance improvements for 12.1 and 12.2 related to dbms_preprocessor slowness.
Please try it once it is released and let us know if it helps.

I'll give some details. there is a loop in the UT_SUITE_MANAGER package, line 274 (see below)
It walks thow all the valid packages in current schema and parses data into memory.
APPS schema in OEBS (for example) has 40k valid packages. ut test puts some data into memory and tries to parse then. judging by v$active_session_history, it takes about 1G of memory. Seems like all the code is fetched into memory before parse. The solution is not suitable for large projects, cache won't fix the issue.
I can see 2 easy ways to fix it

1. Add the optional prefix name restriction to the loop (which I had to do in my installation)

2. use separate schema for the test packages and grant all the preveliges to the objects being tested.

   -- form the single-dimension list of suites constructed from parsed packages
   for rec in (select t.owner
   ,t.object_name
   from all_objects t
   where t.owner = a_owner_name
   and t.status = 'VALID' -- scan only valid specifications
   and t.object_type in ('PACKAGE')
   ) loop
   -- parse the source of the package
   l_suite := config_package(rec.owner, rec.object_name);

   if l_suite is not null then
   l_all_suites(l_suite.path) := l_suite;
   end if;

   end loop;

Hi @mikechip1980
We do not keep all packages in memory, we do them one by one. We only keep annotations.
Dbms_preporessor in current version was causing huge slowness on 12.1 and 12.2 installations.
I'm not sure why the session would consume 1 GB of memory. That doesn't seem right and would need to be investigated separately.
For now I would really like you to wait and try version 3.0.3
You can also add a feature request to allow execution of suites by part of a name (pattern).
This should like a nice extension. We need well documented requirements.

Hi @mikechip1980 , can you install v3.0.3 and give feedback on it's prformance?
It will allow us to prioritize further actions.

Hello!
At first, I'd like to thank you for the new release! I really appreciate the efforts of people wrking on it.
So, the first execution of a simple test after 3.0.3 upgrate took 24 min 32 sec. The second run took 1 sec.
Putting here tha last 100 lines of the session history(see link below). As you can see, the process occupied more than 2Gb of PGA. I think the prefixed scan is the only change which could help with such a projects.
https://drive.google.com/open?id=0B87VBUz-5kNaYlMzTElKZm1VQTA

Here is sql for the report
select sample_time, (select sql_text from v$sql where sql_id=h.sql_id and rownum=1) sql_text, pga_allocated from ( select * from v$active_session_history h where session_id=318 order by sample_id desc) h where rownum<100

@Pazus I think we need to take a close look at the way we use our CLOB's.
https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:2957424542334
Most probably we have memory leaks in CLOB handling.

@mikechip1980
Just for clarity:

• what DB version are you on?
• what OS is the DB on?
• what is the exact call that you execute to run your tests?

@mikechip1980
How many Unit Test packages (annotated) are there in the schema you are referring to?

I did a test on my DB in a schema with over 500 packages.

declare
  l_pga  number;
  l_pga1 number;
  function get_stat_val( p_name in varchar2 ) return number
  as
    l_val number;
  begin
   select b.value
     into l_val
     from v$statname a, v$mystat b
    where a.statistic# = b.statistic#
      and a.name = p_name;
    return l_val;
  end;
begin
  l_pga := get_stat_val( 'session pga memory' );

  ut.run('my_schema:com.my_company.my_project.my_module');

  l_pga1 := get_stat_val( 'session pga memory' );
  if ( (l_pga1-l_pga) <> 0 ) then
      dbms_output.put_line ( ' pga before ' || l_pga || '.... ' || (l_pga1-l_pga) || ' pga change' );
      dbms_output.put_line ( ' pga after ' || l_pga1 );
  end if; 
end;

... 
Finished in .635632 seconds
36 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 
 pga before 8416888.... 2752512 pga change
 pga after 11169400

So it seems that the run took 2.7MB of PGA.
Consecutive runs do not cause PGA to grow, so there is no memory leak in the main code.
I didn't check the expectations in detail yet.

Are you sure that it's not the unit test itself that is causing excessive PGA usage?
Can you try running a smaller test?

Sorry for not reading in details:
You're working on schema that has 40.000 packages!
Did you consider putting unit tests in a separate schema, outside of production code?
The suite manager should not use much memory unless the package specs of those 40k packages use annotation-style comments --%something.
Can you check for the presence of those?

If you find such annotation-style comment lines, can you share some of them?

Hello!

what DB version are you on?
12.1.0.2.0
what OS is the DB on?
NAME="Oracle Linux Server"
VERSION="6.8"
what is the exact call that you execute to run your tests?

begin
ut.run('apps:unit.fnd.auto_test.utplsql3.stringutils1');
end;
where the test is just call of substr function with some expectation.

Don't think this actually matters, there are 5563151 lines of the code in package specifications in APPS schema, only the query below takes 2 minutes. There are no invalid packages.

select count(1) from dba_source where owner='APPS' and type='PACKAGE'

I think the version and os don't matters if test scans all the code, it's just a huge data and won't work fast. The only simple way is to take a piece of it, as I suggested, using prefix. I've put the prefix into ut_suite_manager package and now the first test takes seconds.

Putting the tests into separate schema is a good option, I'll try it (This idea I put also into my previos posts). The schema should have access to all the APPS objects. Will see if it helps

There are 758 lines returned by query
select count(1) from dba_source where owner='APPS' and text like '--%\%%' escape '\' and type='PACKAGE'

but basically these are lines like this. Please give me another template if you would like me to check.
-- p_extra_info_rec Yes per_assignment_extra_info%ROWTYPE

I'm afraid that caching won't fix our problem. We use unit testing on our continuous integration build server. After every build the build server will get a flashback to its starting point, ready for the next build.

Can you check on your DB how long it takes for this query to execute in EBS schema?

select t.owner, t.object_name 
  from all_objects t 
 where t.owner = :a_owner_name 
   and t.status = 'VALID' and t.object_type in ('PACKAGE')
   and t.object_name IN (
     select name from all_source 
      where owner = :a_owner_name and lower(text) like '%suite%'
  )

and same on dba_views

select t.owner, t.object_name 
  from dba_objects t 
 where t.owner = :a_owner_name 
   and t.status = 'VALID' and t.object_type in ('PACKAGE')
   and t.object_name IN (
     select name from dba_source 
      where owner = :a_owner_name and lower(text) like '%suite%'
  )

That could give us a clue on possible hotfix.

Idea is to limit the scanned sources to those that are unit test suites.

@mikechip1980
would cache work for you?
@safokkens - it seems like placing tests in other schema would be the only option for you then unless you can flashback to a point with cache created

The first query takes 20.647 seconds.
The second query 72.139 seconds.

Ok, so up to 80-100 seconds to start the runner

Guess that would be much better than what you face now

much better indeed! For now I'll try putting my tests in another schema.

@safokkens @mikechip1980 ,
Would you be able to help me with testing on Oracle EBS schema for this branch?
https://github.com/utPLSQL/utPLSQL/tree/feature/annotations_restructuring

It should give a huge performance boost for:

• initial scan / first run of tests
• consecutive run in same or different session
• consecutive run after package/packages get recompiled

Your help in validating performance improvements is much appreciated as you were facing significant performance issues.

Sorry for my late reaction, I had a few days off.

I tried the branch you mentioned. It needed a small modification before I was able to run it. I tried pushing my change back to github, but I'm not authorized.

I changed ut_annotation_manager.pkb, line 37 "ut3" in:
]'||l_ut_owner||q'[

After that, I tried to measure the execution time but I failed. Do you know how I can empty the cache for a clean new run?

Hi, please delete from ut_annotation_cache_info
I will look into the issue you reported and see why it was not captured by our tests.

Thank you!
On my test system I got 47.789 seconds, pretty impressive!
As you would imagine, after that the run takes around 0.2 seconds every time.

50 seconds for initial scan on EBS schema - nice! :)

Thinking about it, a tried adding a filter to limit the packages that are searched for annotations.
Because in EBS there is a standard where user-created packages are named 'XX.....' I added

name like 'XX%

After clearing the buffer, I got 4.7 seconds! So, maybe you can add this (undocumented?) filter for EBS systems. This is not a naming convention by us, but an Oracle standard.

Link

To reduce the risk that your custom application short name could conflict with a future Oracle E-Business Suite short name, we recommend that your custom application short name begins with "XX". Oracle reserves all three to four character codes starting with the letter O, the letters CP, and the letter E, as well as all names currently used by Oracle E-Business Suite products (query all applications in the Applications window).

When using this convention, there won't be an unit test package that doesn't start with 'XX'. People who don't use the convention don't have the benefits of filtering. They'll get an initial time of 47 seconds.

With cache introduced in annotations_restructuring, only the scan with cache invalidated or empty will take long. This means:

• when all packages got recompiled
• when utPLSQL cache is empty (new install)

Limiting by object name prefix is very strong restriction and isn't the direction I'd like to go.

When designing, we wanted to keep architecture open, so it is applicable to various projects, that have their own requirements and constraints.

I tried the branch you mentioned. It needed a small modification before I was able to run it. I tried pushing my change back to github, but I'm not authorized.

@safokkens, to contribute to the project, you need to:

• have your own fork of utPLSQL project
• make the changes you want to make in your own repo
• create a Pull Request to our repo (either develop or specific feature branch)

You can have a look into contributing guide

I understand, but as mentioned earlier, we have to flashback our database every run. The database for unit tests is our continuous integration database: every release gets built and tested the same way. Therefore, every run we have to make an initial scan.

I do understand you can't make modifications for every project using utPLSQL, but I'm thinking about Oracle EBS users in general. That's why an optional restriction on the package name (flexible or fixed 'XX%') might be a solution for all those users.

Maybe the filter shouldn't be on the name, but some kind of flag for Oracle EBS systems?

BTW: thanks for showing the contributing guide!

Since you run your tests with CI, which is definitely a way to go, could you take the initial snapshot of the DB with:

• initial state
• utPLSQL installed
• utPLSQL executed once on EBS schema (even without any custom code installed)

If you would put all of the above as initial state of your DB, utPLSQL would start within ~<1 sec.

For schema-level object filtering by name prefix, the only way i would see it is to have it as parameter for the run command.
Still, the side effect would be, if there are dependencies to objects not matching filter (parent suites in a hierarchy), the suite hierarchy would be built incorrectly.

This is the main reason for not putting any ability to filter the scanning - so we're always scanning for parent suites when executing a nested suite.

Okay, I tried your suggestion by only deleting object_name like 'XX%' from ut_annotation_cache_info.
Now it takes 15 seconds to finish. Because the build server is dynamically created: is it possible to export this table and load it on every run, or do I need more tables than that one?

You need 2 tables:
ut_annotation_cache and ut_annotation_cache_info

I would hold with that however and wait for the chage to be released