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
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
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.
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 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 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).
select count(distinct cust_id) from sh.sales;
COUNT(DISTINCTCUST_ID)
———————-
7059
Elapsed: 00:00:00.614
select approx_count_distinct(cust_id) from sh.sales;
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:
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
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.
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:
Recent comments
2 years 51 weeks ago
3 years 11 weeks ago
3 years 15 weeks ago
3 years 16 weeks ago
3 years 20 weeks ago
3 years 41 weeks ago
4 years 10 weeks ago
4 years 39 weeks ago
5 years 24 weeks ago
5 years 24 weeks ago