are LIOs always relavent?

This is going to long, but its interesting. I hope it's worth the read. 

I am performing a few tests. Here is the basic framework of the tests.

Test 1:
GTT joined to Heap table on a unique index and the data is perfectly 
colocated(ordered and dense)

Test 2: 
GTT joined to List partitioned table on a unique index. It is now two columns. 
It leads with the same unique column in the first test and the second column in 
a partition key. The data is perfected colocated. 

Our GTT has 5,000 rows and 20,000 rows. We are using dynamic sampling 2. 10G 
release 2. 

The data in the GTT are perfectly colocated. All the rows in a GTT match the 
rows in one partition in the list partitioned table. The base tables are 
identical accept for partition. Exact same data. They have between 20 million 
and 200 million rows. Tables are analyzed with autosample size option. 

Test Code:
1. Same queries for both. The partitioned tables use queries that include the 
partition key.
2. Open 1,10,20,50,100 threads.
3. For 1 thread grab tkprof. Use awrpt for multiple threads. Sometimes I 
grabbed 10046 for multiple threads, but it was skewing my system results. 
4. No one query is the bottleneck. All of them have somewhat comparable 
results. 
5. We tested 8 queries run in a stored procedure.
6. We bounce all relavent tablespaces between tests and flush the shared pool. 

Results: 
1. Elapsed time is about the same. CPU is about the same. Physical IO is a 
little higher for the non-partitioned tables. 
2. We noticed a 20-50% spike in LIOs for the partitioned tables depending on 
the number of threads we run. When we look at the row source in the tkprof 
output the increase is almost entirely on 'partition list iterator'. 

First off. Has anyone seen a spike in LIO do to the partition list iterator row 
source and second off, if my LIOs are so much higher how come I do not see an 
increase in elapsed time or CPU? We tested this understress with up to 100 
threads. Does not appear to affect CPU or response time. Part of that may be 
because we have less waits with partition tables under stress. I will try a 
higher dynamic sampling. However, I am not sure it matters if CPU and elapsed 
time are not effected. I believe Mogens Noorgaard had a chapter in the oak 
table book about some LIOs are more costly than others, but we don't have the 
granualarity to tell yet? 

I don't have a production system to test the effects against something life. 
Its still in development. 

I am putting this on asktom also. I hope no one minds. 

Other related posts: