Here I’m putting some example code on how to use SQLPatch for HINTing SQLs when it’s not possible to touch the application code or use other techniques to fix execution plan.
- work since 18.104.22.168
- are internally based on SQL Profiles;
- may be used to add HINTs to SQLs when you can’t touch code(like with Packaged Application);
- don’t require Diagnostics or Tuning Pack Licenses;
- sql text is normalized before storing, so additional spaces and case mismatch is not a problem;
- don’t handle SQL embedded literals, except through unpublished call to I_CREATE_SQL_PROFILE – see example later;
- RESULT_CACHE hint is honored since 22.214.171.124 and 12.2 – Check BUG:16974854;
- to check if SQL Profile was used add +NOTE when displaying execution plan
- SELECT * FROM table(dbms_xplan.display_cursor(format=>’+NOTE‘));
- to use HINTs with corrent syntax get them from desired execution plan:
- SELECT * FROM table(dbms_xplan.display_cursor(format=>’+OUTLINE‘));
Funny situation was discovered recently by one of our customers. Actually, it was an incident with Core Banking System, so, in reality, it wasn’t Funny….
I have checked and found that this issue reproduces not only with customer’s database using software version 126.96.36.199, but with version 188.8.131.52 with latest Oracle Database Proactive Bundle Patch(160719) installed.
To reproduce issue you need: Continue reading
Here I’ll describe some strange/interesting/ behaviour of Global Application Context connected with usage of Session Client Identifier, that is quite usually met in Application Express, J2EE Agent, Oracle Data Integrator starting from release 184.108.40.206 and many other applications that uses JDBC Connection Pooling in conjunction with setting Session Client Identifier.
So what is the problem ?
The problem is that Global Application Context doesn’t work as expected and described in documentation – attributes’ VALUES ARE NOT VISIBLE… Continue reading
Oracle RDBMS PatchSet version 220.127.116.11 is available for downloading since 27-AUG-2013 ! again some time earlier than OOW2013
But(as of 29-AUG-2013) it’s still available only for Linux x86-64 + Linux x86 and all Oracle Solarises(x86, x86-64, SPARC 32-bit, SPARC 64-bit)
Checked on 29-OCT-2013 – patchset aditionally available for next platforms: Microsoft Windows(32-bit), Microsoft Windows x64(64-bit), HP-UX Itanium(32-bit), HP-UX Itanium(64-bit), IBM AIX on POWER Systems (32-bit), IBM AIX on POWER Systems (64-bit)
Based on NOTE:742060.1 Release Schedule of Current Database Releases:
18.104.22.168 on Linux x66(new platform ? ;)) released – this joke no more present in mentioned document 😉
- this is the terminal patch set for 11.2 Continue reading
Today I was contacted by one of our customers with very non-typical issue on their 10.2.0.4 database. They have added new column to one table at the middle of the day and now they are not able to compile some of invalidated packages.
Now I’m clarifying what does it mean ‘NOT ABLE TO COMPILE‘:
ALTER PACKAGE PKG_ERP_MAIN COMPILE;
ALTER PACKAGE PKG_ERP_MAIN COMPILE BODY;
Package body altered.
select object_type,status from dba_objects where object_name='PKG_ERP_MAIN';
PACKAGE BODY INVALID
Funny! Isn’t ? Continue reading
I have faced quite interesting situation with Flashback Data Archive(FBDA) that wasn’t covered yet in blogosphere.
So I have just created new Flashback Archive and altered some tables specifying it(Flashback Archive).
Just after some time of any DMLs on altered tables next error appears in alert.log
Starting background process FBDA
Thu Feb 21 11:07:07 2013
FBDA started with pid=203, OS id=3626
Errors in file /u01/app/oracle/diag/rdbms/siam_opt/siamopt/trace/orcl_fbda_3626.trc (incident=57628):
ORA-00600: internal error code, arguments: [ktfa_error_handler_map], , 
ORA-00942: table or view does not exist
Please, notice presence of additional funny error – ORA-00942. Why it may be here ? we’ll see later… Continue reading
Just short post about Something Coming Soon from mentioned note
Modified:Jan 23, 2013
COMING SOON!!! Stay Tuned
Still have to wait 😉