How to collect statistics

In most cases, tuning a poorly performing query starts with taking a look at the collected stats on the tables involved. One wrong/stale stat, or no stats on tables, can turn a regular query into a shark query that eats up all resources on the system. Let's explore more about how and when to collect these statistics.

The following is the EXPLAIN plan of some query:

2) Next, we execute the following steps in parallel.
    1) We do an all-AMPs RETRIEVE step from SYSDBA.SITE_ID in view SYSDBA_VIEWS.SITES_IDV by way of an all-rows scan with no residual conditions into Spool 2 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 2 is estimated with high confidence to be 55,220rows (5,521,220 bytes).  The estimated time for this step is 0.05 seconds. 

If you need to retrieve statistics data on a system, there are a few tables in DBC where metadata for stats are stored, they are: 

• DBC.ColumnStatsV: Gives stats information of ‘single’...