Oracle

About 7 months ago I wrote about sitting the Oracle Database SQL Expert (1Z0-047) exam. Since then I’ve been promising myself I’d write some articles about some of the sections tested by the exam. Lots of the content is pretty straight forward if you’ve been writing SQL for a few years. Also, there are lots of things that are covered in existing articles on the site.

• ANSI ISO SQL Support In Oracle9i
• MERGE Statement Enhancements in Oracle Database 10g
• Flashback New Features and Enhancements in Oracle Database 10g
• Top-N Queries
• WITH Clause

I finally got round to writing a few posts about some of the exam content that isn’t covered, or is a bit “dispersed”, on my site.

• NULL-Related Functions – Lots of them mentioned in other articles, but one article to bring them all together seemed like an ok idea.
• ALL, ANY and SOME Comparison Conditions in SQL – I don’t use them when I’m writing SQL, but the exam uses them a lot and I’ve seen them in mySQL stuff I’ve worked on, so you’ve got to understand them.
• ROLLUP, CUBE, GROUPING Functions and GROUPING SETS – I was sure I must have written about these some time in the past, but I hadn’t.
• Analytic Functions – I’ve done several posts about individual analytic functions over the years, but not one as a general primer, so I thought it was worth doing.

OK, so the last one is not tested in the exam, but it should be.

With a bit of luck I’ll remember to link to these articles when people ask me questions in the future.

Cheers

Tim…

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

Due to a lot interest I’m going to do another run of my Advanced Oracle Troubleshooting v2.0 Online Deep Dive seminars in April and May (initially I had planned to do it no earlier than Sep/Oct…)

Check the dates & additional info out here:

• http://tech.e2sn.com/oracle-training-seminars

P.S. People who already attended the AOT2 seminars last year – I will schedule the follow-up Q&A sessions in mid-March!

I had a question about how to quickly identify which Oracle process runs out of which ORACLE_HOME on Linux.

I have uploaded a little script for that – it’s basically looking up all PMON process IDs and then using /proc/PID/exe link to find out where is the oracle binary of a running process located.

You may have to run this as root (as on some Linux versions I get “ls: cannot read symbolic link: Permission denied” error even when running this command as the owner of all Oracle homes (it seems to happen when your users UID and primary GID are different than thet setuid/setgid bits on the oracle binary):

oracle@linux03:~$ sudo ./findhomes.sh
   PID NAME                 ORACLE_HOME
  4421 asm_pmon_+ASM        /u01/app/oracle/product/11.2.0/db_1/
  4545 ora_pmon_demo112     /u01/app/oracle/product/11.2.0/dbhome_1/
  4547 ora_pmon_test112     /u01/app/oracle/product/11.2.0/dbhome_1/

You can use a similar approach on other Unixes too where the executable location or current working directory (CWD) is externalized in the /proc filesystem – or just use pmap to get this info instead.

I’ve added a couple of new landing pages on my website:

• PL/SQL
• SQL

I think it makes it a bit easier to find stuff if you are not searching for specific keywords.

Cheers

Tim…

Oracle VM VirtualBox 4.0.4 has been released, as pointed out here.

Cheers

Tim…

Somehow I missed a post in the Oracle Database Insider blog with the links to 3D tours of the latest Database Machines – X2-2 and X2-8. I must say they’ve impressed me. Not by the amount of technical details these simple tours have, but rather the way they present data. It’s easy. It’s understandable. It’s absolutely not Oracle style. If you are following Oracle’s press releases you know what I mean. They suck. I spend time reading press releases to get WTF marketing people want to say. Go here for example and tell me what is the configuration of new servers in s/c/t.

Recently I did some tuning of data generation scripts, which purpose is to build large amount of representative data for application testing. Direct-path inserts are in use and as a prerequisite all constraints and indexes on target tables are disabled before the load and are enabled after it. Since I wanted to utilize available resources on the machine for that task, almost each step uses parallel execution. Well, kind of almost, because enabling constraints didn’t run in parallel, although I’ve politely asked Oracle to do so. I’ll explain here why it didn’t work.

Sometimes I get questions as to whether Pythian is one of the competitors battling with Oracle for MySQL support. The answer lies in the distinction of product support and operational support.

At Pythian, we are laser focused on supporting applications and data infrastructure using Oracle, MySQL and Microsoft SQL Server products. A vast majority of our Oracle customers (there are few customers who have very old 7.x and 8.x products running without vendor support) have Oracle maintenance subscriptions that include product updates and product support. Product support entitles the customer to open support requests when the product doesn’t perform according to the specifications (bug reports) as well as fill in enhancement requests. It also covers deployment blue-prints and deployment guidelines in the official vendor documentation and support database.

What you can’t expect from product support are answers to questions like these:

• How do I architect my infrastructure?
• How much CPU do I need to run this database
• How do I setup my backups?
• How do I tune that SQL statement?
• What I need to monitor in my environment to keep it healthy and avoid service outages?

Of course you cannot expect product support to login to your systems and help monitor them, recover a corrupted database or resolve performance issues etc.

Oracle customers usually have clear understanding of the differences between product support and operations support and consulting that Pythian provides. Even then, every now and again we hear rare statements like “I’m not renewing our Oracle product support because we now have you, Pythian, supporting our databases.” Hearing that, we’re catching our breath for few seconds and then patiently explain that this is inadvisable and the product support is totally different from what Pythian does.

Because of its open-source nature, MySQL database customers have somewhat less incentive to sign up for product support relying on public community releases and the ability to patch the product themselves but even then there is a clear distinction between product support and operational support.

All that was a long prelude to answering the question — “Is Pythian Competing with Oracle and other vendors for MySQL product support”? The answer is NO — Pythian provides plan, deploy, manage services — we analyze, design, implement and maintain the infrastructure. We are working with the vendor providing product support (or as part of the community at large when it comes to the open-source community MySQL releases).

With the arrival of Oracle Linux 6 comes the inevitable installation articles.

• Oracle Linux 6 Installation
• Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6

The Oracle installation on Oracle Linux 6 is certainly smoother than the recent Fedora installations have been. Even Enterprise Manager works fine with no meddling.

The official 11gR2 installation guide has not been updated to include Oracle Linux 6 and I can’t see any notes on MOS about it, so I’ve essentially followed the installation for Oracle Linux 5 and adjusted where necessary. I’m guessing when the official notes are released they are going to be pretty close to this. I can’t see any certifications against Oracle Linux 6, so I guess I would avoid it for production Oracle installations at the moment.

Cheers

Tim…