Month: June 2021

CRON not executing scripts – Time zone issue

I ran into a strange issue today on a SunOS 5.11 11.4.24.75.2 sun4v sparc sun4v server. When I used the “date” command, I would see the current date and time displayed as (for example) 15:25 ET. I then created a CRON entry for 15:35 ET. However, the script would not execute at the time defined in CRON. I checked the usual permissions etc., but nothing worked. After some trial and error, a colleague identified that the user’s profile had the following entries:

TZ="EST5EDT"
export TZ

The server was actually running GMT, but the time was being displayed in ET. The scripts I was scheduling in the displayed time (ET) were not executing because CRON was scheduling the jobs in GMT.

After the above two lines were commented out, and the CRON entries defined in GMT, the scripts executed at the expected times.

Large number of deletes causing table fragmentation resulting in poor query performance

I was asked to look into a nightly script that had significantly increased in elapsed time. The script was rather simple in that it deleted rows older than (today’s date – 3 months) from multiple tables. I ran an ADDM report on the database (12.1.0.2.0 – 64bit Production), and one of the deletes popped up in the report:

   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "75csungpxhvv6".
      Related Object
         SQL statement with SQL_ID 75csungpxhvv6.
         delete from PROD_SCHEMA.PROD_TABLE where ORDER_DT<20201212
   Rationale
      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      I/O and Cluster wait for INDEX "PROD_SCHEMA.PROD_TABLE_ORDER_DT" with object ID
      322364 consumed 100% of the database time spent on this SQL statement.

The table in question was not large; a few 100 thousand rows with reasonable row length; no large varchars etc. The obvious issues were quickly eliminated; the table has current stats, there was an index to exactly match the predicate, and the optimizer was choosing the index. However, I did notice that the table was occupying 28 GB of space. A quick calculation of # of rows multiplied by row length indicated that it should be occupying less than 1 GB of space approx.

I ran the below query and confirmed that most of the space was empty

set line 200;
set pages 9999;
col owner                format a15               heading "Owner"
col table_name           format a30               heading "Table Name"
col avg_row_len          format 999,999,999       heading "Avg Row Len"
col num_rows             format 999,999,999,999   heading "Number of Rows"
col TOTAL_SIZE_MB        format 999,999,999       heading "Total|Size|(MB)"
col ACTUAL_SIZE_MB       format 999,999,999       heading "Actual|Size|(MB)"
col FRAGMENTED_SPACE_MB  format 999,999,999       heading "Frag|Size|(MB)"
col percentage           format 999.99            heading "Per|Cent|Frag"
SELECT   owner
        ,table_name
        ,avg_row_len
        ,num_rows
        ,Round((( blocks * 8 / 1024 )), 2) "TOTAL_SIZE_MB"
        ,Round(( num_rows * avg_row_len / 1024 / 1024 ), 2)  "ACTUAL_SIZE_MB"
        ,Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024 / 1024 ) ), 2) "FRAGMENTED_SPACE_MB"
        ,Round(( Round(( ( blocks * 8 / 1024 ) - ( num_rows * avg_row_len / 1024
                                                  / 1024
                                                ) ), 2
                     )
                     / Round((( blocks * 8 / 1024 )), 2) ) * 100, 2)  "percentage"
FROM     dba_tables
WHERE    num_rows > 0
  and    table_name = 'PROD_TABLE'
  and    owner = ' PROD_SCHEMA '
order by FRAGMENTED_SPACE_MB desc 
;

We were able to ask the application for a small outage to export (data pump), truncate, and import that data from the table. After this, the query that used to take almost 8 minutes started executing in less than a second.