Oracle11g: Analyze Table Validate Structure Cascade “FAST” (Slow Burn) March 31, 2011
Posted by Richard Foote in 11g, 11g New features, Oracle Indexes, Validate Structure.9 comments
I always take notice when Oracle introduces a new “FAST” option, so it was with some excitement when I first noticed in Oracle 11g Rel 1 there was a new FAST option when running the ANALYZE TABLE CASCADE VALIDATE STRUCTURE command.
This was described in the manuals as introducing a hashing scheme that was significantly faster than the traditional cascade validation method.
However, when I tested this new feature, the results were rather disappointing to say the least (I’ve tested this on various 11g versions, both R1 and R2 and on various platforms, AIX and Windows). In the example below, the PERSONS table is a rather large table that has a number of associated indexes:
SQL> set timing on SQL> analyze table persons validate structure cascade; Table analyzed. Elapsed: 00:06:01.84 SQL> analyze table persons validate structure cascade fast; Table analyzed. Elapsed: 00:15:20.27 SQL> analyze table persons validate structure cascade; Table analyzed. Elapsed: 00:02:22.27 SQL> analyze table persons validate structure cascade fast; Table analyzed. Elapsed: 00:15:46.28 SQL> analyze table persons validate structure cascade; Table analyzed. Elapsed: 00:02:23.78 SQL> analyze table persons validate structure cascade fast; Table analyzed. Elapsed: 00:14:58.00
When using the so-called “FAST” option, the performance was consistently significantly slower, not faster, when compared to using the default method. Perhaps the default option is “FASTER-STILL” ? (the default is actually “COMPLETE”). Additionally, the results of subsequent executions of the default method often resulted in improved times (eg: elapsed times reduced from 6 mins to around 2.5 mins), whereas the FAST option resulted in uniformly slower elapsed times.
I thought this warranted further investigation and so decided to trace what was actually going on behind the scenes and see where the extra time was being spent.
With the Complete default method, a significant number of I/Os were being performing as Oracle had to effectively perform an Index Full Scan on each index, reading the table primarily using single block reads. However, CPU related overheads were relatively low, with most of the elapsed times attributed to the related I/Os. Following is an extract from a formatted trace file:
analyze table promis.persons validate structure cascade call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.11 17 915 0 0 Execute 1 145.62 352.22 134061 58166955 3 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 145.63 352.33 134078 58167870 3 0
The total results for the trace were as follows:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.11 17 915 0 0 Execute 1 145.62 352.22 134061 58166955 3 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 145.63 352.33 134078 58167870 3 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 87 0.03 0.02 0 0 0 0 Execute 243 0.04 0.07 0 0 0 100 Fetch 577 0.10 0.31 110 1114 0 766 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 907 0.17 0.41 110 1114 0 866
We’ll compare these results later but for now note that elapsed times were 352 seconds in total, with the CPU only contributing about 165 seconds of that time.
If we now look behind the scenes of a “FAST” VALIDATE STRUCTURE CASCADE, we notice that statements such as the following are now recursively executed:
select /*+ full(PROMIS.PERSONS) */ ORA_HASH(ID || rowid) from PROMIS.PERSONS MINUS select /*+ index_ffs(PROMIS.PER_PK) */ ORA_HASH(ID || rowid) from PROMIS.PERSONS 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 1 35.23 37.83 7055 10402 6 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 35.23 37.83 7055 10402 6 0
We notice that Oracle now uses a new method, basically performing a Full Table Scan of the table and processing the indexed column and associated rowid through the ORA_HASH function which in turn returns a computed hash value. The full table scan is a very efficient method of reading all related values from a table but putting each column value and rowid through the ORA-HASH function is a relatively expensive CPU intensive operation. Oracle then does the same for the index by performing an Index Fast Full Scan of the index and processing again the index column value and associated rowid through the ORA-HASH function. Again, this is a very efficient method of reading all values from an index but again relatively CPU intensive putting every indexed value and associated rowid through the ORA-HASH function.
Having performed this for both table and index, in theory if the data structures are indeed valid and non-corrupt, they should both return exactly the same results. So by performing a MINUS of both row sets, if no data is returned, we can be sure indeed all is well with both table/index. If however differences are detected, then there’s some disparity between the data in the index and in the table and hence something is wrong. We’ll have no idea exactly what the differences might be as we only have the resultant hash values to go by, but the analyze validate structure command can at least raise an error and say something is indeed wrong when validating the table and its associated indexes.
We would actually now need to perform an Analyze Validate Structure again (without the FAST option this time) to determine the exact cause of the issue, but assuming detecting an error is a very rare event, it’s not an additional step we would ordinarily need to perform. So if by reading both table and index via multi-block Full Scans and processing the index data via the ORA-HASH function is “faster” and more efficient to determine nothing is actually wrong, then it’s a reasonable strategy to take.
This processing is then repeated for every index associated with the table we’re analyzing. So in theory, because we’re only performing Full Table and Fast Full Index Scans, we end up performing far fewer multi-block I/Os calls and so can complete this whole Validate Structure processing “Faster”.
However, Oracle needs to perform the ORA-HASH function operation for every column in every index, both within the table and associated indexes. So although we end up doing less I/Os, we end up burning much more CPU due to having to call the ORA-HASH function so often. If we look at the total resources when performing the “FAST” validate structure on exactly the same table:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 1 145 0 0 Execute 1 0.03 0.02 190294 3086489 2 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.04 0.03 190295 3086634 2 0 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 49 0.06 0.07 0 0 0 0 Execute 49 0.00 0.00 0 0 0 2 Fetch 46 830.63 878.62 190295 3086514 138 21 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 144 830.69 878.69 190295 3086514 138 23
We notice the query values are significantly reduced (down to just 3M from 58M). This of course is a good thing as this translates to reduced I/O related overheads.
However, if we look at the corresponding CPU related costs, they have increased significantly (up to 830 seconds from just 146 seconds). It’s really expensive putting all the indexed column values through the ORA-HASH function. Additionally, because multi-block Full Scans which aren’t generally cached are being performed during the Fast option, the results remain consistently poor with subsequent executions.
The net result is that the final elapsed times are consistently greater with the FAST option than without (878 seconds up from 352 seconds). Using the FAST option has resulted in slower response times, not faster ones.
Now your mileage may vary based on your specific environments, but my results have not been particularly impressive to say the least. Substantially increased elapsed times (during which the structures remain locked let’s not forget) is not exactly my idea of a “FAST” option.
Richard Foote's Oracle Blog 