The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “oracle optimizer”

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database.

The usual reason is that it “slows down” load performance.

No surprise there. Been hearing that for years.

And I say one of two things:

1. So what! I need my data to be correct and to come out fast too!

or

2. Show me! How slow is it really?

Keep in mind that while getting the data in quickly is important, so is getting the data out.

Who would you rather have complain – the ETL programmer or the business user trying to run a report?

Yes, it has to be a balance, but you should not immediately dismiss including FKs in your warehouse without considering the options and benefits of those options.

So here are my three main reasons why you should include FK constraints in your Oracle data warehouse database:

  1. The Oracle optimizer uses the constraints to make better decisions on join paths.
  2. Your Data Modeling and BI tools can read the FKs from the data dictionary to create correct joins in the meta data of the tool (SDDM, Erwin, OBIEE, Cognos, Bus Objects can all do this).
  3. It is a good QA check on your ETL. (Yeah, I know… the ETL code is perfect and checks all that stuff, bla, bla, bla)

Now of course there are compromise options. The three main ones are I know:

  1. Drop the constraints at the start of the load then add them back in after the load completes. If any fail to build, that tells you immediately where you may have some data quality problems or your model is wrong (or something else changed).
  2. Build all the constraints as DISABLE NOVALIDATE. This puts them in the database for the BI tools and data modeling tools to see and capture but, since they are not enforced, they put minimal overhead on the load process. And, so I am told by those that know, even a disabled constraint helps the optimizer make a smarter choice on the join path.
  3. (really 2a) Best of both – disable the constraints, load your data, then re-enable the constraints. You get optimization and quality checks.

So NOW what is your reason for not using FKs in your data warehouse?

Happy Modeling!

Kent

Advertisements

KScope13 Day Four: Agile, Big Data, and a Very Special Event

Mid-week. Hump day. The day of the BIG event for KScope13.

Lots of anticipation for the annual Special Event… (which I will write about in a minute or so)

Morning Chi Gung as usual, but with 24 people showing up. Biggest group this week. We even have a few locals joining us now. Everyone seems to be enjoying these sessions.

KScope attendees starting the day with Morning Chi Gung on the plaza in front of Harahs casino.

KScope attendees starting the day with Morning Chi Gung on the plaza in front of Harrahs casino.

In fact, the Chi Gung class at KScope may be the original cross over session! Attendees are from across the spectrum from DBAs, to developers, to Hyperion/EPM folks to spouses of attendees.

There is something for everyone in Morning Chi Gung.

Kanban and Scrum

Everyone wants to be “agile” these days. Stew Stryker of Dartmouth University came to KScope to share with us his experience in applying first Kanban then Scrum to the software development life cycle in his IT department.

Stew Stryker, from Dartmouth College, discusses how his team has use Kanban, and now SCRUM, to improve their software development process.

Stew Stryker, from Dartmouth College, discusses how his team has used Kanban, and now SCRUM, to improve their software development process.

One of Stew’s insights was that to effectively implement a change in methodology like this and get adoption it is first necessary for the powers-that-be to recognize the current approach (usually water fall) is failing.

If you do not know you have a problem, there is no motivation to fix it, right?

A key recommendation he had was to get a consultant that knows and has implemented Kanban for database projects to come in and work with you. Don’t try to do it by just reading articles and books or going to training. There are too many nuances and organizational dynamics to account for.

A simple comparison of aspects of a traditional waterfall methodology compared to the Kanban approach.

A simple comparison of aspects of a traditional waterfall methodology compared to the Kanban approach.

Another key to succes was to prevent context switching – that is keep everyone focused on the task at hand for the duration of the interval (or sprint). He did a great little exercise with us that really showed how task switching costs a lot of time. In some case up to 10 times longer.

It was great to hear real world experiences that we could all take back to our offices and implement and discuss. His team has experienced some great success but with lots of lessons learned, which he shared.

They have now switch to SCRUM with even more success.

Hands On Lab #2

I attended my second lab of the conference to learn from Maria Colgan (@SQLMaria) on how to prevent sub-optimal plans on SQL Statements.

Oracle Senior Product Manager Maria Colgan walks us through how to analyze and and tune some queries.

Oracle Senior Product Manager Maria Colgan walks us through how to analyze and tune some queries.

It was a great session using the Oracle Demo Days virtual box image again (from OTN). Maria walked us through several queries with Explain Plans that did not seem quite right and showed us how to diagnose and fix the potential problems.

It was a little tough for those of us who have not used Linux/Unix or command line in a few years but I did learn a lot and should be able to apply that knowledge when we have poor performing queries at my clients. Worse case, I can always start up the vm again and run through the lab.

Inside the Oracle 12c Opimizer

Another killer session from Maria showing us enhancements and new features to the query optimizer in the recently released Oracle 12c.

Overview of how adaptive query optimization works on Oracle 12c

Overview of how adaptive query optimization works on Oracle 12c

How the new Adaptive Execution Plans work in Oracle 12c

How the new Adaptive Execution Plans work in Oracle 12c

The key phrase for 12c “self-healing” and “adaptive”. Remember when there were just 17 rules for the optimizer that we could control with the syntax of the query?

Long ago.

I guess this is better, but there are still rules to know to make the optimizer work well.

And Maria definitely knows them!

Big Data

These days every tech event has to talk about big data. KScope13 is no different.

Alex Shlepakov, from Accenture’s Oracle BI practice, gave a nice talk about integrating Hadoop with OBIEE using ODI.

He did a really nice job explaining all the concepts and moving parts and how Oracle addressed these things.

Alex presented about doing big data analysis using Oracle BI tools.

Alex presented about doing big data analysis using Oracle BI tools.

All the Oracle products that support the analysis of data in a Hadoop environment

All the Oracle products that support the analysis of data in a Hadoop environment

Pretty sure these products cost lots of money too! But if you want to get value out of your big data, you may have to spend big money for the tools to help (unless you have a lot of programers with really big brains).

My main take away from this session is that the tools to support Hadoop and big data analysis are evolving to make it easier for most programmers to get to the data without having to be Map Reduce programmers.

But it will still be pretty hard, so you better have a good business case for digging into it.

Special Event (aka the big party)

As in past years, ODTUG really did it up right. This was truly a special event to remember – we went to Mardi Gras World!

The annual Special Event was held at Mardi Gras World where we got to see some of the big floats from the famous parade.

The annual Special Event was held at Mardi Gras World where we got to see some of the big floats from the famous parade.

What a treat to see some of the big floats used in the famous parade. I even found a full scale replica of the Bat Boat tucked away in the back. (There was a huge Batman statue as well)

The Oracle Data Warrior finds Batman's boat!

The Oracle Data Warrior finds Batman’s boat!

The tour of the Mardi Gras warehouse included plenty of bead throwing from the floats by the board of directors and the various KScope vendors. This was followed by a nice evening of drinks and a buffet dinner with lots of great food (even some gluten free and vegetarian options).   There was plenty of dancing to great cover band called The Mixed Nuts.

We finished the evening with a spectacular fire works display (which seems to becoming a standard at this event).

We had a spectacular fireworks display (shot off a barge) at the annual KScope Big Event

We had a spectacular fireworks display (shot off a barge) at the annual KScope Big Event

Over too soon, it was last call, last dance, then back to the buses and a short ride to the hotel.

And then there were the after parties….

Stay tuned for my notes on our final day in New Orleans.

Ciao!

Kent

Welcome to the Biggest KScope Conference Ever!

Day breaks over the Mighty Mississippi

Day breaks over the Mighty Mississippi

Day one at ODTUG’s KScope13 was awesome as always with a great set of symposiums and networking events.

At the speaker meeting we learned that there are 1,400 registered attendees, 30 countries represented and over 50 exhibitors making this the largest event and largest exhibit hall in the history of the Oracle Development Tools User Group (ODTUG).

So we must be doing something right to attract such a large crowd.

Part of what we do right is getting the top Oracle developers, consultants, and Oracle ACEs in the world to present. And we get stellar participation from the Product Management and Development teams at Oracle Corporation.

Even though it was Sunday, there was a tremendous turnout for our free Sunday Symposiums. In the Symposiums we had rooms dedicated to specific topics so attendees could stay in one place all day and get a series of related talks.

After conducting my now annual Morning Chi Gung class (had nine attendees!) I spent the day in the DB & Developer’s Toolbox Symposium. It had quite a line up.

First I attended Jeff Smith’s session  on SQL Developer 4.0 which will come out later this summer.

SQL Developer Product Manager, Jeff Smith of Oracle, introduces new features in the upcoming 4.0 release

SQL Developer Product Manager, Jeff Smith of Oracle, introduces new features in the upcoming 4.0 release

Got lots of great tips and trick which I am sure Jeff will be blogging about at ThatJeffSmith.com in the near future.

Next was Oracle Technologist Tom Kyte who spoke about many new features and enhancements to SQL And PL/SQL in the upcoming Oracle 12c release of the database.

Tom Kyte (of AskTom.com fame) introduces attendees to new SQL and PL/SQL features in Oracle 12c

Tom Kyte (of AskTom.com fame) introduces attendees to new SQL and PL/SQL features in Oracle 12c

And last for my day of learning was Maria Colgan discussing changes to the Oracle Optimizer in 12c.

Oracle Product Manager, Maria Colgan, discusses the Evolution of the Oracle Optimizer: Rules based to 12c

Oracle Product Manager, Maria Colgan, discusses the Evolution of the Oracle Optimizer: Rules based to 12c

As always, Maria provided so much information my head was ready to explode from information overload. And later in the week she has a second session on this topic with even more new features to discuss. If every Oracle DBA could attend one of her sessions, all of our databases would run better!

After the sessions it was time for formal and informal networking. There was the speakers meeting and the official welcome reception in the exhibit hall. I got to attend an official Oracle ACE dinner cruise on a steam powered paddle wheel river boat complete with live jazz band. Very much in the spirit of New Orleans.

Stay tuned for more reports as the week progresses.

Kent

The Oracle Data Warrior

P.S. I got to do a little touring as well and visited the oldest Catholic Cathedral in the United States. It is quite a beautiful church inside and out.

The beautiful St Louis Cathedral near Jackson Square  in New Orleans

The beautiful St Louis Cathedral near Jackson Square in New Orleans

Post Navigation

%d bloggers like this: