DBA Blogs

Challenge Me! Capturing SQL Server Data with PowerShell

Pythian Group - Fri, 2014-11-21 09:05

Hello PowerShell lovers! If you are reading this blog post, then you are likely interested in attending my session at SQLBits (well, so far it is an abstract. Hopefully I will be selected!)

Any data professional who is responsible for performance tuning, has their way of gathering data to troubleshoot and solve issues. But what if I told you there was a different way, a better way, to collect the data you need without consuming additional SQL Server resources?

Attend my session and learn how to leverage PowerShell to collect data from Perfmon, DMVs, and more. This session will feature demos where attendees will learn about some .Net classes to control timer events and the PowerShell Register-Objectevent cmdlet.

Attendees are encouraged to send their challenges to me by email at laertesqldba@outlook.com. I will review their scripts and select some of them to demonstrate in the session by offering an alternative using PowerShell and the event  subsystem.

I will split your challenges into three categories:

1. Perfmon Counters
2. DMVs, DMFs, etc.
3. Other

Using one challenge from each category, here are examples of the kinds challenges I will demonstrate:

Example 1: Perfmon Counters

Hello Laerte,

My name is James Tiberius Kirk, I am Captain of the starship USS Enterprise and we are testing a new warp drive and our SQL Server´s servers are very, very busy with that. Our DBAs need a way to collect some performance counters, in an interval of time of 15 minutes and the data stored in a repository for further analysis. This process need to be done remotely and we cannot use any kind of SQL Server resources for that. We don’t want to use any kind of GUI for that as well. The performance counters are:SQLServer: Buffer Manager: Buffer cache hit ratio

SQLServer: Buffer Manager: Page life expectancy
SQLServer: SQL Statistics: Batch Requests/Sec
SQLServer: SQL Statistics: SQL Compilations/Sec
SQLServer: SQL Statistics: SQL Re-Compilations/SecCan you help us?  Thanks!

Example 2: DMVs, DMFs, etc.

Hello Laerte,

My name is Anakyn Skywalker, (a.k.a. Darth Vader, the most powerful Sith Master ever.) We are building a new Death Star and our SQL Server´s servers are extremely busy with inserts and updates. We need to capture and store the fragmentation data from one table called BlowUpThePlanet in a server called DeathStarProd3 Database VaderIsTheBest. As I told you before and I will not say again, it is a very busy server and because of that, the interval of gathering needs to be hourly. I am awaiting your help today. *FORCE CHOKING*

Example 3: Other

Hello Laerte,

My Name is Neo. Everything you know, or you think know, actually does not exist—but we can talk about that later.  We are trying to inject a new code in the Matrix (don’t worry about that right now either) and I need to capture some data. I will send to you the TSQL and all information that I need. Can you help me?  Thanks.

TSQL
Insert into XXXX Select XXXX
Interval – 10 seconds
Server Name – XXX
Database Name – YYY

Please send your challenges to laertesqldba@outlook.com. I will select three of them, one from each category and I will demonstrate how to do them using PowerShell without using any kind of SQL Server resources, after  I explain the Timer class and the register-objectevent cmdlet.

That is it! And remember: Always… if it is PowerCool, it is PowerShell.

Categories: DBA Blogs

#DOAG2014 bits of info for DBAs

The Oracle Instructor - Fri, 2014-11-21 07:36

Just wanted to share some pieces of information from the recent DOAG annual conference that you may find interesting.

Database Replay is extremely useful to predict after-upgrade performance on a test system, especially we can record the production load on 10g even.

From Carsten Czarski’s talk about XML DB:

With 12c, XML DB is mandatory and it provides an easy way to upload BLOBs via ftp into the database.

From Ulrike Schwinn’s talk about the Resource Manager I took away that

The resource manager becomes more and more popular and important, especially for Multitenant

- something Hans Forbrich reinforced later on.

Particularly I liked way she presented later on about ADO: Very many live demonstrations – that’s how I try to do my own presentations also :-)

Frank Schneede did a great job debunking Exadata myths. For example,

You don’t need to have all cores enabled with Exadata X4 in order to save license cost. That’s called Capacity on Demand.

If I should name one presentation that was most useful for me, it’ll be probably Frank’s.

Markus Michalewicz delivered an excellent talk as expected about RAC cache fusion:

Two important messages:

RAC scales well (far) beyond three nodes because there are never more than three nodes involved for cache fusion intercommunication. And Multitenant and RAC are a perfect fit.

One Data Guard snippet out of Larry Carpenter’s talk about Global Data Services (GDS):

GDS makes it possible to automate the failover of the Real-Time Query service to the primary in case the physical standby has an outage.

Hans Forbrich talked about Multitenant. He showed great presentation skills and although I knew the technical details before, the way he highlighted certain aspects was still very helpful for me.

One key message was that

Multitenant is here to stay. DBAs should learn about it and become familiar with it as soon as possible, because sooner than later it will have to be administered in production!
Tagged: #DOAG2014
Categories: DBA Blogs

Oracle Database 12C Certified Professional SQL Foundations by Steve Ries; Infinite Skills

Surachart Opun - Wed, 2014-11-19 01:36
How to become Become an Oracle Certified Associate? That's a good question for some people who want to start working on Oracle Database and get first Oracle Certification for their work life. Step 1 - Pass one SQL Exam: Oracle Database 12c: SQL Fundamentals 1Z0-061 or Oracle Database 11g: SQL Fundamentals I 1Z0-051 or Oracle Database SQL Expert 1Z0-047. Step 2 - Pass Exam Oracle Database 12c: Installation and Administration 1Z0-062.
With Step 1 -  Oracle Database 12c: SQL Fundamentals 1Z0-061 exam that you should have skills SQL SELECT statement, subqueries, data manipulation, and data definition language. I was not encouraging to learn SQL SELECT statement, subqueries, data manipulation, data definition language and things like that, but in the real-world for working with Oracle Database you have to know them. You can read about them in Oracle Documents, Oracle Learning Library, Oracle University and the Internet. If you are looking for some video training that instruct you for Oracle Database SQL Foundations such as  sql-select, dml, ddl and etc. I mention Oracle Database - 12C Certified Professional SQL Foundations by Steve Ries. I watched it on O'reilly,that is very fast for streaming and downloading. The "Oracle Database - 12C Certified Professional SQL Foundations" video training, that helps learning Oracle Foundations looks easier (watch and do by own) and instructor spoke each topic very clear and easy for listening.

FYI, You can watch some free video.

Video Training: Oracle Database - 12C Certified Professional SQL Foundation
Instructor: Steve RiesWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

Change Parameter Value In Another Session

Oracle in Action - Tue, 2014-11-18 22:57

The values of initialization parameters in another session can be changed  using procedures SET_BOOL_PARAM_IN_SESSION and SET_INT_PARAM_IN_SESSION provided in DBMS_SYSTEM package.

Let’s demonstrate:

SQL>conn / as sysdba

SYS> grant dba to hr;

– Currently parameter HASH_AREA_SIZE is set to 131073 in HR session

HR>> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131073

– Find out SID, SERIAL# for HR session

SYS> select sid, serial#, username from v$session where username=’HR'; SID SERIAL# USERNAME ———- ———- —————————— 54 313 HR – Set value of parameter HASH_AREA_SIZE to 131072 in HR session SYS> exec dbms_system.SET_INT_PARAM_IN_SESSION(54, 313, ‘HASH_AREA_SIZE’,131072); – Verify that the value of parameter HASH_AREA_SIZE has been changed in HR session HR> sho parameter hash_area_size NAME TYPE VALUE ———————————— ———– —————————— hash_area_size integer 131072 Similary Boolean initialization parameters can be modified using dbms_system.SET_BOOL_PARAM_IN_SESSION. – Let’s find out the value of parameter SKIP_UNUSABLE_INDEXES in HR session HR> sho parameter skip_unusable indexes NAME TYPE VALUE ———————————— ———– —————————— skip_unusable_indexes boolean TRUE – Modify the value of parameter SKIP_UNUSABLE_INDEXES to FALSE in HR session SYS> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(54, 313, ‘skip_unusable_indexes’,FALSE); – Verify that the value of parameter SKIP_UNUSABLE_INDEXES has been changed to FALSE in HR session HR> sho parameter skip_unusable indexes NAME TYPE VALUE ———————————— ———– —————————— skip_unusable_indexes boolean FALSE References: http://dbaspot.com/oracle-server/143210-how-query-sessions-active-initialization-parameters-other-session.html ————————————————————————————————– Related links: Home Database Index Find Values Of Another Session’s Parameters —————————- Tags: Del.icio.us Digg Comments: 0 (Zero), Be the first to leave a reply! You might be interested in this: Copyright © ORACLE IN ACTION [Change Parameter Value In Another Session], All Right Reserved. 2014. The post Change Parameter Value In Another Session appeared first on ORACLE IN ACTION. Categories: DBA Blogs Capturing SQL Server IO Latencies for a Period of Time with PowerShell Pythian Group - Tue, 2014-11-18 14:54 Today’s blog post is a demonstration of the PowerShell approach to Paul Randal’s recent blog post, Capturing IO Latencies for a Period of Time. Since wait statistics were properly implemented in SQL Server, it turned into a powerful resource to diagnose and troubleshoot SQL Server issues. Paul Randal is, IMHO, one of the best names in this technology. I requested his approval to use and modify his code to demonstrate another approach that can be used not only in this situation, but in others where you need to capture data in an interval and don’t want to use SQL Server resources. Paul, as always, was very kind to allow me to use his implementation to demonstrate mine—thanks a lot, Paul. In his excellent blog post Capturing IO Latencies for a Period of Time, Paul demonstrates how to capture the wait stats in an interval of 30 minutes using only T-SQL. If you have a very busy system perhaps leaving this job to trigger the gathering to another resource than SQL Server may be a good idea. Those who know me, know that I am PowerShell lover, so let’s take a look how to do that using PowerShell. Before we proceed, I strongly encourage readers to check out my good friend Ed Wilson’s fantastic blog post Use Asynchronous Event Handling in PowerShell to understand timer event handlers, asynchronous .NET events, or the cmdlets to work with events. A quick note about .NET System.Timers.Timer class. It is simple—the namespace System.Timers fires an event in a specific interval and the class Timer generates recurring events. In other words, I can set up an event to be fired on each x unit of time (milliseconds.) The first thing to do it it is to create the object System.Timers.Timer and define the interval that we want. For now, let’s use 500 milliseconds by setting the property interval.$Timer= New-Object System.Timers.Timer -Property @{ Interval = 100;autoreset=$true } We instanced the timer and set up the interval. Now we need to register the object (in the case object$timer) that will fire the event and execute the action. For that we will use the Register-Objectevent cmdlet The parameter Eventname we will use Elapsed that is detailed in Use Asynchronous Event Handling in PowerShell  and also in Manage Event Subscriptions with PowerShell.

First, let’s just print something on the screen:

Register-ObjectEvent -InputObject$timer -Action { Write-Host‘Holy PowerShell. It Worked!!!! ‘} -EventName Elapsed -SourceIdentifier stateful Id Name State HasMoreData Location Command — —- —– ———– ——– ——- 1 93ce50c3-6f5… NotStarted False Write-Host ‘Holy Powe… If you simply run this code, you will realize that after 30 seconds nothing happens. Wait, PowerShell is broken? No my dear friend, the .net class is so beautiful that it allows you start and stop the suppression of the event. Yes, you can start the event and will be recurring every 30 seconds, and also stop it. So let’s start:$Timer.Start()

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

And to stop it :

$Timer.Stop() And to Unregister the event use : Unregister-Event -SourceIdentifier stateful How PowerCool is that? Let’s get back to our example. Using Paul’s code, let’s first create the table to store the data. I did some changes to store the date and time so you also can have the historical data if you want and the table is physical. SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms], [num_of_writes], [io_stall_write_ms], [io_stall], [num_of_bytes_read], [num_of_bytes_written], [file_handle] INTO IOLatency FROM sys.dm_io_virtual_file_stats(NULL, NULL); GO alter table IOLatency add [TimeCapture] datetime default getdate() alter table IOLatency add [Identifier] bigint I added this column identifier because my TSQL it is not so great and I want (like Paul’s example) to get the difference between the last top samples between 30 minutes, so this column will have the same value for the last two gatherings and I can easily create the TSQL condition for that. Now let’s play with PowerShell: We need to import the module SQLPS to use the invoke-sqlcmd cmdlet . Also in the Action parameter of the Register-Objectevent cmdlet I am using a scriptblock variable to be more friendly readable. Also I am using the interval as Paul, 30 minutes, so the difference always will be in the 2 last same date times with 30 minutes of difference.$Timer=New-Object System.Timers.Timer -Property @{ Interval = 1800000 ;autoreset=$true } #1800000 30 minutes in milliseconds #Create the ScriptBlock variable to run in the action parameter in the register-objectevent cmdlet$Action= {

#Import the SQLPS Module

Import-Module SQLPS -ErrorAction SilentlyContinue -DisableNameChecking;

#variable to counter the numb er of the events

$Script:counter+= 1; #if the events is more than 2 , increase the the identity if ($counter-eq 3) {

$script:Identity+= 1;$counter= 1

}

#Define the TSQL

$Tsql =@” insert into IOLatency ([database_id], [file_id], [num_of_reads], [io_stall_read_ms], [num_of_writes], [io_stall_write_ms], [io_stall], [num_of_bytes_read], [num_of_bytes_written], [file_handle],[Identifier]) SELECT [database_id],[file_id], [num_of_reads], [io_stall_read_ms], [num_of_writes], [io_stall_write_ms], [io_stall], [num_of_bytes_read], [num_of_bytes_written], [file_handle],$($Identity) FROM sys.dm_io_virtual_file_stats (NULL, NULL); “@; #Invoke the TSQL invoke-sqlcmd -ServerInstance vader -Database Test -Query$Tsql

}

Register-ObjectEvent-InputObject$timer -Action$Action  -EventNameelapsed  -SourceIdentifierstateful

Not it is juts start ….

$Timer.Start() And every 30 minutes the table IOLatency will be populated. If you want to stop the gathering but not unregistered the event :$Timer.Stop()

This way if you want to start again, just timer.start().

Now it is time to get the data. Using The Paul´s query  with a change to get the same identifier :

WITH [DiffLatencies] AS

(SELECT

— Files that weren’t in the first snapshot

[ts2].[database_id],

[ts2].[file_id],

[ts2].[Identifier],

[ts2].[num_of_writes],

[ts2].[io_stall_write_ms],

[ts2].[io_stall],

[ts2].[num_of_bytes_written]

FROM IOLatency AS [ts2]

LEFT OUTER JOIN IOLatency AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

and ts2.Identifier = ts1.Identifier

WHERE [ts1].[file_handle] IS NULL

UNION

SELECT

— Diff of latencies in both snapshots

[ts2].[database_id],

[ts2].[file_id],

[ts2].[Identifier],

[ts2].[num_of_writes] – [ts1].[num_of_writes] AS [num_of_writes],

[ts2].[io_stall_write_ms] – [ts1].[io_stall_write_ms] AS [io_stall_write_ms],

[ts2].[io_stall] – [ts1].[io_stall] AS [io_stall],

[ts2].[num_of_bytes_written] – [ts1].[num_of_bytes_written] AS [num_of_bytes_written]

FROM IOLatency AS [ts2]

LEFT OUTER JOIN IOLatency AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

and ts2.Identifier = ts1.Identifier

WHERE [ts1].[file_handle] IS NOT NULL)

SELECT

DB_NAME([vfs].[database_id]) AS [DB],

LEFT([mf].[physical_name], 2) AS [Drive],

[mf].[type_desc],

[num_of_writes] AS [Writes],

[WriteLatency(ms)] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,

/*[Latency] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,*/

[AvgBPerWrite] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,

/*[AvgBPerTransfer] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE

[mf].[physical_name],

[vfs].identifier

FROM [DiffLatencies] AS [vfs]

JOIN sys.master_files AS [mf]

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

ORDER BY [Identifier],[WriteLatency(ms)] DESC;

GO

If you want to to get only one sample , just add a

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

AND [vfs].identifier = <IdentifierNumber>

This way you also can know what time was made this gathering, just checking the timecapture column in the identifier number. In other words, you also have a historical data that you can query and get the differences or make any calculations that you need.

The data is stored—you can play with whatever query you want and need.

If you are thinking of gathering perfmon counters, it is possible too. Take a look at my blog post on Simple-Talk, The PoSh DBA – Specifying and Gathering Performance Counters and just play with the timer class.

I guess that you realize you can use this approach in any scenario that you need to capture data in a busy system without using any kind (or a minimum)  of SQL Server resources. IMHO, that can be a good approach in those kind environments

Again, I would like to thank Paul Randal for kindly allowing me to use and modify to use his code and blog post to demonstrate my approach, and of course the Hey, Scripting Guy! blog.

Remember kids… If it is PowerCool, it is PowerShell!

Categories: DBA Blogs

Partner Webcast – Business Continuity with Oracle Weblogic 12c

Business Continuity is the vast important feature of the modern enterprises and organizations. Modern IT infrastructure should meet strong objectives and requirements in order to continue to...

We share our skills to maximize your revenue!
Categories: DBA Blogs

12c: Enhancements to Partition Exchange Load

Oracle in Action - Tue, 2014-11-18 04:43

Statistics for Partitioned Tables
Gathering statistics on partitioned tables consists of gathering statistics at both the table level and partition level. Prior to Oracle Database 11g, whenever a new partition was added, the entire table had to be scanned to refresh table-level statistics which could be very expensive, depending on the size of the table.

Incremental Global Statistics
With the introduction of incremental global statistics in 11g, the database, instead of performing a full table scan to compute global statistics, can derive global statistics from the partition level statistics. Some of the statistics, for example the number of rows, can be accurately derived by aggregating the values from partition statistics . However, the NDV of a column cannot be derived by aggregating partition-level NDVs. Hence, a structure called synopsis is maintained by the database for each column at the partition level which can be viewed as a sample of distinct values. The synopses for various partitions are merged by the database to accurately derive the NDV for each column.

Hence, when a new partition is added to a table, the database

• gathers statistics and creates synopses for the newly added partition,
• retrieves synopses for the existing partitions of the table and
• aggregates the partition-level statistics and synopses to create global statistics.

Thus, the need to scan the entire table to gather table level statistics on adding a new partition has been eliminated.

However, if partition exchange loads are performed and statistics for source table are available, statistics still need to be gathered for the partition after the exchange to obtain its synopsis.

Enhancements in Oracle 12c
Oracle Database 12c introduces new enhancements for maintaining incremental statistics. Now, DBMS_STATS can create a synopsis on a non-partitioned table as well. As a result, if you are using partition exchange loads, the statistics / synopsis for the source table will become the partition level statistics / synopsis after the load, so that the database can maintain incremental statistics without having to explicitly gather statistics on the partition after the exchange.

Let’s demonstrate …

Overview:

Source non-partitioned table : HR.SRC_TAB
Destination partitioned table: HR.PART_TAB
Destination partition                  : PMAR

– Create a partitioned table HR.PART_TAB with 3 partitions

• only 2 partitions contain data initially
• set preference incremental = true
• gather stats for the table – gathers statistics and synopses for 2 partitions

– create a non partitioned table HR.SRC_TAB which will used to load the 3rd partition using partition exchange

•  Set table preferences for HR.SRC_TAB
• INCREMENTAL = TRUE
• INCREMENTAL_LEVEL = TABL
• Gather stats for the source table: DBMS_STATS gathers table-level synopses also for the table

– Perform the partition exchange
– After the exchange, the the new partition has both statistics and a synopsis.
– Gather statitstics for PART_TAB – Employs partition level statistics and synopses to derive global statistics.

Implementation

– Create and populate partitioned table part_tab with 3 partitions
PJAN, PFEB and PMAR

SQL>conn hr/hr

drop table part_tab purge;
create table part_tab
(MNTH char(3),
ID number,
txt char(10))
partition by list (mnth)
(partition PJAN values ('JAN'),
partition PFEB values ('FEB'),
partition PMAR values ('MAR'));

insert into part_tab values ('JAN', 1, 'JAN1');
insert into part_tab values ('JAN', 2, 'JAN2');
insert into part_tab values ('JAN', 3, 'JAN3');

insert into part_tab values ('FEB', 2, 'FEB2');
insert into part_tab values ('FEB', 3, 'FEB3');
insert into part_tab values ('FEB', 4, 'FEB4');
commit;

– Note that

•   partition PMAR does not have any data
•  there are 4 distinct values in column ID i.e. 1,2,3 and 4
select 'PJAN' Partition, mnth, id from part_tab partition (PJAN)
union
select 'PFEB' Partition, mnth, id from part_tab partition (PFEB)
union
select 'PMAR' Partition, mnth, id from part_tab partition (PMAR)
order by 1 desc;

PART MNT ID
---- --- ----------
PJAN JAN 1
PJAN JAN 2
PJAN JAN 3
PFEB FEB 2
PFEB FEB 3
PFEB FEB 4

– Set preference Incremental to true for the table part_tab

SQL>begin
dbms_stats.set_table_prefs ('HR','PART_TAB','INCREMENTAL','TRUE');
end;
/

select dbms_stats.get_prefs ('INCREMENTAL','HR','PART_TAB') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','HR','PART_TAB')
----------------------------------------------------
TRUE

-- Gather statistcs for part_tab

SQL> exec dbms_stats.gather_table_stats('HR','PART_TAB');

– Note that global statistics have been gathered and the table has been analyzed at 16:02:31

SQL>alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 6 17-nov-2014 16:02:31

– A full table scan was performed and stats were gathered for each of the partitions
All the partitions have been analyzed at the same time as table i.e. at 16:02:31

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 0 17-nov-2014 16:02:31

– NUM_DISTINCT correctly reflects that there are 4 distinct values in column ID

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 4

– Create source unpartitioned table SRC_TAB
– Populate SRC_TAB with records for mnth = MAR
and introduce two new values for column ID i.e. 0 and 5

SQL>drop table src_tab purge;
create table src_tab
(MNTH char(3),
ID number,
txt char(10));

insert into src_tab values ('MAR', 0, 'MAR0');
insert into src_tab values ('MAR', 2, 'MAR2');
insert into src_tab values ('MAR', 3, 'MAR3');
insert into src_tab values ('MAR', 5, 'MAR5');
commit;

– Set preferences for table src_tab

• INCREMENTAL = TRUE
• INCREMENTAL_LEVEL = TABLE
SQL>begin
dbms_stats.set_table_prefs ('HR','SRC_TAB','INCREMENTAL','TRUE');
dbms_stats.set_table_prefs ('HR','SRC_TAB','INCREMENTAL_LEVEL','TABLE');

end;
/

col incremental for a15
col incremental_level for a30

select dbms_stats.get_prefs ('INCREMENTAL','HR','SRC_TAB') incremental,
dbms_stats.get_prefs ('INCREMENTAL_LEVEL','HR','SRC_TAB') incremental_level
from dual;

INCREMENTAL INCREMENTAL_LEVEL
--------------- ------------------------------
TRUE TABLE

– Gather stats and synopsis for table SRC_TAB and note that table is analyzed at 16:06:03

SQL>exec dbms_stats.gather_table_stats('HR','SRC_TAB');

col table_name for a12
select table_name,num_rows, last_analyzed from user_tables
where table_name='SRC_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
SRC_TAB 4 17-nov-2014 16:06:33

– Exchange partition –

SQL>alter table part_tab exchange partition PMAR with table SRC_TAB;

– Note that table level stats for part_tab are still as earlier
as stats have not been gathered for it after partition exchange

SQL> col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 6 17-nov-2014 16:02:31

– NDV for col ID is still same as earlier i.e. 4 as stats
have not been gathered for table after partition exchange

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 4

– Note that stats for partition PMAR have been copied from
src_tab. Last_analyzed column for Pmar has been updated
and shows same value as for table src_tab i.e. 16:06:33
Also, num_rows are shown as 4

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 4 17-nov-2014 16:06:33

– Gather stats for table part_tab

SQL>exec dbms_stats.gather_table_stats('HR','PART_TAB');

– While gathering stats for the table, partitions have not been
scanned as indicated by the same value as earlier in column LAST_ANALYZED.

SQL> col partition_name for a15

select partition_name, num_rows,last_analyzed
from user_tab_partitions
where table_name = 'PART_TAB' order by partition_position;

PARTITION_NAME NUM_ROWS LAST_ANALYZED
--------------- ---------- --------------------
PJAN 3 17-nov-2014 16:02:31
PFEB 3 17-nov-2014 16:02:31
PMAR 4 17-nov-2014 16:06:33

– Note that num_rows for the table part_tab has been updated by adding up the values from various partitions using partition level statistics
Column LAST_ANALYZED has been updated for the table

SQL> col table_name for a12
select table_name, num_rows, last_analyzed from user_tables
where table_name='PART_TAB';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------ ---------- --------------------
PART_TAB 10 17-nov-2014 16:11:26

– NDV for column ID has been updated to 6 using the synopsis for partition PMAR as copied from table src_tab

SQL> col column_name for a15
select TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
from user_tab_col_statistics
where table_name = 'PART_TAB' and column_name = 'ID';

TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------ --------------- ------------
PART_TAB ID 6

– We can also confirm that we really did use incremental statistics by querying the dictionary table sys.HIST_HEAD$, which should have an entry for each column in the PART_TAB table. SQL>conn / as sysdba col tabname for a15 col colname for a15 col incremental for a15 select o.name Tabname , c.name colname, decode (bitand (h.spare2, 8), 8, 'yes','no') incremental from sys.hist_head$ h, sys.obj$o, sys.col$ c
where h.obj# = o.obj#
and o.obj# = c.obj#
and h.intcol# = c.intcol#
and o.name = 'PART_TAB'
and o.subname is null;

TABNAME COLNAME INCREMENTAL
--------------- --------------- ---------------
PART_TAB MNTH yes
PART_TAB ID yes
PART_TAB TXT yes

I hope this post was useful.

References:

Home

Database 12c Index

===================================================================

Tags:

Del.icio.us
Digg

You might be interested in this:

The post 12c: Enhancements to Partition Exchange Load appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Off May Not Be Totally Off: Is Oracle In-Memory Database 12c (12.1.0.2.0) Faster?

Off May Not Be Totally Off: Is Oracle In-Memory Database 12c (12.1.0.2.0) Faster?
Most Oracle 12c installations will NOT be using the awesome Oracle Database in-memory features available starting in version 12.1.0.2.0. This experiment is about the performance impact of upgrading to 12c but disabling the in-memory features.

Every experiment I have performed comparing buffer processing rates, clearly shows any version of 12c performs better than 11g. However, in my previous post, my experiment clearly showed a performance decrease after upgrading from 12.1.0.1.0 to 12.1.0.2.0.

This posting is about why this occurred and what to do about it. The bottom line is this: make sure "off" is "totally off."

Turn it totally off, not partially off
What I discovered is by default the in-memory column store feature is not "totally disabled." My experiment clearly indicates that unless the DBA takes action, not only could they be a license agreement violation but a partially disabled in-memory column store slightly slows logical IO processing compared to the 12c non in-memory column store option. Still, any 12c version processes buffer faster than 11g.

My experiment: specific and targeted
This is important: The results I published are based on a very specific and targeted test and not on a real production load. Do not use my results in making a "should I upgrade decision." That would be stupid and an inappropriate use of the my experimental results. But because I publish every aspect of my experiment and it is easily reproducible it is a valid data point with which to have a discussion and also highlight various situations that DBAs need to know about.

You can download all my experimental results HERE. This includes the raw sqlplus output, the data values, the free R statistics package commands, spreadsheet with data nicely formatted and lots of histograms.

The instance parameter settings and results
Let me explain this by first showing the instance parameters and then the experimental results. There are some good lessons to learn!

Pay close attention to the inmemory_force and inmemory_size instance parameters.
SQL> show parameter inmemoryNAME         TYPE  VALUE------------------------------------ ----------- ------------------------------inmemory_clause_default       stringinmemory_force        string  DEFAULTinmemory_max_populate_servers      integer  0inmemory_query        string  ENABLEinmemory_size        big integer 0inmemory_trickle_repopulate_servers_ integer  1percentoptimizer_inmemory_aware      boolean  TRUESQL> show sgaTotal System Global Area 7600078848 bytesFixed Size      3728544 bytesVariable Size   1409289056 bytesDatabase Buffers  6174015488 bytesRedo Buffers     13045760 bytes

In my experiment using the above settings the median buffers processing rate was 549.4 LIO/ms. Looking at the inmemory_size and the SGA contents, I assumed the in-memory column store was disabled. If you look at the actual experimental result file "Full ds2-v12-1-0-2-ON.txt", which contain the explain plan of the SQL used in the experiment, there is no mention of the in-memory column store being used. My assumption, which I think is a fair one, was that the in-memory column store had been disabled.

As you'll see I was correct, but only partially correct.

The parameter settings below are when the in-memory column store was totally disabled. They key is changing the default inmemory_force parameter value from DEFAULT to OFF.
SQL> show parameter inmemoryNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------inmemory_clause_default              stringinmemory_force                       string      OFFinmemory_max_populate_servers        integer     0inmemory_query                       string      ENABLEinmemory_size                        big integer 0inmemory_trickle_repopulate_servers_ integer     1percentoptimizer_inmemory_aware             boolean     TRUESQL> show sgaTotal System Global Area 7600078848 bytesFixed Size                  3728544 bytesVariable Size            1291848544 bytesDatabase Buffers         6291456000 bytesRedo Buffers               13045760 bytes

Again, the SGA does not show any in-memory memory space. In my experiment with the above "totally off" settings, the median buffers processing rate was 573.5 LIO/ms compared to "partially off" 549.4 LIO/ms. Lesson: Make sure off is truly off.

It is an unfair comparison!
It is not fair to compare the "partially off" with the "totally off" test results. Now that I know the default inmemory_force must be changed to OFF, the real comparison should be made with the non in-memory column store version 12.1.0.1.0 and the "totally disabled" in-memory column store version 12.1.0.2.0. This is what I will summarize below. And don't forget all 12c versions showed a significant buffer processing increase compared to 11g.

The key question: Should I upgrade?
You may be thinking, if I'm NOT going to license and use the in-memory column store, should I upgrade to version 12.1.0.2.0? Below is a summary of my experimental results followed by the key points.

1. The non column store version 12.1.0.1.0 was able to process 1.1% more buffers/ms (median: 581.7 vs 573.5) compared to to "totally disabled" in-memory column store version 12.1.0.2.0. While this is statistically significant, a 1.1% buffer processing difference is probably not going to make-or-break your upgrade.

2. Oracle Corporation, I'm told, knows about this situation and is working on a fix. But even if they don't fix it, in my opinion my experimental "data point" would not warrant not upgrading to the in-memory column store version 12.1.0.2.0 even if you are NOT going to use the in-memory features.

3. Visually (see below) the non in-memory version 12.1.0.1.0 and the "totally off" in-memory version 12.1.0.2.0 samples sets look different. But they are pretty close. And as I mentioned above, statistically they are "different."

Note for the statistically curious: The red color 12.1.0.1.0 non in-memory version data set is highly variable. I don't like to see this in my experiments. Usually this occurs when a mixed workload sometimes impacts performance, I don't take enough samples or my sample time duration is too short. To counteract this, in this experiment I captured 31 samples. I also performed the experiment multiple times and the results where similar. What I could have done was used more application data to increase the sample duration time. Perhaps that would have made the data clearer. I could have also used another SQL statement and method to create the logical IO load.
What I learned from this experiment
To summarize this experiment, four things come to mind:

1. If you are not using an Oracle Database feature, completely disable it. My mistake was thinking the in-memory column store was disabled when I set it's memory size to zero and "confirmed" it was off by looking at the SGA contents.

2. All versions of 12c I have tested are clearly faster at processing buffers than any version of 11g.

3. There is a very slight performance decrease when upgrading from Oracle Database version 12.1.0.1.0 to 12.1.0.2.0.

4. It is amazing to me that with all the new features poured into each new Oracle Database version the developers have been able to keep the core buffer processing rate nearly at or below the previous version. That is an incredible accomplishment. While some people may view this posting as a negative hit against the Oracle Database, it is actually a confirmation about how awesome the product is.

All the best in your Oracle performance tuning work!

Craig.

Categories: DBA Blogs

Think Stats, 2nd Edition Exploratory Data Analysis By Allen B. Downey; O'Reilly Media

Surachart Opun - Mon, 2014-11-17 08:15
Lots of Python with data analysis books. This might be a good one that is able to help readers perform statistical analysis with programs written in Python. Think Stats, 2nd Edition Exploratory Data Analysis by Allen B. Downey(@allendowney).
This second edition of Think Stats includes the chapters from the first edition, many of them substantially revised, and new chapters on regression, time series analysis, survival analysis, and analytic methods. Additional, It uses uses pandas, SciPy, or StatsModels in Python. Author developed this book using Anaconda from Continuum Analytics. Readers should use it, that will easy from them. Anyway, I tested on Ubuntu and installed pandas, NumPy, SciPy, StatsModels, and matplotlib packages. This book has 14 chapters relate with processes that author works with a dataset. It's for intermediate reader. So, Readers should know how to program (In a book uses Python), and skill in mathematical + statistical.
Each chapter includes exercises that readers can practice and get more understood. Free Sampler
• Develop an understanding of probability and statistics by writing and testing code.
• Run experiments to test statistical behavior, such as generating samples from several distributions.
• Use simulations to understand concepts that are hard to grasp mathematically.
• Import data from most sources with Python, rather than rely on data that’s cleaned and formatted for statistics tools.
surachart@surachart:~/ThinkStats2/code$pwd /home/surachart/ThinkStats2/code surachart@surachart:~/ThinkStats2/code$ ipython notebook  --ip=0.0.0.0 --pylab=inline &
[1] 11324
surachart@surachart:~/ThinkStats2/code$2014-11-17 19:39:43.201 [NotebookApp] Using existing profile dir: u'/home/surachart/.config/ipython/profile_default' 2014-11-17 19:39:43.210 [NotebookApp] Using system MathJax 2014-11-17 19:39:43.234 [NotebookApp] Serving notebooks from local directory: /home/surachart/ThinkStats2/code 2014-11-17 19:39:43.235 [NotebookApp] The IPython Notebook is running at: http://0.0.0.0:8888/ 2014-11-17 19:39:43.236 [NotebookApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation). 2014-11-17 19:39:43.236 [NotebookApp] WARNING | No web browser found: could not locate runnable browser. 2014-11-17 19:39:56.120 [NotebookApp] Connecting to: tcp://127.0.0.1:38872 2014-11-17 19:39:56.127 [NotebookApp] Kernel started: f24554a8-539f-426e-9010-cb3aa3386613 2014-11-17 19:39:56.506 [NotebookApp] Connecting to: tcp://127.0.0.1:43369 2014-11-17 19:39:56.512 [NotebookApp] Connecting to: tcp://127.0.0.1:33239 2014-11-17 19:39:56.516 [NotebookApp] Connecting to: tcp://127.0.0.1:54395 Book: Think Stats, 2nd Edition Exploratory Data Analysis Author: Allen B. Downey(@allendowney)Written By: Surachart Opun http://surachartopun.com Categories: DBA Blogs Repair Replicat after mount point name change DBASolved - Sat, 2014-11-15 11:06 Working on Oracle GoldenGate can be an interesting adventure. In such a case, I have been doing some migration work for a client. Half way though the migration, the target system ran out of resources need to create the tablespaces and store files export and trail files (i.e. disk space and a story for another time). The impact to the migration was that everything had to stop until resources were allocated. Part of the allocation of resources was to change the mount point name. If you know anything about Oracle GoldenGate Replicats, using a static mount point is not the best approach (slipped my mind at the time); however, I made this mistake. When the mount point name changed, all the replicats broke because they couldn’t locate the trail files where specified. Initial: When I initially setup the replicat I used a static mount point. Let’s take a look at the create replicat statement I used initially: --Add Replicat Process ADD REPLICAT REPM01, EXTTRAIL /orabackup/ggate/trail/ars/ra, DESC "Replicat process for a schema” START REPLICAT REPM01, ATCSN  As you can see the replicat is looking for the “ra” trail files on the “/orabackup” mount point. Problem: During the allocation of space the mount point “/orabackup” was changed to “/orabkup”. How does this affect the replicat? Simple, the replicat will through an OGG-01091 error stating that it coudn’t find the trail file. ERROR OGG-01091 Unable to open file “/orabackup/ggate/trail/ars/ra000000″ (error 2, No such file or directory). Solution: The solution to fixing this problem is to capture the last CSN number from the Checkpoint table. SQL> select group_name, rba, seqno, log_cmplt_csn from checkpoint where group_name = 'REPM01'; GROUP_NA RBA SEQNO LOG_CMPLT_CSN -------- ---------- ---------- ----------------------------------- REPM01 544013 1 11108080706671  Once the last completed CSN has been identified, then the replicat can be dropped, recreated with the new path to the trail file. GGSCI> dblogin userid ggate password GGSCI> delete replicat REPM01 GGSCI> add replicat REPM01, EXTTRAIL /orabkup/ggate/trail/ars/ra, DESC "Replicat process for a schema” GGSCI> start replicat REPM01, atcsn 11108080706671 GGSCI> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPM01 00:00:00 00:00:06  Lesson Learned: When setting up locations for your trail files make sure they are not static locations. Realitve locations should be used. In most Oracle GoldenGate architectures the “dirdat” directory under$OGG_HOME is used for trails files; however, if you need more space for trail files the “dirdat” directory can be linked to a directory on a larger mount point. This will keep the replicat consistant for trail file purposes and make it easier to manage the names of the mount point if the static name changes.

Enjoy!

Filed under: Golden Gate
Categories: DBA Blogs

Alerting on plans that change for the worse

Bobby Durrett's DBA Blog - Fri, 2014-11-14 16:45

I’ve uploaded a monitoring script that I have worked on: zip.

The script alerts you when the optimizer runs a SQL statement with a potentially new and inefficient plan so you can intervene.  This script improves upon my earlier script which only alerts you to SQL statements running with new plans.  The new script compares the average elapsed time of the current plan with the average of the most often executed plan.  If the new plan averages more than ten times the most often executed plan then the script alerts you to a possible new slow plan.  The elapsed time for the current plan comes from V$SQL and the time for the most often executed plan comes from DBA_HIST_SQLSTAT. Here is an example output from the first test case: SQL_ID PLAN_HASH_VALUE ------------- --------------- a1fw5xjcwkjqx 1357081020 There are two test cases included in the script. The first test case has a query which uses an index and the plan changes when I drop the index. If you look at the output for the first test case you see the good plan: SQL_ID a1fw5xjcwkjqx, child number 0 ------------------------------------- select /* 9 */ owner,table_name from test where owner='XYZ' and table_name='XYZ' Plan hash value: 268773832 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 58 (100)| |* 1 | INDEX RANGE SCAN| TESTI | 16222 | 221K| 58 (0)| --------------------------------------------------------------- The good plan uses the index. Here is the bad plan. Note how the sql_id and plan_hash_value correspond to the output of the monitor script. SQL_ID a1fw5xjcwkjqx, child number 0 ------------------------------------- select /* 9 */ owner,table_name from test where owner='XYZ' and table_name='XYZ' Plan hash value: 1357081020 --------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 15708 (100)| |* 1 | TABLE ACCESS FULL| TEST | 8111 | 110K| 15708 (1)| --------------------------------------------------------------- I have this running in a cron job on a development server and I plan to put the script into our production server next week and set it up to alert me with an email when the optimizer runs SQL statements with potentially new and inefficient plans. – Bobby Categories: DBA Blogs Log Buffer #397, A Carnival of the Vanities for DBAs Pythian Group - Fri, 2014-11-14 09:58 In the spirit of Movember, the database bloggers are also chipping in with their fair share of contribution, not only with a mo but also with the blog posts. This Log Buffer Edition encompasses that all. Oracle: Will the REAL Snap Clone functionality please stand up? Oracle Linux images for Docker released. In-depth look into Oracle API Catalog (OAC) 12c. Patch Set Update: Hyperion Strategic Finance 11.1.2.3.504. Rollback to Savepoint Does Not Release Locks. SQL Server: Overcoming the OPENQUERY Record Limit for AD. Configuring Critical SQL Server Alerts. What is Biml? – Level 1? Database Configuration Management for SQL Server Free eBook: SQL Server Backup and Restore. Set up a Memory Quota for SQL Server Memory Optimized Databases. MySQL: In C (and C++) you can specify that a variable should take a specific number of bits of storage by doing “uint32_t foo:4;” rather than just “uint32_t foo”. In this example, the former uses 4 bits while the latter uses 32bits. This can be useful to pack many bit fields together. Oracle AVDF post-installation configuration. Everything about MySQL Users and Logins You Didn’t Know and Were Afraid to Ask. Setting up a MySQL Enterprise Monitor 3 Test Environment. While playing with MySQL 5.7.5 on POWER8, I came across a rather interesting bug (74775 – and this is not the only one… I think I have a decent amount of auditing and patching to do now) which made me want to write a bit on memory barriers and the volatile keyword. Categories: DBA Blogs A-Team Mobile Persistence Accelerator now available on GitHub! You might remember the great ADF Mobile extension for offline data caching and syncing by Steven Develaar from Oracle A-Team. The extension was temporarily unavailable, but for a good... We share our skills to maximize your revenue! Categories: DBA Blogs Check out the new Oracle help web site Bobby Durrett's DBA Blog - Thu, 2014-11-13 17:52 I found a broken link to an Oracle help document in one of my posts and when I went to the Oracle 12c database documentation to find the new URL to put in my post I found that Oracle had totally revamped their online manuals. Here is a link to the new high level Oracle help site: url I’ve only looked at it for a few minutes and can’t say whether I like it or not. You can still download the manuals in PDF format so that remains familiar. It looks like the new site integrates documentation across most or all of Oracle’s products in a similar format and that’s pretty cool. Anyway, I just saw this and thought I would pass it along. – Bobby Categories: DBA Blogs NoCOUG watchers protest despicable tactics being used by NoCOUG management Iggy Fernandez - Thu, 2014-11-13 13:49 FOR IMMEDIATE RELEASE NoCOUG watchers protest despicable tactics being used by NoCOUG management SILICON VALLEY (NOVEMBER 13, 2014) – Veteran NoCOUG watchers all over Northern California have been protesting the despicable tactics being used by NoCOUG management to lure Oracle Database professionals to the NoCOUG conference at the beautiful eBay Town Hall next week. Instead […] Categories: DBA Blogs Foreign Archived Log in #Oracle – what does it mean? The Oracle Instructor - Thu, 2014-11-13 02:54 When you look into V$RECOVERY_AREA_USAGE, you see a strange row at the bottom:

SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 10.18 0 73 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0  Curious what that could be? You will see values other than zero on a Logical Standby Database: SQL> connect sys/oracle@logst as sysdba Connected. SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY

SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 14.93 0 9 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 2.03 0 26 0 AUXILIARY DATAFILE COPY 0 0 0 0  In contrast to a Physical Standby Database, this one writes not only into standby logs but also into online logs while being in standby role. That leads to two different kinds of archive logs: When DML (like insert and update) is done on the primary 1) that leads to redo entries into online logs 2) that are simultaneously shipped to the standby and written there into standby logs 2) also. The online logs on the primary and the standby logs on the standby will be archived 3) eventually. So far that is the same for both physical and logical standby. But now a difference: Logical standby databases do SQL Apply 4) by logmining the standby or the archive logs that came from the primary. That generates similar DML on the standby which in turn leads LGWR there to write redo into online logs 5) that will eventually get archived 6) as well. A logical standby could do recovery only with its own archive logs (if there was a backup taken before) but not with the foreign archive logs. Therefore, those foreign archive logs can and do get deleted automatically. V$ARCHIVED_LOG and V$FOREIGN_ARCHIVED_LOG can be queried to monitor the two different kinds of logs. That was one topic of the course Oracle Database 12c: Data Guard Administration that I’m delivering as an LVC this week, by the way. Hope you find it useful :-) Tagged: Data Guard, High Availability Categories: DBA Blogs Sangam 2014 Oracle in Action - Wed, 2014-11-12 23:23 RSS content AIOUG meet “SANGAM – Meeting of Minds” is the Largest Independent Oracle Event in India, organized annually in the month of November. This year, the 6th annual conference, Sangam14 (7th, 8th and 9th November 2014) was held at Hotel Crowne Plaza Bengaluru Electronics City, India. I had the honour to present papers on - Histograms : Pre-12c and now - Adaptive Query Optimization Both the papers were well received by the audience. On the first day, a full day seminar on “Optimizer Master Class” by Tom Kyte was simply great. Hats off to Tom who conducted the session through the day with relentless energy, answering the queries during breaks without taking any break himself. The pick of the second day was Maria Colgan’s 2 hour session on “What You Need To Know About Oracle Database In-Memory Option”. The session was brilliant, to the point and packed with knowledge about the new feature. Aman Sharma’s session on 12c High Availability New features was very well conducted and quite informative. On the 3rd day there was a one hour session by Dr. Rajdeep Manwani on “Time to Reinvent Yourself – Through Learning, Leading, and Failing”. The session was truly amazing and left the audience introspecting . On the whole, it was a learning experience with the added advantage of networking with Oracle technologists from core Oracle technology as well as Oracle Applications. Thanks to all the members of organizing committee whose selfless dedication and efforts made the event so successful. Thanks to all the speakers for sharing their knowledge. Looking forward to SANGAM 15…. —————————————————————— Related Links: Home Tags: Del.icio.us Digg Comments: 0 (Zero), Be the first to leave a reply! You might be interested in this: Copyright © ORACLE IN ACTION [Sangam 2014], All Right Reserved. 2014. The post Sangam 2014 appeared first on ORACLE IN ACTION. Categories: DBA Blogs Is Oracle Database 12c (12.1.0.2.0) Faster Than Previous Releases? Is Oracle Database 12c (12.1.0.2.0) Faster Than Previous Releases? I was wondering if the new Oracle Database 12c version 12.1.0.2.0 in-memory column store feature will SLOW performance when it is NOT being used. I think this is a fair question because most Oracle Database systems will NOT be using this feature. While the new in-memory column store feature is awesome and significant, with each new Oracle feature there is additional kernel code. And if Oracle is not extremely careful, these new lines of Oracle kernel code can slow down the core of Oracle processing, that is, buffer processing in Oracle's buffer cache. Look at it this way, if a new Oracle release requires 100 more lines of kernel code to be executed to process a single buffer, that will be reflected in how many buffers Oracle can process per second. To put bluntly, this article is the result of my research comparing core buffer processing rates between Oracle Database versions 11.2.0.2.0, 12.1.0.1.0 and 12.1.0.2.0. With postings like this, it is very important for everyone to understand the results I publish are based on a very specific and targeted test and not on a real production load. Do not use my results in making a "should I upgrade decision." That would be stupid and an inappropriate use of the my experimental results. But because I publish every aspect of my experiment and it is easily reproducable it is valid data point with which to have a discussion and also highlight various situations that DBAs need to know about. There are two interesting results from this research project. This article is about the first discovery and my next article will focus on the second. The second is by far the most interesting! FYI. Back in August of 2013 performed a similar experiment where I compared Oracle database versions 11.2.0.2.0 with 12.1.0.1.0. I posted the article HERE. Why "Faster" Means More Buffer Gets Processed Per Second For this experiment when I say "faster" I am referring to raw buffered block processing. When a buffer is touched in the buffer cache it is sometimes called a buffer get or a logical IO. But regardless of the name, every buffer get increases the instance statistic, session logical reads. I like raw logical IO processing experiments because they are central to all Oracle Database processing. Plus with each new Oracle release, as additional functionality is inserted it is likely more lines of Oracle kernel code will exist. To maintain performance with added functionality is an incredible feat. It's more likely the core buffer processing will be slower because of the new features. Is this case with Oracle's in-memory column store? How I Setup The Experiment I have included all the detailed output, scripts, R commands and output, data plots and more in the Analysis Pack that can be downloaded HERE. There are a lot of ways I could have run this experiment. But two key items must exist for a fare comparison. First, all the processing must be in cache. There can be no physical read activity. Second, the same SQL must be run during the experiment and have the same execution plan. This implies the Oracle 12c column store will NOT be used. A different execution plan is considered "cheating" as a bad plan will clearly loose. Again, this is a very targeted and specific experiment. The experiment compares the buffer get rates for a given SQL statement. For each Oracle version, I gathered 33 samples and excluded the first two, just to ensure caching was not an issue. The SQL statement runs for around 10 seconds, processes around 10.2M rows and touches around 5M buffers. I checked to ensure the execution plans are the same for each Oracle version. (Again, all the details are in the Analysis Pack for your reading pleasure.) I ran the experiment on a Dell server. Here are the details: $ uname -aLinux sixcore 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux

The Results, Statistically
Shown below are the experimental results. Remember, the statistic I'm measuring is buffer gets per millisecond.

Details about the above table: The "Normal" column is about if the statistical distribution of the 31 samples is normal. If the p-value (far right column) is greater than 0.05 then I'll say they are normal. In all three cases, the p-value is less than 0.05. If fact, if you look at the histograms contained in the Analysis Pack every histogram is visually clearly not normal. As you would expect the "Average" and the "Median" are the statistical mean and median. The "Max" is the largest value in the sample set. The "Std Dev" is the standard deviation, which is doesn't mean much since our sample sets are not normally distributed.

As I blogged about before the Oracle Database 12c buffer processing is faster than Oracle Database 11g. However, the interesting part is Oracle version with in-memory column store 12.1.0.2.0 is slower then the previous version of 12c, 12.1.0.1.0. In fact, in my experiment the in-memory column store version is around 5.5% slower! This means version 12.1.0.1.0 "out of the box" can process logical buffers around 5.5% faster! Interesting.

In case you're wondering, I used the default out-of-the-box in-memory column store settings for version 12.1.0.2.0. I checked the in-memory size parameter, inmemory_size and it was indeed set to zero. Also, when I startup the Oracle instance there is no mention of the in-memory column store.

Statistically Comparing Each Version
As an important side bar, I did statistically compare the Oracle Database versions. Why? Because while a 5.5% decrease in buffer throughput may seem important, it may not be statistically significant, meaning this difference can not be explained with our sample sets.

So going around saying version 12.1.0.2.0 is "slower" by 5.5% would be misleading. But in my experiment, it would NOT be misleading because the differences in buffer processing are statistically significant. The relevant experimental details are shown below.
Version A   Version B   Statistical  p-value                         Difference----------  ----------  -----------  -------11.2.0.1.0  12.1.0.1.0      YES       0.000011.2.0.1.0  12.1.0.2.0      YES       0.000012.1.0.1.0  12.1.0.2.0      YES       0.0000

In all three cases the p-value was less than 0.05 signifying the two sample sets are statistically
different. Again, all the details are in the Analysis Pack.

The chart above shows the histograms of both Oracle Database 12c version sample sets together. Visually they look very separated and different with no data crossover. So from both a numeric and visual perspective there is a real difference between 12.1.0.1.0 and 12.1.0.2.0.

What Does This Mean To Me
To me this is surprising. First, there is a clear buffer processing gain upgrading from Oracle 11g to 12c. That is awesome news! But I was not expecting a statistically significant 5.5% buffer processing decrease upgrading to the more recent 12.1.0.2.0 version. Second, this has caused me to do a little digging to perhaps understand the performance decrease. The results of my experimental journey are really interesting...I think more interesting than this posting! But I'll save the details for my next article.

Remember, if you have any questions or concerns about my experiment you can run the experiment yourself. Plus all the details of my experiment are included in the Analysis Pack.

All the best in your Oracle performance tuning work!

Craig.

Categories: DBA Blogs

Presentations to go to at #DOAG2014

The Oracle Instructor - Mon, 2014-11-10 11:26

As every year, there’s a long list of great speakers with interesting talks to attend at the DOAG (German Oracle User Group) annual conference. Sadly I cannot attend them all, so I’ve got to make a choice:

First day

Datenbank-Upgrade nach Oracle 12.1.0.2 – Aufwand, Vorgehen, Kunden by Mike Dietrich, Oracle

Die unheimliche Begegnung der dritten Art: XML DB für den DBA by Carsten Czarski, Oracle

Advanced RAC Programming Features by Martin Bach, Enkitec

Automatische Daten Optimierung, Heatmap und Compression 12c live by Ulrike Schwinn, Oracle

Second day

Understanding Oracle RAC Internals  The Cache Fusion Edition by Markus Michalewicz, Oracle

Die Recovery Area: Warum ihre Verwendung empfohlen ist – I have to go to that one because I present it myself :-)

Geodistributed Oracle GoldenGate and Oracle Active Data Guard: Global Data Services by Larry Carpenter, Oracle

Oracle Database In-Memory – a game changer for data warehousing? by Hermann Baer & Maria Colgan, Oracle

Oracle Distributed Transactions by Joel Goodman, Oracle

Third day

High Noon – Bessere Überlebenschancen beim Datenbank Security Shoot Out by Heinz-Wilhelm Fabry, Oracle

Tuning Tools für echte Männer und Sparfüchse – vom Leben ohne EM12c by Björn Rost, portrix Systems

Best Practices in Managing Oracle RAC Performance in Real Time by Mark Scardina, Oracle

Maximum Availability with Oracle Multitenant: Seeing Is Believing by Larry Carpenter, Oracle

Tagged: #DOAG2014
Categories: DBA Blogs

ECO 2014 and Slides

DBASolved - Mon, 2014-11-10 07:30

Last week I attended the East Coast Oracle User Group conference, also known as ECO, in Raleigh, NC.  This being my first time at ECO, it was a good event for being a two day conference.  The low-key environment provided a nice, comfortable environment for interaction between the speakers and those in attendance.  If you ever have the chance to catch this conference, it would be a good one to attend.

What you can expect from ECO, is to see great speakers, both local to Raleigh and from around the country. There seems to be opportunities to see also see speakers that we all hear about and would like to see at some point.  As one of the speakers at this year’s conference, I have to say it was nice to have great attendance for my session on Oracle GoldenGate 12c Conflict Detection and Resolution.  My session was scheduled for 45 minutes; due to discussions throughout the session it lasted about 65 minutes.  Although the session ran over, it was exciting to see so many people wantiong to know more about Oracle GoldenGate and what benefits it provides to an organization.

If you would like to see the slides from my ECO session, they can be found here.

Lastly, I would like to say that ECO is one of the smaller user group conferences which seem to draw some great speakers.  Check it out next year!

Enjoy!