I went back to run my test case for the 5th time to make sure I wasn't seeing things. When I ran it, it came out like Tom said it would. I was floored, so I wondered how that happened and started trying different things on differnt nodes. Bottom line: On a RAC system, the system statistics ONLY take affect on the node they were gathered until the DB is bounced. This is how I was getting all of these crazy results! Each time I would connect, I would get a different node. If I got the node where the system stats were collected, then tom's case (above) works, If I get on one of the other nodes, his case didn't work.
Here is what I now know to be true (hopefully tom will agree):
1) There are WORKLOAD system stats and NOWORKLOAD system stats. By default, 10gR2 has NOWORKLOAD stats.
2) With only NOWORKLOAD stats, the init param "db_file_multiblock_read_count" has a BIG effect on CBO's decision to use indexes or do full table scans. Not only does this parameter affect CBO's perception of how costly a full table scan is, but it ALSO effects how many blocks are actually read from I/O during a multi-block read.
3) After the "improved" WORKLOAD system stats are gathered, the init param "db_file_multiblock_read_count" has NO EFFECT on anything anymore. It doesn't affect CBO, and oracle uses other things to figure out how many blocks to read during an I/O. We actually unset the parameter on our system.
4) System stats ARE global to the DB. All nodes in a RAC cluster share the same WORKLOAD (and NOWORKLOAD) system stats.
5) Even though number 4 is true, ONLY the instance that you were connected to when you gathered system stats has the stats actually IN USE. You can connect to other nodes and query sys.aux_stats$, and it will show that the stats are there, but they ARE NOT being used yet. To remedy this, you either need to bounce the DB, or actually connect to each node separately and set the stats using dbms_stats.set_system_stats.
BTW: To the poster above who was talking about a 10.2.0.4 upgrade changing how queries are parsed, See this link:
http://www.dba-oracle.com/t_slow_performance_after_upgrade.htm I wont even pretend to say that Don Burleson is a good source of information, but this page is a good laundry list of things you should independently investigate and verify on your own system. This thread is not about "all the performance things that go wrong after a 10gR2 upgrade".
Here is the script I use to test/prove. You must have access to connect to 2 different nodes in a RAC system at the same time. I setup seperate aliases in my tnsnames.ora and used the "INSTANCE" directive to make sure I get connected to the node I want to get connected to.
Side note: To make all of this testing work, YOU MUST make ALL of the test SQL statements different so it forces a hard parse.
--connect to ANY Node of RAC
--Setup our test case (one time only)
drop table t;
create table t as select * from all_objects where 1=0;
BEGIN
dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000 );
END;
--Now connect to NODE2 of RAC
--MAke sure you are connected to NODE2
select sys_context('USERENV', 'INSTANCE') from dual;
BEGIN
dbms_stats.delete_system_stats();
commit;
END;
-- You should see no system stats
select * from sys.aux_stats$
--Now connect to NODE1 of RAC
--MAke sure you are connected to NODE1
select sys_context('USERENV', 'INSTANCE') from dual;
-- Now start the experiment
BEGIN
dbms_stats.delete_system_stats();
commit;
END;
--You should see just the NOWORKLOAD statics
select * from sys.aux_stats$
--Try to make db_file_multiblock_read_count have an effect
alter session set db_file_multiblock_read_count = 1;
commit;
select 'test1node1' TestVar1 from t;
select * from table(dbms_xplan.display_cursor());
alter session set db_file_multiblock_read_count = 64;
commit;
select 'test1node1' TestVar64 from t;
select * from table(dbms_xplan.display_cursor());
-- AFTER running the 6 statements, you should see that db_file_multiblock_read_count has a big effect on the COST shown in the explain plan.
-- Now gather workload stats
BEGIN
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1015.714 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 4 );
dbms_stats.set_system_stats( 'MREADTIM', 8 );
dbms_stats.set_system_stats( 'CPUSPEED', 1016 );
dbms_stats.set_system_stats( 'MBRC', 13 );
dbms_stats.set_system_stats( 'MAXTHR', 2048 );
commit;
END;
-- Confirm they are there
select * from sys.aux_stats$;
--Try to make db_file_multiblock_read_count have an effect
alter session set db_file_multiblock_read_count = 1;
commit;
select 'test2node1' TestVar1 from t;
select * from table(dbms_xplan.display_cursor());
alter session set db_file_multiblock_read_count = 64;
commit;
select 'test2node1' TestVar64 from t;
select * from table(dbms_xplan.display_cursor());
-- AFTER running the 6 statements, you should see that db_file_multiblock_read_count has NO EFFECT on the COST shown in the explain plan.
--Now connect to NODE2 of RAC
--MAke sure you are connected to NODE2
select sys_context('USERENV', 'INSTANCE') from dual;
-- The stats should still be there from collecting them on NODE1 a minute ago.
select * from sys.aux_stats$;
-- But run the test again and you see, they arent actually IN USE on this node (i.e. db_file_multiblock_read_count has an effect on this node)
alter session set db_file_multiblock_read_count = 1;
commit;
select 'test3node2' TestVar1 from t;
select * from table(dbms_xplan.display_cursor());
alter session set db_file_multiblock_read_count = 64;
commit;
select 'test3node2' TestVar64 from t;
select * from table(dbms_xplan.display_cursor());
-- Now gather workload stats on NODE2
BEGIN
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1015.714 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 4 );
dbms_stats.set_system_stats( 'MREADTIM', 8 );
dbms_stats.set_system_stats( 'CPUSPEED', 1016 );
dbms_stats.set_system_stats( 'MBRC', 13 );
dbms_stats.set_system_stats( 'MAXTHR', 2048 );
commit;
END;
--verify (for giggles)
select * from sys.aux_stats$;
-- Now the system stats are IN USE (i.e. db_file_multiblock_read_count HAS NO effect)
alter session set db_file_multiblock_read_count = 1;
commit;
select 'test4node2' TestVar1 from t;
select * from table(dbms_xplan.display_cursor());
alter session set db_file_multiblock_read_count = 64;
commit;
select 'test4node2' TestVar64 from t;
select * from table(dbms_xplan.display_cursor());