OraNA :: Oracle News Aggregator

The purpose of this blog is not discuss RDA but instead focus on an important option of RDA.  Never shakeout an environment  after  OS upgrade or Oracle install without this option.

The option is hvce (health validation check engine). HCVE performs health check of Oracle environment to find incorrect (more...)

http://collaborate13.ioug.org/p/cm/ld/fid=197

https://portal.hotsos.com/company/news/hotsos-symposium-2013-speakers-announced

I got selected to speak at DOAG 2012. Unfortunately I will not be able to make two international trips in one single month.  I feel very sad in declining this opportunity. Hopefully I will get another opportunity next year or so

Until 11g ,tracing was synonymous  with sessions.  You could not trace anything other than sessions (Don’t take this literally)–

–

With 11g  you have the option of  tracing individual  SQL’s by means a event/trigger.   This is very useful  if  you are executing a long running process  and interested in tracing only a  particular SQL.  You can trace the SQL either at session level or system level.
…

Syntax

• alter system set events ‘sql_trace [sql:sql_id] level 12′
• alter session set events ‘sql_trace [sql:sql_id] level 12′

Note:  The SQL trace command can be executed  for  more than 1 SQL_ID too (more...)

Trying to  predict Oracle’s behavior with respect to using indexes with LIKE operator is not simple.  However you may be able to get close enough with the following algorithm.  Let me  list  the number of ways you can use the single  LIKE operator along with the algorithm.

1. Beginning of search expression (%ABC) :  Less likely to use an Index (Most likely to perform full table scan)
2. End of search expression (ABC%): More likely to use an Index
3. In  between search expression (A%BC) : More likely to used an Index

However one key thing I want to mention is that if possible avoid using LIKE operator (more...)

-

Some of  the complex issues that we face in our day-day operations can be resolved by tracing with or without Oracle support.  It depends on how much you want to grind yourself as understanding some of the trace files are not for faint hearts.  I always end up spending time googling for appropriate  syntax or trace number. Sometimes you  are lucky enough to get  to the intended document but sometimes you end up at wrong place.   So here I am compiling a one stop shop for all tracing needs as series.  In  Part-I , I will (more...)

Lately I have been trying to help my non-DBA colleague to understand optimizer operations. Now that I am putting this effort , why not add it to my blog. I will be writing a series of blogs discussing optimizer operations.  Let me start the honors with NESTED LOOPS.

NESTED LOOPS:  As the name implies, this operation works similar to nested loops in programming language aka a loop within a loop.   For example, let’s use PL/SQL language that most of us are familiar

SQL> BEGIN

2       FOR  OuterLoop IN 1..2 LOOP

3            FOR InnerLoop  IN 1..3 (more...)