Identify Locked objects that are Invalid

Here is a query that is useful to identify locks on objects that are Invalid, preventing the object from being recompiled. These sessions need to be identified and stopped or killed.

with src as
(
    select sid, ao.owner, object, status
                 from v$access v, all_objects ao
                where ao.object_name = v.object
                  and ao.status= 'INVALID'
                )
select v.sid,
       src.owner,
       src.object
       serial#,
       username,
       osuser,
       logon_time,
       program,
       action,
       src.status
  from v$session v, src
where v.sid = src.sid;

Oracle Performance Issues – FTS

The software that we design needs to be periodically reviewed and optimized for performance. We have the option of throwing cheaper hardware (horizontal or vertical scaling) to mitigate performance issues, not to mention licensing requirements. Unless revisited and optimized, the solution does not scale well. Leaving some boiler plate code that can be reused causes more headaches down the line.

While analyzing one such performance issue, I identified that a dozen staging tables had silently grown to several gigabytes over the years. Even though only few thousand records get processed during each pass, a SELECT query against these tables was using Full Table Scans. Under stress, the performance issue was noticeable.

Now, I had to know what other queries were being executed that was using a full table scan. The query below helps with just that.

WITH src
     AS (  SELECT TRUNC (MAX (sn.begin_interval_time)) last_used,
                  ds.owner,
                  hsp.object_name,
                  ROUND (ds.bytes / 1048576, 0) size_in_mb,
                  hs.sql_id
             FROM dba_hist_sql_plan hsp,
                  dba_hist_sqlstat hs,
                  dba_hist_snapshot sn,
                  dba_segments ds
            WHERE     hsp.object_owner <> 'SYS' --Exclude System Objects
                  AND hsp.object_owner = ds.owner
                  AND hsp.object_name = ds.segment_name
                  AND ds.bytes > 20971520 --Find Objects Greater than 20 MB
                  AND hsp.operation LIKE '%TABLE ACCESS%'
                  AND hsp.options LIKE '%FULL%'
                  AND hsp.sql_id = hs.sql_id
                  AND hs.snap_id = sn.snap_id
         GROUP BY owner,
                  object_name,
                  hs.sql_id,
                  ds.bytes
         ORDER BY object_name)
  SELECT src.*, a.sql_text
    FROM src LEFT OUTER JOIN v$sqlarea a ON a.sql_id = src.sql_id
   WHERE last_used > SYSDATE - 7
ORDER BY object_name, last_used

The size of the object is denoted in column size_in_mb, to help identify high value items.

If the sql_text columns gets Truncated, go to the below view instead and use the SQL_ID. Some parsing may be required or use LISTAGG to concatenate the rows to return a single column.

select sql_text from   v$sqltext
 where sql_id = 'b2t8xugd549k5'
 order by piece

PL/SQL – Find the first and last day of the month

I have always used the Financial / Manufacturing Calendar as setup in Oracle ERP, extracted into a Date Dimension. The query below calculated the First and Last of a month outside of the Financial Calendar.

There is an inbuilt function called LAST_DAY that calculated the last date of the provided date. The first day however can be achieved thru a Format as shown below.

SELECT TRUNC (SYSDATE, ‘MM’) AS start_date,
TRUNC (LAST_DAY (SYSDATE)) AS end_date
FROM DUAL

Safely convert VarChar that contains Numbers and Text to a Number – PL/SQL

The PL/SQL function TO_NUMBERS throws an exception ORA-01722: invalid number exception if any of the field values have a String or Alpha Numeric value.

Writing a PL/SQL Function or Stored Procedure to Capture the Exception and return a number, like the example shown below (CONVERT_TO_NUMBER) seems to Crash the AIX Server hosting the database under a heavy workload. So test this function before implementing it.

CREATE OR REPLACE FUNCTION CONVERT_TO_NUMBER(p_string IN VARCHAR2 )
  RETURN NUMBER
IS
  l_value NUMBER;
BEGIN
  l_value := to_number( p_string );
  RETURN l_value;
EXCEPTION
  WHEN others THEN
    RETURN 0;
END;

I have now moved to using the Query instead and this is backward compatible with Oracle 9.2 also

 TO_NUMBER(
 CASE
 WHEN MY_FIELD IS NULL THEN '0'
 WHEN LENGTH (TRIM (TRANSLATE (MY_FIELD, '0123456789', ' '))) IS NULL
 THEN MY_FIELD
 ELSE '0'
 END) AS MY_FIELD_NUM