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 ...
