Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

January 2015

Why Automate Target Patching with Enterprise Manager 12c

Submitted by dbakevlar on Tue, 2015-01-13 18:51

Every job comes with tasks that no one likes to perform and database administration is no exception.  Patching is one of those necessary tasks that must be performed and when we are expected to do more with less everyday, the demands of patching another host, another agent, another application is often a task that no one looks forward to.  It’s not that it goes wrong, bu

Free Version of Delphix !

Submitted by khailey on Tue, 2015-01-13 15:49

Delphix is now available for  30 day trial direct download ! ( if you would like a longer version please contact me at kyle@delphix.com year trials and even indefinite trials  potential possible for partners, bloggers, Oracle Aces etc)

Just got to the

The Delphix download trial consists of 3 pre-configured virtual machines downloadable as OVA files

Inappropriate behaviour

Submitted by connor_mc_d on Tue, 2015-01-13 13:04

You pick up little funny things in the day to day with Oracle.

Like this one when you try to drop a tablespace with a queue table in it:

drop tablespace MY_TSPACE including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: Inappropriate utilities used to perform DDL on AQ table
SCOTT.QUEUE_TABLE

You would think that since you’ve asked to drop everything, that well…everything could be dropped, but no :-)

The solution here is to use DBMS_AQADM.DROP_QUEUE_TABLE (with force=true if necessary) to clean it out, and the re-issue your command.

Data Pump import makes me crabby

Submitted by connor_mc_d on Tue, 2015-01-13 12:04

I’m sitting here watching the import of a moderately sized database via transportable tablespaces.  You know…the thing you use when a full export / import would be too slow, and this is meant to be … well…fast.

And fast it is.. until it reaches the following step:

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Now understandably, there’s plenty of stats to import, so its fair that it should take a little while.  But a quick look at the SQL that’s being run, reveals something truly hideous.  You’ll see a succession of giant PL/SQL blocks, chock full of literals and row-by-row (slow by slow) processing.  Things like this:

Adaptive plans and v$sql_plan and related views

Submitted by martin.bach on Tue, 2015-01-13 10:14

Adaptive plans are one of the coolest new optimiser features in Oracle 12c. If you haven’t seen or heard about them in detail I recommend the following resources:

Oracle 12c SQL – Approximate Count Distinct

Submitted by Johnjayking on Tue, 2015-01-13 04:21

Approximate Count Distinct

Oracle 12.1.0.2 documents a new aggregate function named APPROX_COUNT_DISTINCT (first added internally to Oracle 11g to speed table statistics gathering). APPROX_COUNT_DISTINCT provides an approximate value without actually processing all of the database rows (Oak Table Network member and Oracle Ace Director Christian Antognini has documented consistent accuracy of plus-or-minus 4% with considerable performance improvement).

Standard COUNT DISTINCT

 

select count(distinct cust_id) from sh.sales;

COUNT(DISTINCTCUST_ID)
———————-
7059

Elapsed: 00:00:00.614

 

APPROX_COUNT_DISTINCT

select approx_count_distinct(cust_id) from sh.sales;

FBI Bug reprise

Submitted by Jonathan Lewis on Mon, 2015-01-12 12:52

I’ve just had cause to resurrect a blog note I wrote three years ago. The note says that an anomaly I discovered in 9.2.0.8 wasfixed in 10.2.0.3 – and this is true for the simple example in the posting; but a recent question on the OTN database forum has shown that the bug still appears in more complex cases.  Here’s some code to create a table and two indexes:

Installing Oracle 12.1.0.2 RAC on Oracle Linux 7-part 2

Submitted by martin.bach on Mon, 2015-01-12 08:22

In the first part of the article series you could read how a kickstart file made the installation of Oracle Linux 7 a lot more bearable. In this part of the series it’s all about configuring the operating system. The installation of Grid Infrastructure and the Oracle database is for another set of posts.

There are quite some differences between Oracle Linux 6 and 7

Video Tutorial: XPLAN_ASH Active Session History - Introduction

Submitted by randolf.geist on Sun, 2015-01-11 22:38

I finally got around preparing another part of the XPLAN_ASH video tutorial.

This part is about the main funcationality of XPLAN_ASH: SQL statement execution analysis using Active Session History and Real-Time SQL Monitoring.

In this video tutorial I'll explain what the output of XPLAN_ASH is supposed to mean when using the Active Session History functionality of the script. Before diving into the details of the script output using sample reports I provide some overview and introduction in this part that hopefully makes it simpler to understand how the output is organized and what it is supposed to mean.

This is the initial, general introduction part. More parts to follow.

count(*) – again !

Submitted by Jonathan Lewis on Fri, 2015-01-09 12:56

Because you can never have enough of a good thing.

Here’s a thought – The optimizer doesn’t treat all constants equally.  No explanations, just read the code – execution plans at the end: