Pythian Group

Ask not what you can do for your data. Ask what your data can do for you!

Join us for an informal networking event alongside Rittman Mead on Monday December 8th during UKOUG. We will be discussing how to leverage data to drive your organization’s success. Come meet with peers and industry experts, Mark Rittman and Jon Mead of Rittman Mead, and Marc Fielding and Christo Kutrovsky of Pythian. The networking event will take place at PanAm Bar and Restaurant in Liverpool from 6-8 PM, and will include drinks and light refreshments.

After migrating a 10g database to 11g, I asked the Application team to start their tests in order to validate that everything was working as expected. I decided to keep an eye on OEM’s Top Activity page while they were running the most mportant job. I already knew what kind of “colors” I would find because I had checked its behavior in the former version. Suddenly, a strange kind of wait appeared on my screen: it was my first encounter with Backup Restore Throttle Speed.

I had never seen this wait before. It was listed in a user’s session so its name really confused me. No RMAN operations were running at that time. FRA was almost empty. I checked Oracle’s documentation and My Oracle Support. I found nothing but one Community post from 24-SEP-2013 with no conclusions. In the meantime, the job ended and I got the confirmation that everything was well, even faster than in the old version. Weird, very weird. It was time to review the PL/SQL code.

After reading lots of lines, a function inside the package caught my attention:

Sleep (l_master_rec.QY_FIRST_WAIT_MIN * 60);

Since the job was using a log table to keep track of its execution, I was able to match the wait time with this function pretty quickly. This code was inside the function’s DDL:

Finally I found the reason for this wait (and the explanation for its backup/restore related name): DBMS_BACKUP_RESTORE.SLEEP. As described in MOS note “How to Suspend Code Execution In a PL/SQL Application (Doc ID 1296382.1)”, the package was used to pause job’s execution while waiting for another task to be finished.

Lastly, it’s worth noting that OEM did not graph this wait on the 10g database but it was always there.

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.

“Hadoop is generally deployed in a single data center, multi-RAC deployment, but they’re all reasonably geographically co-located with each other,” Alex explains. Cassandra on the other hand, “…is frequently deployed in a very distributed fashion… Somewhere in Asia, Europe, North America… So you end up with a very fault-tolerant environment.” Learn how the two platforms compare by watching Alex’s video Hadoop vs. Cassandra.

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.

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 Big Data expertise.

This Log Buffer Edition brings some freshly picked blog posts right out of the gardens of Oracle, SQL Server and MySQL.

Oracle:

Suppose you have a process A that you want to run faster. This process doesn’t commit (much), so it doesn’t wait on log file sync. However, there is another multi-threaded process, B, that commits very frequently, and spends a lot of time on “log file sync”.

By this stage, you should be familiar with the basics of SQL Server indexes. We’ve discussed what an Index actually is, as well as some of the most common types you’re likely to encounter. Now that we’ve seen some simple examples of how Indexes can be useful, we’re going to delve deeper into nonclustered indexes, as we’ll see how they can improve the performance of more complex queries.

The Project Deployment Model introduced in SSIS 2012 speeds up the deployment of database projects in which there may be hundreds of SSIS packages per project. Not only that, but deployments can be configured differently for each environments such as test and staging, and there are now ways of monitoring the status and performance of packages and of versioning the SSIS Catalog.

A technique to deal with lack of metadata for stored procedures when used with SSIS.

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

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 down below.

UPDATE: Join us for an informal networking event alongside Rittman Mead on Monday December 8th during UKOUG. We will be discussing how to leverage data to drive your organization’s success. Come meet with peers and industry experts, Mark Rittman and Jon Mead of Rittman Mead, and Marc Fielding and Christo Kutrovsky of Pythian. The networking event will take place at PanAm Bar and Restaurant in Liverpool from 6-8 PM, and will include drinks and light refreshments.

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.

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.

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.

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.

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.

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

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.

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.

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.

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 Big Data expertise.

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.

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.

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:

Perfmon Counters

DMVs, DMFs, etc.

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

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.

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.

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.

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

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.

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.

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.

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.

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.

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.