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

Panorama: Determining candidates for storage reorganization in Oracle-DB

Image
Knowing that Oracle-DB does not automatically release storage below the high water mark it would be necessary to do it yourself. Usually you may scan your database from time to time to identify tables, indexes or lobs where reorganization of these objects can make significant amount of storage in tablespace available for other objects. The PL/SQL package DBMS_SPACE allows consideration of free space in DB blocks for an object. This information can be used to forecast the effect of object reorganization. Unfortunately DBMS_SPACE.Space_Usage takes longer time for evaluation of larger objects, so it is quite expensive to scan a whole schema or system using DBMS_SPACE. Following I'll show how the performance analysis tool Panorama  handles determining candidates for reorganization. To determine candidates for deeper inspection Panorama uses a simple calculation based on avg. row-length, Pct_Free and Ini_Trans to list suspected objects that are worth to reorganize. Sorting th...
Read more

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