Re: are LIOs always relavent?

I just read my email. That test plan is too complicated to explain. I will probably bore people.

The real question is... How can I have a large spike(sometimes double) in LIOs, have the same physical IOs, no additional waits, and at the same time have similiar CPU usage and response time?

I am seeing the spike at the row source: 'partition list iterator' for list partitions.

• Original message -------------- From: ryan_gaffuri_at_comcast.net

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.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 30 2006 - 19:51:20 CDT