SQL Tuning First Checks

Global Statistics:

1.   Statistics_level= ALL , problem with Linux if we set to this. May be a bug.
2.   Db_file_multiblock_read_count
3.   NLS_sort is need to Binary.
4.   Optimizer_dynamic_sampling between 1 and 3.
5.  Stats gathering should not
6.    High version count
7.    Using First Rows
8.   Fixed objects stats missing
9.   CBO stats with non-defaults values
10.   System stats are need to gather if not default stats are used which are calculated at the first DB startup
11.  Sreadtim < 2 or mreadtim <3
12.  Sreadtim > 18 and mreadtim > 522
13.  Table with Bitmap indexes
14.  Need to check any change in the indexes

Table checks:

1. Empty_blocks > blocks …. Need to re-organize of the table needed.
2. Table DOP is set.
3. DOP differences between Tables and Indexes.
4. No stats.
5. No rows
6. Small sample size
7. Extended stats.
8. Columns with no stats
9. Columns missing low/high values
10. Columns with old stats
11. More Nulls than Rows
12. More distinct values than rows
13. Zero distinct values on columns with value
14. Incorrect NDV in long char column with histogram
15. Frequency histograms with less buckets than NDV
16. Frequency histogram with 1 bucket
17. Height balanced Histogram with no popular values
18. Analyze command was used
19. Average row length is 100 is bug
20. Tables with stale stats
21. SQL with policies as per dba_polices
22. SQL with policies as per DBA_AUDIT_POLICIES
23. Table partitions with no stats
24. Table partitions with number of rows=0
25. Table partitions with outdated stats
26. Partitions with no column stats
27. Partition columns with outdated stats

Index checks:

1.       No stats

2.       More rows in index than its tables

3.       Clustering factor > rows in table

4.       Stats on zero while columns have value

5.       Table/Index stats out of sync

6.       Analyze was used

7.       No column stats in single column index

8.       NDV on column > num_rows in single- column index

9.       NDV is zero but column has values in single-column index

10.   NDV <> NDK in single-column index

11.   Index partitions with no stats

12.   Index partitions where num rows=0

13.   Index partitions with outdated stats

Reference: Oracle  Docs