Performance Problems with Dynamic Statistics in Oracle 12c

I’ve been making some tests recently with the new Oracle 12.1.0.2 In-Memory option and have been faced with an unexpected  performance problem.  Here is a test case:

create table tst_1 as
with q as (select 1 from dual connect by level <= 100000)
select rownum id, 12345 val, mod(rownum,1000) ref_id  from q,q
where rownum <= 200000000;

Table created.

create table tst_2 as select rownum ref_id, lpad(rownum,10, 'a') name, rownum || 'a'  (more...)

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause: New column datatype causes type-checking error for existing column
default value expression.
*Action: Remove the default (more...)

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions (more...)

LGWR terminating instance due to error 338

Recently we came across a issue where our DB crashed with ORA-00338 error . Errors in file /oracle/diag/rdbms/orcl11g/orc11g/trace/orc11g_lgwr_24118.trc: ORA-00338: log 2 of thread 1 is more recent than control file ORA-00312: online log 2 thread 1: '/oracle/oradata/orcl11g/redo02.log' LGWR (ospid: 24118): terminating the instance due to error 338 DB couldn't be restarted as it gave same errors…

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in (more...)

Is Oracle Database Time Correct? Something Is Not Quite Right.

Is Oracle Database Time Correct? Something Is Not Quite Right.


Oracle Database tuning and performance analysis is usually based on time. As I blogged HERE, the Oracle "database time" statistic is more interesting than simply "time spent in the database." It is the sum of CPU consumption and non-idle wait time. And Elapsed Time is the sum of all the database time related to perhaps a session or a SQL statement execution. However...

Installing ORDS 3.0 (Early adopter) in Weblogic.

Now the Oracle REST Data Services 3.0 Early Adopter is available, let us have a look how it’s to be installed in Weblogic 12c!

I’ve ommitted the installation or upgrade of Apex here as there is plenty of documentation on that topic, and the ORDS can be applied for other uses than being an Apex Listener since v2.0.4.

Installing ORDS

Download the Application Express and ORDS 3.0 installer from the Oracle (more...)

Oracle 12.1.0.2 Bundle Patching

I’ve spent a few days playing with patching 12.1.0.2 with the so called “Database Patch for Engineered Systems and Database In-Memory”. Lets skip over why these not necessarily related feature sets should be bundled together into effectively a Bundle Patch.

First I was testing going from 12.1.0.2.1 to BP2 or 12.1.0.2.2. Then as soon as I’d done that of course BP3 was released.

So (more...)

Adding a DEFAULT column in 12C

I was at a talk recently, and there was an update by Jason Arneil about adding columns to tables with DEFAULT values in Oracle 12C. The NOT NULL restriction has been lifted and now Oracle cleverly intercepts the null value and replaces it with the DEFAULT meta-data without storing it in the table. To repeat the 11G experiment I ran recently:

 

SQL> alter table ncha.tab1 add (filler_default char(1000) default 'EXPAND' not  (more...)

12.1.0.2 Introduction to Zone Maps Part III (Little By Little)

I’ve previously discussed the new Zone Map database feature and how they work in a similar manner to Exadata Storage indexes. Just like Storage Indexes (and conventional indexes for that manner), they work best when the data is well clustered in relation to the Zone Map or index. By having the data in the table […]

Approximate Count Distinct (12.1.0.2 new feature)

With the release of the Oracle Database 12.1.0.2 there was a number of new features and options. Most of the publicity has been around the in-Memory option. But there was lots of other features for the DBA and a few for the developer.

One of the new SQL functions is the APPROX_COUNT_DISTINCT(). This function is different to the tradition count distinct, COUNT(DISTINCT expression), in that is performs an approximate count distinct. (more...)

ECO 2014 Slides

Just a quick note that I posted slides for the 2 talks I did at ECO in Raleigh this week:

Keynote: Creative Problem Solving (for Oracle Systems)

In-Memory In Action (without Tanel Poder) :)

Great crowd. I really enjoyed myself.

Note: You can also find other presentations on my Whitepapers/Presentations page via the link at the top of the screen.

Interesting Oracle Syntax Error

As shared by a well known Oracle and Big Data performance geek!

SQL> ALTER SYSTEM SET inmemory_size = 5T SCOPE=spfile;
ALTER SYSTEM SET inmemory_size = 5T SCOPE=spfile
*
ERROR at line 1:
ORA-32005: error while parsing size specification [5T]


SQL> ALTER SYSTEM SET inmemory_size = 5120G SCOPE=spfile;

System altered.

:)

Index Advanced Compression vs. Bitmap Indexes (Candidate)

A good question from Robert Thorneycroft I thought warranted its own post. He asked: “I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 […]

12.1.0.2 Introduction to Zone Maps Part II (Changes)

In Part I, I discussed how Zone Maps are new index like structures, similar to Exadata Storage Indexes, that enables the “pruning” of disk blocks during accesses of the table by storing the min and max values of selected columns for each “zone” of a table. A Zone being a range of contiguous (8M) blocks. I […]

Something new in 12c: FETCH FIRST x ROWS

In this post I want to show some example of using a new feature in 12c for selecting the first X number of records from the results set of a query.

See the bottom of this post for the background and some of the reasons for this post.

Before we had the 12c Database if we only wanted to see a subset or the initial set of records from the results of a query we (more...)

Installing Oracle 12.1.0.2 on Windows 64bit

The following steps are what I did for installing 12.1.0.2 on Windows.

1. Download the Oracle installation ZIP files from the Oracle Downloads page.

DB Install 15

2. Unzip the two 12c downloads files into the same directory.

3. Go to the newly created directory (it is probably called 'database') and you will find a file called setup.exe. Double click on this file.

DB Install 1

After a couple of seconds you will see the Oracle Database (more...)

Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl)

Sometimes, a few pictures (or in this case index block dumps) is better than a whole bunch of words :) In my previous post, I introduced the new Advanced Index Compression feature, whereby Oracle automatically determines how to best compress an index. I showed a simple example of an indexed column that had sections of index entries that were […]

12c Upgrade and Concurrent Stats Gathering

I was upgrading an Exadata test database from 11.2.0.4 to 12.1.0.2 and I came across a failure scenario I had not encountered before. I’ve upgraded a few databases to both 12.1.0.1 and 12.1.0.2 for test purposes, but this was the first one I’d done on Exadata. And the first time I’d encountered such a failure.

I started the upgrade after checking with the (more...)

Index Compression Part V: 12c Advanced Index Compression (Little Wonder)

I’ve finally managed to find some free time in the evening to write a new blog piece :) This will have to be the record for the longest time between parts in a series, having written Part IV of this Index Compression series way way back in February 2008 !! Here are the links to the previous articles […]