Repository navigation
Optimize availability check for "Run utPLSQL test" menu option #76
Description
In the preferences you can configure if you want to check for the availability of the menu option "Run utPLSQL test". When set, the extension enables the menu option only when at least one test has been found. This may be slow for two reasons:
-
because the annotation cache needs to be updated
this happens only, if you do not use a DDL trigger to track changes, in this case changes are detected delayed, when running a test or when checking for availability of tests -
large schemas
there is more data in the annotation cache and in the data dictionary, affecting the runtime performance of the queries, especially when no DDL trigger is in place, due to delayed annotation detection
Since version 3.1.8 the following functions are available in ut_runner:
has_suitesis_suiteis_test
These functions are optimized for performance and are faster than the generic table function get_suites_info. Hence these functions should be used whenever possible by the SQL Developer extension.
In fact these functions are available since utPLSQL version 3.1.3, but since version 3.1.8 they are considered reliable (see also utPLSQL/utPLSQL#976). Hence the use of this functions should be limited to utPLSQL version 3.1.8 and higher.
Activity
Merge pull request #77 from PhilippSalvisberg/feature/76_optmize_chec…
jgebal commentedon Jul 16, 2019
@phsalvisberg
I would need to double check but I think it was reliable before.
Only develop (3.1.8-develop) had a bug as I went too far with optimizetions.
Let me check change history to see.
PhilippSalvisberg commentedon Jul 16, 2019
@jgebal I checked. My JUnit test case fails with 3.1.7. All functions return false instead of true, because they do not update the annotation cache (without using the DDL trigger). I have not tested that with 3.1.8 yet. Maybe this is an issue there as well.
jgebal commentedon Jul 16, 2019
The functionality was introduced in this change
Since then it wasn't changed until this change and then this change in 3.1.8-develop
I'm no sure why the functions don't work for you in 3.1.7. They should as they should update cache.
Perhaps it's not worth digging in old versions.
If this didn't work for you before 3.1.8, lets leave it active since 3.1.8 only.
PhilippSalvisberg commentedon Jul 16, 2019
The problem is that
has_suite,is_suiteandis_testdo not check if the annotation cache is up-to-date. This is a problem when you install utPLSQL withinstall_headless.sqlinstead ofinstall_headless_with_trigger.sql. Hence this works in the develop environment and in our CI environment whereinstall_headless_with_trigger.sqlis used behind the scenes. These functions do not deliver a reliable result. Even in 3.1.8 :-(PhilippSalvisberg commentedon Jul 16, 2019
IMHO the functions should check if the DDL trigger is active. If not they have to update the annotation cache first. Similar logic as in
get_suites_info.PhilippSalvisberg commentedon Jul 16, 2019
If this will not be fixed in 3.1.8 I'll have to undo this change.
jgebal commentedon Jul 16, 2019
They do, and they always did.
line
Is responsible for doing the check and cache refresh.
PhilippSalvisberg commentedon Jul 16, 2019
It's not working.
jgebal commentedon Jul 16, 2019
I can have a look at this in the evening.
PhilippSalvisberg commentedon Jul 16, 2019
@jgebal Why have you reopened this issue?
jgebal commentedon Jul 16, 2019
Because we're discussing it and As you said, you might need to revert the change.
Feel free to close if you want. I can open a different issue on utPLSQL
PhilippSalvisberg commentedon Jul 16, 2019
Ok, I checked it again.
It works as expected (without trigger) in v3.1.8.
It does not work (without trigger) in v3.1.7. JUnit test fails.
Hence I close this issue.