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;
Advertisements

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

 

Vah-Reh-Vah Query – Cartesian join to find Patterns

I was looking at the employee master and a thought came to mind. I wanted to see, how many people were actively on Payroll during any given week and to compare this over several years to see a trend.

Assumptions

  • PL/SQL Query, since this was an Oracle DB
  • Checked ALL_TAB_COLUMNS to make sure there are more than 7320 rows (365 days X 20 years). You can use any table as long as they have enough records.
  • FRIDAY Being the last day of the Working Week
  • The Employee table is SCD1. Rehired employees are not counted as part of this exercise
  • EMPLOYEE_DOJ : Is Employee Date Of Join or the Rehire Date
  • TERMINATION_DATE : Is NULL or the Actual Termination Date
  • 500K Employee Records covering 20+ years of engagement

Here is the Query

WITH WORK_WEEKS AS
 (SELECT DATE_SEQ AS WEEK_END_DATE
 FROM (SELECT TRUNC (SYSDATE - ROWNUM + 6) AS DATE_SEQ,
 TRIM (TO_CHAR (TRUNC (SYSDATE - ROWNUM), 'DAY')) AS DAY
 FROM ALL_TAB_COLUMNS) F
 WHERE DAY = 'FRIDAY' AND DATE_SEQ >= SYSDATE - (366 * 20))
 SELECT WEEK_END_DATE, COUNT ( * ) AS ACTIVE_TOTAL
 FROM WORK_WEEKS, EMPLOYEE_MASTER
 WHERE (EMPLOYEE_DOJ < WEEK_END_DATE AND NVL (TERMINATION_DATE, WEEK_END_DATE) >= WEEK_END_DATE)
 GROUP BY WEEK_END_DATE
 ORDER BY WEEK_END_DATE

WORK_WEEKS : I am using the ALL_TAB_COLUMNS to get to the auto generated ROWNUMBER’s, which is then subtracted from SYSDATE to generate DATE_SEQ (date sequences). Using the Date Sequence,  I pick the FRIDAY’s to get the WEEK_END_DATE.

The next step is to join the EMPLOYEE_MASTER and COUNT any employee hired before the WEEK_END_DATE and Terminated after the WEEK_END_DATE. Since the WORK_WEEK and EMPLOYEE_MASTER are not directly joined, we get a Cartesian Join.

Exporting the results into Excel, I had a nice chart. Now you get the idea.

chart