The count(*) returns how many rows?  1.

The array size does even begin to kick in.  That statement is processed in ONE call.

Also, the count(*) may very well *never be touching the table*.  It might be just using an index.

Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................320
Total Bytes.............................2621440
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................7
Last Used Ext BlockId...................40969
Last Used Block.........................60

PL/SQL procedure successfully completed.

<b>so the table consumes about 316 blocks..</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
22905 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
       1823  consistent gets
         46  physical reads
          0  redo size
    2704019  bytes sent via SQL*Net to client
     169811  bytes received via SQL*Net from client
       1528  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      22905  rows processed

<b>
22905/15 = 1527+316 = 1843 which is about our consistent gets (what we were expecting)....</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        319  consistent gets
         46  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

<b>And the count(*), since it was done in a SINGLE CALL -- no arraysize -- only did a consistent get on each block ONE TIME.  Hence it did 319 consistent gets (which is about the size of the table)

Now, adding a primary key to the table:</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from t;


Statistics
----------------------------------------------------------
         79  recursive calls
          4  db block gets
         82  consistent gets
          1  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

<b>we can see that the consistent gets go way down.  why?  because the optimizer counted the rows in the index, not the table in this case.</b>


 

db_block_gets + consistent_gets = LOGICAL IO
physical_reads = PHYSICAL IO


I don't see where I might have mislead you on that here -- can you point out which part of the above indicated to you that they were physical IO?

You might be right, you might not be. Some other query and/or operation may have cached the data already.


On the second point, you might be right, you might not be. It depends on

o the amount of data touched
o the size of the buffer cache
o what else is going on in the system
o the use of various features/functions in the database (eq: parallel query)

In general, yes, you would expect in many cases that the second execution would be almost all logical io.

No, you are wrong in your statement.

A consistent get is a block gotten in read consistent mode (point in time mode).  It MAY or MAY NOT involve reconstruction (rolling back).

Db Block Gets are CURRENT mode gets -- blocks read "as of right now".


Some blocks are processed more then once, yes, the blocks will have more then 1 consistent read in the process.  Consider:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T')
Free Blocks.............................0
Total Blocks............................320
Total Bytes.............................2621440
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................7
Last Used Ext BlockId...................40969
Last Used Block.........................60

PL/SQL procedure successfully completed.

<b>Table has 316 blocks, 22,908 rows..</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly statistics;

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 15
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
22908 rows selected.

<b>here with an array size of 15, we expect

22908/15 + 316 = 1843 consistent mode gets.  db block gets -- they were for performing the FULL SCAN, they had nothing to do with the data itself we selected</b>


Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets<b>
       1824  consistent gets</b>
        170  physical reads
          0  redo size
    2704448  bytes sent via SQL*Net to client
     169922  bytes received via SQL*Net from client
       1529  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      22908  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 100
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

22908 rows selected.

<b>Now, with 100 as the arraysize, we expect

22908/100 + 316 = 545 consistent mode gets.</b>

Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        546  consistent gets
        180  physical reads
          0  redo size
    2557774  bytes sent via SQL*Net to client
      25844  bytes received via SQL*Net from client
        231  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      22908  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set arraysize 1000
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

22908 rows selected.

<b>now, with arraysize = 1000, we expect:

22908/1000+316 = 338 consistent mode gets...</b>

Statistics
----------------------------------------------------------
          0  recursive calls
         12  db block gets
        342  consistent gets
        222  physical reads
          0  redo size
    2534383  bytes sent via SQL*Net to client
       2867  bytes received via SQL*Net from client
         24  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      22908  rows processed




<b>so yes, the blocks are gotten in consistent mode MORE THEN ONCE when the array fetch size is lower then the number of rows to be retrieved in this case</b>

This is because we'll be 1/2 way through processing a block -- have enough rows to return to the client -- and we'll give UP that block.  When they ask for the next N rows, we need to get that halfway processed block again and pick up where we left off.
 

read the original question / answer. I went through it there.

In this case, the numbers:

22908 = number of rows
15 = arraysize.

22908/15 = number of times you'll fetch

so, that is the number of times you'll have to read a block more then once.

315 was the number of blocks in the table (in a perfect world, we would do 315 consistent gets. With an arraysize of 15 and 22908 rows, we'll add 22908/15 consistent gets to that total)...

No -- it won't "distort them".

You can use the array size in sqlplus to see what your APP would do.

Array size by default is 15 in plus
10 in JDBC
2 in pro*c
1 in OCI
??? in odbc (no idea, never use it)

It is NOT distorted -- it is reality! It shows what really happens as you use different array sizes to fetch.

TKPROF -- that would show exactly the same information. The same -- from jdbc to plus to pro*c (sqlplus is JUST an oci application)


with reports see:

ARRAYSIZE (RWBLD60)

Description ARRAYSIZE is the size of the array in kilobytes for use with ORACLE array processing.
Syntax
[ARRAYSIZE=]n


with business objects -- refer to their documentation.

It is because we need to get a block more then ONCE.

Suppose we do that simple "select * from t"

Suppose every block has 16 rows on it.

Suppose we have an array size of 15.


We do the first fetch. That'll get block 1 (one consistent get) and get the first 15 rows and return.

Now, we do the second fetch. That'll get block 1 again (two consistent gets) and then get block 2 to get the next 14 rows (three consistent gets). We return.

We do the 3rd fetch. We get block 2 again (four consistent gets) to get the remaining 2 rows and then get block 3 (five consistent gets) to get the next 13 rows.

And so on. We end up getting every block 2 times -- because we stopped processing it partway through to return the result to the client.


Hence, if you full scan a table with N rows and use an array fetch of M rows, you stand a change of getting the blocks an extra N/M times.

Ok, 10 rows/block:

on first fetch we:
get block 1 (1 consistent get) 10 rows
get block 2 (2 consistent gets) 5 rows / 5 left

on second fetch we:
get block 2 (3 consistent gets) 5 rows / 0 left
get block 3 (4 consistent gets) 5 rows / 5 left

....

and so on. We visit EVERY OTHER block 2 times (the N/M still holds here. If I have 100 rows on 10 blocks and an array size of 10 -- I'll add 10 consistent gets for a grand total of 20 consistent gets).

Sorry --- I've explained this beyond my best ability at this point. I've explained many many times. This is my last try:

The table has 20 blocks in it.
You would expect 20 consistent gets on a full scan.
But, with an array size of 15 we will revisit some blocks MORE THEN ONCE.

In fact, we might revisit upto 10000/15 = 666 blocks more then ONCE.

This is because we "start" and "stop" the query in the middle of a block. (see the two followups immediately above this).


If you full scan a table T that has N blocks and R rows and you use an array fetch size of A, we will typically perform the following number of consistent gets:

N + R/A


We have to read N blocks (that should be obvious)
We might revisit R/A blocks more then once (since we stop in the middle)

13 db block gets are blocks gotten in CURRENT mode -- they were blocks that told us were all of the other blocks were! (they were the segment headers).

set arraysize 100

and try again in sqlplus (and watch your consistent gets start to go down)


Now, also bear in mind -- this is not two minutes just to read the blocks. This is two minutes to

o read the blocks
o send them over the network
o have sqlplus process them

1) in some cases it'll reduce the LIO's yes (in many cases). In all cases -- using a healthy but not "overweight" arraysize is recommended - in all cases.

2) not sure what N+r/a is (hopefully that is not A=arraysize cause it doesn't go that way -- the arraysize can have an effect - I used a case where I could predicate the effect but there are many more cases where

a) it'll not have an effect
b) it'll have an effect but a wholly unpredicable one.

db block gets (autotrace) are current (tkprof)
consistent gets(autotrace) are query (tkprof)

3) one to one - physical reads(autotrace) is disk (tkprof)

4)
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 <code>

It would be a question for peoplesoft to answer -- they wrote the code. OCI can do anysize, they would have had to of programmed support for more than one row at a time.

the arraysize doesn't affect the plan.

they arraysize DOES affect the statistics (it would be a shame if it didn't!)

The arraysize in sqlplus demonstrates that "hey, I should look at my client and set the bulk fetch size appropriately". Pretty much all tools have the ability to do it.

A com object is like saying "I use CORBA, how to set arraysize in CORBA". Doesn't make sense. COM isn't a "language", COM doesn't do sql. You look at the language you are using. If it is an open systems language like C, Java -- I'lll be glad to tell you. If it is a closed language like VB -- you'll have to refer to the docs for that -- I've no idea as I've never considered programming with them.

Developers are the ONLY bothered people with regards to this. They are responsible for making sure the batch fetch size in their client applications is set to a reasonable number (i like 100 personally)

yup. if you want the client to fetch N rows at a time where N is some number you wish to control.

Nope, rollback is to be found in the buffer cache just like anything else. It is just a rollback SEGMENT after all -- just another segment (like an index segment).

it just does in 8i for some reason. In 9i, it does not.

Note that most people would never see this as a single row table created via CTAS is not very common. if you do a "real sized" table -- you'll see the additional header gets (the db block get -- current mode gets) but averaged out over a large full scan, it is less than "noise"

consitent gets:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
20 consistent gets


or in tkprof query + db block gets.

yes it does -- by definition.

consistent gets = logical IO's = number of block gets.

it does not equal the number of DISTINCT blocks you processed if that is what you mean, it means the number of block gets (which require cpu cycles and latches = locks and so on)

if you've been reading since then -- you've heard me say:

"if you read the concepts guide AND
you retain 10% of that information YOU'LL HAVE
90% more knowledge about Oracle then most people out there..."


</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#2570 <code>

all about multi-versioning.

All queries done by Oracle are done "at a point in time", consistently -- read that (read the entire book in fact) and you'll get an understanding of the single most important concept to understand about Oracle ever.

If you have my book "Expert one on one Oracle" -- i cover it in gory detail in there as well.

PLSQL can be optimized using BULK COLLECT (array fetching) and FORALL (bulk modification)

search the PLSQL manual's table of contents for those terms and read on....

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
20 consistent gets
0 physical reads

that would be that number.

it might be 0, it might be 20000000, it might be 22 to read 100,000 database blocks in answer to a query (for PIO's)

db block gets are blocks gotten in CURRENT (as opposed to consisten) mode. They are block gets we did to discover the extent map for the table (to know what to read). We get that in "current, as of right now" mode instead of "in consistent read, as of the point in time the query began" mode.

Oracle did 4 current mode gets in both cases to figure out what blocks to read.

In the first example, it did 686 logical IO's
In the second, only 30

It did no physical IO.

because -- just because. In different releases -- different count.

In your example, you didn't do a full table scan -- apples to toaster oven comparision.

You are using the CBO
You have a primary key on T
It read the index using an index full scan.

Or you are using Oracle9i and new features like ASSM...

Or ....


Let me put it this way -- if you see 4 there, and you did a simple full table scan -- then the 4 db block gets are what I said they where.

I did not say "you shall always see 4 there", rather, that when you do -- thats what they are.

what is the difference?

What do you mean by "how many blocks did Oracle need to read".

To me that is one of two things:

o how many block gets did Oracle do in order to satisfy my query. That is logical IO

o how many blocks did Oracle physically read from disk in order to satisfy my query. That is phyiscal IO.


Those are the only two numbers that mean anything. Those are the only two numbers you can get.

That there are 100 blocks in the table, and we did 1,000 Logical IO's to read it -- of those two numbers the only relevant one is "1,000".

need to read -- that is PIO

need to process that is LIO

nothing else is relevant nor obtainable.

TKPROF would tell you if arraysize is a factor -- divide fetches into rows - if the number is 1, they aren't using array fetches.

A FACTOR in consistent gets is arraysize.

ARRAYSIZE does not determine consistent gets.

consistent gets means getting a block "as of a point in time" and MAY require read asides to the rollback segment in order to reconstruct the block.  emphasis on MAY.

the RBS is just a segment (that is what the S is for).  It resides in the cache just like an index, table or anything else does.


Here is an example showing the "read asides" to the rbs.  It shows that we MAY require a read to the rbs by running the same exact query -- but doing lots of work in between:


ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA920> commit;

Commit complete.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable x refcursor
ops$tkyte@ORA920> variable y refcursor
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA920> begin
  2     open :x for select * from t X;
  3     open :y for select * from t Y;
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> print x

         X
----------
         1

ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
  2      for i in 1 .. 10000
  3      loop
  4          update t set x = i;
  5          commit;
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> print y

         X
----------
         1

ops$tkyte@ORA920>


So, they return the same exact information but TKPROF shows us:

SELECT * from t X

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        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=7 r=0 w=0 time=162 us)
********************************************************************************
SELECT * from t Y

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        1      0.04       0.06          0      10002          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.06          0      10002          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 83     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL OBJ#(34399) (cr=10002 r=0 w=0 time=60325 us)


<b>that it took 10k consistent gets for Y -- versus 7 for X</b> -- both queries return the same data -- it is just that for the first fetch from Y we had to "undo" the 10,000 updates that happened between the OPEN of Y and the fetch.

 

I like this quote:

<quote>
The reality is that with Oracle, as with any other database, you design and code your application with an understanding of the underlying isolation and concurrency model
</quote>

I've been saying that for years myself...


but then they say:

<quote>
No other database vendor has implemented Oracle's Multi Version Read Consistency isolation nor has it proven to be a performance advantage in industry standard, ISV or real life customer benchmark.
</quote>

well, they sort of need to do their homework -- I would like us to be the only ones with this feature -- but we ain't. RDB (which we bought a while ago) had it for a loooonnnnnggg time. Borlands Interbase (someone bought that I believe) did. Heck, mysql does it. So, to make us look "unique", that is very nice of them -- but not really true. And given that I don't even see DB2 in the top 10 tpc-C results -- even on AIX for goodness sake -- don't see where they are going with this benchmark concept (oh thats right, they don't actually run the tpc-c's, not even on their own machines).... Ok, going to the SAP website

</code> http://www.sap.com/benchmark/index.asp?content=http://www.sap.com/benchmark/ato2tier.asp <code>

looks like the top ones in this ISV benchmark (run by a company that doesn't necessarily promote Oracle) would be .... (not DB2).

Oh, i see way down at the bottom they refer to a read only benchmark -- a tpc-h. How appropriate in a paper describing CONCURRENCY CONTROLS that affect reads/writes at the same time, (not).

Oh I see they say we bypass our MV in the tpc-c because we use serializable. Boy -- just shows a TOTAL and FUNDEMENTAL lack of understanding on their part of how MV works in Oracle -- they totally 100% missed the boat. Our serializable is even MORE depenedent on multi-versions then our read committed is!!!


Now, they say:

<quote>
With Oracle, the data you see in any query is the data as it existed at the start of that query. This does not conform to any ANSI standard isolation level, nor is it the way other RDBMSs work.
</quote>

Wrong and Wrong (2 for 2). ANSI describes isolation in terms of "phenomena".
They take three phenomena and create four isolation levels based on the
existence, or lack thereof, of the above phenomena. They are:

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted Permitted Permitted Permitted
Read Committed Permitted Permitted
Repeatable Read Permitted
Serializable

Oracle supports explicitly two of the above isolation levels as they are defined
-- read committed and serializable. This doesn't tell the whole story however.
The SQL standard was attempting to set up isolation levels that would permit
various degrees of consistency for queries performed in each level. Repeatable
read is the isolation level they claim you must be at to get a read consistent
result from a query. Read committed does not give you consistent results. Read
uncommitted is the level to use to get non-blocking reads.

In Oracle -- Serializable and Repeatable Read are synonymous. Read uncommitted with its dirty read is unTHINKABLE.

but wait -- lets see what DB2 has to say in their technical documentation - not their marketecture documentation:

<quote>
IBM Redbook for DB2

3.3.1.1 Strategy 1 - Conversation Integrity (Not Recommended)

A commit point is created at the end of the conversation. The lock duration may include user think time. Therefore, this strategy should not be used in applications where other users need concurrent access to the tables.


Read-only application might get away with this strategy in some cases. S-locks are normally released before commit point, but an S-locked page or row cannot be updated by another process and an S-lock may have a very long duration. It may include user think time.

We do not recommend this strategy even for read-only applications, although it may be the most convenient one from a programming point of view.
</quote>

what they are talking about there is "getting the right answer". In DB2 getting the right answer from a query that accesses more then one record requires leaving shared read locks behind -- that is sort of a bummer in a multi-user environment isn't it.

or, going onto the read uncommitted level they are so proud of (apparently)

<quote>
1.3.2.1 Case 2 - Without Locking

1. User A updates value 100 to 0 into the DB2 table column.
2. User B reads the updated value 0 and makes program decisions based on that value.
3. User A cancels the process and changes value 0 back to value 100 for the
DB2 table column.

This case shows that, without locking, User B made a wrong program decision, and because of that the business data in the database is potentially corrupted

</quote>

well, thats not too brilliant.

Of course, I've already addressed the "hey no one else does it like this" (which is a really poor argument don't you think. Is IBM a company that would jump off a bridge because everyone else does?? I don't get where they are going with that little tidbit of inaccuracy)



It is funny that they continue on to bash our detour into the RBS to read around locks -- when they themselves would STOP. "Bad Oracle -- they use extra CPU, Good IBM -- we just stop you cold, no wasting of a resource you cannot put in the bank and use later, no siree bob!"



Great -- they get into a technical discussion of how Oracle manages rollback and manage to totally muck it up in the first paragraph:

<quote SRC=IBM accuracy=LOW>
Rollback segments are simply disk space set aside to store old images of data. They are physically a set of operating system files

inaccurate -- they are a segment that may span tons of files/logical volumes

of a predefined size

inaccurate -- they can grow and shrink

that work in a circular fashion.

accurate to a point -- but they get it wrong in the following

That is, old data is put into rbs 1 followed by rbs 2 and so on. When all of the rollback segments are full, the process wraps back around to the first segment.

wrong wrong wrong -- so wrong.

Undo Tablespaces in Oracle 9i work in a similar fashion except that the information is stored in an Oracle managed tablespace which is made up of operating system data files so the process is almost identical. There is a DBA management burden to configure and maintain rollback segments.

lets look below, after this quote for a tidbit on that

How big should your rollback segments be? What if you have a single transaction that does not fit into your rollback segments? What happens if you run out of space?

in IBM with fixed sized logs that cannot wrap (as opposed to Oracle) -- how big should your logs be? what if you have a single transaction that doesn't fit into your logs? what if you run out of space? POT calling KETTLE black here?

</quote>

Ok, so Oracle unduly burdens you with RBS? How about:


<quote src= IBM udb release notes>

If maxlocks is set too low, lock escalation happens when there is still enough lock space for other concurrent applications. If maxlocks is set too high, a few applications can consume most of the lock space, and other applications will have to perform lock escalation. The need for lock escalation in this case results in poor concurrency.&#65533;
</quote>

hey, whats this lock escalation thing and why didn't they talk about this "feature" in their marketecture paper? What undue burden does this "feature" place on the DB2 dba.


Oh yeah, they forget to mention in 9i using undo tablespaces, the dba sets up an undo retention period and -- oh yeah, thats it.


continueing on:

<quote>
Well running out of space is not a problem...Oracle simply cancels your transaction! Yes that's right, if there is not enough rollback segment space for your transaction, the transaction fails. ...
</quote>

How evil that would be (if it were true). But wait -- ask IBM "hey, db2 guys, what happens if i run out of log? oh -- you cancel my transaction. Well, I don't think that is very nice do you?"


a) we cancel the statement -- not the transaction.
b) we can suspend the statement allowing the dba to add more space if needed
c) we can dynamically grow the space (do their logs do that?) if needed making this self correcting if need be
d) we can even set up quotas to prevent runaways from consuming too much space (hey, db2, can you?)



as for the tiring repeated quotes of "these are a bear to manage", tell me -- how about maxlocks, lock esculation, sizing of logs, sizing of memory -- etc etc etc in DB2? you are trading X for Y here.


They then go onto say Oracle is page based -- not row based (this is stretching it a BIT far don't you think...)


<quote>
If a transaction is looking for record 5 on block 106, it may have to clone and reconstruct several different version of block 106 even if record 5 has never changed. With DB2, the concurrency mechanisms work on the row level so if one transaction is locking row Y and another transaction wants to look at row X (even if they are both on the same page), then both transaction would be able to proceed.
</quote>


Well, lets see. Change the example to

transaction looking for record 5.
someone is modifying record 5.

Oracle gives you -- record 5.

DB2 gives you -- the hand. As in "stop", "wait", do not pass go, do not collect $200 go directly to jail. (dirty reads i guess would be their "get out of jail free" card -- if you want dirty data)



<quote>
Similarly if you took a DB2 application and ported it directly to Oracle, you would find that it may have problems with ORA-1555 because of the frequent commits in the application.
</quote>

Oh that is rich. And why did the DB2 program have frequent commits? Why aren't the transactions "right sized"? Why do you commit all of the time -- even before the TRANSACTION is finished in DB2? Could it be because -- gasp -- readers block writers and writes block reads in DB2? (yes, yes it could be).


<quote>
Each implementation does what it is designed to do and it's the application development that either makes the best use of the technology or runs into issues.
</quote>

Now it is like talking to a schizophrenic person. Moment of lucidity wrapped in periods of craziness. That quote above -- beautiful, perfect, excellent -- totally correct.

They actually back up my premise that "database idenpedence is a farce", unfortunately -- they do so in a paper so riddled with mistakes that is calls into question even the accurate information.


Then they get into the locking/blocking stuff (bottom of page 8 into 9) and say

"Note that this last example is the default behaviour with DB2"

What they don't mention is that it is the ONLY behavior with DB2. Unless you want to read UNCOMMITTED -- dirty data that is.

yes it does -- don't forget to include indexes that could be on the table as well....

an UPDATE (and delete for that matter - and sometimes an insert) have two components -- a consistent READ part and a modification part.

inline views will be useful here to see this, instead of coding:

update t set object_id = object_id;


say you wrote:

update
( select *
from t )
set object_id = object_id;

the part in "bold" is done as a consistent read -- the update, modification part regets the block in current mode to actually update the data.

Why must this be? well, consider what would happen if:

create table t ( x int );
create t_idx index on t(x);


update t set x = x+100 where x > 5;

Now, say that update used t_idx to get the data, to find rows where x >5. Now, we get the first row -- x = 5, we update to 105 and update the index et.al. Now, we continue one and eventually we get into rows where x > 100.... We find a row 105 -- we already updated (but do not realize this in current mode, only in consistent read mode). We would make it 205 and so on.

You would have an infinite loop. Don't laugh -- sqlserver (MS and Sybase) both have the ability for this to happen. I remember the first time I saw it happen -- I wrote a program to read a table and generate a complex update. I wrote the update to stdout. I had the update go into isql, something like this:


isql (with a query) | filter_to_generate_updates | isql (to do the updates)


that table had 5000 rows in it. without consistent reads -- and since their update will sometimes be a "delete+insert" -- what happened was row 1 became row 5001 and so on. when we got to row 5000 instead of stopping, it kept reading.

25,000 plus updates later I killed it -- really confused. took a while to figure out what exactly had happened.

say you:

create table t ( x int );

insert into t values ( 1 );
insert into t values ( 2 );
commit;


update t set x = x+1;

Oracle will

a) read T in consistent read mode as of the point in time the update was started
b) that'll get the single block T is on in consistent mode.
c) Oracle will see the first row -- x=1 -- and say "hey, I gotta update that"
d) Oracle will get that block in current mode, modify it and let go of it
e) Oracle will see the second row -- x=2 -- and say "hey...."
f) Oracle will get that block in current mode, modify and let go of it.


consider:

ops$tkyte@ORA920> create table t ( x int );
Table created.

ops$tkyte@ORA920> insert into t select rownum from all_users;
46 rows created.

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> set autotrace traceonly statistics
ops$tkyte@ORA920> update t set x = x where rownum <= 1;

Statistics
----------------------------------------------------------
          2  db block gets
          7  consistent gets
          1  rows processed

ops$tkyte@ORA920> update t set x = x where rownum <= 2;

Statistics
----------------------------------------------------------
          2  db block gets
          7  consistent gets
          2  rows processed

ops$tkyte@ORA920> update t set x = x where rownum <= 5;

Statistics
----------------------------------------------------------
          5  db block gets
          7  consistent gets
          5  rows processed

ops$tkyte@ORA920> update t set x = x where rownum <= 10;

Statistics
----------------------------------------------------------
         10  db block gets
          7  consistent gets
         10  rows processed

ops$tkyte@ORA920> update t set x = x where rownum <= 20;

Statistics
----------------------------------------------------------
         20  db block gets
          7  consistent gets
         20  rows processed


The number of consistent gets is "consistent" (we full scanned the table in consistent read mode each time).  The number of db block (current mode) gets goes up as the rows modified goes up. 

that table (very very small table) would have been on a single block.  the same block was gotten over and over and over.

ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> insert into t select rownum from all_users;

46 rows created.

ops$tkyte@ORA920> select dbms_rowid.rowid_block_number(rowid), count(*) from t
  2  group by dbms_rowid.rowid_block_number(rowid);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
------------------------------------ ----------
                                 584         46


there was one block, block 584... 

yes, confirmed.

process 1 reads and prints to stdout

process 2 converts data into update statement

process 3 updates data

( using a pipe, i had isql | awk | isql ). If the row is updated via a DELETE+INSERT and there are no clustered indexes, you may will be able to reproduce this as the insert tends to go at the end of the table.

unfortunately, I don't have either of those products installed (well, actually one won't install on any computer I have, no DLLs you know)...

You can see a similar effect by doing this

o create a large table with "known data" -- something that will take a couple of seconds to full scan.

o make sure you know the "first" record that will be hit in the full scan and the "last record". eg, in Oracle I might:

insert into this_table ( some known value );
insert into this_table select lots of data;
insert into this_table ( some other known value );

Now, make sure one of the columns is a number you can "sum up".

o have two sessions ready to go

o in session one "select sum(of_that_number) from table", remember that number. also, remember what value row 1 has.

o while that is running, but before it is done do these updates in session 2

update table set of_that_number = 0 where <predicate to get the first row>;
update table set of_that_number = <value that was in row1> where <last row>;


o make sure the updates run after the first row was read, but before the last. Tell me, is the sum you get an answer that ever existed in your database at any point in time? The results can be even more amusing if

o the update to zero is to some number other then zero (say 5)
o the update to row 1 causes it to move to the end of the table (or
even the middle) as it might (might not, they got better at that over time)


you can end up reading row 1 more then once giving you really ambigous results.

the block header has this information. the transaction header in the block itself has this information.

looks like a small issue with 8i sqlplus and autotrace with arraysizes below 9.

not a "big deal"

more cpu, more latches (which are locks) more work.

IT IS IMPORTANT for performance (less network roundtrips), for scalability (less latching), and for resource utilization (less resources used).

yes....

you are logged in as sys.

You should never use sys, system, or ANY of the oracle accounts for anything. they are "ours"

Create your own account to play with.

the plan is:

  for x in ( select * from T1 )
    for y in ( select * from T2 )
        output x+y
    end loop
  end loop

from the rbo.  The formula for the consistent gets will then be:

number_of_blocks_in_t1 + number_of_rows_in_t1 * number_of_blocks_in_t2

so, here are some sample numbers to play with:

ops$tkyte@ORA920> declare
  2      l_small_rows   number := 104;
  3      l_big_rows     number := 34486;
  4
  5      type numArray is table of number;
  6
  7      l_small_blocks numArray := numArray( 1,     2,    4,    8,   16 );
  8      l_big_blocks   numArray := numArray( 300, 600, 1200, 2400, 4800 );
  9
 10      l_small_drives number;
 11      l_big_drives number;
 12  begin
 13      for i in 1 .. l_small_blocks.count
 14      loop
 15          for j in 1 .. l_big_blocks.count
 16          loop
 17              dbms_output.put_line( 'small blocks = ' || l_small_blocks(i) ||
 18                                    ' big blocks = ' || l_big_blocks(i) );
 19
 20              l_small_drives := (l_small_blocks(i) + l_small_rows*l_big_blocks(j));
 21              l_big_drives   := (l_big_blocks(i) + l_big_rows*l_small_blocks(j));
 22              dbms_output.put_line( 'small drives = ' || l_small_drives ||
 23                                    ' big drives = ' || l_big_drives ||
 24                                    ' difference = ' || abs( l_big_drives-l_small_drives ) );
 25          end loop;
 26      end loop;
 27  end;
 28  /
small blocks = 1 big blocks = 300
small drives = 31201 big drives = 34786 difference = 3585
small blocks = 1 big blocks = 300
small drives = 62401 big drives = 69272 difference = 6871
small blocks = 1 big blocks = 300
small drives = 124801 big drives = 138244 difference = 13443
small blocks = 1 big blocks = 300
small drives = 249601 big drives = 276188 difference = 26587
small blocks = 1 big blocks = 300
small drives = 499201 big drives = 552076 difference = 52875
small blocks = 2 big blocks = 600
small drives = 31202 big drives = 35086 difference = 3884
small blocks = 2 big blocks = 600
small drives = 62402 big drives = 69572 difference = 7170
small blocks = 2 big blocks = 600
small drives = 124802 big drives = 138544 difference = 13742
small blocks = 2 big blocks = 600
small drives = 249602 big drives = 276488 difference = 26886
small blocks = 2 big blocks = 600
small drives = 499202 big drives = 552376 difference = 53174
small blocks = 4 big blocks = 1200
small drives = 31204 big drives = 35686 difference = 4482
small blocks = 4 big blocks = 1200
small drives = 62404 big drives = 70172 difference = 7768
small blocks = 4 big blocks = 1200
small drives = 124804 big drives = 139144 difference = 14340
small blocks = 4 big blocks = 1200
small drives = 249604 big drives = 277088 difference = 27484
small blocks = 4 big blocks = 1200
small drives = 499204 big drives = 552976 difference = 53772
small blocks = 8 big blocks = 2400
small drives = 31208 big drives = 36886 difference = 5678
small blocks = 8 big blocks = 2400
small drives = 62408 big drives = 71372 difference = 8964
small blocks = 8 big blocks = 2400
small drives = 124808 big drives = 140344 difference = 15536
small blocks = 8 big blocks = 2400
small drives = 249608 big drives = 278288 difference = 28680
small blocks = 8 big blocks = 2400
small drives = 499208 big drives = 554176 difference = 54968
small blocks = 16 big blocks = 4800
small drives = 31216 big drives = 39286 difference = 8070
small blocks = 16 big blocks = 4800
small drives = 62416 big drives = 73772 difference = 11356
small blocks = 16 big blocks = 4800
small drives = 124816 big drives = 142744 difference = 17928
small blocks = 16 big blocks = 4800
small drives = 249616 big drives = 280688 difference = 31072
small blocks = 16 big blocks = 4800
small drives = 499216 big drives = 556576 difference = 57360
 
PL/SQL procedure successfully completed.
 


As you can see -- the number of consistent gets for that plan is radically affected by the NUMBER OF BLOCKS in the table.  


the cbo plan -- it is not even remotely comparable.  but it basically read BIG once, sorted it and read it from temp (temp reads are NOT consistent gets)
 

clarity is in the original answer and followups??

that was the crux of the first answer on this page. do you have a specific question?

give me a "for example" to work with at least.

use tkprof.

first, the autotrace stuff takes time and is counted.

second, you are seeing the total elapsed time -- including sqlplus "screen paint", network, etc

use tkprof to find out for real what is taking place.

yes, it makes sense/comes into consideration.

Look at the OCIDefine functions -- valuep can be an array.

ocidefinebypos

for example in 8 and above

see $ORACLE_HOME/rdbms/demo/oci06.c for "old style" array fetch example

you, as developers, would have a parameter file with configuration options that the end user could edit.


it is a CLIENT thing, cannot be done in the server.

the ROI?  

I do that all of the time with runstats and sqlplus.  I did it in chapter 2 of my new book "Effective Oracle By Design".  I do it in my "whats wrong presentation" i give from time to time.  Here is short excerpt showing the effects of array fetching on logical IO's from the book:

<quote>
Array Size Effects

The array size is the number of rows fetched (or sent, in the case of inserts, updates, and deletes) by the server at a time. It can have a dramatic effect on performance. 

To demonstrate the effects of array size, we'll run the same query a couple of times and look at just the consistent get differences between runs:

ops$tkyte@ORA920> create table t as select * from all_objects;
Table created.

ops$tkyte@ORA920> set autotrace traceonly statistics;

ops$tkyte@ORA920> set arraysize 2
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
      14889  consistent gets

Note how one half of 29,352 (rows fetched) is very close to 14,889, the number of consistent gets. Every row we fetched from the server actually caused it to send two rows back. So, for every two rows of data, we needed to do a logical I/O to get the data. Oracle got a block, took two rows from it, and sent it to SQL*Plus. Then SQL*Plus asked for the next two rows, and Oracle got that block again or got the next block, if we had already fetched the data, and returned the next two rows, and so on. 

Next, let's increase the array size:

ops$tkyte@ORA920> set arraysize 5
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
       6173  consistent gets

Now, 29,352 divided by 5 is about 5,871, and that would be the least amount of consistent gets we would be able to achieve (the actual observed number of consistent gets is slightly higher). All that means is sometimes in order to get two rows, we needed to get two blocks: we got the last row from one block and the first row from the next block. 

Let's increase the array size again:

ops$tkyte@ORA920> set arraysize 10
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
       3285  consistent gets

ops$tkyte@ORA920> set arraysize 15
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
       2333  consistent gets

ops$tkyte@ORA920> set arraysize 100
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
        693  consistent gets

ops$tkyte@ORA920> set arraysize 5000
ops$tkyte@ORA920> select * from t;
29352 rows selected.

Statistics
----------------------------------------------------------
        410  consistent gets

ops$tkyte@ORA920> set autotrace off

As you can see, as the array size goes up, the number of consistent gets goes down. So, does that mean you should set your array size to 5,000, as in this last test? Absolutely not. 

If you notice, the overall number of consistent gets has not dropped dramatically between array sizes of 100 and 5,000. However, the amount of RAM needed on the client and server has gone up with the increased array size. The client must now be able to cache 5,000 rows. Not only that, but it makes our performance look choppy: The server works really hard and fast to get 5,000 rows, then the client works really hard and fast to process 5,000 rows, then the server, then the client, and so on. It would be better to have more of a stream of information flowing: Ask for 100 rows, get 100 rows, ask for 100, process 100, and so on. That way, both the client and server are more or less continuously processing data, rather than the processing occurring in small bursts.
</quote>


and to put it in terms they (coders) will understand -- array fetching <b>goes faster</b>.  On:

http://asktom.oracle.com/~tkyte/flat/index.html

I have a pro*c program that accepts an array size as input.  Look at the difference between single row fetching:

[tkyte@tkyte-pc array_flat]$ time ./array_flat userid=big_table/big_table 'sqlstmt=select * from big_table where rownum <= 100000' arraysize=1 > /dev/null
 
Connected to ORACLE as user: big_table/big_table
 
Unloading 'select * from big_table where rownum <= 100000'
Array size = 1
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
100000 rows extracted
 
real    1m10.250s
user    0m30.852s
sys     0m2.365s
[tkyte@tkyte-pc array_flat]$ !!
time ./array_flat userid=big_table/big_table 'sqlstmt=select * from big_table where rownum <= 100000' arraysize=1 > /dev/null
 
Connected to ORACLE as user: big_table/big_table
 
Unloading 'select * from big_table where rownum <= 100000'
Array size = 1
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
100000 rows extracted
 
real    1m13.662s
user    0m20.514s
sys     0m0.910s

<b>versus array fetching:</b>


[tkyte@tkyte-pc array_flat]$ time ./array_flat userid=big_table/big_table 'sqlstmt=select * from big_table where rownum <= 100000' arraysize=100 > /dev/null                                                                                                                                                            
Connected to ORACLE as user: big_table/big_table
 
Unloading 'select * from big_table where rownum <= 100000'
Array size = 100
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
100000 rows extracted
 
real    0m6.145s
user    0m3.816s
sys     0m0.211s
[tkyte@tkyte-pc array_flat]$
[tkyte@tkyte-pc array_flat]$ !!
time ./array_flat userid=big_table/big_table 'sqlstmt=select * from big_table where rownum <= 100000' arraysize=100 > /dev/null
 
Connected to ORACLE as user: big_table/big_table
 
Unloading 'select * from big_table where rownum <= 100000'
Array size = 100
ID,OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
100000 rows extracted
 
real    0m5.491s
user    0m3.795s
sys     0m0.230s
[tkyte@tkyte-pc array_flat]$


<b>that should raise an eyebrow or two</b> -- hey, you mean we can make our code run faster with array fetching! 

it picks up where it left off -- on the last block. you know it had 10 rows, I know it had 10 rows, but the database just said "get me 10 and remember where we stopped". it didn't look necessarily to see that "hey, there isn't an 11th row, we should pick up on the NEXT block next time"

with the full scan extent map reading -- it is actually "more" then 20

1) it is not concerned only for full table scans. It is a mode blocks are gotten in in many cases.

blocks are either gotten in current mode (db block gets) or consistent read mode (consistent gets). We get the blocks in the mode we need them. If you do not know what "consistent gets" means -- please see the concepts guide and read the concurrency control sections -- and learn about multi-versioning and read consistency.

2) consistent gets ALWAYS come from the buffer cache.

rollback is buffered in the cache.

but yes, a consistent get may well involved reading rollback data for the affected segment.

current mode = get the block as it exists right now.

consistent mode = get the block as it existed at some point in the past.

that is wrong.

A physical IO performed in order to read a block into the cache will be followed by a subsequent logical IO to get the block out of the buffer cache. We can observe this via something like this:

big_table@ORA920LAP> alter tablespace users offline;

Tablespace altered.

big_table@ORA920LAP> alter tablespace users online;

Tablespace altered.

big_table@ORA920LAP>
big_table@ORA920LAP> select blocks from user_tables where table_name = 'BIG_TABLE';

BLOCKS
----------
13804

big_table@ORA920LAP> set autotrace on
big_table@ORA920LAP> select /*+ FULL(big_table) */ count(*) from big_table;

COUNT(*)
----------
1000000


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1329 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1329 Card=998400)




Statistics
----------------------------------------------------------
458 recursive calls
0 db block gets
13769 consistent gets
13647 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

big_table@ORA920LAP> set autotrace off


The alter's invalidate the buffers in the cache, ensuring a PIO. As you can see, if we use the formula:

memory reads = total reads - disk reads

we'd get it wrong. memory reads = consistent gets+db block gets.


I don't know what he means by memory reads, sometimes information taken out of context is hard to interpret. I don't know if it is a "mistake" or just taken out of context.

There are other nuances to consider as well -- direct IO done to TEMP (reads and writes) will appear as PIO but not as LIO -- that is, you can have a query that does more PIO then LIO (rare, but can happen) due to this.


I've been asked from time to time to put out "challenges". As I'm pressed for time this morning -- I'll try one.

In the following output -- we always process 10,000 or 20,000 rows. Only difference is a sort to disk. Soooooo tell me -- why the big differences in LIO's and PIO's between the two???

but it shows that the above formulas are not really accurate. some PIO's are followed by LIO's -- this is the "normal" case. some PIO's (direct reads) are not (hash joins, sorts may well result in this)

big_table@ORA920LAP> alter session set workarea_size_policy=manual;

Session altered.

big_table@ORA920LAP> alter session set sort_area_size = 65537;

Session altered.

big_table@ORA920LAP>
big_table@ORA920LAP> set autotrace traceonly
big_table@ORA920LAP> select * from big_table where rownum <= 10000;

10000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1329 Card=10000 Bytes=940000)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1329 Card=998400 Bytes=93849600)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
794 consistent gets
134 physical reads
0 redo size
773075 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

big_table@ORA920LAP> select * from big_table where rownum <= 10000 order by 1,2,3,4,5,6,7,8;

10000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82617 Card=10000 Bytes=940000)
1 0 SORT (ORDER BY) (Cost=82617 Card=10000 Bytes=940000)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1329 Card=998400 Bytes=93849600)




Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
138 consistent gets
257 physical reads
0 redo size
773086 bytes sent via SQL*Net to client
7825 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
10000 rows processed

big_table@ORA920LAP> select * from big_table where rownum <= 20000;

20000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1329 Card=20000 Bytes=1880000)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1329 Card=998400 Bytes=93849600)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1591 consistent gets
257 physical reads
0 redo size
1570644 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed

big_table@ORA920LAP> select * from big_table where rownum <= 20000 order by 1,2,3,4,5,6,7,8;

20000 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=82617 Card=20000 Bytes=1880000)
1 0 SORT (ORDER BY) (Cost=82617 Card=20000 Bytes=1880000)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1329 Card=998400 Bytes=93849600)




Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
279 consistent gets
536 physical reads
0 redo size
1570607 bytes sent via SQL*Net to client
15162 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
20000 rows processed

big_table@ORA920LAP> set autotrace off

this sort of shows why the outline I had in chapter 10 of Expert one on one Oracle is so important.

FIRST

a) tune in isolation. get that app really tuned in single user mode. here you are isolated from external events like this

THEN

b) tune in multi-user situations -- test to scale. then side effects from other users will become apparent (locking, blocking, things like this)

and lastly

c) tune the "system" after A and B are done.


In an OLTP environment -- for the query to be really affected by this consistent read mechanism means you are running a really really long query in a system with tons of updates. Normally the queries would be short and sweet -- the odds of having to undo 10,000 updates would be exceedingly small.

In a read mostly environment -- it won't happen of course.

So, it is more of a "mixed workload" environment and would only be suspected in the execution of long running queries against active tables (eg: your KNOWLEDGE of the charactertistics of the system will be crucial in looking at information)

1) well, it'll get the index block to find the table block, and then get the index block to find the next table block, and so on.

ops$tkyte@ORA920LAP> set echo on
ops$tkyte@ORA920LAP> /*
DOC>drop table t;
DOC>create table t ( x int, y int );
DOC>create index t_idx on t(x);
DOC>insert into t select rownum , rownum from all_objects;
DOC>*/
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA920LAP> begin
  2          for x in ( select * from t where x < 100 )
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> begin
  2          for x in ( select * from t where x < 200 )
  3          loop
  4                  null;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

select * from t where x < 100

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      100      0.00       0.00          0        200          0          99
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      102      0.00       0.00          0        200          0          99

Rows     Row Source Operation
-------  ---------------------------------------------------
     99  TABLE ACCESS BY INDEX ROWID T (cr=200 r=0 w=0 time=1748 us)
     99   INDEX RANGE SCAN T_IDX (cr=101 r=0 w=0 time=984 us)(object id 41082)
********************************************************************************
select * from t where x < 200

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      200      0.00       0.03          0        400          0         199
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      202      0.00       0.03          0        400          0         199

Rows     Row Source Operation
-------  ---------------------------------------------------
    199  TABLE ACCESS BY INDEX ROWID T (cr=400 r=0 w=0 time=29098 us)
    199   INDEX RANGE SCAN T_IDX (cr=201 r=0 w=0 time=27490 us)(object id 41082)


so, 100 rows -- 200 consistent gets.  200 rows -- 400 consistent gets.  2 consistent gets per row....


2) well -- it is a matter of the number of rows per block, the array size and such.  It just "was" in this case.  do that test case and play with the arraysize - set it up and watch that go down. set it down and watch it go up.

3) no, no it isn't.  you need to look closer at the example.  look at the consistent gets between colocated and disorganized.  It is all about the LOGICAL IO, even more so then physical.  they may well perform the same amount of physical io.

4) yes...


 

queries scale in many different ways.

there are queries that have the same number of CG's (consistent gets) regardless of the volume of data (eg: select * from t where pk = :x)

there are queries that will use N*CG's -- where N is some multiple of the data. So, if you have 100,000 rows, it'll do 1*CG, if you have 200,000 rows it'll do 200,000 rows

There are other queries that will use exp(CG) meaning -- it'll get worse and worse and worse in a linear/exponetial fashion as the volume increases.

i didn't mention that.

someone else did.

i showed it to be "not correct"

you cannot -- it is the nature of the query itself!!  it is a characteristic of the underlying data and how it works.

Ok, lets say you have a table:

create table emp ( empno int primary key, ename varchar2(25), deptno int );


very simple.  The rules are:

a) there are never more then 100 depts in any company, EVER.  data is somewhat distributed equally over deptnos 

b) empno is unique

c) we know NOTHING about ename really


Well, the query:

select * from emp where empno = :x

will scale with pretty much the SAME number of CG's regardless of the number of rows.  There will be 2/4 LIO's against an index followed by a table access by index rowid.  You'll have between 3/5 LIO's for that query regardless of whether you have 10,000 or 10,000,000,000 rows.


The query:

select * from emp where deptno = :x

will scale somewhat linearly with respect to the volume of data.  In general, the number of rows returned will be total_number_of_rows_in_table/100.  If you have 10,000 rows, you would expect 100 returned value.  10,000,000,000 rows -- 100,000,000 rows.  the number of CG's will go up in a somewhat predicable fashion as the number of rows increases.


The query

select * from emp where ename = :x

is a sort of an "unpredicable" query.  Say :x = 'SMITH'.  Well, That might return 1 row, all rows, most rows, some rows, many rows from the table (smith is a very popular name).  It could be that SMITH is not a popular name in small companies located in California.  It could be that SMITH is the *most* popular name for small companies located in Texas.  So, this query -- even with small tables could return wildly different CG's in different implementations -- due solely to data skew.

does that make sense?

Now, consider 

select ..
  from t1, t2, t3, t4, t5 ....
 where ......


and you can see, it gets harder to generalize.  IF YOU KNOW your data and what it means, you can generalize that.  


I guess one way to do this would be

a) set up your tables
b) fill them with a bit of data
c) analyze & explain plan your queries
d) fill them with a bit more data
e) goto c/d a couple of times


then, you can look at the row sources to see how the queries are going to scale.  for example, let's run the above scenario using "emp"


ops$tkyte@ORA920PC> create table emp ( empno int primary key, ename varchar2(25), deptno int );
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create index emp_ename_idx on emp(ename);
 
Index created.
 
ops$tkyte@ORA920PC> create index emp_deptno_idx on emp(deptno);
 
Index created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into emp
  2  select object_id, owner, mod(rownum,100)
  3    from (select * from all_objects order by reverse(object_name) )
  4   where rownum <= 100;
 
100 rows created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> variable x number
ops$tkyte@ORA920PC> variable y varchar2(25)
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> analyze table emp compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA920PC> set autotrace traceonly explain
ops$tkyte@ORA920PC> select * from emp where empno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=10)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=10)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C007176' (UNIQUE)
 
 
 
ops$tkyte@ORA920PC> select * from emp where deptno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=10)
   2    1     INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=1)
 
 
 
ops$tkyte@ORA920PC> select * from emp where ename = :y;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=20 Bytes=200)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=20 Bytes=200)
   2    1     INDEX (RANGE SCAN) OF 'EMP_ENAME_IDX' (NON-UNIQUE) (Cost=1 Card=20)
 
 
 
ops$tkyte@ORA920PC> set autotrace off
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into emp
  2  select object_id, owner, mod(rownum,100)
  3    from (select *
  4            from all_objects
  5                   where not exists (select null
  6                                       from emp
  7                                                          where empno=object_id)
  8           order by reverse(object_name) )
  9   where rownum <= 900;
 
900 rows created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> analyze table emp compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA920PC> set autotrace traceonly explain
ops$tkyte@ORA920PC> select * from emp where empno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=10)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C007176' (UNIQUE) (Cost=1 Card=1000)
 
 
 
ops$tkyte@ORA920PC> select * from emp where deptno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=100)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=10 Bytes=100)
 
 
 
ops$tkyte@ORA920PC> select * from emp where ename = :y;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=59 Bytes=590)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=59 Bytes=590)
 
 
 
ops$tkyte@ORA920PC> set autotrace off
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into emp
  2  select object_id, owner, mod(rownum,100)
  3    from (select *
  4            from all_objects
  5                   where not exists (select null
  6                                       from emp
  7                                                          where empno=object_id)
  8           order by reverse(object_name) )
  9   where rownum <= 9000;
 
9000 rows created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> analyze table emp compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA920PC> set autotrace traceonly explain
ops$tkyte@ORA920PC> select * from emp where empno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=11)
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C007176' (UNIQUE) (Cost=1 Card=10000)
 
 
 
ops$tkyte@ORA920PC> select * from emp where deptno = :x;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=100 Bytes=1100)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=100 Bytes=1100)
 
 
 
ops$tkyte@ORA920PC> select * from emp where ename = :y;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=294 Bytes=3234)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=4 Card=294 Bytes=3234)
 
 
 
ops$tkyte@ORA920PC> set autotrace off


so, we see:


    rows  =>    100         1,000            10,000
empno = :x        1             1                 1
deptno =:x        1            10               100
ename = :y       20            59               294



now, you see how each of those will "scale".


It is the nature of the data itself.

 

No, card = cardinality = estimated number of rows.

consistent gets and card may be miles and miles apart.

multi-block-read count affects:

a) the cost of the full scan as seen by the CBO
b) the performance of the full scan if we have to do physical IO


it will not in this case affect consistent gets....

All I wanted to do was show

1) getting 10,000 rows
2) getting 10,000 rows and sorting them


so, rownum was OK for this demo.

sort = read all - sort it, put in into private memory, retrieve it

no sort = read it bit by bit by bit as you hit it.


the sort was like a "bulk fetch with arraysize = ALL ROWS at once", less consistent gets overall since we "consistently got them" all at once - instead of row by row by row.

(it was part of the 'challenge')

No, do not think about using a "sort" as a "pattern"

Use order by when you need sorted data.

It used more memory in the server process, it did more work to get the first row then the last, you waited longer to start seeing data, it used perhaps more CPU.

for a full scan, we read the segment header to get the extent map. you are seeing that extra read done to figure out what to read for you.

we use consistent gets to get a block consistently (as of a point in time) it may or may NOT require a read aside to the RBS but the block is gotten "consistently" regardless.

1) no, we ALWAYS get the blocks in consistent read mode.  You cannot tell until you get to the block if the data is "good enough for you".  You always process in CR mode therefore.

2) yes, it is read consistency there.  Your query read each block and had to roll back all of the inserts on that block in order to see that "hey, this block is actually EMPTY as far as I'm concerned"

3) the index is probably on a NULLABLE column.

ops$tkyte@ORA920PC> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create index test_idx on t(x);
 
Index created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> set autotrace traceonly explain
ops$tkyte@ORA920PC> select /*+ index( t test_idx ) */ * from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)
 
 
 
ops$tkyte@ORA920PC> alter table t modify x NOT NULL;
 
Table altered.
 
ops$tkyte@ORA920PC> select /*+ index( t test_idx ) */ * from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=82 Bytes=2132)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=826 Card=82 Bytes=2132)
   2    1     INDEX (FULL SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=26 Card=82)
 
 
 
ops$tkyte@ORA920PC> set autotrace off


<b>since entirely null keys are not put into b*tree indexes -- we cannot use the index if X allowed nulls (you would not get the right answer since the index does not contain all of the rows in the table)</b>

 

1) no
2) wait events -- use 10046 level 12 trace event (search for 10046 on this site if that doesn't mean anything to you) and see what it is waiting for.

and remember, two machines differ in thousands of ways.

3) queries against the data dictionary, last analyzed is in there as well as sample sizes

4) since I don't know what they "problem" is or even if there is one, a checklist won't help. it is usually "obvious". do you have a 9i tkprof with wait events that one could look at.

5) show us a tkprof - it should be somewhat "obvious"

6) you do not infer from tkprof -- it is spelled out (there is a physical reads column, there are current and query mode (logical) columns. they are told to you.

from autotrace, again, they are spelled out -- they are right there. consistent gets, db block gets ( logical ios) and physical reads. no infering, just reading.

nope, as i said -- show us the tkprof and it should be "obvious"

goes up and down.  40k is "noise" as far as I'm concerned.  test was really skinny, not a good test.

we'll just attribute it to gremlins this time....


bigger, fatter table:

ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2> select * from all_objects;
 
29512 rows selected.
 
 
Statistics
----------------------------------------------------------
    2097689  bytes sent via SQL*Net to client
 
ops$tkyte@ORA9IR2> set arraysize 100
ops$tkyte@ORA9IR2> /
 
29512 rows selected.
 
 
Statistics
----------------------------------------------------------
    1895377  bytes sent via SQL*Net to client
 
ops$tkyte@ORA9IR2> set arraysize 1000
ops$tkyte@ORA9IR2> /
 
29512 rows selected.
 
 
Statistics
----------------------------------------------------------
    1863191  bytes sent via SQL*Net to client
 
ops$tkyte@ORA9IR2> set arraysize 5000
ops$tkyte@ORA9IR2> /
 
29512 rows selected.
 
 
Statistics
----------------------------------------------------------
    1860287  bytes sent via SQL*Net to client
 

you cannot compare the cost of two different plans under two different settings of optimizer parameters.

doesn't make sense.


the optimizer parameters cause the costing model to "change", that is their goal. by changing them -- you changed the method by which the numbers are assigned. It would be expected that the costs are "different", the way these parameters work in general is by changing the costing model.

Now, your test is a little invalid. You asked "please optimize to get the first 10 rows ASAP" and then fetched all 359. You "lied" to the optimizer ;) Use this:

alter session set sql_trace=true;
declare
cnt number := 0;
begin
for x in ( select * from customers c, sales s
where c.cust_id = s.cust_id
and cust_last_name = 'Smith'
and s.time_id <= TO_DATE( '31-DEC-1998' ) ) /* pet peeve of mine */
loop
cnt := cnt + 1;
exit when (cnt=10);
end loop;
end;
/

and use tkprof to analyze the outcome with different optimizer settings.

I've also said that there are diminishing marginal returns to everything. I found an arraysize of about 100 is the "sweet spot" for array fetching in general.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12226527910608#15916220123225 <code>

yes, the goal is to reduce LIO's in general when tuning a query. array fetching is a tool but only to a point.

the sort caused them to be read all at one -- on the FIRST fetch.

do this:


begin
for x in ( select x, y, z, a from t )
loop
exit;
end loop;
end;
/

begin
for x in ( select x, y, z, a, count(*) over () cnt from t )
loop
exit;
end loop;
end;
/

instead and you'll see what I mean. reads from "temp" are not LIOs.

it is as if you set arraysize = infinity.

I would *not* recommend this approach (in fact, i'm on record time and time and time and time and time again saying 'counting the hits is "not a good thing" (tm)') at all, not even a tiny bit.

er? what link -- not really sure what you mean (nor would I agree with "i can't use bulk collect")

1) it just does, don't know, it seems to happen due to the CTAS (if you create empty, insert append, it doesn't happen).

2) the redo size >0 is block cleanout (if you have expert one on one oracle, I go into this in detail)

the consistent gets are high just due to the RBS processing.

3) every row update will result in a CURRENT MODE read -- and db block gets are current mode reads. we do a consistent read to find the rows, and then a current mode read to update it.

it'll be just like an update (subject to restart). It will be read consistent, perhaps not with respect to the point in time at which it was submitted, but at some point in time between the time it started and the time it completed.

see
</code> http://asktom.oracle.com/~tkyte/wc.html <code>

in tkprof they are at the level of the application,that particular execution.

in a statspack they are system wide, over "every" possible tkprof....

so tkprof is a detailed view, for a particular session whereas statspack is over all sessions for that period of time.

sure, if the table was really "hot" and you were updating lots of indexed columns and the query used lots and lots of indexes and/or the query fetched a row and worked on it a while and then fetched a row

sure. seems extreme, but yes.

using sqlplus -- we have array fetching going on. 

Lets say the table data was ordered with respect to the index (eg: index on x, table data is sorted by x).

now, we ask for x < 100.

we are using sqlplus -- arraysize defaults to 15.


we as for first row (we are doing to get 15).  we read an index block, locate row one.  we get the table block for row 1 (and don't give it up)..  we locate row 2 (still have the index block we need) and get table block for row 2 -- discover "we already have that block in our session -- cool, just use it" and so on.  we get the first 15 rows with 2 LIOS

suppose we did that row by row.  we would have 2 LIOS per row or 30 LIOs for the first 15.


That is how array fetching can not only improve wall clock response time (takes less time to do 2lios and transfer 15 rows in bulk then it does to do 30 lios and transfer a row by row by row) but also scalability - since the LIO's involve latching.


try this out:

ops$tkyte@ORA9IR2> drop table t;
Table dropped.
                                                                                
ops$tkyte@ORA9IR2> create table t ( x int primary key, y int );
Table created.
                                                                                
ops$tkyte@ORA9IR2> insert into t select rownum, rownum from all_objects where rownum <= 101;
101 rows created.
                                                                                
ops$tkyte@ORA9IR2> set autotrace traceonly statistics
ops$tkyte@ORA9IR2> set arraysize 2
ops$tkyte@ORA9IR2> select * from t where x < 100;
                                                                                
99 rows selected.
                                                                                
Statistics
----------------------------------------------------------
        101  consistent gets
                                                                                
ops$tkyte@ORA9IR2> set arraysize 10
         22  consistent gets
ops$tkyte@ORA9IR2> set arraysize 20
         12  consistent gets
ops$tkyte@ORA9IR2> set arraysize 50
          6  consistent gets
ops$tkyte@ORA9IR2> set arraysize 100
          4  consistent gets


the bottom was edited - i just reran the same query with different array sizes. 

"block cleanouts" (search for that) could cause it.  

and I see 7 recursive calls -- could be contributing factors as well.

ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2> select rownum from all_objects;
 
28713 rows selected.
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     134211  consistent gets
          0  physical reads
          0  redo size
     399714  bytes sent via SQL*Net to client
      21553  bytes received via SQL*Net from client
       1916  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      28713  rows processed
 
ops$tkyte@ORA9IR2> select s.nextval from all_objects;
 
28713 rows selected.
 
 
Statistics
----------------------------------------------------------
      20104  recursive calls
       4351  db block gets
     135647  consistent gets
          0  physical reads
    1214332  redo size
     409812  bytes sent via SQL*Net to client
      21553  bytes received via SQL*Net from client
       1916  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      28713  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off


<b>there were in this case updates to SEQ$ executed as "autonomous transactions" that caused the redo</b>
 

autonomous transactions have been in the server forever and an age ago.

they were simply exposed in 815.

Want to guess how long "flashback query" has been around?


There are thousands, perhaps millions, of things "internal" that are not exposed.

no.
only thing you can say is somewhere between 0 and 318.

q1) one time query is done here, next time over there (remote). autotrace shows only your local statistics.

q2) local only

q3) yes, it can if it wants to (but you won't see the consistent gets, they happened remotely)

for the query in question -- a simple full scan.

This example demonstrates the effect arraysize can have on logical IO. Oracle will get and reget a block over and over and over. If you are full scanning a table with 100 blocks and each block has 100 rows and you array fetch 10 at a time, you'll get each block 10 times -- and therefore perform 1000 logical IO's.

If you array fetch 100 at a time, you'll get each block once and performan 100 logical IO's

But, if you select count(*) from that_table -- you'll do 100 logical IO's regardless of arraysize.

There is no hard and fast formula for every query.

no you aren't? there was one physical IO, from disk to buffer pool. there was one logical io from buffer pool

unless we are doing a direct read, yes. A physical IO to read into the buffer cache would be followed by "get it from the buffer cache"

I see the decrease, no increase?

but without a predicate, one could assume a different plan and a different plan will likely have different IO's associated with it.


I don't like your code at all. You should just be issuing a DELETE, no procedural code at all, especially if you are concerned about reducing the amount of work performed

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

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=7237 Card=50000
Bytes=26100000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTAB' (Cost=7237 Card=50000
Bytes=26100000)
2 1 INDEX (FULL SCAN) OF 'XPKMYTAB' (UNIQUE) (Cost=94 Card=50000)


select /*+ FIRST_ROWS */ RowID RID, MyPKCol, MyTxtCol from MyTab where MyPKCol >
:A order by MyPKCol
---------------------------------------------------------------------------------
-------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=67 Card=2500
Bytes=1305000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTAB' (Cost=67 Card=2500
Bytes=1305000)
2 1 INDEX (RANGE SCAN) OF 'XPKMYTAB' (UNIQUE) (Cost=2 Card=450)


totally different plans, I would be amazed IF they did the same IO.

No where clause -- it read the ENTIRE index (index full scan) from front to well, when you told it to stop.

With where clause -- it range scanned PART of the index. getting the rows immediately, skipping the "front" of the index.


You cannot even remotely expect the IO's from two different plans to be even remotely similar except by pure accident.

the index RANGE scan sure.

the index FULL scan without the predicate -- NOT.


the index FULL SCAN does not start at the very first *existing* row, it started way over in the left hand side of the index which could contain deleted entries. You have what I call a "sweeper" index.


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2913600659112#24081704877485 <code>

because it depends on how the query is processed. In your case - I see 5 sorts in memory. Most likely, the last row of the query is "found" (processed) before the FIRST row is retrieved. Therefore, the entire result set is in TEMP before you get the first row. IO from TEMP is not a "consistent get" (doesn't need to be, we did the consistent get to put the data into temp in the first place).


This arraysize setting will affect LIO for queries that are "first row" type queries only -- generally, ones without say a GROUP BY (group by is generally an "all rows" query -- you have to get an process all of the rows before you get the first row back from the database.


If you have effective Oracle by Design - I cover what I call "fast return" and "slow return" queries in a lot more detail.

select count(*)  <<<<====

think about how many rows that processes, sends back.  hmmm, how many round trips happened there...


In the second case, you see exactly what I described?  There are blocks gotten just because "it is a full scan" and you did some consistent gets just to figure out what to full scan:

ops$tkyte@ORA9IR2> set arraysize 5
ops$tkyte@ORA9IR2> set autotrace traceonly
ops$tkyte@ORA9IR2> select * from emp where empno=0;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        589  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
 
<b>two of them in fact, for zero rows we did 2 gets to figure out that emp is a one block table and read it finding nothing...</b>

ops$tkyte@ORA9IR2> select * from emp;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1582  bytes sent via SQL*Net to client
        521  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed
 
ops$tkyte@ORA9IR2> set autotrace off

<b>Now, we did 2 LIO's to figure out what to read - leaving 4 LIO's to account for.  SQLPlus (you can turn on trace to see this) fetches the first row AND THEN starts array fetching so

1 LIO for row 1
1 LIO for rows 2, 3, 4, 5, 6
1 LIO for rows 7, 8, 9, 10, 11
1 LIO for rows 12, 13, 14


</b>

 

consistent gets come from buffer cache. Don't know where the "irrespective" comes in.

a consistent get may well have been preceded by a physical io, but the get is from the buffer cache.

the 4 will vary from release to release. It was the cost of donig a full scan - to read the segment header to figure out what to read.

sure, I can reproduce that:

ops$tkyte@ORA9IR2> set autotrace on
ops$tkyte@ORA9IR2> select * from p where x = 1;

         X
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'SYS_C003797' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        372  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA9IR2> delete from p where x = 1;

1 row deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'P'
   2    1     INDEX (UNIQUE SCAN) OF 'SYS_C003797' (UNIQUE)




Statistics
----------------------------------------------------------
        219  recursive calls
          7  db block gets
        488  consistent gets
          0  physical reads
        556  redo size
        790  bytes sent via SQL*Net to client
        798  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>



I should tell you what I did though to make that happen (like you should with me, set up a test case please....)


create table p ( x int primary key );
insert into p values ( 1 );
insert into p values ( 2 );
create table c
as
select 2 x, all_objects.* from all_objects;


any unindexed foreign keys sitting out there in your system?
 

indeed, missed the

alter table c add constraint c_fk foreign key (x) references p
/

in the cut and paste.

so, can you create a table, and reproduce the issue. help me help you - give me a test case to work with.

all I said was "given what you've given me so far, yes, I can make a delete do lots more work...."

so, give me a test case to work with - you should be table to create a table just like table1, insert some data and reproduce the issue. Then we can look at it.

doubtful, more likely something hidden like auditing, fine grained access control, a missed trigger, a foreign key - something like that that you will find as you make the test case ;)

indeed! but how so given 1 row deleted :)

I suppose it could be the indexes, but - i would have to set up something to simulate it.

6572 consistent gets
4262 physical reads

for 1 row deleted, they would have to be some seriously damaged indexes to do that.


Data purging via delete - we need to talk about that, there are better ways. Tell you what you are deleting 10,000,000 rows - out of how many? and by what criteria? and is this the FIRST TIME or the ONLY TIME you are going to do this? what is the table structure and what do the indexes look like on it (no tablespaces/storage, just simple ddl)

that indeed would be it.

surprised you did not have deadlocks all over the place - everything must be running faster now.

but still, answer the questions from above:

Data purging via delete - we need to talk about that, there are better ways.
Tell you what you are deleting 10,000,000 rows - out of how many? and by what
criteria? and is this the FIRST TIME or the ONLY TIME you are going to do this?

... "I would prefer to move the "wanted" data to another table, "....

me too. why can't we do that - it would take just a couple of minutes.


the bitmaps should be replaced with b*trees, your indexes are likely many times bigger than they should be and are not making things "faster"



2) because they don't know better. They have been scared off by people that talk like they know what they are talking about saying integrity "slows down" databases which is complete $@#.

this would be a offline operation.

or:

create new empty table
put indexes on it, grant on it, etc....

then:

rename old_table to HOLD;
rename new_table to old_table;
insert into old_table select * from hold where <keep these is true>;


it would be "offline" for the duration of the two renames only.


or (my preference)

design a new approach to this alltogether so you never are faced with this again (eg: partitioning), and then create a new partitioned table and do the switch...

no, I used the segment header reads as an example.  We do current mode reads in other cases as well -- UPDATE for example will use consistent read to FIND a block - it will use current mode reads to actually modify the block (you have to modify the current copy of the block after all)


ops$tkyte@ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA10GR2> set autotrace on statistics;
ops$tkyte@ORA10GR2> update t set object_name = lower(object_name);

49937 rows updated.


Statistics
----------------------------------------------------------
        642  recursive calls
      52769  db block gets
      51340  consistent gets
        686  physical reads
   17110600  redo size
        920  bytes sent via SQL*Net to client
        960  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      49937  rows processed

 

1) there is no "array fetching" with the update - the update is a single call, all rows processed (or not)

2) transaction (undo) work was taking place as well. look at used_ublk in v$transaction next time. you were generating undo.

what happens when you

set arraysize 100
or
set arraysize 500

in sqlplus with the hash join (the somewhat obvious answer is "likely the hash join"

the first one reads the tables once, sorts them into temp and returns the data from temp (no logical IO on temp)

the second one reads and hashes one of the tables - and the reads the second table and hash joins to the other one. You are likely having more than 15 rows per block on the second table being processed - hence you are getting and "re-getting" blocks.

Increasing the array fetch size to something more appropriate for a query that returns 1.2 million records will reduce the LIO.




big_table@ORA10GR2> set autotrace traceonly statistics;
big_table@ORA10GR2> select * from big_table;

1000000 rows selected.


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
80146 consistent gets
14061 physical reads
608 redo size
112310317 bytes sent via SQL*Net to client
733711 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

big_table@ORA10GR2> set arraysize 100
big_table@ORA10GR2> select * from big_table;

1000000 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24302 consistent gets
13574 physical reads
564 redo size
104943607 bytes sent via SQL*Net to client
110374 bytes received via SQL*Net from client
10001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed

what are you confused about first of all?

I suggested 100, not 500.

hash joins work like this:


a) read one of the tables from start to finish and hash it.
b) wait for client to ask for some rows
c) when client asks for some rows - read some rows from table2


it is step c) that would be affected by the client array fetch size. If the client asks for a single row (and lets say that table1 and table2 have a one to one relationship to eachother to make the math easy) AND table2 has 100 rows per block - you end up getting the first block 100 times.

If the client on the other hand array fetches 100 rows, you end up getting block one from the buffer cache once.

your stats are likely out of date.

but you include nothing really to reproduce with - I would never expect scott.emp to have 16'ish blocks.

ok, that makes sense with ASSM - it wastes space in order to increase concurrency during modifications.


As for the test - I would just say "way too small", this is a case of "six one way, half a dozen the other". The costs are basically about the same in this case.

sqlplus always does a single row fetch and then starts array fetching, that'll affect this definitely. So,

arraysize 9

read 1 - read block 1 row 1
read 2 - read block 1 rows 2-9, block 2 row 1
read 3 - read block 2 rows 2-9, block 3 row 1
read 4 - read block 3 rows 2-9, block 4 row 1

So, each block is read twice in that case.

arraysize 10

read 1 - read block 1 row 1
read 2 - read block 1 rows 2-9, block 2 rows 1-2
read 3 - read block 2 rows 3-9, block 3 rows 1-3
...

and there almost every block is read twice.


sql_trace=true is your friend. You can see the rows fetched on each call in the trace file directly.


I set arraysize 9 and ran a select * from t with 200 rows:

PARSING IN CURSOR #13 len=15 dep=0 uid=78 oct=3 lid=78 tim=1113963019093011 hv=1134051363 ad='43c7cd88'
select * from t
END OF STMT
PARSE #13:c=4999,e=4824,p=0,cr=32,cu=0,mis=1,r=0,dep=0,og=1,tim=1113963019092993
EXEC #13:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1113963019093243
FETCH #13:c=0,e=146,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1113963019093481
FETCH #13:c=0,e=110,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019094329
FETCH #13:c=0,e=93,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019094882
FETCH #13:c=0,e=76,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019095326
FETCH #13:c=0,e=72,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019095788
FETCH #13:c=0,e=57,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019096166
FETCH #13:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019096484
FETCH #13:c=0,e=52,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019096816
FETCH #13:c=0,e=50,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019097121
FETCH #13:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019097412
FETCH #13:c=0,e=51,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019097739
FETCH #13:c=0,e=47,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019098048
FETCH #13:c=0,e=45,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019098336
FETCH #13:c=0,e=49,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019098661
FETCH #13:c=0,e=51,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019098970
FETCH #13:c=0,e=48,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019099273
FETCH #13:c=0,e=55,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019099588
FETCH #13:c=0,e=52,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019099904
FETCH #13:c=0,e=47,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019100207
FETCH #13:c=1000,e=84,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019100546
FETCH #13:c=0,e=137,p=0,cr=4,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019101181
FETCH #13:c=0,e=81,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019101954
FETCH #13:c=1000,e=184,p=0,cr=2,cu=0,mis=0,r=9,dep=0,og=1,tim=1113963019102596
FETCH #13:c=0,e=88,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1113963019103128


r=1 shows the rows fetched on each call.

because for the IOT we read the root block, and stop.

We don't need to read the segment header block to figure out what blocks to full scan - we just goto the root block and discover "there are no leaves to goto" and stop.


full scan = "read segment header to figure out what block ranges to full scan, then full scan them"

iot range scan = "read root and from root figure out what to read next - like a branch block, leaf block, whatever. In the case the root = leaf, you are done in 1 IO"

the number of IO's performed against the segment header (and even the TYPE of IO done - current or consistent) vary by version.

We never promised to read it just once.

Older releases would have even a higher count.

I will guess

band is indexed.

tell us more. This is likely a case where the IO's are against the index being maintained. You appear to be updating every row an AVERAGE OF 4.5 TIMES!!!! Why is that!

give us the details on the gtt, what band is datatype wise, indexes involved, the transaction semantics (where is the commit and such),


Something is funny there - you executed this 523,042 times - but the row source only shows 189,300 - we'll need more information - details as to how to reproduce this.