Oracle: Checking Object Index Leaf Blocks
This note is supplied describing the Oracle Index, its number of rows, leaf blocks and status if it is invalid or valid.
For the past couple of days I was thinking how will I know if the query is optimized or not until today I had time to experiment and used SQL Hint on my statement.
I checked Cost-Based vs Rule-Based deals with my statement and check how each react to the SQL code, found out using Rule-Based result is much faster and better optimized.
# of Rows retrieved: 29 rows selected.
Rule-Based Execution Time: Elapsed: 00:00:00.09
Cost-Based Execution Time: Elapsed: 00:00:02.43
Rule-Based Consistent Gets: 414 consistent gets
Cost-Based Execution Time: 2249 consistent gets
Also one significant output is the execution of the statement Nested Loop has significant advantages against Hash Loop.
The big significant difference between the result is Rule-Based have better execution plan against the Cost-Based. Using RULE vs CHOOSE the result of the consistent gets is 70% smaller than the CHOOSE.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | -- RULE-BASE SELECT /*+ RULE */ owner, index_name, NUM_ROWS, LEAF_BLOCKS, TO_CHAR(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') LAST_ANALYZED, LOGGING, STATUS FROM DBA_INDEXES WHERE owner = 'ORACLE' ORDER BY last_analyzed DESC; Elapsed: 00:00:00.09 Execution Plan ---------------------------------------------------------- Plan hash VALUE: 3787337137 Elapsed: 00:00:00.09 --------------------------------------------- | Id | Operation --------------------------------------------- | 0 | SELECT STATEMENT | 1 | SORT ORDER BY | 2 | NESTED LOOPS OUTER | 3 | NESTED LOOPS OUTER | 4 | NESTED LOOPS | 5 | NESTED LOOPS OUTER | 6 | NESTED LOOPS | 7 | NESTED LOOPS OUTER | 8 | NESTED LOOPS | 9 | NESTED LOOPS | 10 | TABLE ACCESS BY INDEX ROWID |* 11 | INDEX UNIQUE SCAN |* 12 | TABLE ACCESS BY INDEX ROWID *** omitted further execution plan Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 414 consistent gets 0 physical reads 0 redo SIZE 1859 bytes sent via SQL*Net TO client 407 bytes received via SQL*Net FROM client 3 SQL*Net roundtrips TO/FROM client 1 sorts (memory) 0 sorts (disk) 29 ROWS processed |
versus
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | -- COST-BASED SELECT owner, index_name, NUM_ROWS, LEAF_BLOCKS, TO_CHAR(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') LAST_ANALYZED, LOGGING, STATUS FROM DBA_INDEXES WHERE owner = 'ORACLE' ORDER BY last_analyzed DESC; --------------------------------------------- | Id | Operation --------------------------------------------- | 0 | SELECT STATEMENT | 1 | SORT ORDER BY |* 2 | HASH JOIN RIGHT OUTER | 3 | TABLE ACCESS FULL | 4 | NESTED LOOPS OUTER |* 5 | HASH JOIN | 6 | TABLE ACCESS FULL |* 7 | HASH JOIN |* 8 | HASH JOIN OUTER |* 9 | HASH JOIN RIGHT OUTER | 10 | TABLE ACCESS FULL |* 11 | HASH JOIN | 12 | NESTED LOOPS | 13 | TABLE ACCESS BY INDEX ROWID |* 14 | INDEX UNIQUE SCAN *** omitted further execution plan Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2249 consistent gets 3 physical reads 0 redo SIZE 1872 bytes sent via SQL*Net TO client 407 bytes received via SQL*Net FROM client 3 SQL*Net roundtrips TO/FROM client 1 sorts (memory) 0 sorts (disk) 29 ROWS processed |
Disclaimer:
This note is and incomplete note. If wanted to get further information with the consistent gets and SQL Hints please refer to the Reference section.
The result here might not be the same with you existing environment. Better understanding with SQL Hints and few expirements on its might change dramatically in your SQL statement either significantly or worst.
References:
1. Consistent Gets
2. SQL Hints
I am Josef Panerio an Oracle Certified Database Administrator and a Pinoy Photographer.
This page is my repository of my recent research related to my Oracle Database career,
Photography and way of life.
