“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in March 2016. You will also see these tidbits, one tidbit at a time, for each page refresh on the right side of this blog as well… Hope you find these helpful to learn something new or to remind you of its existence and use…
| #oratidbit The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of AWR snapshot IDs. Run this report to inspect or debug the performance of a SQL statement. |
| #oratidbit Generate Performance Hub active report by running the perfhubrpt.sql SQL script. Performance Hub active report enables you to view all performance data available for a time period that you specify. Different tabs are available in the Performance Hub, depending on whether real-time or historical data is selected for the time period. |
| #oratidbit After you do EXPLAIN PLAN on a statement, use UTLXPLS.SQL to display the plan table output for serial processing, or use UTLXPLP.SQL for including parallel execution columns. Using DBMS_XPLAN.DISPLAY can also used. |
| #oratidbit All SGA components except the redo log buffer allocate and deallocate space in units of contiguous memory called granules. Granule size is platform-specific and is determined by total SGA size. |
| #oratidbit Every Oracle ASM disk is divided into allocation units (AU). An AU is the fundamental unit of allocation within a disk group. The values can be 1, 2, 4, 8, 16, 32, or 64 MB, depending on the specific disk group compatibility level. Larger AU sizes typically provide performance advantages for data warehouse applications that use large sequential reads. |
| #oratidbit V$SQL_PLAN contains the execution plan for every statement stored in the shared SQL area. |
| #oratidbit The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor. |
| #oratidbit In 12c 12.1.0.2, the Oracle ASM filter driver rejects any I/O requests that are invalid. This action eliminates accidental overwrites of Oracle ASM disks that would cause corruption in the disks and files within the disk group. |
| #oratidbit Typical Job role separation groups on Linux Oracle RDBMS install are: OSDBA (dba), OPOPER (oper), OSBACKUPDBA (backupdba), OSDGDBA (dgdba), OSKMDBA (kmdba). ASM install groups are: OSASM (asmadmin), OSDBA (asdba), OSOPER (asmoper). And in practical world for most DBAs, all groups are “dba”! |
| #oratidbit In 12c, Oracle Cluster Registry Backup supported on ASM Disk Group. Storing the OCR backup in an Oracle ASM disk group simplifies OCR management by permitting access to the OCR backup from any node in the cluster should an OCR recovery become necessary. |
| #oratidbit In 12c, Oracle Grid Infrastructure and Oracle Real Application Clusters can no longer be installed on 32-bit systems. |
| #oratidbit To prepare Linux system for Oracle Grid or RDBMS install run the Oracle Preinstallation RPM (OEL6) or Oracle Validated RPM (OEL5). yum install oracle-rdbms-server-12cR1-preinstall. |
| #oratidbit Specify DEFERRED clause in CREATE MATERIALIZED VIEW to indicate that the materialized view is to be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite. This is a good option to get all materialized views created quickly. |
| #oratidbit You cannot create a database link in another user’s schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name as the name of a database link in your schema. |
| #oratidbit The ALTER DATABASE LINK statement is intended only to update password for fixed-user database links. For all other changes, the database link must be dropped and recreated, including connected user change. |
| #oratidbit FLASHBACK ARCHIVE ADMINISTER and CREATE TABLESPACE system privileges are required to create flashback data archive. Flashback data archives retain historical data for the time duration specified and data queried using the Flashback Query AS OF clause. |
| #oratidbit To create a normal restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege. To create a guaranteed restore point, you must have the SYSDBA system privileges. |
| #oratidbit In #DB12c to enable or disable in-memory queries, you can specify the INMEMORY or NO_INMEMORY hints, which are the per-query equivalent of the INMEMORY_QUERY initialization parameter. |
| #oratidbit In 12c, DGMGRL command, VALIDATE DATABASE, enables you to determine whether a database is ready to perform a role change. The checks use information available in various Oracle Data Guard views as well as the Automatic Diagnostic Repository. |
| #oratidbit in CDB 12c standby architecture, redo from primary must always shipped to the service defined on the root container (CON_ID=1). |