Skip navigation.

Pythian Group

Syndicate content
Love Your Data
Updated: 17 hours 50 min ago

Data Collector and Policy-Based Management: Part 1

Fri, 2015-11-27 14:23


This article will address the main techniques for the efficient management of multiple instances in a SQL Server database 2014 using the Tools Data Collector and Policy-Based Management. In addition, it will be demonstrated in a practical way how to configure each of the tools, as well as how to extract and analyze the metrics collected.

With the exponential growth of the amount of data generated by applications, comes the increased complexity in managing database environments for the database administrator. With this growth, combined with the low cost of storage media, servers began a scenario in which the database administrator left to administer dozens of databases and proceeded to administer hundreds of them.

Since the main responsibilities of a database administrator is to ensure the integrity, performance and stability of all instances of SQL Server under its administration, the greater the number of instances and databases used within an enterprise, the greater the difficulty in monitoring and managing such an environment in proactive and automated fashion.

For this type of scenario, SQL Server makes it possible to centralize both the execution of routine tasks for an administrator, since the collection of performance metrics from all instances and existing databases is through the Tools Data Collector (DC) and Policy-Based Management (PBM). For example, there is the need for all databases that have the recovery model parameter set to Full to perform a backup of the log file every hour. So, instead of this policy existing only as a  concept, requiring you to check manually on all database servers, you can use the PBM to create “physically” a policy and ensure that it is applied at once in all instances of SQL Server.

In order to facilitate the understanding of the management of multiple instances, the presentation of the tools will be performed in the following order: first we will look at the Policy-Based Management and then we will know the Data Collector.


What is the Policy-Based Management?

The Policy-Based Management (PBM) is a feature available starting with SQL Server 2008 that enables the creation and implementation of policies on their SQL Server instances. The PBM works similarly to the created group policy through the Active Directory.

Note: Group policies provide centralized management of applications and users, by means of rules created by system administrators and that can be applied at various levels of the directory structure defined in Active Directory.

PBM applies a policy on a particular target, for example, a database, a table, a view, or a stored procedure and then it is checked to see if the target is in accordance with the rules of this policy. If the target does not agree, it is possible to both enforce the rules of politics as raise an alert to the administrator of the database so he/she knows of this violation.

One of the great advantages of the PBM is the implementation of a policy on multiple instances of a SQL Server database at once, facilitating the Administration and management of all the infrastructure of the Corporation Bank.

Many features of SQL Server 2014, such as Resource Governor, Data Compression and In-Memory OLTP need Enterprise Edition or Developer. This is not the case for the PBM, which is available in all editions of SQL Server, including Express (although with the Express Edition is not possible to create a Central Management Server).

As soon as the instance of SQL Server is installed in 2014, it is possible to create and evaluate the policies against any existing SQL Server in your environment, including in versions prior to 2014.


Policy-Based Management Components

The PBM is composed of three main components: Policies, Conditions and Facets, as shown in Figure 1. These components are arranged in a sort of hierarchical order for using the PBM. A facet is required for creating a condition, and the condition is necessary for the creation of policies. The policies, in turn, are applied to specific targets .

Figure 1. Policy Management in SQL Server 2014.



The targets are the managed objects for a particular policy and can be of various types: servers, databases, instances, stored procedures, etc. An important detail is that you can use more than one target at the same time in a policy. For example, we have a policy which States that only object names starting with the db _ prefix are correct and perform a validation of this rule on tables, functions and stored procedures of one or more instances at the same time.



A facet is a group of properties that relate to a particular target. SQL Server 2014 has 86 facets, each containing several different properties. This allows the use of hundreds of properties in the creation of a policy.

You can view the properties of a facet expanding Facets folder and double-clicking any of the options. For example, the facet Data File has several properties, such as maximum size of the data file, number of readings and writings and if the data file is online, as shown in Figure 2.

Note: The facets are available as read-only, i.e. it is not possible to create customized facets or modifications of existing ones. Currently new facets can be included only by Microsoft, through service packs or by upgrading the version of SQL Server.

Figure 2. Facet Properties Data File.



A condition can be described as a rule for a policy to be evaluated. Basically, the rule checks a target and, if this target is not in accordance with the policy, it fails. It is worth mentioning that a policy can evaluate only one condition, however it is possible that a condition has different rules for different properties. A condition can be viewed by expanding the Conditions folder and double-clicking any one of the available options, as shown in Figure 3.

Note: In an instance of SQL Server, a database will not exist conditions customized unless previously imported or created manually, that is, initially there will only be the conditions of system.

Figure 3. Condition created on Facet Database.



The policies are complete packages that include conditions, facets, targets, assessment modes and Server restrictions (the evaluation modes and the server are discussed in the next topic).

When created, the policies are stored in the system database msdb, but you can export them into an XML format. This portability allows database administrators with greater ease to share and compare the policies created. To view a policy it is necessary to expand the Policies folder and double-click any one of the options, as shown in Figure 4.

Figure 4. Details of a policy created

Note: In an instance of SQL Server, databases will not exist as customized policies, unless previously imported or created manually, that is, there will be only the initial policies.


Policy Evaluation Modes

The PBM has four distinct ways of performing a policy and that determines how the evaluation will occur under a predefined target. The following modes of evaluation may be available, depending on the facet used in policies:

  • On Demand: This evaluation mode specifies that the implementation should occur manually. By default, any policy with this evaluation mode is disabled automatically after it is created. However, even though I disabled it it can still be evaluated at any time.
  • On Schedule: By selecting this mode you can schedule the evaluation policy to be evaluated at any time. By default, you can select a schedule already created or create a new schedule that meets your needs. Creating a schedule allows you to set options such as the recurrence of execution, execution frequency per day, frequency of execution per hour and how long a policy should be executed. For example, you could run a particular policy for the next two weeks.
  • On Change: Log Only: when you select this mode, the policy will be evaluated only if a change is made to the target specified. If the change violates the policy, the event will be executed and the results of the violation will be stored in the event log and in the system database msdb. This evaluation mode helps the database administrator without affecting the performance of the environment.
  • On Change: Prevent: This evaluation mode is very similar to the On Change: Log Only, namely, the assessment will be the moment an event to perform any change in target. But unlike the Log Only option, Prevent performs the rollback procedure of any amendment which violates the policy.

The Figure 5 shows an example of a policy and evaluation modes available for the same.

Figure 5. Evaluation Modes.


Server Restrictions

In conjunction with the targets and the facets, the server restrictions are another way to control how a policy is evaluated. A server restriction is nothing more than a condition used to delete a particular policy server through the facet Server.

With the use of the PBM, you can create a server restriction to limit the evaluation of a policy only on instances of SQL Server using the Standard editions or Enterprise. When this policy is applied it will not be assessed by the instances that do not use these specific issues.


Management of Policies

SQL Server 2014 has some features that facilitate the management and evaluation of policies created. One of these features is the ability to create categories to group similar policies and use the Central Management Server (CMS) to execute the policies throughout the database environment.



The categories are a logical group of one or more policies that assist in the management and execution of the same. For example, you can create a policy group that will be evaluated only in test or development environments. When a policy is created, specify a category Description option, as shown in Figure 6.


Figure 6. Category definition


Central Management Server (CMS)

CMS functionality is not part of the architecture of the PBM, but has become extremely important in the use of policies in a SQL Server database consisting of multiple servers.

Through the CMS you can specify a database instance (or greater) to be a central management and store a list of registered instances that can be organized into one or more groups, as shown in Figure 7.

Figure 7. Central Management Server (CMS)



Once the policies are configured and implemented, there is no need to constantly check the servers to make sure that they are in accordance with the conditions set out in the policies. Instead, we can use the SQL Server Agent alerts to receive notifications automatically when a policy is violated.

In the next post of this 3 part series we will learn how to create a policy and how to use it.


Discover more about our expertise in SQL Server.

Categories: DBA Blogs

SQL On The Edge #5 – Custom Visualizations in PowerBi

Fri, 2015-11-27 13:39


Have you ever used Microsoft’s PowerBi service? If you have, have you used it within the last year? The reason I ask is that the current PowerBi service is so radically different from the initial release that pretty much only the name is what they have in common. Today I’m going to do a short summary of how we got here, where the service is, where it’s going and the actual topic for my video, the new awesome custom visualization functionality!

A Short History Lesson

A few years ago, Microsoft went on the direction of empowering business analysts on what is one of the most common business applications in the world: Excel. With this push, they started releasing some amazing plugins for Excel: PowerPivot, PowerQuery, PowerView, PowerMap. Suddenly we could import millions of rows from all kinds of sources into Excel! And transform them! And visualize them! Then with the release of Office 365 and Sharepoint Online, a service was created to make it easy to share and consume all these Excel-born reports. And thus PowerBi was born but it required all these other tools and subscriptions. It didn’t catch on.

Fast Forward To Today

This initial offering of PowerBi had too many external dependencies. You needed Excel with all the bells and whistles to do the reports and then all these other satellite cloud services. Thankfully someone saw and executed on a clearer vision: one service, no dependencies, no cost of entry, fully mobile friendly. It’s been a very interesting journey from a market perspective to see how MS released something, course corrected and then went head-on with their improved vision.

The PowerBi desktop designer is free. The mobile apps (all major mobile OS’es AND Windows Phone), free as well. The service itself also has a free fully functional tier, it’s only limited by the amount of data and the enterprise sharing capabilities. Add the ease of use of the tools and the natural language query capabilities and this is now a strong tool that can finally become a contender with the Tableau and Qlikviews of the world.

No, it’s not perfect but it is growing and an insane amount of new features are getting added constantly. New vendors are adding content packs and now custom visualizations have made an appearance.

Community Involvement

The idea behind the first batch of custom visuals was great. MS opened a contest for people to submit their best custom visuals and the community responded with amazing creativity and participation. Not only do these immediately provide more value to every current user of PowerBi but they also serve as examples for further development of more custom visuals.

The full gallery of custom visuals can be found in the PowerBi Visual Gallery.

And if you don’t have your PowerBi account, try it out, it’s free! Head over to the official PowerBi site.

Demo Time

For the demo of working with a PowerBi project on the desktop, online and importing a custom visual, let’s check out the video! Enjoy!



Discover more about our expertise in SQL Server.

Categories: DBA Blogs

Log Buffer #451: A Carnival of the Vanities for DBAs

Fri, 2015-11-27 12:59


The show goes on. This Log Buffer Edition picks some blogs which are discussing new and old features of Oracle, SQL Server and MySQL.


  • Directory Usage Parameters (ldap.ora) list the host names and port number of the primary and alternate LDAP directory servers.
  • Data Visualization Cloud Service (DVCS) is a new Oracle Cloud Service. It is a subset offering of the currently supported Business Intelligence Cloud Service (BICS).
  • ORA-24247: network access denied by access control list (ACL).
  • Latches are low level serialization mechanisms, which protect memory areas inside SGA. They are light wait and less sophesticated than enqueues and can be acquired and released very quickly.
  • handling disks for ASM – when DB, Linux and Storage admins work together.

SQL Server:

  • How to use the Performance Counter to measure performance and activity in Microsoft Data Mining.
  • Phil Factor demonstrates a PowerShell-based technique taking the tedium out of testing SQL DML.
  • Sandeep Mittal provides an introduction to the COALESCE function and shows us how to use it.
  • Hadoop many flavors of SQL.
  • Installing and Getting Started With Semantic Search.


  • Support for storing and querying JSON within SQL is progressing for the ANSI/ISO SQL Standard, and for MySQL 5.7.
  • Loss-less failover using MySQL semi-syncronous replication and MySQL Fabric!
  • Memory consumption The binary format of the JSON data type should consume more memory.
  • This post compares a B-Tree and LSM for read, write and space amplification. The comparison is done in theory and practice so expect some handwaving mixed with data from iostat and vmstat collected while running the Linkbench workload.
  • If you do not have a reliable network access (i.e. in some remote places) or need something really small to store your data you can now use Intel Edison.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Delayed Durability and Impact on Mirroring

Fri, 2015-11-27 12:39

To Test the delayed durability feature with mirroring in high performance and high safety mode. The idea is to confirm what the performance improvement is of the transaction and if it has any benefit in high latency networks.


Test Scenario

We have two databases configured with mirroring in high safety mode, which will require that commits happen at the secondary first. One database called “DalayedTEST” has delayed durability enabled with FORCED mode. Then I have configured performance counters to check latency and performance of each database each second, I have added mirroring, transaction, lock and physical disk counters so we can compare the performance when using delayed durability or not in a mirrored environment. Then we are going to switch mirroring to high performance and see its behaviour as well. No Witness is configured




Using SQLQueryStress tool I am loading 5000 transactions to a log table with 50 threads enabled. This is equivalent to 50 persons loading 5000 records to the same table at the same time. Should be a good test to analyze behaviour of the databases.

Normal Database Test (Full Transactions) High Safety Mode









Locks and Transactions



Physical Disk




A failover happens 30 seconds after starting the test. The database is able to failover and record count 98850.




Delayed Database Test (Delayed Transactions) High Safety Mode









Locks and Transactions



Physical Disk





A failover happens 30 seconds after starting the test. The database is able to failover and record count 165757.


Conclusion – High Safety Mode

Mirroring High Safety Mode



Having delayed transactions enabled in a database with high safety mirroring improves performance under high contention scenarios. In this case having lots of transactions running at the same time and requesting for the same table object proved to be better, as the execution time was faster when using delayed transactions.

When checking the mirroring behaviour one can see that the mirroring log queue is bigger with delayed transactions. The difference is that with full transactions the queue is processed sequentially, which decreases the queue while the transaction completes. As a result, you will see a high spike at the beginning of the stress test which is the full queue , and then it decreases in time. When using delayed transactions one can see spikes spread evenly every 20 seconds or so, this means that one batch is processed, and then other batch is processed, and this process carries on until the queue is empty.

So having a sequential mirror queue processing vs. batch processing makes a difference in latency? It is clear that transaction delay is 3 times worse with delayed transactions as it will processes batches in a cyclic way, which will saturate more at the endpoint on every iteration and also keep the transaction waiting for a commit acknowledgement from the mirroring partner. So, having delayed transactions with high contention, it is faster locally as it has less logical reads/writes, but slows down the mirroring communication as it works in batches.

But total execution time was better with delayed transactions?

Having delayed transaction improves execution time but increases mirroring delay, so it is simply a matter of doing the math. The local processing was so much better that it compensated for the slower mirroring transaction delay and in general terms is faster in this scenario (I used a local network), however if you add other variables to the stress test, such as a high latency network for the mirroring, the end result might not be compensated and the total execution time can end up being around the same or worse.


Now, let´s see the numbers with a high performance mode:

Normal Database Test (Full Transactions) High Performance Mode









Locks and Transactions



Physical Disk



Delayed Database Test (Delayed Transactions) High Performance Mode









Locks and Transactions



Physical Disk



Conclusion High Performance Mode

Mirroring High Performance Mode



Having delayed transactions means we have a database with high performance mirroring that is not improving performance, despite the fact that indicators show less locking and less logical reads. Seems the mirroring queue was not being handled properly. The mirroring queue is considerably bigger when using delayed transactions and despite the fact that we are working in high performance mode the general execution time is the worst in this test environment. After I realized this I ran other tests to see if the results were the same, and generally speaking the total execution time is almost the same, a difference of 4-10 seconds tops.


General Conclusion

When using delayed transactions in all the tests we can conclude that consistently the mirroring queue, and the amount of data transferred is considerably larger than the one produced by full transactions.

When using high safety mode, it seems delayed transactions have a better disk performance, which compensate the higher mirroring/network values and results in a faster execution time. This situation can change if the network has high latency producing the same or worse performance.

When using high performance mode it seems delayed transactions have an ok to good disk performance, but it is not good enough to compensate the higher mirroring/network values, and results in the same or slower execution time.

I would recommend enabling delayed transactions only to fix high contention rates. Also avoid using it in high latency or very busy networks when mirroring high performance environments. These recommendations are based on a small test and in a controlled environment and should not necessarily extend to all environments out there. The rule of thumb is that using delayed transactions does impact mirroring performance, which by itself is a technology known to introduce certain performance issues depending on the system, so do not take the decision to use delayed transactions lightly and test first in a staging environment before rolling into production.


Discover more about our expertise in SQL Server.

Categories: DBA Blogs


Fri, 2015-11-27 11:39


This blog post is a continuation to an earlier post about my musings on Oracle EBS support for virtual host names.

Actually, most parts of Oracle E-Business Suite work with virtual host names with out any problem. The only component that doesn’t work when using virtual host names are the Concurrent Managers. Concurrent Managers expect that the node name defined in the Concurrent Manager definition screen matches the host name FNDLIBR executable reads at the server level. Having the virtual host name as an alias in the hosts file in the server doesn’t cut it for the FNDLIBR executable. FNDLIBR reads the host name of the server using the Unix system call.

This behaviour of FNDLIBR can be hacked by overriding the Unix gethostname system call using LD_PRELOAD functionality. There is already a prebuilt program out there on github to achieve this functionality. It’s called fakehostname. I have tested this and verifies that it works with Oracle 11i, R12.0 and R12.1 version without any problem.

Here is a demo:

$ hostname
$ export LD_PRELOAD=/home/oracle/fakehost/
$ export MYHOSTNAME=ebsfakehost
$ hostname
$ export MYHOSTNAME=newebshost
$ hostname


This utility helps in making concurrent managers thinking that it’s running on the virtual host by overriding the gethostname system call. This method of getting EBS to work with virtual hostnames doesn’t work any more with EBS R12.2. The reason for this EBS R12.2 is that it it’s shipped in a mix of 32bit and 64bit components. Earlier releases of EBS like 11i, 12.0 and 12.1 are 32bit only, even though they run on 64bit platforms. We can get EBS R12.2 working by having both 32bit and 64bit versions of the fakehostname library in the LD_PRELOAD, but EBS borks too many warning messages about not being able to load 32bit/64bit libraries, which defeats the whole purpose of having a simple solution.

I am working on another way of getting virtual host names working in EBS R12.2. I will post that in my next blog post. Stay tuned!


Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Advanced Compression Option Caveat in Oracle 12c

Tue, 2015-11-24 12:18


Oracle 12c introduced a new capability to move a partition online, without any interruptions to DML happening at the same time. But, there’s a catch. So far we’ve been able to use basic table compression without having to worry about any extra licensing – it was just a plain EE feature.

If you are planning to use the online partition move functionality, carefully check if you’re not using basic compression anywhere. For example:

create tablespace data datafile '+DATA' size 1g

create user foo identified by bar
default tablespace data
quota unlimited on data

grant create session, create table to foo

connect foo/bar

create table test (x int, y varchar2(20))
partition by range (x)
partition p1 values less than (100) tablespace data compress,
partition p2 values less than (200) tablespace data,
partition p3 values less than (300) tablespace data

So we now have this, and our licensing is still as we know it:

select partition_name, compression, compress_for from user_tab_partitions
------------------------------ -------- ------------------------------

We can use the new feature on partition p3:

alter table test move partition p3

Or, we can use the traditional means to compress the partition p2:

alter table test move partition p2

But as soon as we do this move “online”, we are required to purchase the Advanced Compression Option:

alter table test move partition p2

And, even sneakier:
alter table test move partition p1

Notice how partition p1 – which was previously compressed – also was online moved to a compressed format:

select partition_name, compression, compress_for from user_tab_partitions

—————————— ——– ——————————


And that, therefore, required the Advanced Compression Option.

Also note that the usage of this is not caught by dba_feature_usage_statistics (tested on

select name, currently_used from dba_feature_usage_statistics where lower(name) like '%compress%';

—————————————————————- —–
Oracle Advanced Network Compression Service FALSE
Backup ZLIB Compression FALSE
Backup BZIP2 Compression FALSE
Backup BASIC Compression FALSE
Backup LOW Compression FALSE
Backup MEDIUM Compression FALSE
Backup HIGH Compression FALSE
Segment Maintenance Online Compress FALSE
Compression Advisor FALSE
SecureFile Compression (user) FALSE
SecureFile Compression (system) FALSE
HeapCompression FALSE
Advanced Index Compression FALSE
Hybrid Columnar Compression FALSE
Hybrid Columnar Compression Row Level Locking FALSE

15 rows selected.

I also tried to bounce the database and the data wasn’t updated in my tests. I would’ve expected this to show up under “Segment Maintenance Online Compress”, but in my tests, it did not.

This feature restriction isn’t documented anywhere in the official product documentation – at least not that I could find. The only place where I could find this information was in this Oracle document.


Discover more about our experience in the world of Oracle.

Categories: DBA Blogs

Implementing Fuzzy Search in SQL Server – Part 2: Levenshtein Distance

Mon, 2015-11-23 15:18


The Levenshtein Distance, as discussed in my last post, is a way to measure how far two strings are located from each other. There are several T-SQL implementations of this functionality, as well as many compile versions. In addition, the MDS library in SQL Server has a Similarity function which uses the Levenshtein Distance to find how similar two words are.

In this post, I’ve done a simple comparison of performance using a C# CLR implementation of Levenshtein Distance (The code is from the Wiki), and a well written T-SQL implementation from Arnold Fribble.

As many of you might expect, the C# implementation is much quicker. Needing only 2504 ms to run through dictionary table of 203,118 words. The T-SQL implementation took 42718 ms for the same work.

A comparison of two ways to implement the Levenshtein Distance

Levenshtein Distance Comparison


Levenshtein Distance CLR

To implement the Levenshtein Distance CLR, run this SQL Script


Levenshtein Distance T-SQL

To implement the Levenshtein Distance in T-SQL, run the below code. Please note that this function has a cut-off value (@d) where it simply gives up and returns -1.


CREATE FUNCTION edit_distance_within(@s nvarchar(4000), @t nvarchar(4000), @d int)
DECLARE @sl int, @tl int, @i int, @j int, @sc nchar, @c int, @c1 int,
@cv0 nvarchar(4000), @cv1 nvarchar(4000), @cmin int
SELECT @sl = LEN(@s), @tl = LEN(@t), @cv1 = ”, @j = 1, @i = 1, @c = 0
WHILE @j <= @tl
SELECT @cv1 = @cv1 + NCHAR(@j), @j = @j + 1
WHILE @i <= @sl
SELECT @sc = SUBSTRING(@s, @i, 1), @c1 = @i, @c = @i, @cv0 = ”, @j = 1, @cmin = 4000
WHILE @j @c1 SET @c = @c1
SET @c1 = UNICODE(SUBSTRING(@cv1, @j, 1)) + 1
IF @c > @c1 SET @c = @c1
IF @c @d BREAK
SELECT @cv1 = @cv0, @i = @i + 1
RETURN CASE WHEN @cmin <= @d AND @c <= @d THEN @c ELSE -1 END


Discover more about our expertise in SQL Server

Categories: DBA Blogs

ORA-10173: Dynamic Sampling Time-Out Error

Mon, 2015-11-23 15:06


Recently on a heavily used and freshly upgraded ware-house type database, we started seeing lots of ORA-10173 dumped into the alert log. The information out there on this error is somewhat sparse, and it is often linked to Tuning Advisor functionality. Since we’re not running that advisor on this database, a little digging was in order.

What I always do as a first step if I am confronted with an error where I am not certain why and exactly where it is raised, is to set up an error trap. In Oracle, this can be done by setting an errorstack event, like this:

alter session set events '10173 trace name errorstack level 1';

Please note: whenever possible you’d want to set this on the smallest scope possible – starting with the session level, eventually in a login trigger for multiple sessions, and only when all that fails, on the system level using the alter system statement.

Once you have trapped an occurrence or two, you can disable it again by running:
alter session set events '10173 trace name errorstack off';

Upon activating this event, the next time around when this exception is raised Oracle will write a trace file to the diagnostics repository. Two two most prevalent pieces of information in the trace file are the current SQL query:

----- Current SQL Statement for this session (sql_id=anbp9r5n8ysu6) -----
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM (SELECT "INV"."CASE_UPC" "ITEM_1","INV"."WHS_NBR" "ITEM_2",SUBSTR("INV"."CASE_UPC",3,11) "ITEM_3" FROM "XX"."XX_WHS_INV" "INV","XX"."XX_CASE_DIM" "CD2","XX"."XX_WHS_DIM" "WD" WHERE "WD"."WHS_DESC" LIKE '%PEY%' AND "CD2"."WHS_NBR"="INV"."WHS_NBR" AND "CD2"."CASE_UPC"="INV"."CASE_UPC" AND "INV"."WHS_NBR"="WD"."WHS_NBR" GROUP BY "INV"."CASE_UPC","INV"."WHS_NBR",SUBSTR("INV"."CASE_UPC",3,11)) "VW_DIS_13") innerQuery

And the stack trace, which looks something like this:

skdstdst()+29 call kgdsdst() 7FFFA592F860 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
ksedst()+112 call skdstdst() 7FFFA592F860 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbkedDefDump()+1153 call ksedst() 000000000 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
ksedmp()+26 call dbkedDefDump() 000000001 ? 000000000 ?
7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbkdaKsdActDriver() call ksedmp() 000000001 ? 000000000 ?
+880 7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
dbgdaExecuteAction( call dbkdaKsdActDriver() 7F482FDE8618 ? 7FFFA5932830 ?
)+319 7FFFA5912120 ? 7FFFA5912238 ?
7FFFA592FF18 ? 7FFFA592F810 ?
... ( rest omitted for clarity and we don't need the stack for the purpose of this post )

For my case, the interesting bit was the query that caused the error to be raised. This query provides us with some hints about what’s going on:

– We can see the DS_SVC comment at the beginning of the query
– We can see lots of hints, the more interesting ones being dynamic_sampling(0) and result_cache(snapshot=3600)

A little bit of research led to a new feature introduced in 12c: Dynamic Statistics (or Adaptive Statistics). This is an enhancement to dynamic sampling, where the optimizer can automatically choose to gather more statistics as part of the parse phase. The sampling is controlled internally with a time out, and if that time out is hit, ORA-10173 is raised to the alert log.

This means that these errors are generally safe to ignore, as they are raised purely internally and your application won’t see these exceptions. Your query didn’t, and won’t fail. However, your query may well be spending more time parsing and sampling data than what makes sense. My colleague Slava has already blogged about such a case here: Performance Problems with Dynamic Statistics in Oracle 12c and there are other documented cases.

The feature can be controlled through the optimizer_dynamic_sampling parameter. If it’s set to the default value of 2, the optimizer may choose to raise that temporarily to 11 which enables these new dynamic features. You can tell by looking at an execution plan of a query using dbms_xplan.display_cursor for example, and looking at the notes section:

- dynamic statistics used: dynamic sampling (level=AUTO)

As always, to determine whether or not this feature is helpful or harmful to your applications: benchmark it. You probably don’t want to turn it off system-wide, but it may make a lot of sense to disable it for certain sessions, or certain queries in your application. The ORA-10173 you may see in the alert log however is no reason to panic, if your application is not negatively impacted. If you are mostly running queries that take minutes or longer to execute, spending a couple seconds gathering more statistics may not impact you. If on the other hand your queries are fast, and Oracle spends more time parsing and sampling than it actually takes to execute the query, or you are seeing loads and loads of ORA-10173, you may want to take action. Alternatively you may also resort to setting a _fix_control as mentioned in Slava’s post – but as always, double-check with Oracle support if setting it has the desired effect on your exact version of Oracle.

In essence, the ORA-10173 indicates wasted work, because Oracle is spending time gathering data, hits the timeout and then throws the partial data away since it’s incomplete. This certainly isn’t optimal, but again, your mileage may vary if this really impacts your applications. In our case, we are seeing thousands of them, and we have opened a case with Oracle, since obviously somewhere the optimizer keeps making the wrong choice. The bug is currently still with BDE but I shall keep you posted if anything develops.

Another thing worth noting is that Oracle stores the results of these dynamic sampling queries in the result cache, and marks them for expiration after a certain time has elapsed. This timeout that we are seeing with the result_cache(snapshot=3600) hint, comes from the hidden parameter _optimizer_ads_result_cache_life which defaults to 3600 in This may also help in reducing the frequency of the dynamic sampling queries by increasing the lifetime for which they remain cached. But as always, before changing any hidden parameters, please consult with your local Oracle support representative!


Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

What’s the Ratio of Your Marketing Budget to Cyber Security Budget?

Sun, 2015-11-22 19:45

This was a question asked over Twitter by @DanBarker to TalkTalk, the major British telco after they managed to lose a significant portion of its customers’ details, apparently through an SQL Injection attack by a 15 year old.

The question wasn’t answered, but the sooner companies realise that a security incident can wipe out a significant part of the brand’s goodwill, the more this ratio will adjust.

Here are three top tips to ensure you’re investing wisely in cyber security, and protecting your brand’s good name:

1. Keep everything patched and up to date – old databases have security holes that can lead to an attack. A new client of ours was running SQL Server 2002 and failed a pen-test in 30 minutes. But it doesn’t need to be that old to fail.

2. Audit and document everything. What data is where? Who or what has access? Do they need it? Are they still with the company? Not knowing what data might be lost was the major problem at the NSA post-Snowden. And within hours of the TalkTalk hack, the CEO said “I don’t know today whether all four million customers’ details have been stolen” (it was about 150,000 in the end, but by then the brand damage was done).

3. Check how employees and third party suppliers access your production environment, to make sure it’s from a safe, virus-free place. Can you see what they see? Do you know what they’re looking at?

Overall, to use Pythian’s tagline, just learn to “love your data”.

If your in-house team doesn’t have the skills and expertise to take care of these tasks, then find a company or contractor that does. The cost will be far less than a major security incident. And probably lower than your marketing budget too.

Categories: DBA Blogs

Log Buffer #450: A Carnival of the Vanities for DBAs

Fri, 2015-11-20 15:08

This Log Buffer Editions picks few blog posts from Oracle, SQL Server and MySQL.


  • If you grant the DBA role to a user, Oracle also grants it the UNLIMITED TABLESPACE system privilege. If you then revoke the DBA role from this user, Oracle also revokes its UNLIMITED TABLESPACE system privilege.
  • Lost SYSMAN password OEM CC 12gR5.
  • How Terminal Emulation Assists Easy Data Management.
  • Using EMCLI List Verb to Get Detailed Information of EM Targets.
  • How to change apex_public_user password in ORDS.

SQL Server:

  • When the connection between you and the the target host are multiple servers across the continent, the latency will drive crazy mad.
  • SQLCMD and Batch File magic.
  • Greg Larson walks through the GUI installation process for SQL Server 2016 and explore these new installation options.
  • A Single-Parameter Date Range in SQL Server Reporting Services.
  • Is SQL Server killing your application’s performance?


  • MariaDB Galera Cluster 10.0.22 and Connector updates.
  • Building MaxScale from source on CentOS 7.
  • Orchestrator & Pseudo-GTID for binlog reader failover.
  • InnoDB holepunch compression vs the filesystem in MariaDB 10.1.
  • Open-sourcing PinLater: An asynchronous job execution system.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Pythian Champions Gender Diversity in Tech

Wed, 2015-11-18 13:45


At Pythian, we embody and value diversity in every form—in our religion (or lack thereof), our geography, our culture, and of course the location we’ve chosen for our home.  We deeply believe that the best teams are diverse and we are committed to increasing our diversity in all these areas.

We’ve been doing time-zone diversity for over a decade and we’ll keep doing it—we’re actually pretty good at it. In support of time zone diversity, we’ve become an industry leader in the art of creating high-performance teams by blending people from all over the world regardless of religion, culture, country of origin or residence. But now it’s time for us to turn our attention to gender diversity.

And so, with this note and video, I am proud to announce the Pythia Program. We’re going to increase the number of brilliant women who work with us and we will take a leadership position in a very important conversation to encourage more women to enter STEM fields and the tech industry.

Inspired by Microsoft, Twitter, Pinterest and Google, Pythian is the first technology company headquartered in Canada to announce its gender stats: 35 percent of the company’s leadership team are women, 27 percent of our managers are women and only 9 percent of technical roles at Pythian are held by women. Pythian also claims a Pythia Index of 56 percent.

The Pythia Index is the ratio of people in a business or in a team that are women leaders or roll up to a woman leader, as a percentage of total headcount. At Pythian the Pythia Index is 56%, which means that 56% of Pythianites are themselves women leaders or report up the org chart to a woman. Conversely, 44% of Pythian employees have zero women leaders all the way up the org chart from them.

So how do we currently compare to industry leaders? According to the most recent published numbers, Microsoft reported that women comprise 29.1 percent of its workforce, but only 16.6 percent work in technical positions and just 23 percent hold leadership roles. Twitter said women fill 10 percent of its technical jobs, with 21 percent in leadership. And women Googlers account for 17 percent of the search giant’s tech jobs, while only 21 percent manage others. Industry averages among all surveyed major US tech firms are 15.6% of technical roles and 29.6% of leadership.*

With this announcement, I am challenging our business to surpass industry averages within no more than three years, and sooner if possible.

The Pythia Program is central to how we plan to realize the best possible talent mix. By acknowledging and strengthening our deliberate talent blending practices, we hope not only to garner interest from potential employees, but to encourage other businesses to set similar diversity goals. This is not a corporate social responsibility initiative, it’s good for business, and it’s good for the technology sector.

Under the Pythia program we will:

  • Continue to focus on eliminating any unconscious gender biases in our recruiting and management practices.
  • Actively promote Pythian as a diversity-focused company so we can get more than our fair share of the top female leaders and tech talent.
  • Help build our future pipeline of female leaders and tech talent by working with and supporting organizations who have programs to encourage more women to go into tech.

This project is very important to me personally and I’ll continue to blog on the subject in the future.


Discover more about the Pythia Program.



Categories: DBA Blogs

High “cursor: pin S wait on X” waits?

Tue, 2015-11-17 14:59

If your system meets any of the following criteria:

– Oracle or higher
– Partitioned tables
– Parallel query heavily used
– Bind variables in use, as they should be

and, you’re seeing unusually high “cursor: pin S wait on X” waits, then you may want to know that this week, patch 21834574 was released.

To give you a little bit of background information; the issue is with a new code path introduced in Oracle version 12, which is related to some of the brand new infrastructure life-cycle management (ILM) functionality. This ILM feature – whether you are intentionally using any ILM functionality or not – causes delays when a new child cursor is created in the library cache. On a client’s system we have observed waits of up to 0.3 seconds.

In a nutshell, here’s what’s happening:

– A new parallelized query using bind variables is hard parsed.
– Oracle creates a new cursor, and adds a child cursor.
– Each of your PX slaves will then wait for the following event: “cursor: pin S wait on X”.
– The mutex being waited on is of type Cursor Pin at the location “kkslce [KKSCHLPIN2]”. This is normal and these waits can’t be fully avoided.

But what’s not normal is that the mutex is being waited on for a quarter of a second in each slave session.

Oracle has now released a patch which implements a new _fix_control which can be set to enable the fix once the patch is applied. As always, please consult with Oracle Support before applying this patch and setting this parameter, to make sure that you really are seeing this particular bug. There are others in the current versions of Oracle which share very similar symptoms, and the only way to be certain is to double-check with Oracle support or development.

Happy patching!


Discover more about our expertise in the world of Oracle

Categories: DBA Blogs

Comparing Schemas Between Hive Clusters

Fri, 2015-11-13 13:36


When running several different hive instances, we found the process of maintaining/confirming synchronization to be quite time consuming. While several tools made available by Cloudera and other frameworks do provide a visual interface for an individual instance of hive, there was no available tool for comparing across clusters. We came up with a useful way to compare hive schemas between clusters.

Our process contains 3 main steps:

  1. Fetch the schema from all hive environments, store the schema as a dictionary in a file.
  2. Create a class to perform comparisons leveraging python list, dict and pandas dataframe structures.
  3. Display the results/visualize the changes using a web interface (this approach uses python Flask).


Step 1- Fetch the Schema From all Environments
To fetch the schema we use the hive metastore api via the package.

First, we create a socket to connect to the hive thrift server.

transport = TSocket.TSocket(host, port)
transport = TTransport.TBufferedTransport(transport)
protocol = TBinaryProtocol.TBinaryProtocol(transport)

global metastore_client
metastore_client = ThriftHiveMetastore.Client(protocol)

Once that is done, it’s straightforward to get all of the tables in a db (there are lots of other great methods  that have been documented).
db = 'myDatabase'
tables = metastore_client.get_all_tables(db)

Once we get the tables, we can call the get_fields() method
for table in tables:
for field in metastore_client.get_fields(db, table):
# field is a named tuple FieldSchema(comment, type, name)
print field

Using the methods above, we retrieve all of the values and store them in a dictionary, locally in a text file, one file per environment.
{'myDatabase': {
FieldSchema(comment=None, type=int, name='user_id'), FieldSchema(comment=None, type='string', name='first_name'), FieldSchema(comment=None, type='string', name='last_name')
FieldSchema(comment=None, type=int, name='order_id'), FieldSchema(comment=None, type='string', name='item_id'), FieldSchema(comment=None, type='string', name='price')


Note that thrift gives us a tuple for the ddl, so for ease, we declare this named tuple locally.
FieldSchema = collections.namedtuple(‘FieldSchema’, [‘comment’,’type’, ‘name’])


Step 2 – Create Class to Compare Dictionaries

Now that we have a separate schema file for each hive instance, we can load these files into dictionaries and begin to compare them.

f_stage_dict = open('schema_files/stage_schema_thrift.out')
f_prod_dict = open('schema_files/prod_schema_thrift.out')
d_stage = eval(
d_prod = eval(

Create a class to compare the dictionaries. Comparisons are done leveraging a variety of nice python tools (lists, set comparison, pandas dataframes, etc..). More detail in the repo.

Class DictCompare:
def get_fields_by_table(self, db, table):...
# returns list of fields for tableA and tableB
def get_databases(self):...
# union of all dbs in all schemas A,B
def compare_fields(self, db, table)...
# returns tablediff tuple showing all field differences
def compare_dbs(self, db)...
#returns list of differences between dbs
tables_a = [table for table in self.dict_a[db]] tables_b = [table for table in self.dict_b[db]] db_diffs_a = set(tables_a).difference(set(tables_b))
db_diffs_b = set(tables_b).difference(set(tables_a))

Instantiate the class by passing the path of the schema files, and the friendly names used in the config.json file.
dc = sc.DictCompare(schema_files_path,”dev”, “stage” )
dbs = dc.get_databases()

# show all of the databases..
print "DBS=",dbs

# get all diffs for a given database
compare_dict = dc.compare_dbs("my_db")


Step 3 Display the Results / Visualize the Changes

I used a quick and simple python flask website to display the differences. Note there is a form on the page that allows users to select the hive instances to compare.

def homepage():
env_a = # 'stage', for example
env_b = # 'prod', for example
dc = sc.DictCompare(schema_files_path,env_a, env_b )
dbs = dc.get_databases()
return render_template("home.html", dbs=dbs, a_name =dc.a_name, b_name = dc.b_name,form =form )

Source code (open sourced under Apache2 license):

I Look forward to any comments or feedback.


Discover more about our expertise in Big Data and Infrastructure technologies. 

Categories: DBA Blogs

Log Buffer #449: A Carnival of the Vanities for DBAs

Fri, 2015-11-13 13:19


This Log Buffer Edition covers some of the niftiest blog posts from Oracle, SQL Server and MySQL.


  • OBIEE 11g and Essbase – Faking Federation Using the GoURL.
  • You can use one of these to link an exception name with an Oracle error number. Once you have done this, you can use the exception name in the exception block which follows the declaration.
  • This is a short post to help out any “googlers” looking for an answer to why their EM Cloud Control install is failing in the make phase with
  • A short video that Jonathan Lewis did at the OTN lounge at RMOUG a couple of years ago has just been posted on YouTube. It’s about the improvements that appear in histograms in 12c.
  • Changing the name of the server or load-balancing server handling your BI Publisher workload for OEM can be done with a single EM CLI command.

SQL Server:

  • Manoj Pandey was going through some sample Scripts provided by Microsoft SQL Server team on their site, and was checking the JSON Sample Queries procedures views and indexes.sql script file.
  • When you open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window In Power Pivot, click Get External Data, choose From Other Sources Choose Microsoft Analysis Services.
  • After the introduction of the StringStoresCompatibilityLevel property in SSAS dimension after SQL Server 2012, SSAS database designer may try to create a MOLAP dimension with more unique values than what is allowed in previous SQL Server versions.
  • After SQL Server 2012 is released, SSISDB provides stored procedures to create SSIS package executions. There is one problem though.
  • This is the fourth installment in a blog series. The previous entry is located here Based on the previous blogs in this series, you should have gotten your database hosted in WASD by now & secured access to your server.


  • finally runs MySQL 5.7 which was released on October 21.
  • What Does The Universal Scalability Law Reveal About MySQL?
  • There are many dimensions by which a DBMS can be better for small data workloads: performance, efficiency, manageability, usability and availability.
  • The new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available.
  • Nginx is well-known for its ability to act as a reverse-proxy with small memory footprint. It usually sits in the front-end web tier to redirect connections to available backend services, provided these passed some health checks.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

US-EU Safe Harbor Framework Invalidated – It’s time to Love Your Data!

Fri, 2015-11-13 12:43

Pythian has been closely following the developments in the EU relating to the invalidation of the US-EU Safe Harbor Framework.

On Friday, November 6, 2015, the European Commission issued guidance on the cross-border data transfer issues. This guidance spoke more to “alternative transfer tools” available to legitimize data flows to jurisdictions deemed “not adequate,” like the United States.

If you do transfer data and rely on Safe Harbor principles, we recommend you:

Keep Calm
Don’t rush to other transfer mechanisms that may turn out to be less than ideal.

Love your data!
What personal data you are transferring outside the EU?
Where is it going?
What arrangements have you made to ensure that it is adequately protected?

Carry on and call Pythian
We can help you mitigate your risk of incidex* and prepare you for the pending EU regulation, General Data Protection Regulation (GDPR) slated to become law by end of 2015 and will mandate that all companies with EU customers follow the new rules wherever they’re based by 2018.

*Incidex: Today’s formula for total cost of ownership isn’t the traditional capex + opex + long-term expenses. Instead, there’s another more important cost component in today’s application landscape that Pythian refers to as “incidex”, or the cost of not investing in technologies that will secure and protect your revenue generating systems. If you redefine TCO as capex plus opex plus incidex, it’s a more holistic approach. And you can use the holistic TCO analysis to justify an investment in security technology.

Categories: DBA Blogs

Oracle ASM Rebalance – Turn it up. To 11?

Wed, 2015-11-11 14:01


If you’ve ever seen or heard of the movie This is Spinal Tap then you have likely heard the phrase Turn it up to 11.

Why bring this up?

When ASM was introduced as a method for configuring storage for Oracle, one of the features was the ability to rebalance the data across all disks when disks were added or replaced.  The value used to control how aggressively Oracle rebalances the disks is the REBALANCE POWER. And yes, the maximum value for rebalancing was 11, as an homage to the movie.

Here is an example of a command to only rebalance a disk group:

 alter diskgroup data rebalance power 11; 

That is rather straightforward, so why blog about it?

The reason is that the maximum value for REBALANCE POWER changed with Oracle, as per the documentation for the ASM_POWER_LIMIT parameter.

From, the maximum value is no longer 11, but 1024.

I’ve asked a number of DBA’s about this, and it seems that knowledge of the rebalance power limit is not really too well known.

Why does it matter?

Imagine that an ASM diskgroup has had disks replaced, and the task took longer than expected.

Now you want to speed up the rebalance of the disk group as much as possible:

 alter diskgroup data rebalance power 11; 

Will that bit of SQL do the job?

On 10g that would be fine. But on an database that would set the POWER limit to 1.07% of the maximum allowed value, having little effect on how aggressive Oracle would be in rebalancing the disks.

The correct SQL in this case would be:

 alter diskgroup data rebalance power 1024; 

The following is a short demonstration of REBALANCE POWER on, and databases.  These examples just confirm the documented maximum values for REBALANCE POWER.


SQL> select version from v$instance;

SQL> alter diskgroup ASM_COOKED_FS rebalance power 12;
alter diskgroup ASM_COOKED_FS rebalance power 12
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup ASM_COOKED_FS rebalance power 11;

Diskgroup altered.


SQL> select version from v$instance;


SQL> alter diskgroup fra rebalance power 1025;
alter diskgroup fra rebalance power 1025
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup fra rebalance power 1024;

Diskgroup altered.


SQL> select version from v$instance;


SQL> alter diskgroup data rebalance power 1025;
alter diskgroup data rebalance power 1025
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup data rebalance power 1024;

Diskgroup altered.


Discover more about our expertise in the world of Oracle.

Categories: DBA Blogs

Oracle Upgrade Failures due to METHOD_OPT and XDBCONFIG

Wed, 2015-11-11 13:51

I recently experienced a problem when upgrading an old Oracle database to that had no matches in a My Oracle Support (MOS) or Google search. The problem presented itself initially when upgrading as the following error was reported by the upgrade script:

ERROR at line 1:
ORA-20001: invalid column name or duplicate columns/column groups/expressions
in method_opt
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4


Initially, the problem was reported in the upgrade log file for the ORACLE_OCM schema which is not critical. However, it later caused the XDB component to become invalid and consequently other components that depend on XDB to also become invalid. The error reported when trying to validate XDB was:

Warning: XDB now invalid, could not find xdbconfig


Even if not upgrading, this error could be encountered when trying to install or re-install the XDB component in an 11g database. XDB is a mandatory component as of Oracle 12c but is optional with 11g and below. Hence, it’s possible to experience this same problem if you’re trying to add the XDB component to an 11g database that didn’t already have it.


“Warning: XDB now invalid, could not find xdbconfig”

Several MOS documents already exist describing the error “Warning: XDB now invalid, could not find xdbconfig”. Those include:

  • Utlrp.sql results to “Warning: XDB Now Invalid, Could Not Find Xdbconfig” (Doc ID 1631290.1)
  • XDB Invalid after Utlrp during Activation of Extended Datatypes (Doc ID 1667689.1)
  • XDB Invalid After utl32k.sql during activation of extended datatypes (Doc ID 1667684.1)

Unfortunately, none of those applied as either the cause or the solution to the problem I encountered. Either going through the XDB installation logs or simply manually running utlrp.sql shows that the xdbconfig is missing due to the “ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt” error.

For example:

SQL> @?/rdbms/admin/utlrp

COMP_TIMESTAMP UTLRP_BGN  2015-11-09 11:36:22

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>   Use the following queries to track recompilation progress:
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
ERROR at line 1:
ORA-20001: invalid column name or duplicate columns/column groups/expressions
in method_opt
ORA-06512: at "SYS.UTL_RECOMP", line 865
ORA-06512: at line 4

COMP_TIMESTAMP UTLRP_END  2015-11-09 11:36:23

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.


DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.


Function created.

PL/SQL procedure successfully completed.

Function dropped.

Warning: XDB now invalid, could not find xdbconfig
ORDIM registered 0 XML schemas.
The following XML schemas are not registered:

PL/SQL procedure successfully completed.



Hence the ORA-20001 error is the true cause of the XDB problem.


“ORA-20001: Invalid column name or duplicate columns/column groups/expressions in method_opt”

Searching My Oracle Support (MOS) for this error leads to the following notes:

  • Gather Table Statistics Fails With ORA-20001 ORA-06512 On “invalid Column Name” (Doc ID 1668579.1).
  • 11i – 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1).
  • Gather Schema Statistics Fails With Error For APPLSYS Schema (Doc ID 1393184.1).
  • Performance Issue Noted in Trading Partner Field of Invoice Workbench (Doc ID 1343489.1).

Unfortunately, those are all related to specific tables from Oracle Applications Technology Stack, Oracle EBS, or Oracle Payables – none of those were applicable in my case. In my case the application was home grown.

Hence, MOS and Google searches returned no relevant results.


The Root Cause & Solution

The root cause of this problem was the METHOD_OPT parameter of DBMS_STATS.

The METHOD_OPT parameter is related to how optimizer statistic histograms are collected for columns. METHOD_OPT is set using DBMS_STATS.SET_PARAM and can be queried through DBMS_STATS.GET_PARAM or directly from the underlying base table SYS.OPTSTAT_HIST_CONTROL$.

For example:


PL/SQL procedure successfully completed.



SQL> select sname, spare4 from SYS.OPTSTAT_HIST_CONTROL$ where sname = 'METHOD_OPT';

SNAME                          SPARE4
------------------------------ ----------------------------------------



The actual root cause of the ORA-20001 error and all of the subsequent failures and invalid components is that in the problematic database, the METHOD_OPT was set to the rarely used and outdated setting of “FOR COLUMNS ID SIZE 1”. From the database that experienced this issue:





The “FOR COLUMNS ID SIZE 1” setting was sometimes used in older versions of Oracle to prevent histogram buckets for being collected for primary keys and for plan stability through statistic changes. However, it should not be used for modern 11g or 12c databases. In fact it’s not even settable through the DBMS_STATS package after Oracle 10g.  Executing against an database will give:

SQL> exec dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1');
BEGIN dbms_stats.set_param('METHOD_OPT','FOR COLUMNS ID SIZE 1'); END;

ERROR at line 1:
ORA-20001: method_opt should follow the syntax "[FOR ALL [INDEXED|HIDDEN]
COLUMNS [size_caluse]]" when gathering statistics on a group of tables
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at "SYS.DBMS_STATS", line 13268
ORA-06512: at "SYS.DBMS_STATS", line 13643
ORA-06512: at "SYS.DBMS_STATS", line 31462
ORA-06512: at line 1


Though it can still be set in by directly updating SYS.OPTSTAT_HIST_CONTROL$, which is definitely NOT recommended.

And of course this setting can be present in an 11g database that was upgraded from an older version such as a 10g release.

Reverting this parameter to “FOR ALL COLUMNS SIZE AUTO” resolved the ORA-20001 error with UTL_RECOMP allowing the XDB component to validate and become VALID in the registry and subsequently all other components that depend on XDB.



If upgrading an older databases to (to remain on a supported version) it is prudent to check the setting of the METHOD_OPT parameter of the DBMS_STATS package. This isn’t mentioned in any of the pre-upgrade documents or checklists and isn’t caught by even the most recent version of Oracle’s Database Pre-Upgrade Utility (MOS Doc ID 884522.1) or the DB Upgrade/Migrate Diagnostic Information (MOS Doc ID 556610.1).

The check and solution are simple and should be incorporated into your own pre-upgrade procedure:




PL/SQL procedure successfully completed.





Discover more about our expertise in the world of Oracle

Categories: DBA Blogs

Dynamic MySQL Credentials with Vault

Wed, 2015-11-11 13:20

Recently I have been looking at the Vault project as a means to manage secrets for applications and end-users. One of the use cases that immediately drew my attention was the ability to create dynamic role-based MySQL credentials.

Why Dynamic MySQL Credentials?

There are a few reasons why dynamic credentials would be beneficial, all of which can be handled by Vault, including:

  • The database environment is too large to manage individual users.
  • A need to authenticate on an external service, such as LDAP or GitHub organization.
  • Provide credentials to external resources, such as auditors or outside consultants that automatically expire.
  • Compliance requirements for strict audit logs for database access.
A High-Level Overview of Vault

Vault is a fairly new project by HashiCorp, the folks behind projects such as Vagrant and Consul. The goal is to decouple the handling of secrets from applications to enforce access control, encryption standards, and create an audit trail.

There are several components to Vault:

  • Authentication such as LDAP, GitHub or custom app-id.
  • Authorization using path-based ACL policies.
  • Encrypted storage backend using one of several options such as Consul, etcd, Zookeeper, S3, or MySQL.
  • Secret backends that define how secrets are stored or generated. Options include MySQL and AWS IAM credentials, among others.
  • Audit logging of token generation and secrets access.

To begin working with a Vault deployment, Vault must be initialized and unsealed. Unsealing Vault is a very important aspect of Vault’s security model, but is beyond the scope of this post.

After Vault is unsealed, users can begin interacting with Vault either by a CLI tool or HTTP API. Users, whether they are human or applications, are authenticated based on tokens. These tokens can be revoked at any time, be given a time-to-live (TTL) or a specific number of uses.

Authentication methods are associated with access control list (ACL) policies to define which secrets the token will have access to.

Certain secret backends can generate actual credentials upon request. This includes the MySQL secret backend, which creates users with specific grants based on their role and passes only the generated user credentials to the requesting token.

Creating Dynamic Credentials with Vault

Let’s generate a read-only MySQL credential using Vault. To follow along with this exercise, you will need to install Docker Toolbox and clone my vault repository. The docker-compose file will look like this:
View the code on Gist.

Next, we will bring up the vault and mysql containers. 
View the code on Gist.

The script will initiate and unseal the vault, then set the environment variable ‘VAULT_TOKEN’ that we will use later. It also creates a ‘vault’ alias that allows us to interact with the Vault CLI within the docker container. The output will look like this:
View the code on Gist.

Now that Vault is unsealed, we can create the MySQL backend. Vault must be provided with the credentials of a MySQL user with GRANT OPTION privilege. For the purpose of our example, we will use the root user.
View the code on Gist.

With the MySQL backend configured, we can finally create our dynamic credentials. The generated credentials will be valid for 10 minutes, then expire.
View the code on Gist.

Final Thoughts

Overall, Vault is a promising new technology to decouple secrets from the application. Perhaps we can use it to begin to move beyond the idea that it’s OK to store credentials in environment variables.

Of course, implementing Vault does add yet another dependency that must be highly available. Care must be taken to deploy Vault in a fault-tolerant manner.

One concern I have with the current workflow of secret handling is that each GET request to /mysql/readonly creates a new user. So a busy environment could thrash the database server with CREATE USER USER commands, which will be cleaned up later with DROP USER commands.

The way I expected it to work is that if the same Vault token requested the same credential endpoint, Vault would return an unexpired credential instead of generating an entirely new user.

To work around this, the user must keep track of the ‘lease-id’ returned from the initial read request and renew the lease before it expires. The user can do this up until the lease_max period.


Discover more about our expertise with MySQL.

Categories: DBA Blogs

Oracle EBS News: Openworld 2015

Tue, 2015-11-10 14:41


Oracle OpenWorld is always an exciting and energizing experience. This year was no different! There are always a big laundry lists of new service offerings and upcoming release information during OpenWorld. This year, the major buzz is Oracle Cloud. Oracle Cloud offerings actually shadowed lot of other imported product updates at OpenWorld.

In the databases area, Oracle announced database 12c Release2 beta program. Now we can have in-memory options enabled in Active Data Guard as well. And Markus Michalewicz session “Introducing the Next Generation of Oracle Real Application Clusters [CON8769]” gave a very good insight into DB 12cR2 RAC new features. Features like Node Weighing during evictions, Domain Service Clusters and Member Clusters.

In the Middleware area, Larry announced weblogic multitenant 12cR2.

Coming to Oracle E-Business Suite, Oracle announced EBS 12.2.5 just few days before OpenWorld. And Almost all OpenWorld EBS sessions were filled with 12.2.5 updates. Cliff Godwin announced new functional updates in 12.2.5 and also confirmed that there will be 12.2.6, 12.2.7 and 12.3 in future. You can get a full list of updates from the Oracle E-Business Suite Learning Stream and this press release. On the technology side, 12.2.5 introduced new Alta UI, which is tablet friendly. 12.2.5 also introduced new tools like adopmon to monitor online patching cycle and adcfgclone dualfs=yes to parallelize patch and run fs configuration during a clone. adop validate option to validate the EBS techstack before even starting online patching. Also now oracle certified Oracle Unified Directory with EBS Release 12.2.5.

Coming to the main buzz of the event Oracle Cloud, there are two things I liked the most.

  1. Oracle Key Vault – Centralized On-Premise Key Manager for the Enterprise through which Client can control their Cloud data.
  2. Oracle Audit Vault – Audit Trails managed by the Customer using an on-premise system, which can help analyze, audit data and detect breaches. I think these two features will set apart Oracle from other Cloud providers.

For the people who are looking for OpenWorld presentation PPTs or PDFs, you can find most of them online.


Discover more about our expertise in the world of Oracle.


Categories: DBA Blogs

Implementing Fuzzy Search in SQL Server – Part 1

Tue, 2015-11-10 14:24


Fuzzy Search in SQL Server is not done very well. This post will cover how to implement Fuzzy Search capabilities using several approaches.


What is it?

Fuzzy Search is the process to locate records that are relevant to a search, even when the search criteria doesn’t match. Fuzzy Searches are used to:

  1. Suggest the correct spelling of a word (“Did you mean this…”).
  2. Find results related to your search term (“You might also like…”).
  3. Finding synonyms for search terms (Search for Dog and also get results for Puppies and Pets).
  4. Probably other things…

What we’re covering here relates to finding results after a search term has been misspelled. Related Results & Synonyms are handled quite a bit differently, and are more like geographic points on a map used to find the closest other points.


What Does it Fix?

As suggested above, Fuzzy Search logic is great for when you or the users don’t know the exact term they’re looking for.

For example, let’s say I want to find the latest James Bond movie, but only vaguely know its name.

An IMDB search for “Specter” brings up the James Bond “Spectre” movie as a suggestion today. If you actually search for the term, you’ll get a wide variety of increasingly weird results (And also the right answer!). I don’t have access to the IMDB code, but I believe they’re using a combination of the Levenshtein Distance, Trigrams, and maybe Double Metaphones. Coincidentally, those are exactly what we’re covering here.

Search on IMDB using Spector

An example search on IMDB


Fuzzy Search in SQL Server

Before implementing Fuzzy Search in SQL Server, I’m going to define what each function does. As you’ll see, they are all complementary to each other and can be used together to return a wide range of results that would be missed with traditional queries or even just one of these functions.

The three functions we’re going to look at are:

  1. Damerau-Levenshtein Distance
  2. Trigrams
  3. Double Metaphones

All of these have been written in T-SQL by very good DBAs. In future posts, I’ll be comparing the T-SQL performance to CLRs written in C# code to hopefully move the string manipulation and comparisons to a more appropriate place.

Damerau-Levenshtein Distance

The Levenshtein Distance is a calculation of how different two strings are, and it’s expressed as the number of steps required to make StringA look like StringB. The steps are counted in terms of Inserts, Updates, and Deletes of individual letters in the two words being compared.

This is good for short strings where not many differences are expected, AKA misspelled words.

A simple example is the word Car to Date. The Levenshtein Distance here is 3, and we get there by:

Step 1: UPDATE ‘C’ to ‘D’
Step 2: UPDATE ‘r’ to ‘t’
Step 3: APPEND ‘e’

So, in the IMDB example, my search for “Specter” has a Levenshtein Distance of 2 from “Spectre”. Again, we get there by:

STEP 1: UPDATE ‘r’ to ‘e’
STEP 2: UPDATE ‘e’ to ‘r’

We’re going to be looking at the Damerau-Levenshtein Distance. This is built on the Levenshtein Distance, but also accounts for transpositions of letters right next to each other. That would have returned a value of 1 with this logic:
STEP 1: Flip ‘r’ and ‘e’


Trigrams are used to find matching sets of words or characters in words or phrases. As the name implies, each Trigram is a set of 3 characters or words, and you simply count how many trigrams in each string match the other string’s trigrams to get a number.

These are great for comparing phrases.

An easy example is to compare a search for “SQL Server” to “SQL Sever”:

STEP 1: Break ‘SQL Server’ up into trigrams
‘SQL’, ‘QL ‘, ‘L S’, ‘ Se’, ‘Ser’, ‘erv’, ‘rve’, ‘ver’
STEP 2: Break ‘SQL Sever’ up into trigrams
‘SQL’, ‘QL ‘, ‘L S’, ‘ Se’, ‘Sev’, ‘eve’, ‘ver’
STEP 3: Count the number of trigrams that match: 5

A matching set of 5 trigrams might mean these are close enough for the application to suggest as an alternative.

Another example is comparing the phrase “Oracle” to “Relational Database Management System”.

STEP 1: Break ‘Oracle’ up into trigrams
‘Ora’, ‘rac’, ‘acl’, ‘cle’
STEP 2: Break ‘Relational Database Management System’ into trigrams
‘Rel’, ‘ela’, ‘lat’, ‘ati’, ‘ona’, ‘nal’, ‘al_’, ‘l_D’, …, ‘tem’
STEP 3: Count the number of trigrams that match between them: 0

As you can see, Oracle isn’t very close to being an RDBMS at all.

Finally, in our IMDB example, you can see that the movie Unexpected was returned. Why? I don’t actually know, but I believe it’s because there are several matching trigrams between “Specter” and “Unexpected” which pushed it into the possible suggestions.

Search on IMDB using Spector

I don’t like scrolling up.

Double Metaphone

Double Metaphones are the updated version of the SOUNDEX() function. Updated as in soundex was first patented in 1918, and double metaphones are from the 1990’s. They both work the same by breaking up consonants into what they sound like and comparing them to the closest matching values; however, soundex assumes each consonant has the same pronunciation, while metaphones allow for multiple pronunciations of the same word.

Double Metaphones are geared towards names

The first example is one where SOUNDEX and a Double Metaphone work identically. The name “Bruce” and it’s common(?) misspelling “Broos”.

STEP 1: SELECT SOUNDEX(‘Broos’) and we get B620
STEP 2: SELECT SOUNDEX(‘Bruce’) and we get B620
STEP 3: Use an online calculator to get the Metaphone values for Broos and Bruce
Both return ‘PRS’ and ‘PRS’

The benefit of the Double Metaphone here is that, because the results for both words are the same, you can have a higher level of confidence that this is a misspelling.

Another example is “Smith” and “Schmidt”.

STEP 1: SELECT SOUNDEX(‘smith’) and we get S530
STEP 2: SELECT SOUNDEX(‘schmidt’) and we get S530
STEP 3: Use an online calculator to get the Metaphone values for Smith and Schmidt
The most common pronunciation is first:
Smith yields ‘SM0’ and ‘XMT’
Schmidt yields ‘XMT’ and ‘SMT’

Using the SOUNDEX() function, you might return this “Smith” as a misspelling of “Schmidt”, which is unlikely. On the other hand, using a double metaphone function, you would know that while these two words are occasionally pronounced identically they more frequently not pronounced the same, and you could either discard the result or push it to the bottom of your suggestions.

If you do pronounce any of these words the same, here’s a website to compare the expected English pronunciations.

In my next post, I’ll implement each of these as CLRs in C# code. I’ll also point out some nice T-SQL implementations I’ve found and compare performance.


Discover more about our expertise in SQL Server

Categories: DBA Blogs