Exadata SQL Optimization in the cloud or not.

“We just moved our Oracle system to Exadata, I don’t need to optimize my SQL any more, right?”

 

Chances are that a lot of your SQL will run amazingly faster in Exadata then it did on a non-Exadata machine.  But to think that optimization is no longer needed is a bit naïve at best.  It is still very possible to have SQL that runs much slower than it should on an Exadata machine. 

 

Here are a two big things that I’ve seen that can cause a query to run in a less than optimal way on an Exadata machine.  

 

1.  Not getting a Smart Scan (offloading).   

 

The number one way that Exadata speeds up a query is the ability to do this thing called Smart Scan.  This is where some (maybe all) of the predicates are “offloaded” to the storage cells to scan the blocks and only return the blocks that satisfy these predicates to the Buffer Cache in the SGA.  

 

Many predicates can be offloaded, but not all.  Generally, the simpler the predicate the more likely it will be offloaded.   You can get a list of what can be offloaded with this query: 

 

SELECT DISTINCT name, datatype, offloadable

FROM v$sqlfn_metadata

WHERE offloadable = 'YES'

ORDER BY name;

 

In a 21c database there are 394 operations that can be offloaded.  That puts the odds in your favor but there could be things you do that aren’t in this list and that will slow things down, a lot.  

 

An example of something that isn’t offloadable is the function calls used to support case insensitive searching when you set the NLS parameters as follows:

 

NLS_SORT = BINARY_CI   (accent-insensitive and case-insensitive sort)

NLS_COMP = LINGUISTIC (sorting and comparison are based on the linguistic rule specified by 

NLS_SORT)

 

Here is a simple query to show what happens (see the code at the end of this if you’d like to run it yourself).  The table is the set of owners form the DBA_OBJECTS view.  The query is run once with the default setting for the two parameters and then run with them set for case insensitive searching as noted above.  Of interest is the predicate information.  I’m using DBMS_XPLAN.DISPLAY_CURSOR to show the plan. 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric';

 

With the default settings this returns zero (0) rows since the names are stored in upper case.  The predicate information in the plan looks like this:

 

storage("OWNER"='ric')

filter("OWNER"='ric')

 

Notice the first one is called STORAGE.  This is the one that can be pushed out to the storage cells for the Smart Scan (offloaded).   Depending on how you get the plan this may just say “Access” and not Storage.  If you see the same predicate with Access and Filter on a same step that is showing one that can be offloaded, and the other is used when it isn’t offloaded.  (More about this is a moment.)  

 

With the NLS parameters set for case insensitive searching this is the predicate information:  

 

filter(NLSSORT("OWNER",'nls_sort=''BINARY_CI''')=HEXTORAW('72696300'))

 

This returned 44 for the count, which is correct in my simple test.  The problem is that this predicate cannot be offloaded. The access step in the plan is the same “TABLE ACCESS STORAGE FULL” but it is being applied after the blocks are returned to the buffer cache in the SGA.  As such, this isn’t taking advantage of the Exadata machine. For this test query it hardly mattered as the table only has about 55,000 rows.  But for tables of significate size, not doing offloading will be a major problem. 

 

How to solve this simple example?  Change the predicate to something like this:

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric';

 

The predicates used are below, and the first one can be offloaded (it likely wasn’t in this case since all the blocks of the table were in the buffer cache).  This returned a count of 44:

 

storage(LOWER("OWNER")='ric')

filter(LOWER("OWNER")='ric')

 

When else might something not get offloaded?  A Smart Scan is a run time decision.  You may have done everything correctly to get a Smart Scan, but it doesn’t happen sometimes.  This is because if the storage cells get over loaded, they will stop doing Smart Scans.   From a code point of view there isn’t really anything you can do about this.  

 

At the DBA level they could spread out the data, mostly by getting more storage cells and redistribute the data.  This can avoid contention at the storage cell level.  Also, it can be a timing issue, don’t try to run everything at the same time.  Even just a few minutes (or even seconds) between jobs might make all the difference.  

 

2.  Using Indexes.

 

This one is a bit tricky.  Indexes are still useful in an Exadata environment, and necessary for things like primary keys.  To compound be this problem, you may be running a mixed work load of transactional based queries (that indexes still will be helpful) and data warehouse queries (that indexes don’t work well for).  Knowing which indexes to keep and which to drop is not exactly clear cut. 

 

You will likely need fewer indexes as compared to non-Exadata.   A really big problem with indexes is that they tend to drive NESTED LOOPS joins (potentially large “stacks” of them) and those can be rather inefficient in Exadata with huge tables.   HASH and SORT MERGE joins tend to be better in Exadata.  Both of these tend to favor full table scans over index scans.  A full table scan can benefit from the Smart scan.  Not so much with index scans.   

 

Other then the INDEX FAST FULL scan (which is works much like a full table scan), offloading doesn’t happen with index scans.  Which as just explained, is the real advantage of using Exadata.  

 

Indexes can still be useful when you are getting a small amount of data from a table. But even here it can be tricky.  Testing is the key.  Here is where you may need to do some SQL tricks to get the optimizer to not use an index, two of my favorites are:

 

COALESCE (NVL doesn’t always work)

In Line Views (ILV), need the NO_MERGE hint

 

Using COALESCE the code would look something like this:

 

COALESCE(my_tab.id,-99999) = other_tab.id

 

The reason that NVL doesn’t always work is that the optimizer knows about things like NOT NULL constraints.  For example, if the column you’re trying not to use an index on is a primary key, the optimizer knows it can’t be null so it will remove the NVL function.  But it wouldn’t remove a COALESCE because it could have multiple options. 

 

To use an ILV it would look something like this:

 

JOIN (select /*+ no_merge */ id, name, status from my_tab) my_tab ON my_tab.id = other_tab.id

 

The NO_MERGE is needed because without it, the optimizer will merge it back into the query and it will be just a normal join, and use the index you’re trying to not use.  With an ILV there are no indexes on any column so it has to do a full scan, which is exactly the point of doing this.  It’s really a good idea to only include the columns you use in the query in the ILV.  You want to keep the ILV small, both in width (columns) and depth (rows) if possible.

 

Of course, other approaches would be to make indexes invisible or use hints to force full table scans.  And there are plenty of other “tricks” to use to make an index not useable for query, like add zero to a number or concatenate a NULL to a string.  Whatever technique you do use, I highly recommend that you put in a comment to explain why you put in something odd in your code.  Use the comment to help yourself know why you did something.  It might be months or years later when you look at this code again, so help yourself and add a comment to anything that is non-standard. 

 

Below is the code I used for the example on the case insensitive searching with the NLS parameters.  If you run it on your system, you’ll like want to change the owner.  I suspect you don’t have a user of RIC that owns any objects in your database. 

 

create table case_test 

as (select owner from dba_objects);

set LINESIZE 200

set PAGESIZE 2000

show parameter NLS_SORT

show parameter NLS_COMP

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session set NLS_SORT = BINARY_CI;

alter session set NLS_COMP = LINGUISTIC;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where owner = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

 

alter session reset NLS_SORT;

alter session set NLS_COMP = BINARY;

show parameter NLS_SORT

show parameter NLS_COMP

 

 

select /*+ gather_plan_statistics */ count(*) from case_test where lower(owner) = 'ric'; 

 

COLUMN PREV_SQL_ID NEW_VALUE PSQLID

COLUMN PREV_CHILD_NUMBER NEW_VALUE PCHILDNO

select PREV_SQL_ID,PREV_CHILD_NUMBER  from v$session WHERE audsid = userenv('sessionid')

/

SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

('&psqlid','&PCHILDNO','TYPICAL ALLSTATS LAST'))

/

SQL Optimization it’s not always about time in the cloud or not

Most often when presented with a Query that needs to be optimized it’s about how long it takes to run.  Something like, “The query is running for over an hour, can we make it run faster?”   But what about a query that runs quickly each time it runs but runs hundreds (or thousands) of times a minute?   Sure, maybe it runs in a second or less, but the cumulative work caused by the query may add up to something that is a problem.  This is commonly referred to as a scaleability issue. 

These types of queries show the issue more with their LIOs (Logical IOs) rather than time.  Here is an example query from many years ago to illustrate the point.   A query much like this was run at a University and on a per run bases it was running in acceptable time.  However, it was showing up in the “Top SQL Report” because it was run very often.  

When it runs the execution plan with stats looks like this:

This was original run way back in version 8 or 9 as I recall, and this version of the code it running on a 21c Autonomous Database, notice the “STORAGE FULL” option for the full table scan.  Even with this way cool stuff, this isn’t really optimal. 

There isn’t a set of steps that is really screaming out “I’m the problem!” in this plan.  None of the operations appear to be taking a lot of time or recourses (like LIOs). 

When looking at a plan like this, one of the first things to do is to figure out “Who’s who in the Zoo?”  Look at the output and look at the tables in the query, do we need them all? 

The output looks like this:

Looking at the tables we have 

– CLASS: Class information

– STUDENTS: Student information 

– GRADES: Grades the students received for a class

– STUDENTCLASS: Intersection table for the Many-to-Many relationship between students and class 

Seems clear that the first 3 tables are needed for the output, but what about the STUDENTCLASS table?  It would be necessary if this report was reporting about more than one class, but it isn’t.   The report is only on one class (English 101 in this instance).  

Looking into the tables we would find that the GRADES table has both the STUDENT_ID and the CLASS_ID columns so we can stitch the tables together with GRADES and we don’t need STDENTCLASS at all. 

Also, there is the access to the CLASS table, from the plan we see that the optimizer chose a CARTESIAN join.  Why?  It’s not because there is a missing join, it’s because it thinks it’s only going to get one row from the table, and it does.  Which is odd since it’s doing a full table scan to get the one row.   How could it know that it was only going to get one row? 

The statistics on that table.  Notice that the table has 10 rows and there are 10 distinct values for the class description column. 

OK so that means the CARTESIAN join isn’t a “problem” in the sense that it’s causing a lot of rows to be unnecessarily returned.  But isn’t there a better way to get one row from the table?  Sure there is, use the primary key, the CLASS_ID column. 

The class is being picked from a list of values so we can change the code to send in the CLASS_ID rather than the CLASS_DESC to the query.  This will use the already existing index on the CLASS_ID column and since it’s a unique index, this is a much better way to find the one row.  Always try to use things that already exist before creating something new. 

Now the rewritten query would look something like this (I kept in some of the old stuff so you can easily see the changes.) 

The new execution plan with stats looks like this:

The key here is the LIOs, it was cut a little bit more then 50%.  Yes, the time went down as well.  However, it’s not even noticeable, 411 micro-seconds to 360 micro-seconds cannot be perceived by a human. 

What did happen is by cutting the LIOs in half (22 down to 10) over all contention went down and throughput was improved in a noticeable way.  In the actual production environment, the tables were much larger than this test case.  The basic improvement was the same cutting the LIOs by over 50%.  With these changes, this query dropped off the “Top SQL Report” for the University.

Getting WITH it in the cloud or not

The WITH clause in Oracle is used to create CTEs, Common Table Expressions also called Sub-Query Factors.  Oracle as a company hasn’t promoted these nearly enough from my point of view, it’s been available since 9.2.  Which is quite a while ago and still many folks are unaware of it.  This little post is to give CTEs a of bit nudge into the light. 

 

So, what is this “CTE” thing?  It’s easiest to think of it as a temporary view that is useable for the duration of the query.  Once the query is finished, they disappear.  Even the syntax is like a view definition:

 

For a view:  CREATE VIEW my_favorite_view AS (select …

 

For a CTE: WITH my_favorite_cte as (select …

 

Generally, I recommend using the MATERLIZE hint for CTEs:

 

WITH my_favorite_cte AS (select /*+ MATERIALIZE */ … 

This hint forces the optimizer to not merge the CTE back into the main query.  Typically, you just did all this work to get it out of there and you don’t want it going back in.  Of course, testing is a great idea, test with and without the hint to be sure you are getting what you want. 

 

Once the CTE is created, use it like you would any table in your query.  You can select from it or join it into a query just as you would any table.  You can even select from an earlier defined CTE in a later one, but you can’t refer to one that hasn’t been defined yet.  No “forward referencing” here. 

 

You create these because they can be very powerful for query optimization. Amazingly powerful.  Here are some reasons to use them. 

 

The most notable cases are when a sub-query within a query is used many times.  Recently I posted about the use with correlated sub-queries in the select list.  Read the post here.  This can be very dramatic in reducing the time and resources used by a query.  

 

Another common case is a subquery that reused many times but  is “slightly” different each time.  For example, a join of a set of tables and the WHERE clause limits it each time on the same set of columns, just different values.  A CTE that gets the super set of the rows used throughout the query can be made, then select from that each time just getting the rows needed for a particular part of the query.  

 

The savings is that the query getting the super set runs only one time, and that set is stored in memory.  Then as it’s joined into each part, only get from it the rows needed.  This is especially good when the set of rows in the super set is much smaller than the total size of all the rows in the joined set of tables.   For example, there are 5 tables in the subquery, total rows of hundreds of millions, but the super set is only tens of thousands.   There is no set number of rows or ratio for this, test to find what works and what doesn’t. 

 

 

Queries using the UNION operator commonly have subqueries that are the same in each part.  Here is another great use of CTEs.  Take the common query and make it a CTE, now just refer to it in each of the UNION sections.

 

You might even use them just to make the query easier to read.  Breaking up the query in to “steps” with the CTEs can make debugging and modification easier later.   This is where maybe the MATERIALIZE hint isn’t needed.  The code might run just fine after the Optimizer smashes it all back together into one query.   

Some personal recommendation on using them.

 

Please give them intelligent names.  Calling them T1, T2 and so on is very painful for debugging.  To further help in reading the query, name them with _CTE as a suffix.  This makes it easier to see what are tables and what are CTEs in large statements.  

 

These next two are meant to keep memory use down.   Each CTE when MATERIALIZED, which more often is what you want, becomes a table like structure in your PGA.  Some guidelines to consider when creating CTEs: 

·      Keep the number of them down.  If you’re creating more than about 6-8 CTEs make sure they really do the trick.  

 

·      Also try to have CTEs that have a “few” number of rows in them.  About a million or less is great.

 

There isn’t really a hard limit of the number of CTEs and the number of rows in one.   Maybe there is a hard limit on the number of CTEs but it you find out what it is, you likely have other problems already.  The point with keeping the number and size down is to conserve memory.  

 

On the other hand, if you have 16 CTEs and the query goes from hours to seconds, then it’s memory well spent.   Same for size, a 500 million row CTE that cuts the run time in a like manner is worth the memory as well.  Over all you might end up uses less memory then when the query runs for hours when exceeding my guidelines purposed here.  Test, test and test again. 

 

It’s time to get WITH it and use CTEs in your queries. 

Tracing in the cloud or not

 

Here is a link to a presentation I did about Oracle Trace events 10046 and 10053, on December 21st.  Enjoy!

Click here or copy paste the link below. 

https://www.youtube.com/watch?v=5x8pnMaepxM&feature=youtu.be

And have some pie.  

Correlated Sub Queries in the cloud or not

Bottom line up front:  Correlated Sub Queries (or sub-selects) are really bad for performance.  

 

You’re writing a query, and in the result set you need the MAX(ORDER_DATE) based on the customer information in each row being returned.  

 

Ah easy-peeze!  I’ll write a sub-select in the select list to do something like this:

 

custord.customer_number,

(select max(order_date) from customer_orders custord2 

where custord2.customer_id = custord.customer_number) LAST_ORDER_DATE,

…

 

Great! It works and life is good.  No need for a nasty “group by” thing.  Yea, I’m the man!

 

Or am I?  How often is that sub-select going to run?  Once for every row returned by the main query.  That could be easily thousands or millions depending on what you are doing.  This one is likely pretty harmless, especially if it’s the only one and it can do a unique look up on an index this one you might not even notice it’s a problem, as long as the data set is relatively small. But it wouldn’t scale well.  

 

How about a 4- or 5-way table join for the sub-selects, or more?  I recently had one that was a 12-way table join (Turned out that at least 4 for the tables weren’t needed when we look into it, but still an 8-way join is a lot to do in a sub-select like this.) 

 

Below is part of a SQL monitor plan of a query that had 16 correlated sub-queries in the select list that I recently worked on for a client.  Each one ran rather fast each time, but each one was running 9,794 times and the total run time was over 10 minutes.  Which to bring back only 9,794 rows, that seemed like a lot.  Notice that the number of rows returned and the times each one ran is the same.  You can see this in the EXECITION column compared to the actual rows returned.  Notice that the first one didn’t return a row for each input row.  This was the nature of that sub-query, there was a pretty good chance it wouldn’t return a row each time.  

 

This screen shot only shows a few of them.  Most were a 3 or 4-way table join.  I’ve hidden the full plan for each sub-query, the plan it used was fine.  It was the excessive runs that was the issue. 

 

 A semi-side point, when looking at this output below notice the estimated rows is 1 (one) for each of the sub-queries.  That estimate is correct.  For each run it returned at most 1 row.  The actual rows returned is higher because that is the total for all runs.  The estimate is a “per run” estimate, and is correct.  This is an excellent example of why actual run time stats is necessary for performance optimization.  You might look at the explain plan and think that the one row is just fine.  In reality that is very misleading.  

So, what to do?  I rewrote the query to have a CTE (Common Table Expression, using the WITH clause, also called a Sub-Query Factor) for each sub-query.  I was hoping to be able to combine some to reduce the number of them but that wasn’t possible from what I could see of the queries and the data.  Maybe some could be but it wasn’t obvious from the code.

 

Now I joined each CTE into the main query just like any other table.  Most of the sub-queries were joined on 2 columns and I did join them all in as LEFT OUTER JOINS because the first one I knew for sure wouldn’t return a row for each row in the main query to be safe I did that with all of them.  I was running out of time on this gig so I didn’t get to test with INNER JOINs, but I did let them know that they would likely be able to convert most of the joins to INNER.  

 

This query was running in 33 seconds after this change.  From over 10 minutes to 33 seconds, yea I call that a win.  And with this change the time to run is going to very slowly go up.  For the originally it will go up sharply as the number of rows returned by the main query go up.  In short, the new code is scalable, the old code was not. 

 

Key point I used the MATERILIZE hint on all the CTEs, each CTE was only referenced once in the query so I didn’t want it to be merged/unnested into the main query.  I find that the MATERILZE hint should be used nearly all the time when creating CTEs.  There have been times that I didn’t need it because the optimizer materialized it anyway (typically when you use it more then once).  And a few times when merging in to the main was a good idea.  Test both way when you are working with CTEs. 

What has this got to do with a fire in my fireplace?  Maybe it's you're "burning CPU LIOs and time" when you do these correlated sub-queries.  Or maybe it's just that I like the picture.  

 

Defaults in the cloud or not

 Use the defaults for nearly all the parameters.  

 

I was working on a gig recently and they had the rather interesting situation where queries were running faster in production then in the test environment.  This had started happening rather recently so the question was, why?  

 

Upon investigation, the key thing was the stats were different in the two systems.  I ran 10053 (optimizer trace) events on a sample query on both systems and it was clear that the differences in stats made the two plans different.  The join orders were different and use of indexes, etc.  The stats weren’t a lot different between the two system, but (generally) the tables in TEST were slightly bigger than in PROD. 

 

This was because they were testing new stuff in test.  It certainly makes sense to do this of course, but the issue was since the two systems were different, the plans could be (and were) different.  And they were different enough that the test query I was provided ran in 6 seconds on PROD and 15+ minutes on TEST.  That’s a pretty huge difference.  

 

My first stab at changing the query was to rewrite with a couple of CTEs (Common Table Expressions use the WITH clause).   I isolated two subqueries that were hierarchal with the good old START WITH/CONNECT BY PRIOR syntax.  I noted that these were being evaluated in different parts of the plan between the two so I thought maybe isolating these as CTEs would help.   And it did.  The plan still was a little different between the systems but now both were running in about 6 seconds.  Win!  

 

But that wasn’t really the root problem. I kept digging around to see what else could be a cause.  Since telling them to rewrite all their code with the “right” CTEs might be difficult at best.  And this is where something in the 10053 trace came in real handy.  

 

Near the top of the 10053-trace file is a section on the parameter used by the optimizer.  This section alone can be a very insightful on what is going on.   The sections start with this:

 

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

PARAMETERS USED BY THE OPTIMIZER

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

 

And has 2 parts one starting with:

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

  PARAMETERS WITH ALTERED VALUES

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

 

The other with:

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

  PARAMETERS WITH DEFAULT VALUES

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

 

As the names suggest, they list parameters that have either changed values or are using defaults.  The list of parameters in the ALTERED VALUES should be quite short, the fewer the better.   They didn’t have a lot, which was very good.  But they did have two that really should be defaults today on both PROD and TEST:

 

optimizer_index_cost_adj

optimizer_index_caching             

 

These were common to change back in version 10 when the default cost model went from IO to CPU.  In 9 the default cost model was IO and in 10 the default became CPU which is what is used today.  Back then many shops had issues when first upgrading to 10 that indexes were not getting used appropriately.   Changing these parameters to non-defaults helped.  

 

But that time is over and the CPU model has been adjusted so these parameters should be the defaults.  The defaults are 100 for the cost adjust parameter and 0 for caching.  This means cost the plan as is, don’t increases the cost (a setting over 100) and don’t discount the cost (a setting under 100).  And for the caching parameter it means don’t assume any of the index is cached (in the buffer cache). 

 

I tested the sample query with following hints.  NOTE: Using the OPT_PARAM hit is great for testing; it should not be used for production code:

 

/*+ OPT_PARAM('optimizer_index_cost_adj' '100') OPT_PARAM('optimizer_index_caching' '0')   */

 

And running the original code with these hints on PROD and TEST the query ran in about 6 seconds on each.   Better Win!  The reason this is better is that now it appears that without changing/rewriting the code they can get the performance they expect.  More testing will need to be done by the client to verify this, but the testing that was done while I was on the gig looked quite good. 

 

But why did this work?  I believe that the differences in the stats was just enough to get exaggerated by the non-default parameters to make for different plans that caused poor performance.  The plans were still very slightly different in the two systems, but not enough to cause a performance issue.   

 

The key to this story is, use the defaults Luke, (substitute your name in for Luke).  Oracle does all of its internal testing with default settings for the parameters.  Once you venture into the land of using non-defaults you are in the space of untested.  Clearly to overcome a bug or a particular situation a non-default can be needed.  Once that situation or bug has be fixed, get back to the defaults. 

Cardinality in the cloud or not

I’ve been teaching and doing SQL optimization for many years now. Given a statement that is 5 lines or 500 lines long, the reason an execution plan is not optimal really comes down to the same thing every time. 

The cardinality calculation is off. 

The key is that it should be in the same magnitude as the actual rows returned. Not the exact same value, that’s cool when it happens but not necessary. The value for cardinality is an estimated number of rows, so it’s expected to not be exact. But when it’s way off, either up or down, things go badly. This is when the optimizer picks to use an index, a full scan, a join method, or a join order that causes the plan to go horribly wrong. 

Recently I was at a company that was moving very large queries from another system to Oracle Exadata. These queries were large in just about every sense of the word. The queries were hundreds of lines long many times and some worked with billions of rows in as well. When the cardinality would go wrong everything after that step in the plan just got worse and worse. It wasn’t uncommon for us to fix that sort of thing and take a query from never finishing to completing in a few minutes or even seconds. 

What kinds of things did we do to fix this? Mostly it had to do with changing the code of the query. It wasn’t common that the stats were wrong. And even if they were all we could do was recommend to the DBA team that the stats appeared to be off and hope they would fix it. 

Mostly it was rewrites of the query. 

 One problem was the over use of OUTER joins. Sure, they are needed, but the folks writing these queries tended to use them all the time. This did a few things; one it more or less forced a join order and it didn’t help the cardinality estimate many times. I fixed many queries but changing OUTER joins to INNER joins. The query got the same result set, of course if it didn’t that isn’t a fix! And could run many magnitudes faster because it would get a better join order and/or get a better cardinality. 

Another problem we banged into was a bug with hybrid histograms. This would kick in mostly with very large sets of data. The histogram would lead the optimizer to think it was getting back a very large set of rows many times and very poor decisions would be made because of that. The customer was still on version 12.1 and I’m pretty sure that bug has been fixed. 

 If you notice that columns with a hybrid histogram is giving very bad cardinality estimates you might want change that. How? Of course, you could drop the histogram (really you can’t, you just recollect stats without creating the histogram on that column), we couldn’t do that. We would typically modify that column in the predicate so it couldn’t use the histogram. Mostly we used NVL or COALESCE on the column. I prefer COALESCE myself because it will work on a column even if it’s a primary key, NVL wouldn’t. 

Sometimes it was expressing the predicate(s) in a different way. The simpler a predicate is the less likely a mistake will happen with the math. This typically requires really understanding the question being asked by the query. If you didn’t write the code, then you’ll likely need to chat with whoever did to find out what is going on. 

 Other times we had to resort to using the CARDINALITY hint to get the optimizer to do the right thing. I talked about this in my post "Hints in the cloud or not" this past March. You might want to check that post out too. It’s a quick read. 

The key with optimizing a query is getting that CARINALITY estimate to be in the right magnitude. Walk the plan “up and out” to find were it goes wrong. Stop there and fix that. Which table and predicates are driving the poor estimate? Really did into what that bit of code is doing, can it be rewritten another way? Sometimes one tiny change to a huge query and all is good. Other times it’s an iterative process, make a change, run it, make another and so on. 

Yes, it’s a lot harder to do this then to add more memory or CPU or other resource. But if you don’t fix it and just “add hardware” to make it go away, all you really did was kick the can down the road and it’s going to come back.