Author: nbayliss-Oracle

Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 3

Introduction

This post covers how Oracle has improved incremental statistics for
partitioned tables in Oracle Database
12c Release 2. If you’re not already familiar with synopses in the context of incremental
statistics then take a look at Part 1 and Part 2 before you read on.

Beginning with Oracle Database 12c Release 2 there’s a new synopsis
format that’s significantly more compact than the format used
in earlier releases.  For brevity, I’ll refer to the Oracle Database 12c Release 2 format as new and the previous format as old.

This enhancement is particularity
relevant if you looked at incremental statistics maintenance in the
past but decided not to use it because of the additional space usage in
SYSAUX. Oracle Database 12c Release 2 resolves this issue and, in many
cases, reduces the amount of system resource required to manage
synopses.

Optimizer Adaptive Features and Upgrading to Oracle Database 12c Release 2

Introduction

Here’s a reminder of the changes we’ve
made to the optimizer’s adaptive feature settings in Oracle Database 12c Release 2. The new default behavior is covered
in an earlier post
but I want to cover the various options available
to you depending on what you’re upgrading from.

Here are the most common upgrade scenarios and for each I’m going to
recommend what adaptive parameter settings to use.

Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 2

Introduction

This post covers how you can manage optimizer statistics efficiently when you use partition exchange load (PEL). This technique is used when large volumes of data must be loaded and maximum performance is paramount.  It’s common to see it used in decision support systems and large operational data stores.

Make sure you’ve taken a look at Part 1, or you are at least familiar with the concept of incremental statistics so that you know what a synopsis is in the context of a partitioned table.

Efficient Statistics Maintenance for Partitioned Tables Using Incremental Statistics – Part 1

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

Introduction

It’s common to have multi-terabyte partitioned tables in an
Oracle database these days. If you are not there yet but you’re heading that
way, then you need to know about how to maintain statistics on large tables. I
will cover this in a series of posts as follows:

  • Part 1 (this post) – Concepts and implementation
  • Part 2 – Incremental statistics and partition
    exchange loading
  • Part 3 – New to Oracle Database Cloud Services
    (Oracle Database 12c Release 2)

There are some additional details and some overlap with an earlier
Optimizer blog post
, so you should check that out too.

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;}

Optimizer Adaptive Features in Oracle Database 12c Release 2

Introduction

In Oracle Database 12c
Release 2 we have changed the way optimizer adaptive features can be controlled.
In this post, I’ll present what has changed and give you guidance on how
to you can choose what settings to use.

These changes are also relevant for Oracle Database 12c Release 1. If
you want to know more about that, there’s information at end of this
post (or take a look at the OOW 2016 Update, below).

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;
mso-fareast-font-family:Calibri;
mso-bidi-font-family:”Times New Roman”;}

Oracle OpenWorld 2016

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

It’s nearly that time of the year again: Oracle OpenWorld kicks
off on September 18th in San Francisco. I’m hoping to meet some of you out there,
but for those of you not going I intend to keep you up-to-date with the latest
optimizer news by posting updates here during OOW week and more when the dust has settled a bit.

For those of you going, the Oracle Optimizer
session is on Monday 19th, 1:45pm to 2:30pm in Moscone South—303. The title is Oracle Optimizer: Upgrading Without Pain
[CON6450]
and I’ll be covering some important changes and enhancements we’ve made to make things easier for you.

Also, check out the panel session Optimizing SQL for Performance and Maintainability
[CON6557]
on Thursday 22nd,
1:15pm – 2:00 pm in Moscone South—103. The developer advocates hosting the panel, Chris and Connor, have asked me to join them so I’ll be there as an official gatecrasher.

Please try and take the opportunity to come and meet the
Oracle Optimizer developers at the Oracle
Optimizer and Statistics
demo ground booth (number 1633). You can ask them in person about all the new features they’ve added and the enhancements they’ve made. I’ll
be at the booth on Tuesday afternoon, and probably on-and-off throughout the
week.

Finally, my fellow product managers in the data warehousing
and big data arena are presenting a bunch of sessions. Check them out in the full
searchable OOW catalog
. If you search using keywords like partitioning, warehousing, warehouse, parallel and analytics you’ll find them.

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:”Times New Roman”;
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569