Oracle White Paper
The Oracle Optimizer Explain the Explain Plan
Introduction
The purpose of the Oracle Optimizer is to determine the most efficient execution plan for your queries.
It makes these decisions based on the statistical information it has about your data and by leveraging Oracle database features such as hash joins, parallel query, partitioning, etc. Still it is expected that the optimizer will generate sub-optimal plans for some SQL statements now and then. In cases where there is an alternative plan that performed better than the plan generated by the optimizer, the first step in diagnosing why the Optimizer picked the sub-optimal plan is to visually inspect both of the execution plans.
Examining the different aspects of an execution plan, from selectivity to parallel execution and understanding what information you should be gleaming from the plan can be overwhelming even for the most experienced DBA. This paper offers a detailed explanation about each aspect of the execution plan and an insight into what caused the CBO to make the decision it did.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
[ 2017-04-30 ]
[ 2017-04-21 ]
Oracle Critical Patch Update - April 2017
April 18th, 2017
Oracle Critical Patch Update for April 2017
The Critical Patch Update for April 2017 was released on April 18th, 2017. Oracle strongly recommends applying the patches as soon as possible.
If you are new to this process, please review Oracle's Security Fixing Policies and the Critical Patch Update Advisory. After reviewing these resources, if you are unable to determine if you require a software update, or how to apply it, please contact Oracle Support.
The Critical Patch Update Advisory is the starting point for relevant information. It includes the list of products affected, pointers to obtain the patches, a summary of the security vulnerabilities for each product suite, and links to other important documents. Supported products that are not listed in the "Affected Products and Components" section of the advisory do not require new patches to be applied.
Also, it is essential to review the Critical Patch Update supporting documentation referenced in the Advisory before applying patches, as this is where you can find important pertinent information.
Critical Patch Update Advisories are available at the following location:
Oracle Critical Patch Update for April 2017
The Critical Patch Update for April 2017 was released on April 18th, 2017. Oracle strongly recommends applying the patches as soon as possible.
If you are new to this process, please review Oracle's Security Fixing Policies and the Critical Patch Update Advisory. After reviewing these resources, if you are unable to determine if you require a software update, or how to apply it, please contact Oracle Support.
The Critical Patch Update Advisory is the starting point for relevant information. It includes the list of products affected, pointers to obtain the patches, a summary of the security vulnerabilities for each product suite, and links to other important documents. Supported products that are not listed in the "Affected Products and Components" section of the advisory do not require new patches to be applied.
Also, it is essential to review the Critical Patch Update supporting documentation referenced in the Advisory before applying patches, as this is where you can find important pertinent information.
Critical Patch Update Advisories are available at the following location:
[ 2017-04-19 ]
Understanding Optimizer Statistics With Oracle Database 12c Release 2 (White Paper)
Oracle White Paper
Understanding Optimizer Statistics With Oracle Database 12c Release 2
Introduction
When the Oracle database was first introduced, the decision of how to execute a SQL statement was determined by a Rule Based Optimizer (RBO). The Rule Based Optimizer, as the name implies, followed a set of rules to determine the execution plan for a SQL statement.
In Oracle Database 7, the Cost Based Optimizer (CBO) was introduced to deal with the enhanced functionality being added to the Oracle Database at this time, including parallel execution and partitioning, and to take the actual data content and distribution into account. The Cost Based Optimizer examines all of the possible plans for a SQL statement and picks the one with the lowest cost, where cost represents the estimated resource usage for a given plan. The lower the cost, the more efficient an execution plan is expected to be. In order for the Cost Based Optimizer to accurately determine the cost for an execution plan, it must have information about all of the objects (tables and indexes) accessed in the SQL statement, and information about the system on which the SQL statement will be run.
This necessary information is commonly referred to as optimizer statistics. Understanding and managing optimizer statistics is critical for achieving optimal SQL execution. This whitepaper is the first in a two part series on optimizer statistics and describes the core concepts of what statistics are and what types are statistics are used by the Oracle Optimizer. The second paper in the series (Best Practices for Gathering Optimizer Statistics with Oracle Database 12c) covers how to keep optimizer statistics up-to-date so that they accurately represent the data that’s stored in the database.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
Understanding Optimizer Statistics With Oracle Database 12c Release 2
Introduction
When the Oracle database was first introduced, the decision of how to execute a SQL statement was determined by a Rule Based Optimizer (RBO). The Rule Based Optimizer, as the name implies, followed a set of rules to determine the execution plan for a SQL statement.
In Oracle Database 7, the Cost Based Optimizer (CBO) was introduced to deal with the enhanced functionality being added to the Oracle Database at this time, including parallel execution and partitioning, and to take the actual data content and distribution into account. The Cost Based Optimizer examines all of the possible plans for a SQL statement and picks the one with the lowest cost, where cost represents the estimated resource usage for a given plan. The lower the cost, the more efficient an execution plan is expected to be. In order for the Cost Based Optimizer to accurately determine the cost for an execution plan, it must have information about all of the objects (tables and indexes) accessed in the SQL statement, and information about the system on which the SQL statement will be run.
This necessary information is commonly referred to as optimizer statistics. Understanding and managing optimizer statistics is critical for achieving optimal SQL execution. This whitepaper is the first in a two part series on optimizer statistics and describes the core concepts of what statistics are and what types are statistics are used by the Oracle Optimizer. The second paper in the series (Best Practices for Gathering Optimizer Statistics with Oracle Database 12c) covers how to keep optimizer statistics up-to-date so that they accurately represent the data that’s stored in the database.
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
[ 2017-04-18 ]
PSU Jan 2017 integrado a la Base Image 17.2.1.0.0 para bases single-instance
Otra de las novedades presentadas en Abril para la Base Image 17.2.1.0.0, es la integración del PSU de enero 2017 para despliegues en single-instance y configuraciones Dataguard (siempre de una única instancia). Con esto, los nuevos despliegues de "Oracle Database Cloud Services" incorporarán todas las funcionalidades incluidas en el nuevo PSU.
Aqui los datos del nuevo PSU. Patch 25732082 PATH SET UPDATE 12.1.0.2.170117
Aqui los datos del nuevo PSU. Patch 25732082 PATH SET UPDATE 12.1.0.2.170117
Voy a comprobar la incorporación del nuevo PSU creando un nuevo despliegue:
Me conecto a la VM:
[oracle@TEST12cR1 ~]$ ps -ef|grep pmon
oracle 2862 24274 0 12:43 pts/0 00:00:00 grep pmon
oracle 20426 1 0 Apr13 ? 00:00:15 ora_pmon_DB12CR1
Voy a utilizar la forma para verificar parches que trata en su blog Mike Dietrich:
[oracle@TEST12cR1 ~]$ sqlplus / as sysdba
SQL> select xmltransform(dbms_qopatch.is_patch_installed('24732082'), dbms_qopatch.get_opatch_xslt) from dual;
XMLTRANSFORM(DBMS_QOPATCH.IS_PATCH_INSTALLED('24732082'),DBMS_QOPATCH.GET_OPATCH
--------------------------------------------------------------------------------
Patch Information:
24732082: applied on 2017-02-08T10:31:23Z
Ahora de la forma tradicional consultando al inventory:
[oracle@TEST12cR1 ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.8
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/dbhome_1/oraInst.loc
OPatch version : 12.2.0.1.8
OUI version : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/opatch2017-04-16_12-37-27PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-04-16_12-37-27PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: TEST12cR1.compute-arprosegur.oraclecloud.internal
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
There are 1 products installed in this Oracle Home.
<<< Parte de la salida fué omitida >>>
Patch 24732082 : applied on Wed Feb 08 10:31:23 UTC 2017
Unique Patch ID: 20904347
Patch description: "Database Patch Set Update : 12.1.0.2.170117 (24732082)"
Created on 21 Dec 2016, 07:15:01 hrs PST8PDT
Sub-patch 24006101; "Database Patch Set Update : 12.1.0.2.161018 (24006101)"
Sub-patch 23054246; "Database Patch Set Update : 12.1.0.2.160719 (23054246)"
Sub-patch 22291127; "Database Patch Set Update : 12.1.0.2.160419 (22291127)"
Sub-patch 21948354; "Database Patch Set Update : 12.1.0.2.160119 (21948354)"
Sub-patch 21359755; "Database Patch Set Update : 12.1.0.2.5 (21359755)"
Sub-patch 20831110; "Database Patch Set Update : 12.1.0.2.4 (20831110)"
Sub-patch 20299023; "Database Patch Set Update : 12.1.0.2.3 (20299023)"
Sub-patch 19769480; "Database Patch Set Update : 12.1.0.2.2 (19769480)"
Bugs fixed:
21099555, 22175564, 19141838, 22083366, 20842388, 20117253, 19865345
19791273, 21542577, 20951038, 19243521, 22165897, 19908836, 21281532
19238590, 24577566, 21184223, 19134173, 20031873, 20387265, 21575362
19149990, 21263635, 17551063, 18886413, 22160989, 22507210, 19366375
19703301, 19001390, 24285405, 18202441, 20267166, 19358317, 19706965
24739928, 19068970, 18549238, 18797519, 22148226, 20825533, 21196809
19649152, 19670108, 18940497, 18948177, 22496904, 18964978, 19035573
19176326, 20413820, 19176223, 21106027, 20904530, 20134339, 19074147
20868862, 18411216, 21072646, 21322887, 22507234, 20425790, 18966843
21329301, 20562898, 19333670, 20124446, 19468991, 19883092, 18510194
19658708, 19591608, 19402853, 20618595, 21787056, 22380919, 19469538
21266085, 17835294, 19721304, 19791377, 19068610, 22178855, 16777441
[ 2017-04-16 ]
Oracle Linux 6.8 en la Base Image 17.2.1.0.0
Entre las novedades incluidas en la Base Image 17.2.1.0.0 (Abril 2017) encontramos una relacionada con la actualización del sistema operativo. Cuando realicemos un nuevo despliegue de Oracle Database Cloud Service a partir de abril, este será aprovisionado con Oracle Linux 6.8.
Esta info podemos verla en la documentación de Oracle Help Center:
What's New for Oracle Database Cloud Service
Esta info podemos verla en la documentación de Oracle Help Center:
What's New for Oracle Database Cloud Service
Oracle Linux 6.8 integrated into base images
|
(Base image 17.2.1.0.0) When you create a new Database Cloud Service database deployment, Oracle Linux 6.8 is installed as the operating system
|
Acá podemos ver la comprobación empírica de la novedad:
[ 2017-04-15 ]
Novedades Oracle Database Cloud Service - Abril 2017
Abril 2017
| Feature | Description |
|---|---|
Oracle Linux 6.8 integrated into base images
|
(Base image 17.2.1.0.0) When you create a new Database Cloud Service database deployment, Oracle Linux 6.8 is installed as the operating system.
|
Jan 2017 PSU integrated into base image for single-instance databases
|
(Base image 17.2.1.0.0) The January 2017 Patch Set Update (PSU) is now integrated into the base image for new Database Cloud Service database deployments that host single-instance databases and Data Guard configurations with single-instance primary and standby databases. When you create such a database deployment, it will already include the PSU functionality.
|
Updated version of Oracle REST Data Services
|
(Base image 17.2.1.0.0) Newly created database deployments now include Oracle REST Data Services (ORDS) version 3.0.9.
Note: This item does not apply to database deployments that host Oracle Real Application Clusters (RAC) databases; such deployments do not include Oracle REST Data Services.
|
[ 2017-04-11 ]
AROUG Cloud Day 2017
Desde el grupo de usuarios Oracle de Argentina (AROUG), estamos oraganizando el primer Oracle Cloud Day.
Este es un evento de un día que tiene como objetivo acercar y difundir experiencias reales de uso del Oracle Cloud (IaaS/PaaS/SaaS) a la comunidad de usuarios Oracle.
Se llevará adelante el Jueves 11 de mayo de 2017 desde las 08:30 hasta las 17:30 (ART) en las oficinas de Oracle Argentina en Buenos Aires. (Juana Manso 1069 CABA)
En esta oportunidad estaré presentando una charla técnica, donde la intensión es dar una visión general del servicio de DBaaS de Oracle Cloud abordándolo desde la prespectiva de un DBA. Tratando de resaltar los principales detalles tecnológicos, roles y tareas a desarrollar en la gestión de base de datos Oracle en la nube.
El evento es gratuito y para registrarse pueden hacerlo en el siguiente link:
https://aroug-cloud-day.eventbrite.com.ar
Este es un evento de un día que tiene como objetivo acercar y difundir experiencias reales de uso del Oracle Cloud (IaaS/PaaS/SaaS) a la comunidad de usuarios Oracle.
Se llevará adelante el Jueves 11 de mayo de 2017 desde las 08:30 hasta las 17:30 (ART) en las oficinas de Oracle Argentina en Buenos Aires. (Juana Manso 1069 CABA)
En esta oportunidad estaré presentando una charla técnica, donde la intensión es dar una visión general del servicio de DBaaS de Oracle Cloud abordándolo desde la prespectiva de un DBA. Tratando de resaltar los principales detalles tecnológicos, roles y tareas a desarrollar en la gestión de base de datos Oracle en la nube.
El evento es gratuito y para registrarse pueden hacerlo en el siguiente link:
https://aroug-cloud-day.eventbrite.com.ar
Suscribirse a:
Entradas (Atom)