Skip navigation.

DBA Blogs

Pythian at UKOUG 14

Pythian Group - Tue, 2014-11-25 13:39

Will you be joining us at the UKOUG Conference and Exhibition in Liverpool, UK? Over 200 world-class speakers and industry experts will be in attendance, including some of our very own.

Michael Abbey, Oracle ACE and Team Lead at Pythian notes that Pythian’s attendance is important, not only for the company and its employees, but for the Oracle community as a whole. “Pythian’s presence at UKOUG this year is the next chapter in an ongoing participation in database and EIS technical events around the world,” Michael explains. “We pride ourselves on presence on all seven continents and our appearances in Liverpool are strategic to the worldwide user community, as are all the locations we frequent every year.The user group community is one of the fundamental building blocks for technical resources as they hone their skills to better serve their clients and the masses in general. Since its founding, Pythian has been a strategic and financial support organization feeding talent to many of the world’s largest technical shows.”

“You will see a number of presenters from the Pythian suite of experts including Oracle ACEs, Oracle ACE Directors, and members of the OakTable Network. We look forward to catching up with new acquaintances and rekindling existing relationships.” In the meantime, you can find their speaking sessions below:

 

RMAN: The Necessary Basics
Presented by Michael Abbey | Monday December 8, 2014 — 9:00-9:50 AM

This session, best suited for attendees just getting started with RMAN, the basic skills to write consistent backups and perform recovery activities from day one. Highlighted in this masterclass will be the following: RMAN architecture, using a catalog, backups to disk, backups to tape, recovery (complete and incomplete), database duplication, and tips and tricks.

About Michael: Michael Abbey is a seasoned and experienced presenter on the Oracle Database CORE technology. He first cut his teeth on V3 in 1986 and it has been a whirlwind of Oracle since then. Michael co-authored the first work in the Oracle Press series in 1994.

 

Why Use OVM for Oracle Database?
Presented by Francisco Munoz Alvarez | Monday December 8, 2014 — 5:00-5:50 PM

Vibrant session about OVM, that will explain how, when and why use this product for Virtualization. It will also give an overview of how Revera is currently using this product in NZ (Biggest OVM Farm in the ANZ region) and show benchmark results between Bare Metal, OVM and ESX concluding with some tips and showing the scalability and break point of load of the Virtualization solutions.

Come and discover the answers for the following questions:

  • Does an Oracle Database perform well on a virtualized environment?
  • What virtualization technology is more stable and allows an Oracle database to perform faster?
  • What is the performance difference between using a bare metal and a virtualized guest?
  • Is it safe to run a production database in a virtualized environment?

About Francisco:  Working out of Pythian’s Australian office in Macquarie Park, Francisco Munoz Alvarez is Vice President and Managing Director of Service Delivery in Asia Pacific, overseeing its regional expansion effort. Francisco previously served as Chief Technology Officer at Database Integrated Solutions Ltd and has more than two decades’ experience in Oracle databases. As President of the Chilean Oracle Users Group and founder of the Oracle Technology Network (OTN) tours in Latin America and Asia Pacific, he is best known for his evangelist work with the Oracle community.

 

Big Data with Exadata
Presented by Christo Kutrovsky | Tuesday December 9, 2014 — 5:30-6:20 PM

In this presentation, Oracle ACE Christo Kutrovsky will discuss common big data use cases and how they can be implemented efficiently with Exadata. Attendees will learn how Exadata actually delivers most of the benefits touted by newer big data technologies, and can often be the right platform for data scalability.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Measuring Performance in Oracle Solaris and Oracle Linux
Presented by Christo Kutrovsky | Wednesday December 10, 2014 —9:00-9:50 AM

You can’t improve what you can’t measure. If you want to get the most value from your database, you need to start with the basics: are you using your hardware and operating systems efficiently? Attend this session to learn how to measure system utilization in the Linux and Oracle Solaris operating systems and how to use this information for tuning and capacity planning.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Lessons Learned in Implementing Oracle Access Manager 11g with Forms, Reports, and Discoverer
Presented by Sudeep Raj and Maris Elsins | Wednesday December 10, 2014 — 12:30-1:20 PM

Support for SSO has ended in December 2011. To take advantage of the latest security enhancements, it’s always recommended for customers to upgrade their system to the latest and the greatest version of the product i.e OAM/OID 11g, very important to stay on the supported configurations. This session will give you an opportunity to understand how OAM 11g can be configured with Forms/Reports/Discoverer 11g, integration with external directory service like Microsoft AD and discuss about the upgrade considerations for customers planning to upgrade from 10g SSO/OID to OAM 11g and OID 11g.

About Sudeep: Sudeep Raj is a Team Lead/Oracle Applications Database Consultant at Pythian, managing a group of expert DBAs spread across the globe. With nearly a decade of experience as an Apps DBA, he has been involved in and led multiple Oracle E-Business Suite 11i/R12 implementations, maintenance, migration and upgrade projects. Sudeep Raj is an OCP certified professorial and holds a Bachelor of Engineering degree in Computer Science.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 

Optimizing and Simplifying Complex SQL with Advanced Grouping
Presented by Jared Still | Wednesday December 10, 2014 — 3:30-4:20 PM

This presentation will show how these features can be used to simplify SQL that was previously quite complex by reducing the amount of code needed and improving readability, and perhaps most importantly, greatly optimizing the performance of SQL statements.

About Jared: Jared Still is a Senior Database Consultant at Pythian. His experience includes working with Oracle databases beginning with version 7.0. While Oracle has expanded to encompass many aspects of the application environment, Jared’s focus has been on the database itself and related infrastructure.

 

Oracle RAC — Designing Applications for Scalability
Presented by Christo Kutrovsky | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Real Application Clusters (RAC) promises 100% transparent active-active clustering technology – true horizontal scaling, but does it work in all cases? This presentation explores the challenges with Oracle’s active-active solution and how to solve them from both database side and application side. Both conceptual design and highly practical solutions are explored.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Database as a Service on the Oracle Database Appliance Platform
Presented by Marc Fielding and Maris Elsins | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Database Appliance provides a robust, highly-available, cost-effective, and surprisingly scalable platform for database as a service environment. By leveraging Oracle Enterprise Manager’s self-service features, databases can be provisioned on a self-service basis to a cluster of Oracle Database Appliance machines. Discover how multiple ODA devices can be managed together to provide both high availability and incremental, cost-effective scalability. Hear real-world lessons learned from successful database consolidation implementations.

About Marc: Marc Fielding is a passionate and creative problem solver, drawing on deep understanding of the full enterprise application stack to identify the root cause of problems, and to implement effective and sustainable solutions. He has extensive experience implementing Oracle’s engineered system portfolio, including leading one of the first enterprise Oracle Exadata implementations. Marc has a strong background in performance tuning and high availability.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Oracle expertise or read some of our Oracle-related blog posts.

Categories: DBA Blogs

Parsing blocks stats blocks parsing

Bobby Durrett's DBA Blog - Mon, 2014-11-24 13:33

I had a five-minute conversation with Oracle development Friday that rocked my world.  I found out that parsing blocks stats which blocks parsing.

We have a system with queries that are taking minutes to parse.  These queries include the main tables on our database, one of which is interval partitioned and has 20,000 sub-partitions.  We have seen a situation where Oracle’s delivered statistics gathering job hangs on a library cache lock waiting for a query to finish parsing.  But, much worse than that, we find most queries on our system hanging on library cache lock waits blocked by the statistics job.

We have an SR open on this situation because it seems to be a bug, but Friday someone on the phone from Oracle development explained that this parse blocks stats blocks parse situation is normal.  Later after I got off the phone I built a simple test case proving that what he said was true.  I took a query that took a long time to parse in production and ran it on our development database and it took 16 seconds to parse there.  I choose the smallest table that the query included and gathered stats on it.  The stats ran in a fraction of a second when run by itself, but if I started the long parsing query in one window and ran the stats in another window the stats hung on a library cache lock wait for 15 seconds.  Then I created a trivial query against the same small table I had gathered stats on.  The query ran instantly by itself.  But, if I ran the long parsing query first, kicked of the stats which hung on the lock, and then kicked off the short query against the table I was gathering stats on the short query hung on a library cache lock also.  This example convinced me that the parse blocks stats blocks parse chain was real.

This morning I built a standalone test case that others can run to prove this out on their databases: zip of testcase.  To run the testcase you need three windows where you can run three sqlplus scripts in rapid succession.  In one window first just run tables.sql to create the test tables.  Then run these three scripts one after the other in each window to create the three link chain: chain1.sql, chain2.sql, chain3.sql.  Chain1.sql has the explain plan of a query that takes a long time to parse.  Chain2.sql gathers stats on one table.  Chain3.sql runs a simple query against the table whose stats are being gathered.  Chain1 spends all of its time on the CPU doing the parse.  Chain2 and 3 spends all of their time on library cache lock waits.

First I created two tables:

create table t1 as select * from dba_tables;
create table t2 as select * from dba_tables;

Next I kicked off the explain plan that takes a long time to run.  It joined 100 tables together:

explain plan into plan_table for 
select 
count(*)
from
     t1,
     t2,
     t2 t3,
...
     t2 t100
where
  t1.owner=t2.owner and
...
  t1.owner=t100.owner
/

This explain plan ran for 26 seconds, almost all of which was CPU:

Elapsed: 00:00:26.90

...

CPU in seconds
--------------
         26.81

Right after I kicked off the explain plan I kicked off this statement which gathered stats on the first table in the from clause:

execute dbms_stats.gather_table_stats(NULL,'T1');

This ran for 25 seconds and almost all of the time was spent on a library cache lock wait:

Elapsed: 00:00:25.77

...

Library cache lock in seconds
-----------------------------
                        25.55

Right after I kicked off the gather table stats command I ran this simple query making sure that it was unique and required a hard parse:

select /* comment to force hard parse */ count(*) from T1;

This ran for 24 seconds and almost all of the time was spent on a library cache lock wait:

Elapsed: 00:00:24.48

...

Library cache lock in seconds
-----------------------------
                        24.48

Evidently when a session parses a query it needs to obtain a shared lock on every table that the query includes.  When you gather statistics on a table you need to obtain an exclusive lock on the table, even if you are gathering statistics on one partition or sub-partition of the table.  While the statistics gathering session waits to acquire an exclusive lock any new parses that include the same table will hang.

Prior to Friday I did not think that there was any non-bug situation where gathering optimizer statistics would lock up sessions.  I thought that the only negative to gathering statistics at the same time as other application processing was that statistics gathering would compete for system resources such as CPU and I/O and possibly slow down application code.  But, now I know that gathering statistics can hang all queries that use the given table if stats gathering gets hung up waiting for a query that takes a long time to parse.

– Bobby

P.S. After reviewing the SR I wanted to understand what this parse blocks stats blocks parse looked like in a state dump.  The Oracle support analyst explained how the locks looked in a state dump that we uploaded but I didn’t get a chance to look at it closely until today.  I found the most important information in lines with the string “LibraryObjectLock” at the front of the line after some spaces or tabs.  There were three types of lines – the holding share lock, the waiting exclusive lock, and the many waiting share locks:

LibraryObjectLock:  Address=... Handle=0x5196c8908 Mode=S ...
LibraryObjectLock:  Address=... Handle=0x5196c8908 RequestMode=X ...
LibraryObjectLock:  Address=... Handle=0x5196c8908 RequestMode=S ...

The “…” indicates places I edited out other details.  The handle 0x5196c8908 identifies the table being locked.  The “Mode=S” string indicates a successful share lock of that table.  The “RequestMode=X” was from the stats job trying to get exclusive access to the table.  The “RequestMode=S” was all the other sessions trying to get shared access to the table waiting for stats to first get exclusive access.  Anyway, I just wanted to translate what Oracle support told me into something that might be useful to others.  Plus I want to remember it myself!


Categories: DBA Blogs

Top 5 wait events from v$active_session_history

DBA Scripts and Articles - Mon, 2014-11-24 09:30

This query returns the top 5 wait events for the last hour from v$active_session_history view. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Top 5 wait events from v$active_session_history [crayon-547411b39d0be279072425/] This is obviously an approximation, because v$active_session_history contains only 1 second samples, […]

The post Top 5 wait events from v$active_session_history appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Log Buffer #398, A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2014-11-24 09:10

This Log Buffer Edition covers some informative and interesting posts from Oracle, SQL Server and the MySQL.

Oracle:

If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that indirectly created it.

At the NoCOUG fall conference at the eBay town hall in San Jose, we got a first-hand look at the workings of the most complex database environments in the world.

Is there a feature you would like added to Inventory or Inventory Items? Tired of logging Service Requests with Support to request product enhancements? Well those days are over. You can now submit Enhancement Requests (ER’s) for Logistics (Inventory) and/or Inventory Items (APC/PLM/PIM) directly in their respective Communities.

Oracle Database 12c : EXPAND_SQL_TEXT, APPROX_COUNT_DISTINCT, Session Sequences and Temporary Undo.

Integrating Cordova Plugin with Oracle MAF – iOS Calendar Plugin by Chris Muir.

SQL Server:

Learn how to invoke SSRS reports from an SSIS package after the data load is completed.

Questions About Using TSQL to Import Excel Data You Were Too Shy to Ask.

This article shows a step-by-step guide to move the distribution database to a new SQL Server instance.

Monitoring Azure SQL Database.

Stairway to SQL Server Agent – Level 2: Job Steps and Subsystems.

Where in the Application Should Data Validation be Done?

MySQL:

Creating JSON documents with MariaDB.

Geographic replication with MySQL and Galera.

Sys Schema for MySQL 5.6 and MySQL 5.7.

Logging with MySQL: Error-Logging to Syslog & EventLog.

Multi-source Replication with Galera Cluster for MySQL.

Categories: DBA Blogs

Watch: Hadoop vs. Oracle Exadata

Pythian Group - Mon, 2014-11-24 09:05

Every data platform has its value, and deciding which one will work best for your big data objectives can be tricky—Alex Gorbachev, Oracle ACE Director, Cloudera Champion of Big Data, and Chief Technology Officer at Pythian, has recorded a series of videos comparing the various big data platforms and presents use cases to help you identify which ones will best suit your needs.

“Obviously there’s a big difference in cost,” Alex explains. “However, you would require significantly more engineering effort invested in Hadoop—so it’s a matter of scale when a solution like Hadoop becomes cost efficient.” Learn more key differentiators by watching Alex’s video Hadoop vs. Oracle Exadata.

Note: You may recognize this series, which was originally filmed back in 2013. After receiving feedback from our viewers that the content was great, but the video and sound quality were poor, we listened and re-shot the series.

Categories: DBA Blogs

12.1.0.2 Introduction to Zone Maps Part III (Little By Little)

Richard Foote - Mon, 2014-11-24 00:16
I’ve previously discussed the new Zone Map database feature and how they work in a similar manner to Exadata Storage indexes. Just like Storage Indexes (and conventional indexes for that manner), they work best when the data is well clustered in relation to the Zone Map or index. By having the data in the table […]
Categories: DBA Blogs

Great Lakes Oracle Conference 2015 call for speakers now open

Grumpy old DBA - Sun, 2014-11-23 12:17
Calling all geeks speakers big data nerds and application whiz kids the Great Lakes Oracle Conference ( GLOC ) is open for abstracts/presentation proposals ( May 18-20 2015 ).


This is a growing high quality regional conference in Cleveland where we are hoping to pass 350 ( 400? ) in attendance this year.  We have Kerry Osborne and Steven Feuerstein doing keynotes and on Monday 1/2 day workshops before the conference kicks into high gear for the next two days.


Call for presentations information here: GLOC speakers


Please consider joining us in Cleveland and hope to see you there!


John Hurley @grumpyolddba and NEOOUG President

Categories: DBA Blogs

New leafmisscount Option in Flex Cluster 12c

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

In the Oracle RAC previous version we have worked with the option “misscount” of crsctl, which applies for all the servers because there was only one kind of servers, but in the new version of Oracle (12c) we have a new kind of RAC configuration, the “Flex Cluster”. With Flex Cluster we have two kinds of servers “Hub nodes” and “Leaf Nodes”, the Hub Nodes have the same concept of the normal nodes in the previous versions, however the Leaf Nodes are different. I will let Oracle Documentation define these servers:

Hub Nodes are similar to Oracle Grid Infrastructure nodes in an Oracle Clusterware standard Cluster configuration: they are tightly connected, and have direct access to shared storage. In an Oracle Flex Cluster configuration, shared storage can be provisioned to Leaf Nodes independent of the Oracle Grid Infrastructure.

Leaf Nodes are different from standard Oracle Grid Infrastructure nodes, in that they do not require direct access to shared storage, but instead request data through Hub Nodes. Hub Nodes can run in an Oracle Flex Cluster configuration without having any Leaf Nodes as cluster member nodes, but Leaf Nodes must be members of a cluster that includes at least one Hub Node.

Reference: https://docs.oracle.com/database/121/CWADD/bigcluster.htm#CWADD92560

With new kind of servers, we also have a new option to configure the maximum time that a Leaf node can be tolerated with heartbeat issues. This crsctl option is “leafmisscount” and it’s exactly the same than the “misscount” for Hub Nodes, but this is for Leaf Nodes.

Let’s see how does it work:

 My Flex Cluster

Configuration

[root@dg1 ~]# crsctl check cluster -all

**************************************************************

dg1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg3:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

[root@dg1 ~]#

 

[root@dg1 ~]# crsctl get node role status -all

Node ‘dg1′ active role is ‘hub’

Node ‘dg2′ active role is ‘hub’

Node ‘dg3′ active role is ‘leaf’

[root@dg1 ~]#

Checking the current value:

[grid@dg1 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 30 for Cluster Synchronization Services.

[grid@dg1 ~]$

How does it work with 30 secs:

[grid@dg2 ~]$ date

Fri Nov 14 16:03:54 EST 2014

–Stop connectivity between Leaf Node
and Hub Nodes

[root@dg3 ~]# date

Fri Nov 14 16:04:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:04:29 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Now let’s increase the value to 600 sec:

[grid@dg1 ~]$ crsctl set css leafmisscount 600

CRS-4684: Successful set of parameter leafmisscount to 600 for Cluster Synchronization Services.

[grid@dg1 ~]$

Let’s confirm the value:

[grid@dg2 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 600 for Cluster Synchronization Services.

[grid@dg2 ~]$

The maximum value we can specify is 600:

[grid@dg1 ~]$ crsctl set css leafmisscount 3600

CRS-1671: The value for parameter leafmisscount is outside the allowed range of 1 to 600

[grid@dg1 ~]$

Let’s see how does it work with 600:

[root@dg3 ~]# date

Fri Nov 14 16:19:54 EST 2014

–Stop connectivity between leaf
node and hub nodes

[root@dg3 ~]# date

Fri Nov 14 16:24:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:25:18 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:27:30 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:28:08 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:04 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:50 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:30:10 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Categories: DBA Blogs

Challenge Me! Capturing SQL Server Data with PowerShell

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

LaerteHello 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.

From Mike Dietrich’s presentation about Database Upgrade:

Mike Dietrich

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:

Carsten Czarski

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 :-)

Ulrike Schwinn

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.

Frank Schneede

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:

Markus Michalewicz

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.

Larry Carpenter

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.

Hans Forbrich

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

RSS content

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_reads],

[ts2].[io_stall_read_ms],

[ts2].[num_of_writes],

[ts2].[io_stall_write_ms],

[ts2].[io_stall],

[ts2].[num_of_bytes_read],

[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_reads] – [ts1].[num_of_reads] AS [num_of_reads],

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

[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_read] – [ts1].[num_of_bytes_read] AS [num_of_bytes_read],

[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_reads] AS [Reads],

[num_of_writes] AS [Writes],

[ReadLatency(ms)] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

[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,*/

[AvgBPerRead] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) 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

(([num_of_bytes_read] + [num_of_bytes_written]) /

([num_of_reads] + [num_of_writes])) END,*/

[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 [ReadLatency(ms)] DESC

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

RSS content

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.

Your comments and suggestions are always welcome.

References:

http://oracle-randolf.blogspot.in/2012/01/incremental-partition-statistics-review.html
https://docs.oracle.com/database/121/TGSQL/tgsql_stats.htm#TGSQL413
https://blogs.oracle.com/optimizer/entry/incremental_statistics_maintenance_what_statistics
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
https://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables
———————————————————————————-

Related Links:

Home

Database 12c Index

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

 



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 [12c: Enhancements to Partition Exchange Load], All Right Reserved. 2014.

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 inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE

SQL> show sga

Total System Global Area 7600078848 bytes
Fixed Size 3728544 bytes
Variable Size 1409289056 bytes
Database Buffers 6174015488 bytes
Redo 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 inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string OFF
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
SQL> show sga

Total System Global Area 7600078848 bytes
Fixed Size 3728544 bytes
Variable Size 1291848544 bytes
Database Buffers 6291456000 bytes
Redo 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.
  • Use statistical inference to answer questions about real-world data.
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!

about.me: http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs