The AVOID_FULL hint

I saw this very odd statement on an SAP system last week.

         SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0

I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657;
 
  COUNT(*)
----------
        32
 
SYS@DEMO1> @x
 
 (more...)

Hotsos 2014 Presentation

Just a quick note that I’ve added a zip file with my presentation at Hotsos yesterday which includes the scripts that I demoed. You can find it on the Whitepapers/Presentations page. Or you can just click this link:

SQL Gone Bad – But Plan Not Changed!

Does parallel_degree_limit work with parallel_degree_policy=manual?

The Oracle 11g parameter parallel_degree_limit is designed to put a cap on the maximum DOP for a statement.

The Oracle Data Base Reference 11g Release 2 (11.2) says this:

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

But that’s not (more...)

12c Adaptive Optimization – Part 3

This is the third and final post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization (the link goes to a recording of the webcast by the way).

Also, here are links to the 2 earlier posts:

    12c Adaptive Optimization – Part 1.
    12c Adaptive (more...)

12c Adaptive Optimization – Part 2 – Hints

This is the second post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization. The first post is here: 12c Adaptive Optimization – Part 1. Since there were several comments and questions about hints and how they interact with Adaptive Plans, I decided to limit (more...)

12c Adaptive Optimization – Part 1

Last week I did a webinar on 12c Adaptive Optimization. The talk was recorded. The slides are here: 12c Adaptive Optimization V2 PDF. The recording can be found here: 12c Adaptive Optimization Recording. There were a number of follow up questions and emails so I thought I’d summarize here. Since (more...)

APPEND_VALUES and SYS_DL_CURSOR Hints with HCC

The APPEND_VALUES hint was introduced in 11.2 to allow direct path inserts with variables using the VALUES clause. i.e.

INSERT INTO XYZ (COL1, COL2) VALUES (:A, :B);

The feature was designed to allow bulk inserting via arrays of 100′s or 1000′s of records in a single insert statement. (more...)

Enkitec is an Anagram for Necktie

 

Screen shot 2013-10-01 at 3.08.30 PM

 

I work for a small company named Enkitec. I’ve been asked several times recently where the name Enkitec came from. In fact, someone told me just today that they had heard that “Enkitec is an anagram for necktie, which guys that work for Enkitec are universally reluctant to (more...)

System Statistics – “Exadata” Mode

Here’s a very quick note on a relatively recent addition to System Statistics. It’s the so called “Exadata mode”. This mode is intended to give the optimizer a little more info about the storage system on the Exadata platform. Here’s some info from an MOS note.

Oracle Sun Database Machine (more...)

12c – parallel_degree_level (control for auto DOP)

I heard JP Dijcks speak at RMOUG in 2012 about a new parameter that would show up in 12c called parallel_degree_level. It’s basically a knob that you can turn to dial up (or down) the calculated DOP when setting parallel_degree_policy=auto. Early on (11.2.0.1) auto DOP seemed to (more...)