An interesting problem about systimestamp

Recently we scheduled a job to monitor the orders process status. The statement looks like,


SELECT *
FROM ORDERS
WHERE STATUS_LAST_UPDT_TS
BETWEEN TRUNC (SYSTIMESTAMP) AND SYSTIMESTAMP - 30 / 1440

The job is scheduled at 3PM everyday so it should get the orders which were last updated between midnight and 2:30PM. However we found orders placed at 2:30 to 3:00 PM were returned as well. After some investigations I found out the root cause is the data type of systimestamp.

First, let us go over some basic knowledge of timestamp.

Oracle timestamp is an extension of the date datatype. It can store date and time data, including fractional seconds.

Timestamp with time zone is an extension of the timestamp datatype.It includes the time zone information.


Internally the timestamp type takes 11 bytes of storage and the timestamp with time zone takes 13 bytes.

Similar to sysdate, the function systimestamp returns the current timestamp with time zone of the database. localtimestamp returns the timestamp with the time zone of current session. For example,

alter session set time_zone = '-05:00';

Session altered.

select systimestamp,localtimestamp from dual;

SYSTIMESTAMP                                         LOCALTIMESTAMP
--------------------------------------------------    --------------------------------------------------
27-DEC-12 1.47.42.257748 PM -06:00       27-DEC-12 2.47.42.257787 PM

Simiare to sysdate, we can do basic date arithmetic on systimestamp.

alter session set nls_date_format = 'DD-MON-YY HH.MI.SS AM';

Session altered.

select systimestamp, systimestamp - 30/1440 from dual;

SYSTIMESTAMP                                         SYSTIMESTAMP-30/1440
--------------------------------------------------    --------------------------------------------------
27-DEC-12 1.53.48.752908 PM -06:00        27-DEC-12 1.23.48 PM

It works most of the time, but there is some problem with it. Unfortunately we run into this problem.


Here is our story. Our database is in Eastern Time Zone (-05:00) while we are in Central Time Zone (-06:00).

SQL> ALTER SESSION SET TIME_ZONE='-6:00';

Session altered.

SQL> SELECT SYSTIMESTAMP
           FROM DUAL
           WHERE TO_TIMESTAMP_TZ('2012/12/27 14:25 -5:00','YYYY/MM/DD HH24:MI TZH:TZM')
          BETWEEN TRUNC(SYSTIMESTAMP) AND SYSTIMESTAMP - 30/1440;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-DEC-12 02.33.18.399155 PM -05:00

1 row selected.

This is not correct. The current time is 2:33 PM so 2:25 PM should not fall between trunc(systimestamp) and timestamp - 30/1440.

SQL> ALTER SESSION SET TIME_ZONE='-5:00';

Session altered.

SQL> SELECT SYSTIMESTAMP
           FROM DUAL
           WHERE TO_TIMESTAMP_TZ('2012/12/27 14:25 -5:00','YYYY/MM/DD HH24:MI TZH:TZM')
          BETWEEN TRUNC(SYSTIMESTAMP) AND SYSTIMESTAMP - 30/1440;

no rows selected

This is the right answer. But why does it happen? 

The trick is. systimestamp is a timestamp with timezone but (systimestamp - 30/1440) is not longer a timestamp, it is date type. It can be easily demonstrated by,


SQL> select dump(systimestamp),dump(systimestamp-30/1440) from dual;

DUMP(SYSTIMESTAMP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DUMP(SYSTIMESTAMP-30/1440)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=188 Len=20: 7,220,12,27,19,39,0,0,59,94,163,144,251,0,5,0,0,0,0,0
Typ=13 Len=8: 7,220,12,27,14,9,0,0

Snapshot Too Old after SAN outrage

Recently we experienced an SAN outrage. The databases was brought up after SAN was fixed. However we received an ORA-01555 error “Snapshot too old” when we run a SQL statement, e.g.

select * 
  from scott.orders 
 where order_date between trunc(sysdate - 1) and trunc(sysdate);

ORA-01555 is caused by Oracle read consistency mechanism. When the long run query is not able to find the before-image from undo segment, ORA-01555 occurs. But I was very sure it is not our case, since the application server was still down and there were sessions only from dba. And I also noticed the SQL statement was successful without ORA-01555 if

select * 
  from scott.orders 
 where order_date between trunc(sysdate - 2) and trunc(sysdate - 1); 

It makes me think there is data block corruption for the data in the time range of trunc(sysdate - 1) and trunc(sysdate).


Oracle Metalink document 787004.1 describes a very similar situation, except that we have xml data type. So I continued the investigation.

select order_id 
  from scott.orders 
 where order_date between trunc(sysdate - 1) and trunc(sysdate);

select order_xml 
  from scott.orders 
 where order_date between trunc(sysdate - 1) and trunc(sysdate);

The first SQL was successful. The second got the same ORA-01555 error. Now it is confirmed order_xml column was corrupted.


I wrote a script to find out the corrupt data block,

DECLARE
   n   NUMBER;
   s   VARCHAR2 (4000);
BEGIN
   FOR c1 IN (SELECT *
                FROM scott.orders
               WHERE empno)
   LOOP
      BEGIN
         s := XMLType.getClobVal (c1.order_xml);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('ORA' || SQLCODE || ': order_id = ' || c1.order_id);
      END;
   END LOOP;
END;

Oracle Listener Security Alert: A Possible 13 Years Old Oracle Bug?

On Monday 4/30/2012 Oracle finally released a security alert CVE-2012-1675 to address the “TNS Listener Poison Attack” in Oracle database. According to Joxean Koret, "the bug is probably available in any Oracle Database version since 1999 (Oracle 8i) to the latest one (Oracle 11g) ...  The bug was reported to Oracle in 2008 so it only took them 4 years to fix the vulnerability since reported."

A description of the security alert is available at http://www.oracle.com/technetwork/topics/security/alert-cve-2012-1675-1608180.html

A comprehensive vulnerability explanation http://seclists.org/fulldisclosure/2012/Apr/204

There is no current CPU or PSU to fix the bug. Oracle provided a solution to fix the bug. Please follow Support Note 1340831.1 for RAC databases and Support Note 1453883.1 for non-RAC databases. DO NOT blame me if links are not working. Try the links during the day or search in Oracle Metalink.

Error during the installation of Quest Toad

I have used Quest Toad for many years. I like Quest Toad as many other Oracle DBAs, especially when I worked for Quest ;-)

This week I finally got rid of my old laptop with Window XP and received a new laptop with 64-bit Windows 7. I installed Oracle 64-bit client and then installed Toad. The installation was successful without any error. However an error message "You have no Oracle clients installed" popped out when I launched Toad. The error is usually caused by no Oracle client installed, or Toad is installed before Oracle client, which is obviously not my case. I uninstalled and re-installed both Oracle client and Toad but got same error message. It made me think there might have some trick. So I searched online and found the following document,

https://support.quest.com/Search/SolutionDetail.aspx?ID=SOL39689

The reason is Toad does not support a 64-bit Oracle client! Everything works fine once I installed 32-bit Oracle client software.

Load 500,000 rows in 100 minutes?

My friend is a data analyst. He frequently loads the data from Access into MS SQL Server using OLE DB. Yesterday he asked me if there is a fast way to load data. I told him to save Access data into csv, then  using bcp to insert into MS SQL Server. He said he will try bcp, since it makes him crazy to load 500,000 rows in 100 minutes using OLE DB.

Hold on a second, load 500,000 rows in 100 minutes? I am pretty sure OLE DB is not the primary reason. I suspect there are indexes and constraints on the table, which causes the problem. I suggested him to remove indexes on the table before loading the data, and re-create indexes after the load.

OK, here is my conclusion: when a user asks a question, please have a better understanding why he asks this question. That is the way of "good communication".

Performance of Lookup Function

Recently one of my developer friends experienced a performance problem. They run a PL/SQL lookup function over a table with one million rows. Every time the query failed since it reached the CPU usage limit specified by CPU_PER_CALL.

My suggestion is, DO NOT use lookup function if you can do it in SQL statement. SQL statements always have better performance then PL/SQL. Here is my example.

Create two tables,

create table scott.t as select * from dba_tables;

create table scott.u as select * from dba_users;

Now we create a lookup function, 

create function scott.get_uid(l_name in varchar2) return number

as

  ret number;

begin

  select user_id into ret from scott.u where username=l_name;

  return ret;

end;

/

alter system flush shared_pool;
alter system flush buffer_cache;

Now we run two queries,

set timing on

alter session set sql_trace=true; 

select user_id from scott.t, scott.u where t.owner=u.username;

Elapsed: 00:00:01.74

select scott.get_uid(owner) from scott.t;

Elapsed: 00:00:06.86

alter session set sql_trace=false;

Two queries return the same results. The join statement takes 1.74 seconds but the lookup function consumes 6.86 seconds. Why? Look into the trace file and we will find the lookup function is executed 2475 times!!!

********************************************************************************

SQL ID : bta262awhyx23

select scott.get_uid(owner)

from

 scott.t

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      166      0.96       2.80          0        242          0        2475

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      168      0.96       2.81          0        242          0        2475

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: SYS

Rows     Row Source Operation

-------  ---------------------------------------------------

   2475  TABLE ACCESS FULL T (cr=242 pr=0 pw=0 time=68 us cost=25 size=41854 card=2462)

********************************************************************************

SQL ID : 574gd4b0c2rxr

SELECT USER_ID

FROM

 SCOTT.U WHERE USERNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute   2474      0.14       0.61          0          0          0           0

Fetch     2474      0.28       1.96          0       7422          0        2474

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     4949      0.42       2.57          0       7422          0        2474

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  TABLE ACCESS FULL U (cr=3 pr=0 pw=0 time=0 us cost=3 size=17 card=1)

********************************************************************************

How to flush cache in Oracle and MS SQL Server

Sometimes we need to test the performance of queries with a cold buffer cache without shutting down and restarting the database server. 

Oracle

alter system flush shared_pool flushes the library cache in Oracle SGA.

alter system flush buffer_cache flushes the buffer cache in the SGA. This command is not available prior to 10g. 

Oracle 9i had an undocumented command to flush the buffer cache,
alter session set events = 'immediate trace name flush_cache';

MS SQL Server 

DBCC FREEPROCCACHE removes all elements from the cache in SQL Server.

CHECKPOINT; DBCC DROPCLEANBUFFERS flushes the buffers in SQL Server. Please notice that DBCC DROPCLEANBUFFERS only removes clean buffers from the buffer pool. We need to run CHECKPOINT first to force all dirty pages in the buffer to be written to the disk and then remove all clean buffer pages.