Examine the parameter for your database instance: You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan: Identify the reason why the optimizer chose different execution plans.（）A、The optimizer used a dynamic plan for the query.B、The optimizer chose different plans because automatic dynamic sampling was enabled.C、The optimizer used re-optimization cardinality feedback for the query.D、The optimizer chose different plan because extended statistics were created for the columns used.
Examine the parameter for your database instance: You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan: Identify the reason why the optimizer chose different execution plans.（）
- A、The optimizer used a dynamic plan for the query.
- B、The optimizer chose different plans because automatic dynamic sampling was enabled.
- C、The optimizer used re-optimization cardinality feedback for the query.
- D、The optimizer chose different plan because extended statistics were created for the columns used.
相关考题：
You want to analyze a SQL Tuning Set (STS) using SQL Performance Analyzer in a test database. Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set?（）A.Every SQL statement in the STS is considered only once for executionB.The SQL statements in the STS are executed concurrently to produce the execution plan and execution statisticsC.The execution plan and execution statistics are computed for each SQL statement in the STSD.The effects of DDL and DML are considered to produce the execution plan and execution statistics
Examine the following commands and their output:View the exhibit and examine the Flashback Version Query that was executed after the preceding commands.What could be the possible cause for the query not displaying any row?（）A. Flashback logging is not enabled for the database.B. The changes made to the table are not committed.C. Supplemental logging is not enabled for the database.D. The database is not configured in ARCHIVELOG mode.
Examine the parameter for your database instance:You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you notice that the hash join was done in the execution plan:Identify the reason why the optimizer chose different execution plans.（）A. The optimizer used a dynamic plan for the query.B. The optimizer chose different plans because automatic dynamic sampling was enabled.C. The optimizer used re-optimization cardinality feedback for the query.D. The optimizer chose different plan because extended statistics were created for the columns used.
A new report process containing a complex query is written, with high impact on the database. You wantto collect basic statistics about query, such as the level of parallelism, total database time, and the number of I/O requests.For the database instance STATISTICS_LEVEL, the initialization parameter is set to TYPICAL and theCONTROL_MANAGEMENT_PACK_ACCESS parameter is set to DIAGNOSTIC+TUNING.What should you do to accomplish this task?（）A. Execute the query and view Active Session History (ASH) for information about the query.B. Enable SQL trace for the query.C. Create a database operation, execute the query, and use the DBMS_SQL_MONITOR. REPORT_SQL_MONITOR function to view the report.D. Use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to monitor query execution and view the information from the V$SESSION_LONGOPS view.
In your database，the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting？（）A、The optimizer statistics are collected automatically.B、Only the timed operating system （OS） statistics and plan execution statistics are collected.C、The Oracle server dynamically generates the necessary statistics on tables as part of query optimization.D、The snapshots for the Automatic Workload Repository （AWR） are not generated automatically. E、Snapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.
A new report process containing a complex query is written, with high impact on the database. You wantto collect basic statistics about query, such as the level of parallelism, total database time, and the number of I/O requests. For the database instance STATISTICS_LEVEL, the initialization parameter is set to TYPICAL and theCONTROL_MANAGEMENT_PACK_ACCESS parameter is set to DIAGNOSTIC+TUNING. What should you do to accomplish this task?（）A、Execute the query and view Active Session History (ASH) for information about the query.B、Enable SQL trace for the query.C、Create a database operation, execute the query, and use the DBMS_SQL_MONITOR. REPORT_SQL_MONITOR function to view the report.D、Use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to monitor query execution and view the information from the V$SESSION_LONGOPS view.
You are using the Database Resource Manager to manage database resources. You created a resource plan directive for the MANAGERS resource consumer group under the SALES_PLAN by using the following statement： SQLEXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE （PLAN = ’SALES_PLAN’， GROUP_OR_SUBPLAN = ’MANAGERS’， CPU_P1 = 100， CPU_P2 =0， SWITCH_GROUP = ’CLERKS’， SWITCH_TIME_IN_CALL = 600）； A user， SCOTT， who is assigned to the MANAGERS group， starts a database session and executes a query on the database. What is the outcome if the query takes approximately 15 minutes to complete？（） A、 The query starts under the CLERKS group and the user， SCOTT， switches back to the MANAGERS group after the query completes.B、 The query starts under the MANAGERS group but terminates with an error when the execution time exceeds 10 minutes.C、 The query starts under the MANAGERS group and switches automatically to the CLERKS group when the execution time exceeds 10 minutes. The query does not switch back to the MANAGERS group after the query completes.D、 The query starts under the MANAGERS group， the user SCOTT switches automatically to the CLERKS group when the execution time exceeds 10 minutes， and then switches back to the MANAGERS group after the query completes.
The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;（）A、You obtain the results retrieved from the public synonym HR created by the database administrator.B、You obtain the results retrieved from the HR table that belongs to your schema.C、You get an error message because you cannot retrieve from a table that has the same name as a public synonym.D、You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.E、You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.
You execute the following command in your Certkiller .com production database to change the width of the CUST_NAME column of the CUSTOMERS table： ALTER TABLE customers MODIFY （cust_nameVARCHAR2 （40）） / When you execute the command， it displays the following error message： ERROR at line 1： ORA-00054： resource busy and acquire with NOWAIT specified What could be the reason for the error message？（）A、The database instance is not available.B、The ALTER TABLE command does not have WAIT optionC、The table or a row in the table is currently locked by another user session.D、The database instance is busy processing other user sessions commands.E、The CUSTOMERS table has no long running query active at the time when this request is made.F、The server process executing the ALTER TABLE command is busy with another command execution
You query the ORDERS table and discover that order number 1101 is missing. One of your reports， generated at 5：00 A.M. on April 23， 2005 using Oracle Reports， includes this order number in the output. You have inserted 100 new orders into the ORDERS table since generating this report. Which flashback technology would you use to recover order number 1101 into the ORDERS table without losing the 100 orders that you added after the report was generated？（） A、 Flashback TableB、 Flashback QueryC、Flashback Version QueryD、 Flashback Transaction Query
You work as a database administrator for Supportcenter.cn. You received the following error while working on your database： ORA-01555： snapshot too old ，In which situation would you receive such an error？（）A、You performed rollback after commit.B、If a log switch occurs in your database.C、Thearchiverfails to generate an archived log file.D、Instance recovery fails to find one of the redo log files.E、A long-running query is unable to get read-consistent image.F、If you perform manual undo management operations in auto mode.
You want to analyze a SQL Tuning Set （STS） using SQL Performance Analyzer in a test database. Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set？（）A、 Every SQL statement in the STS is considered only once for execution.B、 The SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.C、 The execution plan and execution statistics are computed for each SQL statement in the STS. D、 The effects of DDL and DML are considered to produce the execution plan and execution statistics.
You execute the following commands to audit database activities: SQL ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE; SQL AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL; Which statement is true about the audit record that generated when auditing after instance restarts?（）A、One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.B、One audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.C、One audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command, and contains the execution plan for the SQL statements.D、One audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.E、One audit record is created for the whole session if john successfully executes a SELECT, INSERT,or DELETE command on a table, and contains the execution plan, SQL text, and bind variables used.
You are required to flashback your database. You want to find the amount of flashback data generated since the database was opened. Which task will you perform to obtain the required information？（）A、 Query the V$FLASHBACK_DATABASE_LOG view.B、 Query the V$FLASHBACK_DATABASE_STAT view.C、 Check the value of the DB_FLASHBACK_RETENTION_TARGET initialization parameter.D、 Query the V$RECOVERY_FILE_DEST view.
Examine the parameters for your database instance: Which three statements are true about the process of automatic optimization by using cardinality feedback?（）A、The optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.B、The optimizer can re optimize a query only once using cardinality feedback.C、The optimizer enables monitoring for cardinality feedback after the first execution of a query.D、The optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.E、After the optimizer identifies a query as a re-optimization candidate, statistics collected by thecollectors are submitted to the optimizer.
You want to analyze a SQL Tuning Set (STS) using SQL Performance Analyzer in a test database. Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set?（）A、Every SQL statement in the STS is considered only once for executionB、The SQL statements in the STS are executed concurrently to produce the execution plan and execution statisticsC、The execution plan and execution statistics are computed for each SQL statement in the STSD、The effects of DDL and DML are considered to produce the execution plan and execution statistics
in your database instance， the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impactof this setting（）A、Optimizer statistics are collected automatically.B、Only timed operating system （OS） statistics and plan execution statistics are collected.C、The snapshots for the Automatic Workload Repository （AWR） are not generated automatically.D、Snapshots cannot be collected manually by using the DBMS_WORKLOAD_REPOSITORY package.E、The Oracle server dynamically generates the necessary statistics on tables as part of query optimization.
You find this query being used in your Oracle 12c database: Which method a used by the optimizer to limit the rows being returned?（）A、A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the total rowsB、All the rows are returned to the client or middle tier but only the first 20 percent are returned to the screen or the application.C、A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.D、A TOP-N query is created to limit the rows to 20 percent of the total rows
You are a professional level SQL Server 2005 database administrator in an international corporation named Wiikigo. You are experienced in implementing high-availability solutions， monitoring the database server， and design deployment. In the company， you major job is the deployment， maintenance and support of Microsoft SQL Server 2005. You work as a database administrator for your company. The users report the slow performance of a SQL Server 2005 application. You are required to identify the queries which perform slowly because of blocking. Which action should you perform？（）A、In a SQL Trace log file， you should look for queries with low duration values and high read and write values.B、In a query execution plan， you should look for queries with missing or out-of-date statistics.C、In a SQL Trace log file， you should look for queries with high duration values and low read and write values.D、In a query execution plan， you should look for queries with a large number of table scans.
单选题You execute the following commands to audit database activities: SQL ALTER SYSTEM SET AUDIT_TRIAL=DB, EXTENDED SCOPE=SPFILE; SQL AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE BY JOHN By SESSION WHENEVER SUCCESSFUL; Which statement is true about the audit record that generated when auditing after instance restarts?（）AOne audit record is created for every successful execution of a SELECT, INSERT OR DELETE command on a table, and contains the SQL text for the SQL Statements.BOne audit record is created for every successful execution of a SELECT, INSERT OR DELETE command, and contains the execution plan for the SQL statements.COne audit record is created for the whole session if john successfully executes a SELECT, INSERT, or DELETE command, and contains the execution plan for the SQL statements.DOne audit record is created for the whole session if JOHN successfully executes a select command, and contains the SQL text and bind variables used.EOne audit record is created for the whole session if john successfully executes a SELECT, INSERT,or DELETE command on a table, and contains the execution plan, SQL text, and bind variables used.
单选题You are creating an online catalog application that will display product information on the company Web site. The product data is stored in a SQL Server 2005 database. The data is stored as relational data but must be passed to the application as an XML document by using FOR XML. You test your application and notice that not all of the items matching your query appear in the XML document. Only those products that have values for all elements in the schema appear. You need to modify your Transact-SQL statement so that all products matching your query appear in the XML document. What should you do？（）AAdd an XML index to the table that contains the product data.BAdd the XSINIL argument to the ELEMENTS directive in the query.CAdd a HAVING clause to the query.DAdd the replace value of clause to the query.
单选题The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;（）AYou obtain the results retrieved from the public synonym HR created by the database administrator.BYou obtain the results retrieved from the HR table that belongs to your schema.CYou get an error message because you cannot retrieve from a table that has the same name as a public synonym.DYou obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.EYou obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a FULL JOIN.
单选题In your database，the STATISTICS_LEVEL initialization parameter is set to BASIC. What is the impact of this setting？（）AThe optimizer statistics are collected automatically.BOnly the timed operating system （OS） statistics and plan execution statistics are collected.CThe Oracle server dynamically generates the necessary statistics on tables as part of query optimization.DThe snapshots for the Automatic Workload Repository （AWR） are not generated automatically. ESnapshots cannot be collected manually by using DBMS_WORKLOAD_REPOSITORY PACKAGE.
多选题Which two statements are true regarding the execution of the correlated subqueries？（）AThe nested query executes after the outer query returns the row.BThe nested query executes first and then the outer query executes.CThe outer query executes only once for the result returned by the inner query.DEach row returned by the outer query is evaluated for the results returned by the inner query.
单选题You are required to flashback your database. You want to find the amount of flashback data generated since the database was opened. Which task will you perform to obtain the required information？（）A Query the V$FLASHBACK_DATABASE_LOG view.B Query the V$FLASHBACK_DATABASE_STAT view.C Check the value of the DB_FLASHBACK_RETENTION_TARGET initialization parameter.D Query the V$RECOVERY_FILE_DEST view.
多选题Examine the parameters for your database instance: Which three statements are true about the process of automatic optimization by using cardinality feedback?（）AThe optimizer automatically changes a plan during subsequent execution of a SQL statement if there is a huge difference in optimizer estimates and execution statistics.BThe optimizer can re optimize a query only once using cardinality feedback.CThe optimizer enables monitoring for cardinality feedback after the first execution of a query.DThe optimizer does not monitor cardinality feedback if dynamic sampling and multicolumn statistics are enabled.EAfter the optimizer identifies a query as a re-optimization candidate, statistics collected by thecollectors are submitted to the optimizer.
多选题You want to analyze a SQL Tuning Set （STS） using SQL Performance Analyzer in a test database. Which two statements are true regarding the activities performed during the test execution of SQLs in a SQL Tuning Set？（）AEvery SQL statement in the STS is considered only once for execution.BThe SQL statements in the STS are executed concurrently to produce the execution plan and execution statistics.CThe execution plan and execution statistics are computed for each SQL statement in the STS.DThe effects of DDL and DML are considered to produce the execution plan and execution statistics.