Your database supports an OLTP system.
Examine the parameter values configured in your database:
sga_max_size = 480M sga_target = 480M pga_aggregate_target = 160M
TheCUSTOMERStable contains 8,000 rows. TheCUST_IDcolumn is the primary key and
theCOUNTRY_IDcolumn contains only three possible values: 1111, 2222, and 3333.
You execute the commands:
SQL>EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,‘CUSTOMERS’);
PL/SQL procedure successfully completed.
SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);
Index created.
You then perform a series ofINSERT, UPDATE, andDELETEoperations on the table. View
the Exhibit to examine the query and its execution plan.
Which two options can improve the performance of the query without significantly slowing
down the DML operations?(Choose two.)
A.
creating a bitmap index on theCOUNTRY_IDcolumn
B.
regathering statistics on theCUSTOMERStable
C.
gathering statistics on theCOUNTRY_IDXindex
D.
creating a histogram on theCOUNTRY_IDcolumn
E.
increasing the size of the PGA
F.
creating an SQL profile
G.
creating aKEEPcache
A – wrong. Bitmap index is bad for DML.
The question is about incorrect cardinality estimates. So we need a profile or a histogram.
F and D – right.
Index and Histogram for COUNTRY_ID? Uhm…maybe B,F
BF
D – If DBMS_STATS gathers statistics for a table, and if queries have referenced the columns in this table, then Oracle Database creates histograms automatically as needed according to the previous query workload.
No need to create manually
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,‘CUSTOMERS’); will collect histograms and the index range scan will be used! The question must be missing something or simply invalid!!!
A is wrong for sure
F is correct for sure by definition to fix the invalid cardinality of 8000! This invalid number makes me assume that histograms auto collection was disabled, because with the presence of histograms cardinality for a simple equi predicate cannot go wrong! So, I would go with F and D!
D,F
D: You cant assume the gather table stats will create the histogram for you, even more so when the index for the column was created afterwards (Oracle considers histograms on indexed columns more worthy of the overhead involved). so a manual creation or probably regathering stats but again we cant be sure of that so we need to discard B I’d say.
F: A profile, so dynamic sampling is performed, can help with the estimation so engine may come up with a more efficient execution plan
DF
regathering will not change anything to the issue : first stats where gathered with existing index and column data has not been said to change during workload. So if no histogram first time, why second ??