So far, the previous examples have been on tracing the Execution of SQL statements and/or the Execution Plan used.
In the previous posts, I have traced eitherSELECTorINSERT or UPDATE or DELETEstatements I have pointed out that the block statistics are reported as “FETCH” statistics for SELECTs and “EXECUTE” statistics for the DMLs What if we have an INSERT … AS SELECT ?
So far, all the examples of SQL Tracing that I have provided in this series are for SELECT statements.
In the previous two posts, I have demonstrated direct path read and db file scattered read being used for Full Table Scans.
In previous blog posts here and here, I have demonstrated Full Table Scans of a table called ALL_OBJECTS_MANY_LIST being executed via Direct Path Reads (shown as ‘direct path read’) in the Wait Events.
Continuing with the same table as in the previous example, but with the addition of(a) Statistics on the table(b) An Index on the CREATED column SQL> select count(*) from all_objects_many_list; COUNT(*)---------- 7197952SQL> SQL> select min(created) from all_objects_many_list;MIN(CREAT---------28-AUG-11SQL> SQL> select min(created), max(created) from all_objects_many_list;MIN(CREAT MAX(CREAT--------- ---------28-AUG-11 15-NOV-15SQL> SQL> Let’s examine the tkprof output first.