Online Access Free 1z1-064 Exam Questions

As part of an application upgrade, new objects are being added to an application schem a. You want to check the effect of the new schema objects on the performance of the SQL workload generated by the application.
How would you accomplish this?

• A.Capture the workload, set the OPTIMIZER_USE_PENDING_STATISTICS to FALSE, and then replay the workload.
• B.Capture the workload in an STS and submit to SQL Access Advisor as an input to generate recommendations for indexes and materialized views.
• C.Set the PUBLISH statistic preference to FALSE, gather statistics, capture the workload in a SQL Tuning Set (STS), and submit the STS to SQL Tuning Advisor.
• D.Capture the workload in an STS and submit to SQL Tuning Advisor as an input to generate recommendations for indexes and SQL profiles.

Which three statements are true about the interpretation of an execution plan? (Choose three.)

• A.An INDEX UNIQUE SCAN in the Operation column always implies that only one row will be returned.
• B.The cost of the entire plan is indicated by the line with Id 0 and always includes both I/O and CPU resources.
• C.A TABLE ACCESS FULL in the Operation column for a query with a WHERE clause occurs only if no index exists for the filter column.
• D.The ROWS column indicates the cardinality of each operation and is always calculated by dividing the total number of rows in the table by the number of distinct values in the column used in the WHERE clause predicate.
• E.A HASH JOIN in the Operation column always implies that two tables are joined by using an equijoin.

Your database supports an OLTP workload where applications primarily perform small random I/Os. You notice an increase in the I/O requests queued up against the storage, and an increase in the wait time in queue. The database uses file system storage.
What would you recommend to decrease the wait time of I/O requests?

• A.Increase the value of the DB_WRITER_PROCESSES parameter.
• B.Increase the size of the buffer cache.
• C.Suggest storage admin to add more disks (spindles) to storage and stripe data across disks.
• D.Decrease the value of the DBWR_IO_SLAVES parameter.

You are administering a database that supports multiple applications, which make dedicated connections to the database instance by using different services.
You execute the command to enable tracing of the ORCL1 service:

Which two statements are true? (Choose two.)

• A.SQL trace is not enabled for the service because a module name is not specified.
• B.A SQL trace file is generated for each session that maps to the ORCL1 service.
• C.A single trace file is generated for all sessions mapped to the ORCL1 service.
• D.SQL trace is enabled for all modules and actions for sessions mapped to the ORCL1 service.
• E.A SQL trace file is generated for each of the modules using the ORCL1 service.

Examine the parameters set for your database instance:

You are administering a database that supports an OLTP workload. Users complain about the degraded performance of some queries. While diagnosing, you notice a large number of hard parses occurring for several syntactically almost identical SQL statements that differ only in literal values in the WHERE clause.
Which two actions would you recommend to improve performance? (Choose two.)

• A.Create the KEEP cache and cache the tables used in the queries.
• B.Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
• C.Set the CURSOR_SHARING parameter to FORCE.
• D.Use bind variables instead of literals.
• E.Create SQL plan baselines for the almost identical SQL statements and load them into the cursor cache.