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
2 FROM all_tables
3* 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?

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
2 FROM all_tables
3* 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 1

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


L. Zhu

L. Zhu

A is right. re-org can get rid of empty blocks and reduce I/O