What would you recommend to reduce the I/O?

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_rows
FROM all_tables
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?
Exhibit:

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_rows
FROM all_tables
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?

A.
reorganizing the table to use fewer blocks

B.
rebuilding the index on the CUST_FIRST_NAME column

C.
increasing the value for the PCTFREE attribute for the CUST table

D.
increasing the value for the PGA_AGGREGATE_TARGET initialization parameter



Leave a Reply 0

Your email address will not be published. Required fields are marked *