News, views, tips and tricks on Oracle and other fun stuff

Oracle Magazine PL/SQL Columns by Steven Feuerstein, the Whole List ↗

Dating back to January 2003.

Comments Off | Filed in Oracle | Tags:

Handy Oracle EBS Trading Community Architecture (TCA) Diagram ↗

Via Puneet Rajkumar.

Comments Off | Filed in Oracle | Tags:

Extended SQL Trace (Event 10046) Full List of Levels ↗

Christian Antognini published a handy list of currently available levels.

Comments Off | Filed in Oracle | Tags:

Delete Column Histograms to Improve SQL Plan Stability ↗

Carlos Sierra:

If you are on 10g, or if on 11g but ACS (Adaptive Cursor Sharing) is not an option, then deleting column Histograms may be your best option to improve your plan stability. Like any other change, you rather test first on a non-production environment. Even in such test environment, you may want to restrict your test to only those tables accessed by your SQL. On 11g, DBMS_STATS.DELETE_COLUMN_STATS provides an option to delete just the Histograms while preserving all other column statistics. This is by using parameter COL_STAT_TYPE with value HISTOGRAM. If you want to do the same on 10g, you may want to use the SQLT HGRM module. In any case you can always restore column statistics using DBMS_STATS.RESTORE_TABLE_STATS.

Carlos gives us a tip on how to disable the use of Histograms with a hidden parameter:

If you are considering deleting Histograms to test the effect on an execution plan and the performance of your SQL, you may want to test first asking the CBO to simply ignore them. If patch for bug 9550277 has been applied in your system then you can command below.

Worth noting that Jonathan Lewis was very successful in tuning a customer’s query by simply deleting histograms:

The most critical piece of advice I had given them was to get rid of ALL the histograms they had on their system, and then watch very carefully for any signs that they might need to re-introduce a handful of histograms over the next few weeks. One of their critical queries completed in less that 2 seconds when histograms were removed, but took 33 seconds to complete when histograms were in place.
Comments Off | Filed in Oracle | Tags:

SQL Injection Attacks Up 69%, Here is How to Protect Yourself

Emil Protalinski:

SQL injection attacks are becoming significantly more popular amongst hackers, according to recent data. Between Q1 2012 and Q2 2012, there has been an estimated 69 percent increase of this attack type.

SQL injection can be easily avoided. How? Just use bind variables. Here is a quote from Tom Kyte:

If you use bind variables, you cannot be SQL Injected – this is true for PL/SQL, for Java, for any and all languages. If you use bind variables you cannot be SQL Injected – period. It is that simple, really and truly.

Tom also links to an excellent paper on this subject written by Bryn Llewellyn: How to write SQL injection proof PL/SQL [PDF].

It baffles me how such a simple and easy solution to such a big security problem is not implemented in the code base of all of these big companies that have recently been hacked.

Comments Off | Filed in Oracle, Security | Tags:

Is ANSI SQL Join Syntax Your Default Approach to Writing Queries in Oracle? ↗


I think if Oracle had extended its ANSI SQL support beyond SELECT to the other DML commands the argument over whether to use the new syntax would have died out years ago.

It would have helped for sure. I also think that if you work in an environment where ANSI SQL is not the “standard” it is hard to adopt it as your main approach to writing queries.

Comments Off | Filed in Oracle | Tags: ,

Another Reason Why You Should Upgrade Your Oracle Client Software ↗


Apparently somewhere around the version 10 of sqlplus they changed the autotrace command to use DBMS_XPLAN.DISPLAY to show the plan of a query run with set autotrace on. But, I’ve never taken advantage of this feature because I’m using a 9.2 version of the Oracle client on my laptop.
Comments Off | Filed in Oracle | Tags:

See How Easily You Can Improve Performance by Using These Five Data Caching Techniques

I will be presenting a free Webinar, brought to you by ODTUG, on Tuesday, July 17, 9:00 AM – 10:00 AM PDT. I will be talking about and demonstrating SQL and PL/SQL caching techniques that will considerably improve the performance of your queries and programs. The Webinar will cover the following five features:

  • SQL Query Result Cache
  • PL/SQL Function Result Cache
  • Package-Based Cache
  • Deterministic Function Cache
  • Scalar Subquery Cache

Click here to register.

The webinar went very well. I thank all of you who attended and ODTUG for hosting it.
Click here to download a zip file containing the following files:

  • Power Point presentation
  • Corresponding white paper
  • All the demo scripts
3 Comments | Filed in Oracle | Tags: , ,

Oracle Linux, Very Convenient (and Free) For Your Personal Use ↗

Wim Coekaerts:

Now, at least I can get a copy of Oracle Linux for free (even if I was not working for Oracle) and I can/could use that on as many servers at home (or at my company if I worked elsewhere) for testing, development and production. I just go to and download the version(s) I want and off I go.
Some of my servers are on the external network and I need to be current with security errata, but guess what, no problem, my servers are hooked up to which is open, free, and completely up to date, in a consistent, reliable way with any errata, security or bugfix. So I have nothing to worry about. Also, not because I am an employee. Anyone can.
Comments Off | Filed in Oracle | Tags:

Larry Ellison Tells It Like It Is: The Full D10 Interview (Video) ↗

Kara Swisher:

Pull up a chair and enjoy what turned out to be one of the highlight interviews of the 10th D: All Things Digital conference, which is essentially Ellison very, very unplugged:
Comments Off | Filed in Oracle | Tags: