are LIOs always relavent?
- From: ryan_gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 30 Jun 2006 21:52:54 +0000
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:
