I am working on an SR with Oracle support and they asked me to do a 10128 trace to look at how the optimizer is doing partition pruning. I did some quick research on this trace and wanted to pass it along.
Here are the names of the two Oracle support documents that I found most helpful:
How to see Partition Pruning Occurred? (Doc ID 166118.1)
Partition Pruning Min/Max Optimization Fails when Parallel (more...)
The questions in the quiz were, of course, all trick questions.
Question 1: Answer D
SELECT * FROM T1 LEFT NATURAL JOIN T2;
SELECT * FROM T1 NATURAL LEFT JOIN T2;
The second example shows the correct syntax for a natural outer join. In the first example, LEFT is interpreted as a table alias for T1 and so an inner join is used.
QUESTION 2: Answer E
SELECT * FROM T1 INNER JOIN T2 ON (more...)
Monday was the first day of the main part of the UKOUG Tech 14 conference, after SuperSunday’s additional content. I had a great night’s sleep and woke at 7 am, much to the disgust of Richard Foote who had been up for four hours already and didn’t seem to appreciate my #NoJetLagHere hashtag replying to his lack of sleep tweet. I managed to get to the conference relatively dry, but realized yet again that you (more...)
I’m on my way to UKOUG Tech14, where I will present Database 12c MATCH_RECOGNIZE: Beating the Best Pre-12c Solutions on Tuesday at 3 P.M., room 4B. With all the Oaktable ACE Directors presenting at the same time, I don’t expect a big crowd but I’ll do my best for those who do turn up. This is my […]
In the database world (especially among the database developers) a commit is often viewed as some sort of a virtual “save” button — i.e. it’s something that you need to do to make your changes permanent. This is one of the reasons why developers are often happy to commit work as soon as they get a chance (even if it’s not properly finished), so that they wouldn’t lose their changes in case of an (more...)
There’s still time to register for the webinar that Fishbowl Solutions and Oracle will be holding tomorrow from 1 PM-2 PM CST! Innovation in Managing the Chaos of Everyday Project Management will feature Fishbowl’s AEC Practice Director Cole Orndorff. Orndorff, who has a great deal of experience with enterprise information portals, said the following about the webinar:
“According to Psychology Today, the average employee can lose up to 40% of their productivity switching from task (more...)
Just curious if anyone else is seeing this behaviour..
SQL> set long 500000
SQL> @pt "select * from v$sql where sql_id = '1km492z723vpu'"
SQL_TEXT : alter table scott.emp
SQL_FULLTEXT : alter table scott.emp
SQL_ID : 1km492z723vpu
Suffice to say, there’s a lot more to that SQL statement, but its lost in v$sql. Once complete, it does appear in full in v$sqlstats, but its a pain when you cant see the whole statement..
Let’s consider a hypothetical scenario. Suppose you have a process A that you want to run faster. This process doesn’t commit (much), so it doesn’t wait on log file sync. However, there is another multi-threaded process, B, that commits very frequently, and spends a lot of time on “log file sync”. You don’t care about the process B, your only goal is to make A go faster. After exhausting your tuning arsenal (to no avail), (more...)
I had a five-minute conversation with Oracle development Friday that rocked my world. I found out that parsing blocks stats which blocks parsing.
We have a system with queries that are taking minutes to parse. These queries include the main tables on our database, one of which is interval partitioned and has 20,000 sub-partitions. We have seen a situation where Oracle’s delivered statistics gathering job hangs on a library cache lock waiting (more...)
Tableau Software is a self-service BI-tool that allows data visualisation, meaning that even business users should be able to easily visualise their data (without needing help of IT). You can check out the Tableau website for more information about this great tool.
As you may have already seen in several guides or tutorials, Tableau is able to link certain dimensions like postal code, countries, etc. to a certain geographical area. Unfortunately, most of these (more...)
This article describes how to standardize your JDeveloper Installation as requested in Planning & Getting Started – Team, Skills and Development Environments. It is part of the ADF Architecture Practice lessons and describes the installation with examples for windows.
There is a tool ojwebupdate to handle this. It is fully supported but it’s not documented at the moment, documentation is planned for 12.1.4. But you can get a lot (more...)
Another piece of good news — Oracle has opened a bug for yet another anomaly I reported earlier in my blog: row-by-row processing of bulk DML when the block size on the target table is less than the default 8k. So it’s now officially bug 20039770 – “DML SLOW WITH 4K BLOCK SIZE VS 8K BLOCK SIZE”. Their bug description seems a bit off (because 2K shows the same behavior as 4K, and 16/32K as (more...)
I’ve uploaded a monitoring script that I have worked on: zip.
The script alerts you when the optimizer runs a SQL statement with a potentially new and inefficient plan so you can intervene. This script improves upon my earlier script which only alerts you to SQL statements running with new plans. The new script compares the average elapsed time of the current plan with the average of the most often executed plan. (more...)
I found a broken link to an Oracle help document in one of my posts and when I went to the Oracle 12c database documentation to find the new URL to put in my post I found that Oracle had totally revamped their online manuals.
Here is a link to the new high level Oracle help site: url
I’ve only looked at it for a few minutes and can’t say whether I like it or (more...)
At Oracle OpenWorld, there was a lot of Oracle ADF material. I presented on Oracle ADF Bindings as part of the well-attended ADF track on User Group Sunday, and Oracle also gave some very interesting presentations on new and coming features in ADF. For example there is now an ADF component that you can use to visualize any network of nodes and edges.
But what struck me more was the fact that when Oracle buys (more...)
Oracle 12c came up with one nice new feature: Opatch, the tool known to all database administrators is now integrated into the database in the form of DBMS_QOPATCH package. If the database contains pluggable databases, the package must be executed from the root (CDB$ROOT) level, as user sys.
Also, a minor inconvenience is that there are no tabular results, results are returned as XML. It is a bit cumbersome to work with and I do (more...)
Continuous integration offers huge efficiency gains for companies but is continuous integration even possible when the application’s backbone is a massive relational database. How can one spin up database copies for developers, QA, integration testing, and delivery testing ? Its not like Chef or Puppet can spin up a 10TB database copy in a few minutes the way one can spin up a Linux VM.
There is a way and that way is called data virtualization (more...)
The annual two day AUSOUG conference in Perth is well underway, and so far, its been a wonderfully successful event. In particular, we Australians are notorious for not really getting into the networking thing, so conferences often have a lot of ‘awkward silences’ when sessions are not on, and we are meant to be (god forbid) *talking* to each other :-)
But this year has been different. Lots of banter, lots of discussion. Also, the (more...)