Troubleshooting ORA-01555

A couple of days ago the app batch tasks that resides in product oracle 19.11 with the architecture of primary and secondary ran failed and subsequently reported a level 1 warning for application at 09:03, I went to log into that oracle database to look into it immediately when the project group sent that warning message to me.

Based on the advice of the app developer I found out the log file recording app batch tasks result then checked the last some lines of log file, it has expected to show ORA-01555 rollback segment “_SYS_XXXXXXX” too small for the PL/SQL package.procedure name. It’s difficult to make sure what stuff there has in those procedure and I have to ask the developer to check that procedure so that he is able to find some clue. Waiting for a while he checked a sql (due to the security reason I don’t put it on here) and at the same time I’ve also found that sql (including sql id, sql text and the error ORA-01555) in oracle alert log.

Next I went to check the history execution situation of that sql, OMG, it has produced two different execution plans becaus it has shown 2 number of plan_hash_value(s) and the current plan_hash_value has the more elapsed time (1800 seconds) but the previous plan_hash_value has just shorter elapsed time (200 seconds), obviously, the good execution plan used the three indexed and the bad one used only one index. I talked the developer whether he can rewrite the sql, he said it’s really hard for him to rewrite it, if so, I have to bind the good execution to the sql by sqlprofile approach. I am able to discover the sql profile of sql_id of the sql from dba_sql_profiles when I finished binding sql execution plan.

Furthermore, I’ve also checked the used ratio of the historical UNDO tablespace, it’s always 95% – 99% at 8:00-11:00 every day but other time periods it’s only 10% – 30%. By the way, the used ratio of UNDO tablespace has contained the sum(bytes) for the status of undo is in ACTIVE, UNEXPIRED. Unfortunately there has also occupied plenty of unexpired usage (not much too usage in active) in UNDO tablespace when I observed the used ratio of UNDO at that moment. If the sql stuff doesn’t exist in the unexpired of undo and oracle goes to fetch them as possible as it can now it will report the ORA-01555. Afterwards I’ve doubled the total size of undo tablespace.

The parameter undo_retention is also a considerable factor reflecting undo entry. Therefore, I’ve also adjusted it from the original 3600s to the 5400s.

That’s all stuff I shared with you, happy Sunday!

Oracle Tablespace Used Rate

Basically we use the following sql to get oracle tablespace used size, sum size and the used rate.

SELECT t.tablespace_name,
       nvl(s.used_bytes,0)/power(2,20)                                      AS used_size_mb,
       f.sum_bytes/power(2,20)                                              AS sum_size_mb,
       round(decode(f.sum_bytes,0,0,nvl(s.used_bytes,0)/f.sum_bytes*100),2) AS used_rate,
       f.autoext,
       f.status,
       t.bigfile
FROM dba_tablespaces t
LEFT JOIN (SELECT tablespace_name,
                  SUM(bytes) used_bytes
           FROM dba_segments
           WHERE segment_name NOT LIKE 'BIN$%'
           GROUP BY tablespace_name
          ) s
ON t.tablespace_name = s.tablespace_name
INNER JOIN (SELECT tablespace_name,
                   min(online_status)                                               AS status,
                   DECODE(SUM(DECODE(autoextensible,'NO',0,1)),0,'NO','YES')        AS autoext,
                   sum(case autoextensible when 'YES' then maxbytes else bytes end) AS sum_bytes
            FROM dba_data_files
            GROUP BY tablespace_name
           ) f
ON t.tablespace_name = f.tablespace_name
ORDER BY 1;

Recently one of my clients found out there exists a weird oracle tablespace from our monitor platform and the used rate of that tablespace on our platform has a big difference from the result after cusotmer uses his sql to query. Afterwards he responded to me probably some tables has the lob data within that tablespace and that lob data have been dropped (not purge), in other words, they goes into oracle recyclebin directly. According to his description I did some test, yes, it’s right! After adjusting the previous sql on our monitor platform he told me the oracle tablespace used rate is the same as his.

Hence I’ll share this case with you in this blog post.