Wednesday, January 31, 2007

Running shell script from Oracle

It’s simple!!

Especially with 10g, it is even more simpler.

Here is an example:

Step 1: Create a shell script and grant execute priv to oracle user. I created a simple one:

bash-3.00$ cat /tmp/
echo "Test succeeded `date`" >> /tmp/test.log

Note: Do not forget to start your shell script with the shell you are using.
>> #!/usr/bin/ksh should be present in your shell script otherwise, the job will fail with ORA-27369 job of type EXECUTABLE failed with

bash-3.00$ chmod 755 /tmp/

Step 2: Create the job:
Remember user doing the following should have “CREATE JOB” privilege.

3 job_name => 'TEST_SHELL',
4 job_type => 'EXECUTABLE',
5 job_action => '/tmp/',
6 start_date => sysdate,
7 repeat_interval => 'FREQ=MINUTELY; INTERVAL=1', /* every one hour */
8 enabled => TRUE,
9 comments => 'Testing shell script from Oracle');
10 END;
11 /

PL/SQL procedure successfully completed.

Step 3: Check if the job is running:

bash-3.00$ cat /tmp/test.log
Test succeeded Wed Jan 31 01:02:23 PST 2007
Test succeeded Wed Jan 31 01:03:23 PST 2007
Test succeeded Wed Jan 31 01:04:23 PST 2007

Tuesday, January 30, 2007

Index skip Scan – how?

With Oracle 9i, CBO is equipped with many more features, one of them is “Index skip scan” .This means even if you have a composite index on more than one column and you use the non-prefix column alone in your SQL, it may still use index.

I said “may” because CBO will calculate the cost of using the index and if it is more than that of full table scan, then it may not use index.

Index skip scan works differently from a normal index (range) scan.
A normal range scan works from top to bottom first and then move horizontal.
But a Skip scan includes several range scans in it. Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan.

SQL> create table test (a number, b number, c number);

Table created.

SQL> create index test_i on test(a,b);

Index created.

SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into test values(mod(i, 5), i, 100);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname => 'SAC', tabname => 'test', cascade => true);

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp
SQL> select * from test where b=95267;

Execution Plan
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=10)

1 0

2 1
INDEX (SKIP SCAN) OF 'TEST_I' (INDEX) (Cost=21 Card=1)

I above example, “select * from test where b=95267” was broken down to several small range scan queries. It was effectively equivalent to following

Select * from test where a=0 and b=95267
Select * from test where a=1 and b=95267
Select * from test where a=2 and b=95267
Select * from test where a=3 and b=95267
Select * from test where a=4 and b=95267;

In concrete, saying that skip scan is not as efficient as normal “single range scan” is correct. But yet saves some disk space and overhead of maintaining another index.

Wednesday, January 24, 2007

External Tables - 10g

Until 9i external tables could only be used for read purposes.
But 10g allows you to populate external tables.

here is how it does:

Step 1: - Create a directory

SQL> create directory my_dir as '/usr/test'; --- Make sure Oracle OS user has
privilege to write on this directory.

Step 2: - Grant permission to user SCOTT

SQL> grant read,write on directory my_dir to scott;

Step 3: - Create the External Table: -

SQL> CREATE TABLE scott.external_emp_dept
LOCATION ('emp_dept.exp')
reject limit unlimited
select e.ename,d.dname from emp e, dept d where

SQL> select * from scott.external_emp_dept;

---------- --------------

---------- --------------

And you will find a file generated i.e. 'emp_dept.exp' in /usr/test directory.

Now you can take this file to the target database and create an external table
and associate the file with that table.

Step 4: - to be executed on the source database
SQL> set long 2000
SQL> Select dbms_metadata.get_ddl('TABLE','EXTERNAL_EMP_DEPT') from dual;

The above command will give you the DDL command to create the table at target

Similar note is also available on metalink 249321.1 which was also written by me when I was with Oracle Support.

Friday, January 12, 2007


They 2 considered to be the most important parameter for shared pool tuning, but I guess most of us generally don’t use them or sometimes use them incorrectly.

The idea to put them here to understand “what they do?”, “when to use them?”, “how to use them?” and finally “see the impact”.


In most of the environments, there are many SQL’s which are re-fired many a times within a session, and every time they are issued, the session searches the shared pool for the parsed state, if it doesn’t get the parsed version, it will “hard parse” it, and if it exists in shared pool, it will still do a “soft parse”.

As we know “hard parse” is a costly operation, even a “soft parse” requires library cache latch and CPU overhead, which if aggregated is a significant number.

This parameter if set to a non-zero value (default is 50), improves the “soft parse” performance by doing a softer soft parse.


If enabled, oracle maintains a local session cache which stores recently closed cursors of a session.

To avoid this space getting misused or overused, oracle maintains the cursors for which there have been 3 parsed calls in the past, so all the SQL’s issued by a session are not here. Remember each cursor if pinned here, is not freeable and hence you may require more shared pool area.

A normal cursor in shared pool is sum of 2 components:
a) Heap 0 – size 1KB
b) SQL Area – size multiple of 4k

When we use session_cached_cursors only first component of cursor which is HEAP 0 is pinned in local session cache and if there is a call for re-parse for a statement, Oracle first checks the existence of the cursor in local cache and if found, it gets the address of the rest of the cursor which is in SQL Area (assuming if it is not aged out), so hereby saving CPU overhead and library cache latch contention.

How much it is getting used ?

SQL> select max(value) from v$sesstat
2 where STATISTIC# in (select STATISTIC# from v$statname where name='session cursor cache count');


This shows maximum value for session_cached_cursors in past. If this value= “session_cached_cursors” (init.ora parameter), you should consider increasing it.

If you want to see how is your session cache doing?

SQL> select cache/tot*100 "Session cursor cache%"
2 from
3 (select value tot from v$sysstat where name='parse count (total)'),
4 ( select value cache from sys.v_$sysstat where name = 'session cursor cache hits' );

Session cursor cache%

A value near 100 is considered very good. But you may still consider increasing this parameter if MAX(VALUE) in query one shows you equal number of cached cursor which you have set.

Conclusion: In an OLTP application, where the same set of SQL is issued number of times, one must configure this parameter more than its default value (50).
Also increasing this parameter will mean extra memory required for shared pool, so you must increase your shared pool when you use this parameter.


SQL can be aged out of shared pool in 2 cases:
a) When the cursor is closed: When the cursors are closed by application, they can still be in shared pool, until there comes a request for a new cursor and oracle needs to use LRU algorithm. SESSION_CACHED_CURSORS helps you in pinning (partial because it only pins HEAP 0) when the cursors are closed.

b) When the cursor is open: Oracle requires parsed state of SQL at PARSE and EXECUTE phase. If oracle parses (soft or hard) a statement, there is a likely hood that Oracle may age out your SQL out of shared pool after PARSE state if it requires to accommodate a new SQL coming its way. So in the EXECUTE state, there is a possibility that parsed information is lost and oracle parse it again.

CURSOR_SPACE_FOR_TIME if set to TRUE, ensures that SQL is not aged out before the cursor is closed, so in EXECUTE phase, you will have the PARSE information.

But this is generally a rare case and happens in a very highly active environment because to accommodate a new SQL, Oracle first check the free space and if it doesn’t get, it checks the closed cursors and see if any cursor can be aged out and when there is no space which can be reclaimed, Oracle comes to open cursors which are not EXECUTED.
This generally happens when the space of shared pool is too less.

I don’t suggest setting this parameter to TRUE in most of the cases.

There are some other serious tradeoffs also.
When a cursor is pinned, it cant be aged out and related memory cannot be freed for any new SQL and if you set this parameter to TRUE, you are telling Oracle to keep all the open cursors pinned and not freeable.
If you use this parameter, you are pinning the whole cursor not just the HEAP 0 which is 1k, you are pinning HEAP 0 (1k) + SQL Area (multiple of 4k) which makes shared pool life tough because of space issues.


As I said, I don’t suggest setting this parameter to TRUE in most of the cases. An alternative to set this parameter is to increase shared pool size or/and check your code on how many numbers of cursors you are opening/closing. That will be a better approach. Setting this parameter is like taking paracetamol without knowing the cause of fever.

Saturday, January 6, 2007

Should I rebuild or coalesce my indexes ?

Should I rebuild or coalesce my indexes ?

My last 2 notes on this blogs were specific to rebuilding (when and to which) indexes.

There is one more critical aspect which I wanted to discuss is the cost/effort related to rebuilding indexes.

Rebuilding an index is quite a costly operation and you must evaluate the benefit Vs effort before rebuilding an index.

Rebuilding (online) an index requires additional resources like space, cpu usage, time.

Here is one more option, which is less used or probably less popular “coalesce”.

Rebuild Vs Coalesce


  • Can move an index to a different tablespace

  • Resource consuming process

  • Takes more time

  • Creates a new tree

  • Shortens the height of an index if it was increased due to DML activities

  • Rebuilding can affect future DML’s because index becomes compact and for future DML’s index has to be extend dynamically.


  • Cannot move an index to a different tablespace

  • Comparatively less resource consuming

  • Takes relatively less time

  • Only merge the adjacent free space between leaf blocks within a branch

  • Doesn’t shorten height of index

  • Since coalesce doesn’t effect the total size and only frees up the unused space, it doesn’t affect future DML’s

Image Source: Oracle doc
Coalescing the index, frees up space of adjacent leaf blocks within a branch block. This way the number of blocks or extents which an index is using will not change but there will be more number of free blocks which can be used for future inserts or updates.

In an OLTP environment, where data is highly volatile, coalesce is better because it doesn’t shrink the index and the free space remains with the index segment.

Thursday, January 4, 2007

How to find indexes which are candidate for rebuild?

I'm writing this in response to Harvinder's comment.

I know it’s a very arguable question, but let me put my words and then you can comment.

I follow different approaches to find out indexes for rebuild

- Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3

- Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
First "Analyze the index with validate structure option" and then,

SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

But (a big but), the reason to rebuild should be because of poor performance of your queries using indexes. You should/must not rebuild indexes if you find both the above reason true for index if it is not coupled with poor SQL performance.

See this example:

SQL> analyze index TEST_INDX validate structure; -- First analyze the suspect index

Index analyzed.

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

------------ ---------- ---------- ----------- -------------
TEST_INDX 8 938752 29575 73342

You can see height of the index is 8 and also high number of DEL_LF_ROWS

SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';

Elapsed: 00:00:27.25

Execution Plan
Plan hash value: 870163320

Id Operation Name Rows Bytes Cost (%CPU) Time

0 SELECT STATEMENT 1 117 10 (0) 00:00:01


*2 INDEX RANGE SCAN TEST_INDX 115 13455 10 (0) 00:00:01

1 recursive calls
0 db block gets
764 consistent gets
757 physical reads

0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Now you rebuild the indexes

SQL> alter index TEST_INDX rebuild;

Index altered.

SQL> select count(*) from TEST_TABLE where TEST_COL like 'http://www.hots%';


Elapsed: 00:00:06.18

Execution Plan
Plan hash value: 870163320 - See here although it is using the same plan but still it is faster

Id Operation Name Rows Bytes Cost (%CPU) Time


0 SELECT STATEMENT 1 117 6 (0) 00:00:01


* 2 INDEX RANGE SCAN TEST_INDX 115 13455 6 (0) 00:00:01

15 recursive calls
0 db block gets
592 consistent gets
588 physical reads
0 redo size
516 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;

------------------------------ ---------- ---------- ---------- -----------
TEST_INDX 4 865410 15434 0

This clearly indicates rebuild helped my query performance. The height of index is reduced to 4 and DEL_LF_ROWS is 0

Now coming to second of part of Harvinder's comment.

Possible ways of Rebuilding.

- Online/Offline.

ONLINE Rebuild (8i onwards)

SQL> Alter index rebuild online;

This allows parallel DML to go on while Index is getting rebuild. Remember, online index requires more space, as it creates a new index before dropping the old one.

Index Rebuild is primarily a 3 step process

Prepare phase: Oracle locks the table for a fraction of second (actually not felt) to build index structure and populate data-dictionary.

Build phase: Most of the work is done in this phase. Oracle engine populates the index using the table and allows parallel DML's, parallel DML's uses a temporary journal table (b tree index like structure) to host the entries while the new index is getting populated.

Merge phase: Now the final stage, Oracle merges the new index with the journal table and drops the old index. Even during the merge phase, any changes to the table are recorded in the journal table and they get merged towards end of this phase.

9i onwards, online index rebuild feature includes Reverse Key, Function Based and Key Compressed indexes.

Offline Index rebuild.

SQL> Alter index rebuild;

This is conventional rebuild which was used(still available) before 8i. In this rebuild process, oracle drops the old index and creates a new one. In this process, no extra space is required, but parallel dml's are not supported.

Tuesday, January 2, 2007

When should I rebuild my indexes?

Need is necessary for any change. I hope all agree to this. So why many DBA’s (not all) rebuilds indexes on periodical basis without knowing the impact of it?

Let’s revisit the facts stated by many Oracle experts:

- B Tree indexes can never be unbalanced
- The distance of any leaf block (which contains the index value) from the root block is always same.
- B Tree performance is good for both small and large tables and does not degrade with the growth of table

When will rebuild help?

• When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based.

If your SQL’s use “=” predicate, rebuilding of index may not help you at all unless the DML’s (updates/deletes) on the table are cause of increasing the height of index. Due to heavy updates/deletes on a table, the space in the index can be left unused (unless it is reused) due to which block splits and if the splitting goes beyond the level of accomodation, the height of index increases by 1.
In simpler terms, unless you reduce the height (BLEVEL) of index, the time taken to reach the leaf block from root block will not change.

As explained above, the height of index can be decreased by rebuild of an index(only if height was increased to updates/deletes).

Only in case of range scan, rebuild (consider coalesce option also) may help.

Select * from emp where empno between 1000 and 2000;

In above case there could be lot of deleted/updated data which will also be read while reading between 1000 to 2000. Rebuilding will reclaim any unused space and hence the select could be faster.

• Index fast full scan/ Index full scan. (rare case)
If your SQL’s are designed in a way that mostly data is selected from index (it doesn’t goes to table). In this case the whole index is read for data not for redirecting to table.

Select count(empno) from emp; -- you have an index in empno
Select empno from emp -- you have an index in empno

Both the above SELECTs will fetch the data from Index only. Since the whole index is getting read, the space used by deleted rows is also read at the time of SELECT operation

Happy New Year - 2007

I wish everyone a very happy, prosperous, peaceful, joyful new year 2007.

With this, I want to thank you for the superb response to this blob.
I wanted to mention this earlier, but I was waiting for applauding mails to pause, so I could publish them in one go.

Great, Its really great, Thanks.

Ajay Kumar
Nice work I must say.... Well Done and BIG WOW

Mushtaque Syed

Very nice and interesting, knowledgeable,
I appreciate

Hi Sachin,

This certainly is a great blog and an excellent idea initiated. Just wanted to appreciate you of your hard work.


vinodh kumar
Hi Sachin,

What u have tried is a valuable thing.Thanks for taking time to prepare this blog.
I appreciate if u share ur knowledge more in tuning the SQL statements.But still what u have done is an excellent job.Keep going.

Vinodhkumar V.