Oracle Logical IO / Consistent Reads inconsistent

oracle

I use Logical IO to tune my Oracle queries and generally ignore wall-clock times as we have a large Oracle DB in a cluster with a total of about 80Gb SGA and an EMC cluster with tiered storage (Gigs of cache, SSD, Fiber disk and finally SATA)

This measure works for me, but I have noticed that sometimes the first Consistent Read value differs from subsequent ones (say 4000 IOs then 200 IOs where from them on the 200 IO remains consistent).

Whats up with that? Why is the the first request higher? I should be dealing with Logical IO and this should not be affected by whether the blocks we are looking for are in a buffer somewhere or whether we go directly to disk.

🙂

To preempt your next question – I am looking at LAST_CR_BUFFER_GETS from Autotrace in SQL Developer.

Best Answer

My guess would be that the first query doing the extra work of delayed block cleanout because some other session at some earlier point in time modified (or inserted) the data without being able to do a commit cleanout. That causes Oracle to do a bit of extra work the next time the dirty block is read to update its status. Here is a good example of the impact of delayed block cleanouts on consistent gets. I'm not 100% certain that delayed block cleanouts are reflected in LAST_CR_BUFFER_GETS from DBMS_XPLAN but I would certainly expect that they are.

Related Solutions

Sql-server – Oracle query against SQL Server database giving inconsistent results

I'd suspect some for of characterset conversion issue. When you do a TO_CHAR, you are getting back "┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐" (18 characters). That suggests that there is some confusion about whether the data contained in the column is single-byte or multi-byte.

"The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value" This should ideally come across to Oracle as a RAW datatype not a VARCHAR2 or CHAR.

If you do a

CREATE VIEW temp_view as select  "id", "folder_id" from dbo.mediasite_presentation@ms

what shows up when you do a DESC temp_view

Inconsistent Data Type -ORA-00932

The type InstalmentList is a nested table of InstalmentEntry, so you need to build a set of InstalmentEntry objects to get the list.

The following query works on 11.2:

SELECT column_value AS list, rownum AS rownumber
  FROM TABLE(powermultiset(
               CAST(MULTISET (SELECT InstalmentEntry(1, 1) /*Entry "constructor"*/
                                FROM dual) 
                    AS InstalmentList)
             ));

Best Answer

Related Solutions

Sql-server – Oracle query against SQL Server database giving inconsistent results

Inconsistent Data Type -ORA-00932

Related Question