READ Free Dumps For Oracle- 1z0-062
| Question ID 21739 | Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges?
|
| Option A | Bind Variable Peeking
|
| Option B | SQL Plan Baselines
|
| Option C | Adaptive Cursor Sharing
|
| Option D | Bind variable used in a SQL statement
|
| Option E | Literals in a SQL statement
|
| Correct Answer | A,C,E |
Explanation Explanation: * In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement. When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan. C: Oracle 11g/12g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration.
| Question ID 21740 | You want to capture column group usage and gather extended statistics for better cardinality estimates for the CUSTOMERS table in the SH schema.
Examine the following steps:
1. Issue the SELECT DBMS_STATS.CREATE_EXTENDED_STATS (`SH', `CUSTOMERS')
FROM dual statement.
2. Execute the DBMS_STATS.SEED_COL_USAGE (null, `SH', 500) procedure.
3. Execute the required queries on the CUSTOMERS table.
4. Issue the SELECT DBMS_STATS.REPORT_COL_USAGE (`SH', `CUSTOMERS') FROM dual statement.
Identify the correct sequence of steps.
|
| Option A | 3, 2, 1, 4
|
| Option B | 2, 3, 4, 1
|
| Option C | 4, 1, 3, 2
|
| Option D | 3, 2, 4, 1
|
| Correct Answer | B |
Explanation Explanation: Step 1 (2). Seed column usage Oracle must observe a representative workload, in order to determine the appropriate column groups. Using the new procedure DBMS_STATS.SEED_COL_USAGE, you tell Oracle how long it should observe the workload. Step 2: (3) You don't need to execute all of the queries in your work during this window. You can simply run explain plan for some of your longer running queries to ensure column group information is recorded for these queries. Step 3. (1) Create the column groups At this point you can get Oracle to automatically create the column groups for each of the tables based on the usage information captured during the monitoring window. You simply have to call the DBMS_STATS.CREATE_EXTENDED_STATS function for each table.This function requires just two arguments, the schema name and the table name. From then on, statistics will be maintained for each column group whenever statistics are gathered on the table. Note: * DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object. * The Oracle SQL optimizer has always been ignorant of the implied relationships between data columns within the same table. While the optimizer has traditionally analyzed the distribution of values within a column, he does not collect value-based relationships between columns. * Creating extended statisticsHere are the steps to create extended statistics for related table columns withdbms_stats.created_extended_stats: 1 - The first step is to create column histograms for the related columns.2 Next, we run dbms_stats.create_extended_stats to relate the columns together. Unlike a traditional procedure that is invoked via an execute ("exec") statement, Oracle extended statistics are created via a select statement.