Relational Databases

Before Oracle Database 23ai, the awrsqrpt.sql script was commonly used to analyze the evolution of a SQL statement’s execution plan, plan statistics, CPU usage, I/O, and elapsed time over a specified period. However, this approach had two main limitations: It required specifying two specific AWR snapshots. The information provided in its report was relatively limited. Sample Output of awrsqrpt.sql: Press enter or click to view image in full size With the introduction of Oracle Database 23ai, a new function, REPORT_SQL, was added to the  DBMS_SQLDIAG  package. This function generates a comprehensive HTML diagnostic report for a specified SQL statement, providing deeper performance insights. This functionality is also backported to Oracle Database 19.28. The REPORT_SQL function provides a graphical report in HTML format, allowing for the analysis of: Execution plan history Cursor sharing information Optimizer statistics history Index details And more REPORT_SQL Function: SQL ...

  As you know, in a RAC environment when creating a service, you can define   preferred instances   and   available instances . In this case, the service will by default run on the preferred instance. If for any reason the preferred instance fails, or the entire node becomes unavailable, the service will be moved to the available instance. Even after restarting the preferred instance, the service will not automatically fail back; it will continue running on the available instance. To move it back to the preferred instance, you need to manually perform a  relocate  operation. Example: In this example, we create a service named  mysrv  in Oracle 11g: [ grid@node1 ~ ]$ crsctl query crs releaseversion Oracle High Availability Services release version on the local node is [ 11.2 .0 .4 .0 ] [ oracle@node1 ~ ]$ srvctl add service -d mydb -s mysrv -r instance1 -a instance2 After creating the service, start it. You will see that the service is runnin...

  In Oracle 12cR1, a new feature was introduced that allowed us to enable or disable the   compression   property for tables during a dump import. This was done using the   TRANSFORM   parameter in the   impdp   command: SQL * Plus: Release 12.2 .0 .1 .0 Production SQL > select compression from dba_tables where table_name = 'TBL1' ; COMPRESS -------- DISABLED SQL > drop table usef.tbl1; Table dropped. [oracle @oshost ~ ]$ impdp usef / a directory = drm dumpfile = test.dmp tables = usef.tbl1 TRANSFORM = TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\" SQL > select compression,COMPRESS_FOR from dba_tables where table_name = 'TBL1' ; COMPRESS COMPRESS_FOR -------- ------------------------------ ENABLED ADVANCED This feature was only available at the  table level , and there was no way to control index compression during import (in Oracle 12c). However, in  Oracle 21c , as a new feature, you can now enable (...