Square DBA – Oracle DBA Talk

Oracle RAC Project

I will be starting a new project where I will be using Oracle RAC 11G. The setup will be a two node RAC setup running on 3PAR’s F400

I will try to document as much as I can here on Square DBA, so stay tuned  . . . .

Oracle’s Analytic Functions to Process CDRs

I love Oracle’s analytic functions. Today I needed to process call records (CDRs) from a phone switch. I needed to find telephone numbers that had 5 or more 20 minute calls within any 60 minute period of time. I needed to scan a month’s worth of calls, so about 200 million records.

My first inclination was to write a PL/SQL procedure to calculate this for me as I did not think straight SQL would get the job done. I was wrong Oracle’s analytical functions worked nicely and very fast. Here is the code I used.

select count(*) OVER (PARTITION BY called_station_id ORDER BY
call_begin_tmst RANGE 1/24 PRECEDING) as cnt
, called_station_id
    from o1dw.cdr_detail
           where summary_date between to_date('20090701','YYYYMMDD')
 and to_date('20090731','YYYYMMDD')
          and billing_duration_sum > 1200; 

This only looks at calls over 20 minutes (billing_duration_sum > 1200) and then counts how many times a phone number (called_station_id ) was seen in any 60 minute period of time (RANGE 1/24 PRECEDING). I was able to scan 200 million records in about 20 minutes. The process worked perfectly.

SQL Worksheet gone in 11g client

I just reinstalled my PC and installed a clean OS. I do this every once in a while. I find just formatting and starting over is better that trying to clean the machine up.

My last load consisted of the Oracle 10g client which came with SQL Worksheet. I like SQL Worksheet because it’s not a full dumbed down GUI and yet it gives me a few point-n-click features. Well it looks like Oracle decided to dump a standalone version of SQL Worksheet for embedding it in SQL Developer. I am not a fan.

I feel confined with this new version. I can launch multiple sessions but they all appear within the SQL Developer framework. I was also viewing object using the Enterprise Manager client, well that is gone too. I know I can view all of my database object in SQL Developer, but I don’t like the change.

So now I am using Enterprise Manager Grid Control and SQL Developer. I will use these tools instead of installing the 10g client since Oracle is obviously going in that direction, but I am not doing it willingly.

Truncate date range partitions

Have you ever needed to truncate old range partitions that are broken up by day? I do all the time. Much of my data warehouse data is located in range partitioned tables. These are generally partitioned by day. I put together the following script to take care of the job for me.

CREATE OR REPLACE  PROCEDURE "LCR"."TRUNCATEOLDPART"  (
 i_table_name IN varchar2,
 i_table_owner IN varchar2,
 i_start_day IN varchar2,
 i_end_day IN varchar2
)

as
   v_value varchar2(255);
   v_name varchar2(30);
   p_s_date date := trunc(sysdate-i_start_day);
   p_e_date date := trunc(sysdate-i_end_day);    

begin
   FOR c1 IN (select high_value, partition_name
     from all_tab_partitions
    where table_name = i_table_name and table_owner = i_table_owner)

   loop

   v_value := c1.high_value;
   v_name := c1.partition_name;

      if to_date(substr(v_value,11,10),'YYYY-MM-DD') between p_s_date
      and p_e_date then dbms_output.put_line('Start Truncating
      Partition '||substr(v_name,1,100)||' at '||
      to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      EXECUTE IMMEDIATE 'ALTER TABLE '||i_table_owner||'.'||
      i_table_name||' truncate partition '||v_name;
      dbms_output.put_line('Completed Truncating Partition '||
      v_name||' at '||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
      dbms_output.put_line('------------------------------------');
      end if;
   end loop;
end;

The usage of this script is as follows.

execute dbms_output.enable(null);
execute lcr.truncateoldpart(table_name,table_owner,
oldest_days_to_truncate, newest_days_to_truncate);

So to truncate all partitions between 60 and 30 days old on the LCR.ENUM_CDR table just run:

execute dbms_output.enable(null);
execute lcr.truncateoldpart('ENUM_CDR','LCR',60,30);

Output Will look like this:

Start Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30
Completed Truncating Partition SYS_P2505671 at 2009-09-22 13:59:30
------------------------------------
Start Truncating Partition SYS_P2505651 at 2009-09-22 13:59:30
Completed Truncating Partition SYS_P2505651 at 2009-09-22 13:59:31

One caveat with a script like this is permissions. This works fine for running again any table in the schema owned by the package owner. If you try to run this against another schema you will get permission errors. To solve this you need to grant “drop any table” to the package owner. Obviously this is potential security risk. I prefer to just re-create the package in every schema that needs to perform this function. That way I don’t need to give any one user the ability to drop any table in my database.

Finding large files in Linux

It’s easy to track down your Oracle datafiles.

select BYTES, NAME from v$datafile;

Will tell the size of each datafile and it’s location on the server. If your server is like mine you will large non-Oracle files on the server as well. From time to time I need to find these large files to see if they can either be compressed, backed up and removed, or deleted all together. Here is a handy little statement I use on my Redhat servers.

find {/path/to/directory/} -type f -size +{size-in-kb}k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

so when I am looking for 200+ MB files on a specific mount I use

find /old_array -type f -size +200000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }' 

or if I want to find files over 500MB in the current directory I will use

find . -type f -size +500000k
-exec ls -lh {} \; | awk '{ print $9 ": " $5 }' 

ORA-01502: index or partition of such index is in unusable state

Do you ever get and ORA-01502? This error code says your index is in an unusable state. It’s easy to find these indexes.

select index_name, status, owner from
dba_indexes where STATUS = 'UNUSABLE';

If you want to build the rebuild SQL that is easy too.

select 'alter index '||owner||'.'||index_name||' rebuild;' from
dba_indexes where STATUS = 'UNUSABLE'; 

Feds OK Sun acquisition

Oracle Corp. said Thursday that the U.S. Department of Justice has approved its proposed acquisition of Sun Microsystems Inc. and terminated the waiting period under the Hart-Scott-Rodino Act.

Santa Clara-based Sun stockholders approved the transaction on July 16. Closing of the transaction is subject to certain conditions, including clearance by the European Commission.

The $7.4 billion acquisition by Redwood City-based Oracle (Nasdaq: ORCL), which has a campus in Rocklin, is being closely watched by the European Union, with questions raised about Sun’s Java software language.

Killing your own session

Ever wanted to kill your own session? You know commit suicide; This works nicely:

alter session set events 'immediate crash';

Nothing like a ORA-03113: end-of-file on communication channel to brighten your day.

Starting the Oracle Enterprise Manager agent in 11g

Do you need to start the Oracle Enterprise Manager agent in an 11g grid control environment? It’s pretty simple, here is the command.

emctl start agent

The biggest issue I see with this is people using the wrong emctl executable. Very often you will have a multiple executables. You may possibly have an emctl in your Oracle home for your database and a separate executable in another home that was setup for grid control. Make sure you are running the correct emctl executable.

Finding currently running SQL

Have you needed to find out what SQL was running in the database? Much of my time is spent on out data warehouse where long expensive queries may be running. When someone calls to ask why things are running slow one area to look is what SQL are they running. The database may not be running slow, but their SQL is.

When Oracle executes a query, it places it in memory. This allows Oracle to reuse the same SQL if needed by the executing session at a later date or by another user that may need the same SQL statement. Oracle assigns a unique SQL_HASH_VALUE and SQL_ADDRESS to each SQL statement. By Oracle doing this, it provides us a method to determine who is executing what SQL based on the join columns from the V$SESSION of SQL_ADDRESS & SQL_HASH_VALUE to the V$SQLAREA view and columns ADDRESS and HASH_VALUE.

Here is a small script to determine what SQL is currently executing.

elect sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null 

Often you may have an active session and actually show a valid SQL statement through the V$SESSION and V$SQLAREA views that seems to be taking very long. Users may be complaining that their query is “stuck” or not responsive. You as a DBA can validate that the SQL they are executing is actually doing something in the database and not “stuck” be simply querying the V$SESS_IO view to determine if the query is in fact “stuck” or is actually doing work within the database. Granted, this does not mean there isn’t a tuning opportunity but you can at least show the SQL is working.

Here you can see the I/O being done by an active SQL statement.

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null 

If by chance the query shown earlier in the V$SQLAREA view did not show your full SQL text because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. It is a piece by piece of 64 characters by line, that needs to be ordered by the column PIECE.

Here is the SQL to show the full SQL executing for active sessions.

select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece 

If you really just want to see what sessions are active this SQL will help.

elect sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
       username,
       type,
       status,
       process,
       sql_address,
       sql_hash_value
  from v$session
where username is not null