Search

Top 60 Oracle Blogs

Recent comments

January 2016

CTEs and Updates

An important target of trouble-shooting, particularly when addressing performance problems, is to minimise the time and effort you have to spend to get a “good enough” result. A recent question on the OTN database forum struck me as a good demonstration of following this strategy; the problem featured a correlated update that had to access a view 84 times to update a small table; but the view was a complex view (apparently non-mergeable) and the update took several hours to complete even though the view, when instantiated, held only 63 rows.

The OP told us that the query “select * from view” took seven minutes to return those 63 rows, and wanted to know if we could find a nice way to perform the update in (approximately) that seven minutes, rather than using the correlated update approach that seemed to take something in the ballpark of 7 minutes per row updated.

Enterprise Manager Cloud Control 13c Upgrade

em-12cA couple of weeks ago I posted about doing a fresh installation of Enterprise Manager Cloud Control 13c (article, blog post). I’ve finally got around to doing an upgrade test from EM CC 12cR5 to 13cR1. You can see the result of that here.

Oracle XE 12c?

There was a post on Oracle-L asking about Oracle Express Edition (XE) 12c. I started to write a reply, but thought a blog post may be more appropriate.

Oracle XE 12c doesn’t exist yet, but people at OpenWorld 2015 confirmed they “plan” to have one. As always, no promises. So when will it arrive? Typically the XE version is put together based on the the first major patchset of release 2 of a version. So the kind of thing you might expect is,

  • 12.2.0.1 : Released mid 2016 maybe.
  • 12.2.0.2 : Released mid 2017 maybe.
  • XE team brought together: Some time after 12.2.0.2 release.
  • XE Released: When it is built and stable.

Things to consider, based on stuff I’ve heard over the last few years.

WordPress 4.4.1

WordPress 4.4.1 has been released.

You can see the list of fixes here. Your site will probably auto-update soon if you can’t be bothered to give it a nudge before.

There is also an update to the Twenty Sixteen theme, which you will have to trigger manually, if you are using it of course. :)

Happy blogging.

Cheers

Tim…

NLS Mess

The Oracle database has all sorts of little details built into it to help it deal with multi-national companies, but since they’re not commonly used you can find all sorts of odd “buggy” bits of behaviour when you start to look closely. I have to put “buggy” in quotes because some of the reported oddities are the inevitable consequences of (for example) how multi-byte character sets have to work; but some of the oddities look as if they simply wouldn’t be there if the programmer writing the relevant bit of code had remembered that they also had to cater for some NLS feature.

FREE webinar about Advanced #Oracle Database Administration

On behalf of Oracle University, I will be delivering a free webinar, Tuesday 26th January 2016:

http://www.oracle.com/uk/education/eblast/uk-dba-advanced-admin-181215-ol-2835011.html

Expect live demonstrations as the major content – I promise to keep the marketing part to a minimum :-)

ANSI bug

In almost all cases the SQL you write using the ANSI standard syntax is tranformed into a statement using Oracle’s original syntax before being optimised – and there are still odd cases where the translation is not ideal.  This can result in poor performance, it can result in wrong results. The following examples arrived in my in-tray a couple of weeks ago:

Video Tutorial: XPLAN_ASH Active Session History - Part 7

#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px; line-height: 16.9px;">The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px; line-height: 16.9px;" />#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px; line-height: 16.9px;">More parts to follow.
#333333; font-family: Verdana, Arial, sans-serif; font-size: 13px; line-height: 16.9px;">

Resolutions for 2016

I remember at one of the first Mathematics lectures I attended at University, the lecturer demonstrated some principle (which I don’t recall, and certainly wouldn’t even comprehend now Smile ), and then turned and said to the class: “Now THAT is a pretty exciting result”.

Being fresh out of high school, and like most teenagers, thinking we were all so cool and hip, most of the class laughed.

New Years Resolution–test cases

If the following email came across your desk

“I have two tables EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) and DEPT(DEPTNO,DNAME,LOCATION).  When I join them I’m getting multiple rows for the same employee”

my question would be – how would you proceed ? What is the first thing you need to do to even begin to assist ?

You need data.  And before you can have data, you need to have tables in which to store that data. 

But there’s a problem:


SQL> EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SP2-0734: unknown command beginning "EMP(EMPNO,..." - rest of line ignored.

Nope…that doesn’t work. Let’s try CREATE TABLE in front of it