Using V$OBJECT_USAGE The V$OBJECT_USAGE view keeps track of whether an index has been used since it was last monitored. You need to start monitoring an index first: Start Monitoring an Index: ALTER INDEX index_name MONITORING USAGE; Query the Usage: After some time, you can check if the index has been used: SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring FROM V$OBJECT_USAGE WHERE index_name = 'INDEX_NAME'; Stop Monitoring an Index: ALTER INDEX index_name NOMONITORING USAGE; Hope it helped !! :)
There are 2 situations where AUTO_SAMPLE_SIZE can occur: If, during processing, the procedure determines that the returned information for the sample is insufficient for accurate access path selection, the sample can be increased by a factor of ten and restarted. For example, if AUTO_SAMPLE_SIZE started with around 5500 rows, when the procedure determines that the sample is insufficient then it will then increase the sample (for example to 55000) and restart. If this is insufficient, it increases by another factor of 10, and so on. However, if the sample size exceeds 25% of the table size, it will switch automatically to COMPUTE (100% sample). There is a case where the sample taken is sufficient for accurate statistics, but the number of rows is judged to be poorly estimated (either for the table or an index). Sample_size is set at the lower value, but then dbms_stats will take an accurate row count by using SELECT COUNT(*) from the table and index. As well as setting the num_rows, dbms_stats will also set the sample_size to this value (as this is the number of rows sampled to produce num_rows). In this case, the sample_size, although accurate for the last operation, is misleading as far as the full sample taken for the other gathered statistics. You expect the sample size from: select table_name, last_analyzed, sample_size, num_rows from dba_tables where table_name = '.........';
Very useful information bro. Thanks
Thank you Anish sir. Your videos are really informative.
Very insightful baba... Hope to collab with you soon
Hi there, how to find the index is in use or not.
Using V$OBJECT_USAGE
The V$OBJECT_USAGE view keeps track of whether an index has been used since it was last monitored. You need to start monitoring an index first:
Start Monitoring an Index:
ALTER INDEX index_name MONITORING USAGE;
Query the Usage:
After some time, you can check if the index has been used:
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM V$OBJECT_USAGE
WHERE index_name = 'INDEX_NAME';
Stop Monitoring an Index:
ALTER INDEX index_name NOMONITORING USAGE;
Hope it helped !! :)
@@anishkumarvideos Thank you
Dba_indexes
nice video!!
Thanks for the visit :)
What is estimate percentage and auto sample size
There are 2 situations where AUTO_SAMPLE_SIZE can occur:
If, during processing, the procedure determines that the returned information for the sample is insufficient for accurate access path selection, the sample can be increased by a factor of ten and restarted. For example, if AUTO_SAMPLE_SIZE started with around 5500 rows, when the procedure determines that the sample is insufficient then it will then increase the sample (for example to 55000) and restart. If this is insufficient, it increases by another factor of 10, and so on. However, if the sample size exceeds 25% of the table size, it will switch automatically to COMPUTE (100% sample).
There is a case where the sample taken is sufficient for accurate statistics, but the number of rows is judged to be poorly estimated (either for the table or an index). Sample_size is set at the lower value, but then dbms_stats will take an accurate row count by using SELECT COUNT(*) from the table and index. As well as setting the num_rows, dbms_stats will also set the sample_size to this value (as this is the number of rows sampled to produce num_rows). In this case, the sample_size, although accurate for the last operation, is misleading as far as the full sample taken for the other gathered statistics.
You expect the sample size from:
select table_name, last_analyzed, sample_size, num_rows from dba_tables where table_name = '.........';