Monthly Archives: March 2017

12c, Administration, Performance

Oracle 12c – SQL Tuning

Leave a comment

Adaptive query optimization in 12c allows for changes to execution plan at run time and gathering of new information to generate better statistics.  The two aspects of Adaptive Query Optimization:

  1. Adaptive Plan – Improving the initial execution of query at run time.
  2. Adaptive Statistics – Gathering new statistics to improve future executions.

 

Adaptive Plans – allows optimizer to make decision on SQL at runtime and in mid execution.  It will actually make sub-plans and collect statistics to make final decision on execution.

  1. This may cause a difference in the EXPAIN PLAN command and the DBMS_XPLAN.DISPLAY_CURSOR where the EXPLAIN PLAN will show the initial plan execution and DISPLAY_CURSOR will show the final plan executed.
  2. In oracle 12c only Join and Parallel queries are supported of adaptive plans.
  3. You can actually see all plans tried with the DBMS_XPLAN function by using the ‘+adaptive’ parameter with the notation (-) against the id column of plan table.
  4. V$SQL view has new column IS_RESOLVED_ADAPTIVE_PLAN which show if SQL has an adaptive plan. If it ‘Y’ the statement has adaptive plan which has been used, if ‘N’ it has an adaptive plan that is not completed yet and if NULL it has no adaptive plan.
  5. INIT parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to TRUE (default is FALSE) information on adaptive plan will be collected but not used. You can then use DBMS_XPLAN plan to view data with ‘+report”.

 

Adaptive Statistics:

Optimizer uses statistics in order to create good execution plans.  Re-optimization uses the execution of previous statements results to determine if the executed plan was optimal and adjusts plans for future execution in an iterative process.

Three types of re-optimization:

  1. Statistics Feedback – also known as cardinality feedback, it compares cardinality of estimated and actual executions. Storing the correct cardinality statistics and creates SQL plane directive for future use.
  2. Performance Feedback – Used to improve degree of parallelism for future SQL statements based on previous executions when Automatic Degree of Parallelism (AutoDOP) is enabled in adaptive mode.
  3. SQL plan directives – Is additional information used by optimizer to determine correct execution plans and are created on query expressions not SQL statements thus can be applied to multiple SQL statements. Multiple directives can be used for single SQL statement.  SQL directives are purged if not used in 53 days.  You can see directives in views:   DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS.

 

 

 

Partition table statistics – Two sets of statistics are gathered at the partition level and table level, in 12c the table level statistics are aggregated by partition level avoiding full table scan.

 

INCREMENTAL_STALENESS – preference setting in DBMS_STATS.SET_TABLE_PREFS allows you to determine when statistics on tables are stale by percentage of change with USE_STALE_PERCENT or locking statistics forcing their use regardless of how much change has occurred with USE_LOCKED_STATS.

 

Incremental statistics improved by EXCHANGE_PARTITION command allows you to statistics from non-partitioned table with those in a partition.  Prior to 12c you had to gather data on all partitions before using incremental global statistics.   Use the DBMS_STATS package preference of INCREMENTAL_LEVEL set to TABLE (the default is PARTITION) to use this EXCHANGE_PARTITION feature.

 

Concurrent Statistics – when global statistics gathering preference CONCURRENT is set, the Job Scheduler and Advance queuing gather states on table/partitions concurrently.  12c has optimized automatic statistics gathering by batching smaller jobs to run together.  When preference CONCURRENT is set to ALL or AUTOMATIC stats a gathered nightly.

 

 

Automatic column group detection – Gathering of statistics on columns used in filter predicates by estimating cardinality.   Determining columns to gather increased stats is determined by workload history.  Three step process determines Auto Column Group detection:

  1. Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
  2. Create the column groups – Use the DBMS_STATS.CREATE_EXTENDED_STATS function to id columns groups based on usage information gathered. Once identified extended statistics will be automatically generated.
  3. Regather statistics – Regather statistics for all tables identified for column stats extended.

 

 

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c -RMAN Backup Overview

Leave a comment

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set.    RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.

 

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE;

 

You can set the default backup to image copy with CONFIGURE DEVICE TYPE command

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

 

Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN.  You can limit the size of size of backup pieces by the MAXPIECESIZE option.   Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.

 

RMAN> BACKUP AS BACKUPSET DATABASE.

 

You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

 

You can backup the entire database with the command:

 

RMAN> BACKUP DATABASE;

 

Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.

 

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

 

Incremental Backups are part of a backup set that only contains changes to the database since the last full backup.   The benefit of an incremental backup is size of the backup and time required to tack the backup.   Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.

 

You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways

 

Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup

 

Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.

 

Example of incremental Level 1:

 

RMAN> backup incremental level 1 cumulative database;

 

RMAN> backup incremental level 1 database;

 

 

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Technology Overview

Leave a comment

Flashback table – by default when a table is dropped oracle does not immediately remove the space, but renames it and places the table and associated objects in recycle bin.   This allows it to be recovered at a later time.

 

The recycle bin is a data dictionary table that contains info needed to recover dropped tables.  Original table stays in place and occupies same space until purged or database needs space for storage.

 

tablespaces are not placed in recycle bin and once dropped are gone.   All tables in that tablespace are removed from recycle bin if they have previously been dropped.

 

Recycle bin can be disabled with recyclebin initialization parameter.

 

FLASHBACK TABLE ….  TO BEFORE DROP;    statement is used to recover tables from recyclebin.  You must use the system generated recyclebin name.  You can rename the table with RENAME clause during recovery.  The USER_RECYCLEBIN view can be used to find the system generated recyclebin name.

example:

 

FLASHBACK TABLE test2_bak TO BEFORE DROP RENAME test2;

 

Larry Catt

OCP

12c, tablespaces

Oracle 12c – Relocate SYSAUX occupants

Leave a comment

In Oracle the SYSAUX tablespace is an auxiliary storage location for the SYSTEM tablespace.  Many of the Oracle database components which are not critical to the core functionality reside in the SYSAUX tablespace.  If the SYSAUX tablespace becomes unavailable, core database will still function, though pieces of the database which rely on SYSAUX components will fail or have limited capabilities.  You can view the components of the SYSAUX tablespace through the view V$SYSAUX_OCCUPANTS  as described below:

 

SQL> desc v$SYSAUX_OCCUPANTS

 Name                                      Null?    Type

 —————————————– ——– —————————-

 OCCUPANT_NAME                                      VARCHAR2(64)

 OCCUPANT_DESC                                      VARCHAR2(64)

 SCHEMA_NAME                                        VARCHAR2(64)

 MOVE_PROCEDURE                                     VARCHAR2(64)

 MOVE_PROCEDURE_DESC                                VARCHAR2(64)

 SPACE_USAGE_KBYTES                                 NUMBER

 CON_ID                                             NUMBER

SQL>

 

Procedure shown in the MOVE_PROCEDURE column of V$SYSAUX_OCCUPANTS details procedure name to move the product. This article will detail the steps to move the LGNMR component from SYSAUX tablespaces to its own tablespace storage.

 

  1. Logon to your database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Wed Jan 11 15:04:41 EST 2017 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Connect to SQLPLUS as sysdba.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 12 09:03:45 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

SQL>

 

  1. Create a new tablespace called LOGMINER.

 

SQL> create tablespace LOGMINER datafile ‘/u01/oradata/orcl/orcl/LOGMNR_data01.dbf’ size 1g;

Tablespace created.

SQL>

 

  1. Extract the move_procedure from V$SYSAUX_OCCUPANTS view for LOGMNR.

 

SQL> select occupant_name, schema_name, move_procedure from v$sysaux_occupants where occupant_name=’LOGMNR’;

OCCUPANT_NAME

—————————————————————-

SCHEMA_NAME

—————————————————————-

MOVE_PROCEDURE

—————————————————————-

LOGMNR

SYSTEM

SYS.DBMS_LOGMNR_D.SET_TABLESPACE

SQL>

 

  1. Execute procedure passing the new tablespace name.

 

execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

 

SQL> execute SYS.DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMINER’);

PL/SQL procedure successfully completed.

SQL>

 

  1. This completes movement of LOGMNR from sysaux tablespace.

 

 

 

Larry Catt

OCP

12c, Administration, privileges, Users

Oracle 12c – User privilege analysis overview

Leave a comment

Oracle 12c contains the feature to analyze the privileges actually used by an individual user account in order to implement least privilege policies within the RDBMS.  This article gives an overview of User privilege analysis in Oracle 12c.

 

 

  1. Privilege analysis is provided by Oracle Data Vault and it allows for creation of profile which captures system and object privileges used by a user.
  2. This profile can be used to bounce used privileges against granted privileges.
  3. You can reduce privileges that are not in use through this analysis.
  4. It is possible to perform privilege analysis with or without have Database Vault configured and enabled.
  5. It is possible to administer privilege analysis by EM Cloud Control or the package DBMS_PRIVILEGE_CAPTURE
  6. The role CAPTURE_ADMIN grants execute privilege on DBMS_PRIVILEGE_CAPTURE package and select on views to results.
  7. The DBMS_PRIVILEGE_CAPTURE package allows you to create/enable/disable/drop privilege analysis policies.
  8. Only one privilege analysis policy can be enabled in the database at a time, but the DBMS_PRIVILEGE_CPATURE.G_DATABASE privilege analysis can be done with one other user defined policy at the same time.
  9. Analysis policies running will still be running after DB restart.
  10. Privilege analysis policies must be disabled, before reports can be generated.
  11. Policies must be disabled before they are dropped.
  12. Dropping a privilege policy, also drops all collected data by that policy.
  13. List of some view available with Privilege Analysis:
    1. DBA_PRIV_CAPTURES — Lists information about existing privilege analysis policies
    2. DBA_USED_PRIVS — Lists the privileges that have been used for reported privilege analysis policies
    3. DBA_UNUSED_PRIVS — Lists the privileges that have not been used for reported privilege analysis policies
    4. DBA_USED_OBJPRIVS — Lists the object privileges that have been used for reported privilege analysis policies. It does not include the object grant paths.
    5. DBA_UNUSED_OBJPRIVS — Lists the object privileges that have not been used for reported privilege analysis policies. It does not include the object privilege grant paths.
    6. DBA_USED_SYSPRIVS — Lists the system privileges that have been used for reported privilege analysis policies. It does not include the system privilege grant paths.
    7. DBA_UNUSED_SYSPRIVS — Lists the system privileges that have not been used for reported privilege analysis policies. It does

Larry Catt

OCP

 

12c, EM, Oracle

EM Express 12c Configuration Page

Leave a comment

Oracle EM Express Configuration Page provides the ability to administrate the initialization parameters of an instance or multiple instances in a RAC configuration; view the memory configuration and usage; usage of various features; and database properties.  This article covers the general layout of EM Express Configuration Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

  1. From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.
  2. Configuration drop down give the four options: Initialization Parameters, Memory, Database Feature Usage, and Current Database Properties.                                                                                                                                                             
  3. Initialization Parameters lists all current instance parameters and SPFILE parameters. From this page you can adjust most parameters.                                                                
  4. Memory option gives more details of memory usage that currently exists and allows the administrator a visual prospective of current environment.                         
  5. Database Feature Usage lists the features of database used by a user or application. This allows the administrator the ability to focus on features which may present a performance problem.         
  6. Current Database Properties lists the value of all database property variables. However this view does not give the ability to update this values.                                                               

 

 

 

This article summarized the feature and use of the Configuration Page of Oracle 12c EM Express.

 

Larry Catt

OCP

Cloud Control, EM, EM Express, Oracle

EM Express 12c Configuration Page

Leave a comment

Oracle EM Express Configuration Page provides the ability to administrate the initialization parameters of an instance or multiple instances in a RAC configuration; view the memory configuration and usage; usage of various features; and database properties.  This article covers the general layout of EM Express Configuration Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.