Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Rebound for database design?

My fellow Oaktable member Robyn Sands posted something very "inline" with the Helsinki declaration. Good comments too.

on the importance of a good data model ...

An article written by Bert Scalzo was published in Information Management this week. The topic is 'Is Data Modeling Still Relavant?': it's short, to the point and well worth reading.The article doesn't recommend a specific tool, it simply recommends the practice of capturing a model of the data at rest (old school approach) in addition to the newer techniques that focus on capturing the data in

Manually Booting Tuxedo Application Server Processes in Parallel

Normally when an Application Server is booted, initialisation of each process completes before the next one is started. The ability to boot Application Server processes in parallel was added to the psadmin utility in PeopleTools 8.48. However, psadmin is merely a wrapper for the BEA Tuxedo tmadmin command line utility, and it has always been possible to do this manually in previous versions of PeopleTools via the tmadmin utility as follows.

1. Boot the Tuxedo Bulletin Board Liaison process.

#boot the Tuxedo administrative processes
boot -A

2. Boot the PeopleSoft Application Server processes and but specify the -w parameter so that they don't wait as they start

boot -g APPSRV -w

If you are running PUBSUB or other servers in other groups then you would also boot them here.

3. Boot the JREPSRV process (which maps Java Classes to Tuxedo Services).

boot -g JREPGRP

4. List the servers with print server so you know that the PeopleSoft servers are booted.

psr

5. When all the other processes have booted, boot the WSL and JSL processes.

boot -g BASE
boot -g JSLGRP

Manually Booting Tuxedo Application Server Processes in Parallel

Normally when an Application Server is booted, initialisation of each process completes before the next one is started. The ability to boot Application Server processes in parallel was added to the psadmin utility in PeopleTools 8.48. However, psadmin is merely a wrapper for the BEA Tuxedo tmadmin command line utility, and it has always been possible to do this manually in previous versions of PeopleTools via the tmadmin utility as follows.

1. Boot the Tuxedo Bulletin Board Liaison process.

#boot the Tuxedo administrative processes
boot -A

2. Boot the PeopleSoft Application Server processes and but specify the -w parameter so that they don't wait as they start

boot -g APPSRV -w

If you are running PUBSUB or other servers in other groups then you would also boot them here.

3. Boot the JREPSRV process (which maps Java Classes to Tuxedo Services).

boot -g JREPGRP

4. List the servers with print server so you know that the PeopleSoft servers are booted.

psr

5. When all the other processes have booted, boot the WSL and JSL processes.

boot -g BASE
boot -g JSLGRP

Resuming transmission...

I've been busy lately: preparing papers and presentations for the upcoming ODTUG, presenting at Hotsos-revisited, and presenting at the Dutch DBA-symposium. All spare time went into these activities, and the Helsinki blog just had to wait in line. But now I intend to resume transmission over here again.A couple of weeks ago I received following comment on this blog:toon,Suppose we have a

Collaborate09 thoughts...

I've been meaning to write up my thoughts from this years IOUG Collaborate event.  Ian Abramson, the president of the IOUG, has a nice series of write ups on the event itself - starting with day 1 through day 3 and then a wrap-up post.  He describes it as a three day event - but to me it is more of a four day thing.  For the last couple of years, I've done a university day at Collaborate - so once again I spent all day Sunday May 3rd in a conference center talking about Oracle for eight hours.  This year I talked about Encryption, all of the Flashback technologies, Read & Write Consistency (a really nice way to introduce the flashback stuff actually, sort of a pre-requisite) and Database Reorganizations (when to, when not to and when you have to - how to).

I also had a one hour "regular" session on Monday where I presented on "What's new in Oracle Application Development" where I touched on general database enhancements, ODP.Net, PHP/Ruby/Python, Java/JDBC, SQL Developer and APEX advances.  This is the session where I got most of the questions/comments this year.

I think I might have scared away some of the DBA crowd with my session title, they see "Application Development" and stay away - but I know for sure at least one DBA was in the audience.  He came up to me afterwards and commented on how glad he was to have attended - for a pretty simple reason.  In each of the tools I discussed (mostly .NET, SQL Developer and APEX) - I pointed out how they easily tied the application into AWR (automatic workload repository) and ASH (active session history) facilities and how they could be used as an interface to the tuning/diagnostic packs.  He is sure the developers he supports are unaware of this tie in and he himself certainly was - but his first course of action upon getting back to the office would be to expose them to it.  The problem he has had in the past is that many people consider tuning "a DBA task" - when in fact - it is really a developer's thing to do.  Since most developers don't have access to, or don't want access to Enterprise Manager - they assumed the advanced tuning features were not available to them.  Not any more - all of the development tools now have tie in's to AWR/ASH and various features of the tuning/diagnostic packs.

One of the areas of large interest is the new (in beta) SQL Developer data modeling features.  A lot of people (self included) still find the good old ERD (entity relationship diagram) a good way to envision/model your schema.  SQL Developer now has an engine to do just that - with the ability to spit out various different physical models from the same logical model - either in support of different databases (yes, it does not just do Oracle), or in support of different environments (simple storage characteristic changes).

 

One thing I find interesting/good about an event like Collaborate is the ability to learn something new.  Even after using Oracle for going on 22 years, I still learn something new often (almost every day).  Regardless of your skill level, there is the opportunity to expand your knowledge of the stuff you use.  For example - Jonathan Lewis (a pretty smart guy when it comes to Oracle knowledge) posted about some things he discovered for the first time.  I strongly encourage everyone to attend at least one event if possible every year - to network, to learn and..... to present.  IOUG Collaborate is a good venue for doing that - UKOUG is another - and the call for papers for the UKOUG event in November is currently happening....  Take a chance - go for it.  It'll probably go much better than my first presentation :)

New job, lots of exciting stuff

It’s been a week since I started my new job at Oracle Corporation. I’m a remote worker which means that the first day of work wasn’t the usual event since I just went to my home office and got on a concall with my new manager. After getting connectivity and accounts set up properly, I was able to pretty quickly work through the new hire checklist of forms and mandatory training.

My new Oracle-provided laptop arrived around mid-week and I realized that, at least for now, I’ll have to revert back to using the Windows-based laptop and (hopefully temporarily) put my MacBook Pro on the shelf. Actually, my wife is very excited since she’ll get the MBP to use now and we’ll do the usual “trickle down” to the kids so that the oldest computer in the “fleet” will get ditched.

I tried “upgrading” to a new DSL line (from cable modem) last week too, but that appears to have failed as the DSL modem drops my connection on a regular basis for a few seconds. That’s just long enough to break the VPN connection and make it appear that I’m bouncing off and on instant messaging every hour or so. Annoying.

I was excited to finally get on to some real technical work late last week and got to login to my first Exadata storage server. The chores were to re-image 4 servers and apply the latest patches to them. The real fun is starting now!

Of course, the most important part of any job (in my opinion) is people. I have had several virtual meetings with my peers and manager so far and, as expected, they’re all superb. I’m looking forward to working more with the team and others in our companion teams as well as the days roll ahead.

User objects created in the SYS schema and the (cost based) optimizer

The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, 10.2.0.4 and 11.1.0.7 on Win32).

Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.

All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.

There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):

1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.

2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.

Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?

Scuba diving pre-ODTUG Kaleidoscope, Monterey, 21-June-2009

I’m very pleased to report that I will be able to meet up with ODTUG Kaleidoscope attendees at both the ODTUG Community Service Day (2nd Annual!) and my own scuba dive outing as well. If you can, I’d love for you to attend both events. If you’re not a certified scuba diver, then you can at least participate in the Community Service Day festivities and help out the local area while enjoying some California weather too!

For those certified scuba divers that will (or can) be in the Monterey Bay area on 21-June, I invite you to come diving with me. I’ve arranged some reserved spots on the Beachhopper II dive charter that I’ve dove with before. Brian and Mary Jo (the captain, crew, and bottle washers) are top notch and we had a great time last fall at the first annual pre-OpenWorld scuba event (look for more details on the 2nd annual event later this summer). The boat isn’t huge, but 10 divers is enough for a lot of fun.

The pre-Kaleidoscope dive day is Sunday, 21-June (Father’s Day). The boat will depart the K dock at Monterey Bay harbor at 8am, so load-up is 7:30am. We’ll have a nice morning, drain 2 tanks at some of the best sites you’ll see in northern California (specific sites will be determined that morning by the captain and diver requests), and then motor back to the harbor probably shortly after noon or 1pm. Mary Jo said that she’d also entertain the option of an afternoon 2-tank trip as well, if there is interest (I know I’m interested). Oh, I almost forgot to mention that snacks are provided and they are amazing–made by Mary Jo herself!

The boat costs break down like this:

  • $70 for the boat trip (weights are not included)
  • plus $20 for two tanks of air ($90 total)
  • or $30 for 2 tanks of Nitrox ($100 total)

The charter doesn’t offer gear rental, so we’ll have to pick that up separately. I previously rented from Glenn’s Aquarius 2 which is located pretty close to the harbor and opens at 7am for morning pickup. Their pricing for rental are:

  • Weights only: $8
  • Wetsuit, hood, gloves: $21
  • Full gear (BCD, reg, exposure suit, etc.): $65

We’re less than 1 month away (I just found out I was going to be able to attend last week), so let me know ASAP if you’re interested in diving with us. Once you contact me, I’ll send you the signup instructions. I’m releasing the remaining open seats on 29-May, but there may still be open spots after that, so contact me (comment below, or email) if you’re interested.

As a special treat, Stanley will be joining us for his first scuba dive as well!

Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple blocksizes - part 4

Back to part 3

Using objects residing in multiple blocksizes

I've already mentioned it several times on my blog but I would like to take the chance here again to stress the point that the cost based optimizer does a bad job when it comes to calculating costs for full table scans of objects residing in non-default block sizes. It really looks like that this feature has been introduced to support transportable tablespaces but it obviously hasn't been tested very thoroughly when it comes to cost calculation.

Each of the different modes has its deficiencies when dealing with objects in non-default blocksizes. The somehow odd thing is that the traditional I/O costing does the best job, and all system statistics based calculations are utterly wrong.

Traditional I/O based costing

The traditional I/O based costing simply scales the MBRC up or down according to the non-default blocksize to come to the same I/O read request size. So if you e.g. have a MBRC of 8 and a default blocksize of 8KB and now calculate the cost for an object residing in a 2KB tablespace, the MBRC will be multiplied 4, which results in a MBRC of 32. The I/O cost will be different although due to the different adjustment used with the higher MBRC setting. The adjusted MBRC for 32 is 16.39 whereas the adjusted MBRC for 8 is 6.59, so the calculated cost for the full table scan of the object residing in the 2KB tablespace will be higher. Likewise the same happens when using an object in a 16KB non-default tablespace. The MBRC will be reduced accordingly to 4 to get the same I/O read size again. Since adjusted MBRC for MBRC = 4 is 4.17, the cost calculated will actually be less for the object residing the 16KB tablespace.

System statistics

Things get more complicated when using NOWORKLOAD or WORKLOAD system statistics.
To recall the formula to calculate the I/O cost of a full table scan with system statistics is:

Number of blocks / MBRC * MREADTIM / SREADTIM

And in case of NOWORKLOAD system statistics the MREADTIM and SREADTIM are synthesized using the following formula:

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTRFSPEED

MREADTIM = IOSEEKTIM + DB_BLOCK_SIZE * MBRC / IOTRFSPEED

Now if the object resides in a non-default blocksize tablespace, the following inconsistent adjustments are applied to the formula:

NOWORKLOAD system statistics:

SREADTIM as above, using _DEFAULT_ DB_BLOCK_SIZE

MREADTIM = IOSEEKTIM + _DEFAULT_ DB_BLOCK_SIZE * scaled MBRC / IOTRFSPEED

I/O cost = Number of blocks / scaled MBRC * MREADTIM / SREADTIM

So obviously something is odd in above formulas: The SREADTIM and MREADTIM values are synthesized with a mixture of a scaled MBRC (according to the block size) but a non-adjusted default DB_BLOCK_SIZE, resulting in a large variation in cost, so a full table scan in a small blocksize is much more expensive than in the default blocksize, and likewise a full table scan in a large blocksize is much cheaper. Unfortunately this doesn't reflect at all the runtime behaviour, since Oracle acutally scales the I/O read request size accordingly meaning that the runtime difference usually is negligible, but the cost calculated is dramatically different.

WORKLOAD system statistics:

MBRC as measured/set

SREADTIM as measured/set

MREADTIM as measured/set

I/O cost = Number of blocks / MBRC * MREADTIM / SREADTIM

This is even worse than above NOWORKLOAD result because the I/O cost calculated simply is different by the factor of number of blocks in non-default block size / number of blocks in default block size, e.g. an object residing in a 2KB block size will have an I/O cost four times higher than an object residing in a 8KB default blocksize, and the MBRC is not adjusted at all for the calculation.

Some calculation examples

The following examples attempt to summarize what you get if you're using an object in a non-default blocksize tablespace:

1. I/O Cost of a full table scan of a 10,000 blocks / 80MB segment residing in a default blocksize of 8KB (in case of system statistics excluding the CPU cost) and using an MBRC of 8:

traditional I/O:
round(10,000 blocks / 6.59) = 1,517

(default) NOWORKLOAD system statistics:
SREADTIM = 10 + 8192/4096 = 10 + 2 = 12
MREADTIM = 10 + 8192*8/4096 = 10 + 16 = 26

round((10,000 / 8) * 26 / 12) = 1,250 * 26 / 12 = 2,708

WORKLOAD system statistics:
same as above provided you use the same for MREADTIM and SREADTIM otherwise as measured

2. I/O Cost of a full table scan of a 40,000 blocks / 80MB segment residing in a non-default blocksize of 2KB (in case of system statistics excluding the CPU cost) and using a default blocksize of 8KB and a MBRC of 8:

traditional I/O:
MBRC used for 2KB blocksize = 8 * 8KB / 2KB = 8 * 4 = 32

adjusted MBRC for MBRC = 32: 16.39

round(40,000 blocks / 16.39) = 2,441

(default) NOWORKLOAD system statistics:
SREADTIM = 10 + 8192/4096 = 10 + 2 = 12
(should be 10 + 2048/4096 = 10 + 0.5 = 10.5)

MREADTIM = 10 + 8192*32/4096 = 10 + 64 = 74
(should be 10 + 2048*32/4096 = 10 + 16 = 26)

round((40,000 / 32) * 74 / 12) = 1,250 * 74 / 12 = 7,708
(should be round((40,000 / 32) * 26 / 10.5) = 1,250 * 26 / 10.5 = 3,095

WORKLOAD system statistics (for the sake of comparison using the NOWORKLOAD defaults):
MBRC = 8
SREADTIM = 12
MREADTIM = 26

round((40,000 / 8) * 26 / 12) = 5000 * 26 / 12 = 10,833
(should be round((40,000 / 32) * 26 / 12) = 1,250 * 26 / 12 = 2,708

3. I/O Cost of a full table scan of a 5,000 blocks / 80MB segment residing in a non-default blocksize of 16KB (in case of system statistics excluding the CPU cost) and using a default blocksize of 8KB and a MBRC of 8:

traditional I/O:
MBRC used for 16KB blocksize = 8 * 8KB / 16KB = 8 * 0.5 = 4

adjusted MBRC for MBRC = 4: 4.17

round(5,000 blocks / 4.17) = 1,199

(default) NOWORKLOAD system statistics:
SREADTIM = 10 + 8192/4096 = 10 + 2 = 12
(should be 10 + 16384/4096 = 10 + 4 = 14)

MREADTIM = 10 + 8192*4/4096 = 10 + 8 = 18
(should be 10 + 16384*4/4096 = 10 + 16 = 26)

round((5,000 / 4) * 18 / 12) = 1,250 * 18 / 12 = 1,875
(should be round((5,000 / 4) * 26 / 14) = 1,250 * 26 / 14 = 2,321

WORKLOAD system statistics:
MBRC = 8
SREADTIM = 12
MREADTIM = 26

round((5,000 / 8) * 26 / 12) = 625 * 26 / 12 = 1,354
(should be (5,000 / 4) * 26 / 12) = 1,250 * 26 / 12 = 2,708

Plan change by moving to non-default block size

And here is a final example that shows by simply moving an object to a non-default blocksize tablespace a change in the execution plan can be observed. An index scan is used instead of the full table scan since the cost of the full table scan operation is significantly increased. The SQL trace will reveal that at runtime however there is no significant difference between the full table scan executed in the default tablespace and non-standard blocksize tablespace.

SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 as
3 select
4 trunc(dbms_random.value(0,25)) as col1
5 , trunc(dbms_random.value(0,25)) as col2
6 , rpad('x', 200, 'x') as filler
7 from
8 (
9 select
10 level as id
11 from
12 dual
13 connect by
14 level <= 10000
15 );

Table created.

Elapsed: 00:00:00.49
SQL>
SQL> create index t1_idx on t1 (col1, col2)
2 ;

Index created.

Elapsed: 00:00:00.14
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.55
SQL>
SQL> explain plan for
2 select /*+ full(t1) */
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.02
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 83 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 96 | 19776 | 83 (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COL1"=1 AND ("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR
"COL2"=4 OR "COL2"=5 OR "COL2"=6))

14 rows selected.

Elapsed: 00:00:00.13
SQL>
SQL> explain plan for
2 select /*+ index(t1) */
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 96 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 96 | 19776 | 96 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 96 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=1)
filter("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR "COL2"=4 OR "COL2"=5 OR
"COL2"=6)

16 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 83 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 96 | 19776 | 83 (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COL1"=1 AND ("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR
"COL2"=4 OR "COL2"=5 OR "COL2"=6))

14 rows selected.

Elapsed: 00:00:00.05
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.02
SQL>
SQL> alter session set tracefile_identifier = 'fts_8k';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> set termout off
SQL>
SQL> select /*+ full(t1) */
2 *
3 from
4 t1
5 where
6 col1 = 1
7 and col2 in (1,2,3,4,5,6);

SQL>
SQL> set termout on
SQL>
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter session set tracefile_identifier = 'idx_8k';

Session altered.

Elapsed: 00:00:00.02
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> set termout off
SQL>
SQL> select /*+ index(t1) */
2 *
3 from
4 t1
5 where
6 col1 = 1
7 and col2 in (1,2,3,4,5,6);

SQL>
SQL> set termout on
SQL>
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> drop table t1 purge;

Table dropped.

Elapsed: 00:00:00.70
SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL>
SQL> create table t1
2 tablespace test_2k
3 as
4 select
5 trunc(dbms_random.value(0,25)) as col1
6 , trunc(dbms_random.value(0,25)) as col2
7 , rpad('x', 200, 'x') as filler
8 from
9 (
10 select
11 level as id
12 from
13 dual
14 connect by
15 level <= 10000
16 );

Table created.

Elapsed: 00:00:00.46
SQL>
SQL> create index t1_idx on t1 (col1, col2)
2 tablespace test_2k;

Index created.

Elapsed: 00:00:00.13
SQL>
SQL> exec dbms_stats.gather_table_stats(null, 't1')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.69
SQL>
SQL> explain plan for
2 select /*+ full(t1) */
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.07
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 245 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 96 | 19776 | 245 (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("COL1"=1 AND ("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR
"COL2"=4 OR "COL2"=5 OR "COL2"=6))

14 rows selected.

Elapsed: 00:00:00.11
SQL>
SQL> explain plan for
2 select /*+ index(t1) */
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.06
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 102 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 96 | 19776 | 102 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 96 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=1)
filter("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR "COL2"=4 OR "COL2"=5 OR
"COL2"=6)

16 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 col1 = 1
8 and col2 in (1,2,3,4,5,6);

Explained.

Elapsed: 00:00:00.02
SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 19776 | 102 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 96 | 19776 | 102 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 96 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=1)
filter("COL2"=1 OR "COL2"=2 OR "COL2"=3 OR "COL2"=4 OR "COL2"=5 OR
"COL2"=6)

16 rows selected.

Elapsed: 00:00:00.10
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
SQL>
SQL> alter session set tracefile_identifier = 'fts_2k';

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> set termout off
SQL>
SQL> select /*+ full(t1) */
2 *
3 from
4 t1
5 where
6 col1 = 1
7 and col2 in (1,2,3,4,5,6);

SQL> set termout on
SQL>
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter session set tracefile_identifier = 'idx_2k';

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> set termout off
SQL>
SQL> select /*+ index(t1) */
2 *
3 from
4 t1
5 where
6 col1 = 1
7 and col2 in (1,2,3,4,5,6);

SQL> set termout on
SQL>
SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> spool off

And here's what we get from the generated trace files.

The 8KB FTS does these multi-block reads:

WAIT #35: nam='db file scattered read' ela= 2296 file#=8 block#=12050 blocks=8 obj#=61857 tim=107988983808
WAIT #35: nam='db file scattered read' ela= 916 file#=8 block#=12058 blocks=8 obj#=61857 tim=107988984970
WAIT #35: nam='db file scattered read' ela= 911 file#=8 block#=12066 blocks=8 obj#=61857 tim=107988986104

And the 2KB FTS does these multi-block reads:

WAIT #37: nam='db file scattered read' ela= 798 file#=6 block#=66 blocks=32 obj#=61859 tim=107991673671
WAIT #37: nam='db file scattered read' ela= 828 file#=6 block#=98 blocks=32 obj#=61859 tim=107991674822
WAIT #37: nam='db file scattered read' ela= 829 file#=6 block#=130 blocks=32 obj#=61859 tim=107991675980

And the TKPROF output confirms that there is not a relevant difference in the wait times.

The 8KB output:

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

select /*+ full(t1) */
*
from
t1
where
col1 = 1
and col2 in (1,2,3,4,5,6)

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 2 0.01 0.06 296 299 0 93
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.06 296 299 0 93

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
93 TABLE ACCESS FULL T1 (cr=299 pr=296 pw=0 time=21098 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 37 0.02 0.05
SQL*Net message from client 2 0.00 0.00
********************************************************************************

The 2KB output:

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

select /*+ full(t1) */
*
from
t1
where
col1 = 1
and col2 in (1,2,3,4,5,6)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.07 1251 1254 0 93
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.07 1251 1254 0 93

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61

Rows Row Source Operation
------- ---------------------------------------------------
93 TABLE ACCESS FULL T1 (cr=1254 pr=1251 pw=0 time=21314 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 40 0.02 0.05
SQL*Net message from client 2 0.00 0.00
********************************************************************************

Don't be mislead by the different number of consistent gets, this is just due to the different block size. As can be seen from the raw trace files the multi-block reads in the 2KB block size are reading 32 blocks at a time, whereas in the 8KB default block size 8 blocks are read at a time, so the number of I/O requests will be similar given a segment of the same size.