How gather stats improves your query performance ?
A query normally goes through 3 phases
Parse
Execute
Fetch
In Parse phase syntactical and symantical check are done
Parsing basically is of two types Hard parse & Soft parse
When a query is fired for the first time the query goes through hard parse meaning server process fetches data from data file into the buffer cahces.
For the the subsequent times the query is stored in the library cache until most recently used alogrithm can hold them during this time the query directly
fetches the data from buffer cache instead of Datafile which is nothing but soft parse.
Execute Phase
If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed.
If the statement is an UPDATE or DELETE statement, however, all rows that the statement
affects are locked from use by other users of the database, until the next COMMIT, ROLLBACK or SAVEPOINT for the transaction.
This ensures data integrity.
Fetch Phase
Data is fetched from database blocks.
In the fetch phase, rows are selected and ordered (if requested by the query),
and each successive fetch retrieves another row of the result, until the last row has been fetched.
Now coming to the point why statistic are gathered
Lets say we have table emp with 10 million records and over a period of time 1 million records have been deleted.
So when a query is executed on this table which is having an index it will traverse through all the rows (even the deleted rows )
because the information regarding the deleted rows is not updated in the DBA_TAB_STATISTICS or ALL_TAB_STATISTICS as gather stats is not collected.
When the query is executed it generates various execution plans and decides on the best plan during this time the optimizer queries the
DBA_TAB_STATISTICS or ALL_TAB_STATISTICS to get the latest information of the tables in question so if your gather stats are upto date then these
ALL_TAB_STATISTICS will help the optimizer give a better execution plan
No comments:
Post a Comment