Impressum

Responsible for content:

Peter Ramm 
Hauptstraße 18 
D-01723 Oberhermsdorf 
Germany 

Email: Peter@ramm-oberhermsdorf.de

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: New SQL Diagnostic Report in rel. 19.28

Image
One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report. This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more. The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL . Which edition or option pack license is needed Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function. As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL. The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor". Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation: The note "How To U...
Read more

Oracle-DB: Cleanup Unified Audit Trail with dynamic number of rows and oldest timestamp, but limited storage size

For housekeeping of Unified Audit Trail in a recent project there were some objectives: A hard rule that audit records must be kept in all cases for at least x days A hard rule that the stored audit records should not exceed a limited storage size, except the the above age limit requires this A soft objective thet audit records should be stored as long as possible if the storage size limit is not reached The package DBMS_AUDIT_MGMT only provides the method CLEAN_AUDIT_TRAIL with the ability to cut the records to purge at a timestamp limit. This would require to estimate a maximum age of held audit records that ensures also in worst cases that the storage size limit will not be exceeded. In result, outside the worst case of audit volume you will purge the audit records far too early. You could have stored them much longer because there's enough space below the storage limit. The following SQL script accepts all named objectives by ensuring: Audit records younger ...
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more