Search

Top 60 Oracle Blogs

Recent comments

February 2014

OTN Yathra 2014 : Mumbai

The Mumbai event took place in the local Oracle office and was attended by a straight technical crowd, which is much easier to present to than a mixed audience. :)

In addition to my own talks, I got to see some others too.

Indexing LOBs

Many years ago, possibly when most sites were still using Oracle 8i, a possible solution to a particular customer problem was to create a function-based index on a CLOB column using the dbms_lob.getlength() function call. I can’t find the notes explaining why this was necessary (I usually have some sort of clue – such as the client name – in the script, but in this case all I had was a comment that “the manuals say you can’t do this, but it works provided you wrap the dbms_lob call inside a deterministic function”).

GITHUB_UTL Tutorial 7: Easy push of code and easy diff of code.

Today I will introduce a couple of new packages as well as show more of the integrated select functionality. The main new package I will show is a utility package to better use the base API functionality for something meaningfull. We will use these utilities to populate our GitHub repository with all our code, and also to detail the status between our live code and what lives in the repository.

Index Compression – aargh

The problem with telling people that some feature of Oracle is a “good thing” is that some of those people will go ahead and use it; and if enough people use it some of them will discover a hitherto undiscovered defect. Almost inevitably the bug will turn out to be one of those “combinations” bugs that leaves you thinking: “Why the {insert preferred expression of disbelief here} should {feature X} have anything to do with {feature Y}”.

Here – based on index compression, as you may have guessed from the title – is one such bug. I got it first on 11.1.0.7, but it’s still there on 11.2.0.4 and 12.1.0.1

GITHUB_UTL Tutorial 7: Easy push of code and easy diff of code.

Today I will introduce a couple of new packages as well as show more of the integrated select functionality. The main new package I will show is a utility package to better use the base API functionality for something meaningfull. We will use these utilities to populate our GitHub repository with all our code, and also to detail the status between our live code and what lives in the repository.

AWR Top 5 Timed Foreground Events

I've noticed that people post how to get AWR Top 5 Timed Foreground Events other a range of snapshots using a SQL query from time to time. Since this is something I've done for years here is the version of the SQL I use in case somebody finds it useful:

select case wait_rank when 1 then inst_id end "Inst Num",
case wait_rank when 1 then snap_id end "Snap Id",
case wait_rank when 1 then begin_snap end "Begin Snap",
case wait_rank when 1 then end_snap end "End Snap",
event_name "Event",
total_waits "Waits",
time_waited "Time(s)",
round((time_waited/total_waits)*1000) "Avg wait(ms)",
round((time_waited/db_time)*100, 2) "% DB time",
substr(wait_class, 1, 15) "Wait Class"
from (
select
inst_id,
snap_id, to_char(begin_snap, 'DD-MM-YY hh24:mi:ss') begin_snap,
to_char(end_snap, 'hh24:mi:ss') end_snap,
event_name,
wait_class,

EM12c Creating a New Agent Registration Password

I know, I know… Just answering questions that I keep receiving from folks repeatedly, so if you know this one, love ya, if you need this answer, here it is! :)

Proper Resync of an Agent

When an agent reports that it’s blocked and needs to be resync’d, most DBAs are going to log into the Enterprise Manager 12c console and attempt a resynchronization to have it fail.  A resync isn’t required very often, but if you do run into “Agent Blocked”, here are the initial steps that should be performed to have a resync complete successfully.

Averages

Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.

Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):