View Exhibit1 to examine the description of the CUSTOMERS table.You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer detect the actual number of rows?
View Exhibit1 to examine the description of the CUSTOMERS table.The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.View Exhibit2 and examine the query execution plan.What would you recommend to improve the optimizer's estimation?
You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.Why is the query using a full table scan instead of an index scan?
View Exhibit1 and examine the indexes on the CUSTOMERS table.The statistics for the CUSTOMERS table have been updated recently by using the following command:SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',method_opt=>'FOR ALLINDEXED COLUMNS SIZE AUTO');View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan.What could be the reason?
You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below:SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows2 FROM all_tables3* WHERE table_name = 'CUST';TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS---------- ---------- ---------- ---------- ----------CUST 13768 80 111060 -What would you recommend to reduce the I/O?
After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)
