OTN Yathra 2015 just got over!!

Quite well said – Investment in knowledge is the gives you the best returns. It was a great fun last week to be the speaker at OTN Yathra 2015, organized by All India Oracle Users Group (AIOUG). The OTN tour covered seven cities and I was the part of it at three cities i.e. Hyderabad, Bangalore … Continue reading

Database design resources: My reading list

Following the database design championship playoff over on the PL/SQL Challenge, one player asked:

“Could you please advise me some resources (boooks, courses) so that I can improve?”

Of course I can! This is what I love to hear – people looking for resources they can use to increase their skills. This is information I’m sure others will benefit from, so here goes:

select materials
from   my_readings
where  topic = 'Database design';

(more...)

SQL Developer: Viewing Trace Files

Just a quick plug for looking at raw sql trace files via SQL Developer.

There is a nice Tree View:
sqldev_trace

Which can be expanded:
sqldev_trace_expand

Also summary view of statistics, filterable:
sqldev_trace_stats

And a list view, filterable and orderable:

sqldev_trace_list

Some sort of right click summary for binds/waits might be a nice addition.


Real time sql monitoring – bulk bound statement

Moving through some observations of an ongoing investigation tackling the easiest ones first.

Previously I observed the slow execution of a recursive delete of statistics history but coming not from MMON but from a number of application processes which should have only been calculating stats for their own partitions.

Statement was sql id 9v9n97qj8z1dg:

	
delete /*+ dynamic_sampling(4) */
from sys.wri$_optstat_histhead_history
where savtime < :1
and rownum <= NVL(:2, rownum);

From the real time sql (more...)

NVL und Count Stopkey

Ein kleiner - aber wichtiger - Hinweis vom Dom Brooks: für Queries mit einer rownum-Einschränkung ist normalerweise die COUNT STOPKEY Optimierung im Spiel, die die Ausführung der Operation abbricht, wenn die gewünschte Satzanzahl erreicht ist. Wenn man allerdings eine solche Einschränkung mit einer NVL-Funktion kombiniert, also etwa:
where  rownum <= nvl(:rn,rownum)
dann wird die Optimierung deaktiviert und die Ausführung bis zum bitteren Ende fortgesetzt.

The German ADF-Community-Book was released today

The German ADF- (and Forms-) Community released their first book.

The "ADF book" is a compilation of German lectures, articles and workshop tutorials in Oracle ADF (some of the contributions are in English!). The authors are members of the German ADF community. The time frame covers the years 2010 to 2014. The project "ADF book" was implemented by a team of staff from partner companies and Oracle.

40 authors wrote 70 contributions on 1400 (more...)

COUNT STOPKEY – NVL

Yesterday I mentioned issues with a recursive delete operation on statistics history.

This is a quick illustration of the last points I made on that post regarding the lack of a COUNT STOPKEY optimisation because of the use of NVL.

COUNT STOPKEY is an optimisation which allows processing to stop once the target number of rows has been reached.

For example:

create table t1 as select * from dba_objects;

alter session set statistics_level = all;

 (more...)

Meet me in Austin?

Late breaking news (aka I forgot to post this earlier)! I will be speaking at the first meeting of the Austin Oracle Users Group for 2015. It will be this coming Friday February 27, 2015 starting at 11:00 AM. I will give an intro talk on Oracle SQL Developer Data Modeler (the FREE data modeling […]

Understanding vs Resolution – Statistics History Cleanup

Today I helped resolve a problem quickly but to the detriment of my understanding of exactly what was going on and why.

And as part of the quicker resolution, I had to destroy the evidence which would have helped understand better.

So… now need to go back and figure it out if I have time to model it properly, etc.

Here’s what little I know so far.

What happened was that there were a number (more...)

Limit für die Verwendung von “colored SQL” im AWR

Das Automatic Workload Repository (AWR) gehört meiner Meinung nach zu den großartigsten Errungenschaften der jüngeren (oder auch nicht mehr ganz so jüngeren) Oracle-Geschichte, da es eine Historie von Abfragen und ihrer Performance bereitstellt. Allerdings ist diese Historie nicht vollständig, da sie auf Sampling basiert - es kann also vorkommen, dass interessante Queries nicht erfasst werden. Um dieses Problem zu lösen, wurde in 11g die Routine dbms_workload_repository.add_colored_sql eingeführt, die es ermöglicht Abfragen "einzufärben", was nichts anderes (more...)

i-Blason Prime

I replaced my OtterBox Defender with an i-Blazon Prime case when upgrading to the iPhone 6. It appeared a good option, and for 4 months it was fine. Yesterday, the latch broke and my iPhone fell to the ground as a walked. Fortunately, my iPhone landed flat, the i-Blazon plastic case absorbed the impact, and my iPhone was undamaged.

Here’s a photograph of the damaged case:

i-BlasonAnnotated

While I wanted to order only the belt portion (more...)

Alexandria PL/SQL Utility Library moved to GitHub

The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.


The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library which highlights some of (more...)

The Database Design Quiz in Numbers: 2014 Review

The results of the first ever database design championship playoff were announced today. Huge congratulations to Sean Molloy, Pavel Zeman and Justin Cave for taking the top three spots. Also thanks to everyone else who’s taken part in the quiz, answering questions, adding to the discusions and pointing out my mistakes ;)

With this in mind here’s a some of the facts and figures from 2014’s database design quizzes, along with related trivia:

-- How  (more...)

Introducing the Official LinkedIn APEX Group

Once upon a time I had a laugh on facebook when someone posted an image showing multiple groups dedicated to stopping duplicate groups, oh the irony.

Recently I've been trying to share my blog posts via LinkedIn but I'm always unsure which of the seven APEX groups I'm a member of I should post to.


It turns out only the SIG groups are owned by those involved with the APEX development team, namely David Peake (more...)

APEX Community Sample Applications

There are a bunch of sample APEX applications out there built by developers (typically with blogs) that are darn useful, but so far I haven't seen a useful central repository.

So I've started one here with a very basic form/report:
https://apex.oracle.com/pls/apex/f?p=73000:APEX_SAMPLES

At the moment it's just a list of those I happened to have bookmarked over time, in one place that others could use and perhaps contribute to.

Depending on feedback I (more...)

10 Cool things about the COMPARE_SYNC package

@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes... STEW_COMPARE_SYNC is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.

2014 Annual SQL Championship

Steven Feuerstein runs a great site at PL/SQL Challenge that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.

PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.

Unfortunately do to a timing issue, (more...)

Twitter as a work tool

Scott Spendolini conducted a little experiment last week, then Jeff Smith & Kellyn Pot'Vin whipped up a little slideshow on the topic.

I thought I'd chime in after some recent experiences were helped along by the use of Twitter, a tool it seems many are underutilising.

Why?

Exhibit A

I had an afternoon of generating JSON with SQL using LISTAGG and I kept facing this issue of being limited by 4000 characters. Knowing I occasionally (more...)

NVARCHAR2, UTL-16 and Emails

Development is often the case of trying several paths through the forest, hoping to find one that leads out the other end. That was the start of my week.

Until we get our shiny new 12c database running on its shiny new box (and all the data shifted to it), we are living with a mix of databases. To begin with, the data we managed was mostly AU/NZ and Europeans stuff, and the character set (more...)

Calculate Age in Javascript

I had a registration form in Apex which asks the applicant to enter their Date of Birth in a date item; I then needed to calculate how old they would be at the start of the event, which determines a number of rules, such as whether we need to obtain their parent’s permission.

In my first release I implemented this with a Dynamic Action which ran SQL something like this:

select round(months_between(start_date
                           ,to_date(:P1_DATE_OF_BIRTH,'DD-MON-YYYY'))
             / 12,1)
from  (more...)