Monitor progress of long running processes

This time I want to share how I use v$session_longops to monitor Oracle long running queries but also how to create your own entries in this view. When monitoring long lasting operation I'm interested what is running now, and what was finished lately. For this purposes I use query:

SELECT sid,
  serial#,
  opname,
  target_desc,
  percent,
  sofar,
  totalwork,
  to_char(start_time,   'hh24:mi:ss') start_time,
  to_char(efin,   'hh24:mi:ss') estimate_fin,
  case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken
FROM
  (SELECT sid,
     serial#,
     opname,
     target_desc,
     sofar,
     totalwork,
     to_char(CASE
             WHEN totalwork = 0 THEN 1
             ELSE sofar / totalwork
             END *100,    '990') percent,
     start_time,
     last_update_time,
     start_time +((elapsed_seconds + time_remaining) / 86400) efin
   FROM v$session_longops
   ORDER BY  CASE
             WHEN sofar = totalwork 
                THEN 1
                ELSE 0 END,
          efin DESC)
WHERE sofar <> totalwork or rownum <= 20;

It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison

efin < sysdate-1/10000

is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Sample output:

Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:

declare
  v_rindex pls_integer;
  v_slno   pls_integer;
begin
  v_rindex := dbms_application_info.set_session_longops_nohint;
  for r_qry in (select t.*, rownum rn, count(*) over () cnt 
                  from ALL_OBJECTS t ) loop
    dbms_application_info.set_session_longops
      ( rindex =>  v_rindex,
        slno   =>  v_slno,
        op_name => 'ALL_OBJECTS processing',
        target  =>  0,
        target_desc => 'ALL_OBJECTS',
        context     => 0,
        sofar       => r_qry.rn,
        totalwork   => r_qry.cnt,
        units       => 'loops'
      );
    dbms_lock.sleep(0.1);
  end loop;
end;

So what is important here:

• rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
• rownum is used to get row number; it is passed to sofar parameter
• analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
• rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed

Additionaly you can add when others handler to set progress to 100%, but you do have to do that. Broken flag described above should work fine, and you know if process ended normally or with errors. Full specification of dbms_application_info.set_session_longops

Paweł

Table that cannot be deleted

Today I run into interesting situation on one of development machines. I was drooping all tables of one user and after that there was still one table with name SYS_IOT_OVER_54321. Seeing that name I've concluded that it must be an overflow table for some Index Organized Table. But there were no other table in that schema. Of course I was not able to delete it.
Than I've realized that there are some tables in recycle bin. This situation is inconsistent as table is in recycle bin but its overflow area is still in normal table list.

Here is test case:

SQL> create table IOT_TEST
  2  (a_pk number not null primary key
  3  ,a_number number
  4  ,big_string varchar2(4000)
  5  ) organization index
  6  including a_number overflow tablespace users;

Table created.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             IOT_TEST                                         
IOT_TEST                                                                        

So lets drop it and see what will happen:

SQL> drop table IOT_TEST;

Table dropped.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

TABLE_NAME                     IOT_NAME                                         
------------------------------ ------------------------------                   
SYS_IOT_OVER_14580             BIN$rKpJH0NuROKt+Woa00+hMg==$0                   

As you can see we still have IOT Overflow table that cannot be deleted:

SQL> drop table SYS_IOT_OVER_14580;
drop table SYS_IOT_OVER_14580
           *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

The only way to get rid of that table is to purge recycle bin:

SQL> purge recyclebin;

Recyclebin purged.

SQL> select table_name, iot_name
  2  from user_tables where table_name like '%IOT%';

no rows selected

In my opinion it is bug. I haven't yet reported it on metalink, but I'll fill SR soon. I've confirmed it on Oracle 10g R2 (versions: XE, 10.2.0.1, 10.2.0.2, 10.2.0.3)

Paweł

„Do not use Linux, please, pirate our software” - Microsoft

It seems that Microsoft wants You to pirate it's software. According to The Register, Jeff Raikes, Microsoft business group president, said that if You have to pirate, You should pirate Microsoft software. The idea behind it is that some percentage of pirates become paying customers. Microsoft wants You to:

• pay and use MS software,
• use MS software not paying for it; might be in future you will pay,
• not use other software. Microsoft does not want You to switch to competing software, especially legal royalty-free FLOSS software such as Linux, OpenOffice.org or Mozilla.org

So why they implemented Windows Genuine Advantage Programme, if they do not want to push You to be legal? Paweł

Polish Students won ACM Programming Contest

Today students from Warsaw University won ACM-International Collegiate Programming Contest. They solved 8 tasks out of 10, see Final results.

Congratulations to my compatriot,
Paweł

Moving/Adding Control file when using SPFile

Adding controlfile or moving to new location is tricky when your database was started with server parameter file (spfile). It is because You cannot edit spfile when database is not running.
Method 1 – preferred by me:

1. alter system parameter to point to new (planed) location of controlfiles

   alter system set control_files
   = '/dir1/CONTROL01.CTL'
   , '/dir2/CONTROL02.CTL'
   , '/dir3/CONTROL03.CTL'
   scope=spfile;

2. shutdown database,
3. copy one of controlfile to new locations,
4. startup database.

Method 2:

1. shutdown database,
2. copy one of controlfile to new locations,
3. copy spfile to pfile:

   create pfile from spfile;

4. manually edit pfile,
5. copy pfile back to spfile:

   create spfile from pfile;

6. startup database.

Cheers, Paweł

Technorati

I've just created my Technorati Profile, so this post is just to interrelate this blog with my Technorati account.
Paweł

the Month of PHP Bugs

For all PHP developers, web admins and users this might be very interesting. the Month of PHP Bugs is an initiative to improve the security of PHP. It focuses on PHP Core vulnerabilities that are dangerous for web servers with applications written in PHP. It is done in hope that flaws that are publicly disclosed will get fixed quickly.

Cheers, Paweł

Yet another tokenizer in Oracle

I was in need to have function that transforms string into table of words. Also words can be separated by many different chars. For this purpose i've created function:

create or replace function tokenizer
  (p_string in varchar2
  ,p_separators in varchar2
  ) return dbms_sql.varchar2s
is
  v_strs dbms_sql.varchar2s;
begin
  with sel_string as 
      (select p_string fullstring 
         from dual)
  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   from (select beg, lead(beg) over (order by beg) end_p, fullstring
           from (select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0
                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))
    where end_p is not null
      and end_p>beg+1;
  return v_strs;
end;

Usage is very simple. First parameter is string that should be tokenized, second parameter is string with characters that should be treated as word separators. Example:

SQL> set serveroutput on
SQL> declare v_strs dbms_sql.varchar2s;
  2  begin
  3    v_strs := tokenizer('I was in need to have function that transforms string into table of words.'
  4        , ' ,:;.<>[]{}()');
  5    for i in v_strs.first..v_strs.last loop
  6      dbms_output.put_line(v_strs(i));
  7    end loop;
  8  end;
  9  /
I                                                                               
was                                                                             
in                                                                              
need                                                                            
to                                                                              
have                                                                            
function                                                                        
that                                                                            
transforms                                                                      
string                                                                          
into                                                                            
table                                                                           
of                                                                              
words                                                                           

PL/SQL procedure successfully completed.

Let me explain some elements

  with sel_string as 
      (select p_string fullstring 
         from dual)

is just to bind string parameter into SQL just once. Otherwise it will have to be binded in many places.

                 select beg, fullstring
                   from (select level beg, fullstring
                           from sel_string
                         connect by level <= length(fullstring))
                   where instr(p_separators ,substr(fullstring,beg,1)) >0

Internal select generates numbers from 1 to length of string. Outer select chooses only these rows (numbers) that are separators in string.

                 union all
                 select 0, fullstring from sel_string
                 union all
                 select length(fullstring)+1, fullstring from sel_string))

adds separator at beginning and end of string, so first and last work can be recognized.

 select beg, lead(beg) over (order by beg) end_p

gives as positions of two consecutive separators, and finally

  select substr(fullstring, beg+1, end_p-beg-1) token
   bulk collect into v_strs
   ...
    where end_p is not null
      and end_p>beg+1;

select words.

Might be you find it useful,
Paweł