12c

Richard Foote's picture

Oracle 12c: Indexing JSON in the Database Part II (Find A Little Wood)

In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]

Jonathan Lewis's picture

Adaptive mayhem

So you run a query and it gives you a plan with a note that says “This is an adaptive plan”.

So you run it again and the plan changes,  with a note that says “Statistics feedback used for this statement”

So you pause to think for a bit, then run the query again and the plan changes, with a note that says “One SQL Directive used, dynamic statistics used”. (You waited too long and the internal re-optimization hints got flushed down into an SQL directive.)

So you decide to think about it the following morning when you’re feeling bright and fresh, and when you run it you get another plan because overnight the automatic stats job gathered stats on the critical table and created a column group that was indicated by the (now defunct) directive.

Happy optimisation!

 

Richard Foote's picture

Oracle 12c: Indexing JSON In The Database Part I (Lazarus)

One of the very cool new features introduced in Oracle Database 12c Rel 1 is the ability to store JavaScript Object Notation (JSON) documents within the database. Unlike XML which has its own data type, JSON data can be stored as VARCHAR2, CLOB or BLOB data types, but with a JSON check constraint to ensure the […]

Johnjayking's picture

Oracle 12c – Invisible Columns!

Oracle 12c – Invisible Columns

Beginning with Oracle 12c columns may be marked “INVISIBLE” in CREATE and ALTER TABLE statements.

#ff0000;">What the?!?!?!?!

Suppose you have code that uses “SELECT *” (no, really this sometimes occurs) and you need to add a column or columns? Oops! Code using “SELECT *” now breaks as does code using INSERT statements without a column list. (I know, ALL of your system’s code uses explicit column name lists in SELECT and INSERT ; this does not apply to your shop… (please excuse the sarcasm))

Jonathan Lewis's picture

Invisible Bug

At this Wednesday’s Oracle Midlands event someone asked me if Oracle would use the statistics on invisible indexes for the index sanity check. I answered that there had been a bug in the very early days of invisible indexes when the distinct_key statistic on the index could be used even though the index itself would not be considered as a candidate in the plan (and the invisible index is still used to avoid foreign key locking – even in 12c – it’s only supposed to be invisible to the optimizer).

Jonathan Lewis's picture

Uniquely parallel

Here’s a surprising (to me) execution plan from 12.1.0.2 – parallel execution to find one row in a table using a unique scan of a unique index – produced by running the following script (data creation SQL to follow):

rshamsud's picture

Accessing HCC compressed objects using an index

Problem

I came across another strange SQL performance issue: Problem was that a SQL statement was running for about 3+ hours in an User Acceptance (UA) database, compared to 1 hour in a development database. I ruled out usual culprits such as statistics, degree of parallelism etc. Reviewing the SQL Monitor output posted below, you can see that the SQL statement has already done 6 Billion buffer gets and steps 21 through 27 were executed 3 Billion times so far.

Statistics and execution plan

Jonathan Lewis's picture

Quiz Night

Here’s an execution plan from a recent OTN database forum posting:

Richard Foote's picture

Storing Date Values As Numbers (The Numbers)

In my last couple of posts, I’ve been discussing how storing date data in a character based column is a really really bad idea. In a follow-up question, I was asked if storing dates in NUMBER format was a better option. The answer is that it’s probably an improvement from storing dates as strings but it’s […]

Richard Foote's picture

Storing Date Values As Characters Part II (A Better Future)

In the previous post, I discussed how storing date values within a character data type is a really really bad idea and illustrated how the CBO can easily get its costings totally wrong as a result. A function-based date index helped the CBO get the correct costings and protect the integrity of the date data. During […]

Syndicate content