Oracle Database 12c – Page 3

“Oracle *daily* TidBits” (#oratidbit) published at https://www.facebook.com/oraclenotes on weekdays in November 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 orapki command line utility enables administrators to manage wallets, certificate revocation lists, and other public key infrastructure (PKI) elements from the command line. It can be used inside scripts, enabling administrators to automate many routine PKI tasks.

#oratidbit By default, Oracle database audit trail records are written to the SYSAUX tablespace. You can designate a different tablespace by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

#oratidbit In #DB12c Oracle Database writes unified audit records to system global area (SGA) queues and then periodically writes them to the AUDSYS schema audit table in the SYSAUX tablespace.

#oratidbit UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter sets the size of SGA area where unified audit queues are saved before writing to AUDSYS schema.

#oratidbit Use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure to manually purge the audit trail from Oracle Database.

#oratidbit In #DB12c the READ object privilege and READ ANY TABLE system privilege are more secure than SELECT and SELECT ANY TABLE privileges. SELECT privilege includes LOCK capability on the table/rows.

#oratidbit In #DB12c DB_WRITER_PROCESSES can be up to 100. The processes are DBW0-DBW9, DBWa-DBWz and BW36-BW99.

#oratidbit #DB12cR2 Duties that belonged exclusively to PMON now belong to the PMON process group, which includes PMON, Cleanup Main Process (CLMN), and Cleanup Helper Processes (CLnn). This process group is responsible for the monitoring and cleanup of other processes.

#oratidbit #DB12cR2 Optimizer Statistics Advisor analyzes how you are currently gathering statistics, the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor maintains rules, which embody Oracle best practices based on the current feature set.

#oratidbit #DB12cR2 #Oracle Sharding is a scalability and availability feature for suitable OLTP applications in which data is horizontally partitioned across discrete Oracle databases, called shards, which share no hardware or software.

#oratidbit #DB12cR2 You can relocate a PDB from one CDB to another with minimal down time.

#oratidbit #DB12cR2 During a PDB clone operation, the source PDB no longer needs to be in read-only mode.

#oratidbit #DB12cR2 You can create a proxy PDB, which references a PDB in a different CDB and provides fully functional access to the referenced PDB.

#oratidbit #DB12cR2 You can use FLASHBACK PLUGGABLE DATABASE command to rewind a PDB to any SCN without affecting other PDBs in a CDB. You can also create a restore point specific for a PDB, and rewind the PDB to this restore point without affecting other PDB.

#oratidbit #DB12cR2 A PDB lockdown profile is a security mechanism to restrict operations that are available to users connected to a specified PDB.

#oratidbit #DB12cR2 You can configure PDB parameters to guarantee or limit SGA memory, PGA memory, sessions, CPU, and I/O rates for each PDB.

#oratidbit #DB12cR2 You can create local, nonshared temporary tablespaces in PDB. When many read-only instances access a single database, local temporary tablespaces can improve performance for queries that involve sorts, hash aggregations, and joins.

#oratidbit #DB12cR2 You can pre-create a pool of server processes by using the DBMS_PROCESS PL/SQL package. The new Process Manager (PMAN) background process monitors the pool of pre-created processes, which wait to be associated with a client request.

#oratidbit #DB12cR2 Read-only and read-write instances can co-exist within a single database. To designate an instance as read-only, set the INSTANCE_MODE initialization parameter to READ_ONLY. The default value of the parameter is READ_WRITE.

#oratidbit #DB12cR2 In the root of a CDB, you can configure a single port (known as the global port), which enables you to use EM Express to connect to all of the PDBs in the CDB using the HTTPS port for the CDB.

#oratidbit #DB12cR2 You can capture Automatic Workload Repository (AWR) data for the PDBs in a multitenant environment. This feature enables performance tuning of PDBs in a multitenant environment.

#oratidbit #DB12cR2 In EM Express, you can view real-time performance statistics for a standby database in the Performance Hub.

Approximate Query Processing appears to be a major feature of Oracle Database 12c Release 2 SQL. In 12cR1 patchset 2 (12.1.0.2), we saw one approximate query function appear – APPROX_COUNT_DISTINCT to use instead of COUNT(DISTINCT). In 12.2 there are several APPROX functions introduced:

APPROX_COUNT_DISTINCT_DETAIL
APPROX_COUNT_DISTINCT_AGG
TO_APPROX_COUNT_DISTINCT
APPROX_MEDIAN
APPROX_PERCENTILE
APPROX_PERCENTILE_DETAIL
APPROX_PERCENTILE_AGG
TO_APPROX_PERCENTILE

According to Oracle documentation, “Approximate query processing is primarily used in data discovery applications to return quick answers to explorative queries. Users typically want to locate interesting data points within large amounts of data and then drill down to uncover further levels of detail. For explorative queries, quick responses are more important than exact values.”

The interesting part is that you can utilize the Approximate functions without changing code. There are three initialization parameters introduced to control which functions should be treated as an approximate function during run time… cool!

The initialization parameters are:

approx_for_aggregation
approx_for_count_distinct
approx_for_percentile

To replace only the COUNT(DISTINCT) function in queries with the APPROX_COUNT_DISTINCT function, set the approx_for_count_distinct initialization parameter to TRUE for the current session or for the entire database.

To replace percentile functions with the corresponding functions that return approximate results, set approx_for_percentile to PERCENTILE_CONT, PERCENTILE_DISC, or ALL (replaces all percentile functions) for the current session or for the entire database.

To run all queries using the corresponding SQL functions that return approximate results instead of the specified SQL functions, set the approx_for_aggregation initialization parameter to TRUE for the current session or for the entire database.

Category: Oracle Database 12c

Oracle Tidbits – November 2016 #oratidbit

Approximate Query Processing in 12c Release 2