1z0-054 | Up to the minute Oracle 1z0-054 exam
Q21. You are working on the Database using file system RAID level 0 striping. You want to migrate the database files to the Automatic Storage Management (ASM). The applications supported by the database primarily perform small, random I/Os in which each foreground process reads a data block into the buffer cache for updates and the changed blocks are written in batches by the DBWR process.
Identify two outcomes of ASM striping in combination with RAID 0. (Choose two.)
A. It provides higher bandwidth.
B. It negatively impacts the write-intensive workloads.
C. It allows you to evenly distribute disks for your data.
D. It causes contention on Data and Flash Recovery Area (FRA).
Answer: AC
Q22. View the Exhibit.Which statement is true about applying the threshold setting in the Exhibit.
Exhibit:
A. The threshold does not depend on any baseline.
B. The threshold is decided by comparing the warning and threshold values with the LAST_NIGHT baseline.
C. The threshold is decided by comparing the warning and threshold values with the SYSTEM_MOVING_WINDOW baseline.
D. The threshold is decided by comparing the warning and critical values with the baseline set on the performance page.
Answer: A
Q23. What is the purpose of running the statspack.snap script during the installation of the Statspack package?
A. It creates the initial baseline.
B. It automates the collection of statistics.
C. It stores the current performance statistics in the Statspack tables.
D. It creates the PERFSTAT user, the Statspack tables, and constraints
Answer: C
Q24. The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database instance. The space consumption has significantly increased by the automatically captured plan baselines. You are suggested to run the following block:
BEGIN
dbms_spm.configure('SPACE_BUDGET_PERCENT', 30);
END;
After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.)
A. increasing the AWR retention period
B. purging the older SQL management objects
C. increasing the size of the SYSTEM tablespace
D. increasing the size of the SYSAUX tablespace
Answer: BD
Q25. View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECT statements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue) is not being used by the optimizer.
Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is more efficient? (Choose two.)
Exhibit:
A. Mark the second plan (in blue) as fixed.
B. Use SQL Tuning Advisor to tune the plan.
C. Use SQL Access Advisor to tune the plan.
D. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.
Answer: BD
Q26. Your system is upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version. After running the SQL Performance Analyzer for optimizer version change, you observe performance regression for a few SQL statements.
What would you suggest to avoid performance regression for these SQL statements?
A. Include the old plans in the SQL plan baseline.
B. Use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE to evolve plans.
C. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE.
D. Increase the value for the OPTIMIZER_DYNAMIC_SAMPLING parameter.
Answer: A
Q27. View the Exhibit and note the workload type selected to quickly configure the metric threshold.
Which statement describes the result after finishing this quick configuration process?
Exhibit:
A. It sets thresholds for all basic metrics.
B. All thresholds set are of the Significance Level type.
C. It uses only the SYSTEM_MOVING_WINDOW baseline for thresholds.
D. The thresholds set by the quick configuration process are of the Fixed Value type.
Answer: C
Q28. The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity for these three columns varies from the selectivity that the optimizer calculates.
What would you recommend to influence the selectivity calculated by the optimizer?
A. creating function-based indexes by concatenating all the columns
B. updating histogram statistics for these columns by using the DBMS_STATS.GATHER_TABLE_STATS procedure
C. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and create index on the virtual column
D. using the DBMS_STATS.CREATE_EXTENDED_STATS function to create a virtual column and the DBMS_STATS.GATHER_TABLE_STATS procedure to collect statistics on the virtual column
Answer: D
Q29. Examine the following query and its output:
SQL>SELECT namespace, gets, gethits, gethitratio, pins, pinhits, reloads,invalidations
FROM v$librarycache
WHERE namespace = 'SQL AREA'
NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS RELOADS INVALIDATIONS
--------- ----- ------- ----------- ----- ------- -------- -------------
SQL AREA 11942 2832 .237146207 65900 62486 28 118
Which two observations are correct? (Choose two.)
A. About 24% of the parse calls were able to find a cursor to share.
B. DML locks were requested 65,900 times for objects in the SQL AREA.
C. Library cache misses happened 118 times because invalidations occurred due to DDL on the dependant objects.
D. Reparsing of SQL statements before they had finished executing happened 28 times due to modification of a dependent object.
Answer: AC
Q30. View the Exhibit and examine the Instance Efficiency Percentages that you got on generating an Automatic Workload Repository (AWR) report for your online transaction processing (OLTP) database.
What can you deduce from this part of the AWR report? (Choose all that apply.)
Exhibit:
A. A low Library Hit % indicates that cursors are not being reused.
B. A low Execute to Parse % indicates that most SQL statements are reusing the existing execution plans.
C. A high In-memory Sort % indicates that most sorts are being done in memory, thereby causing the performance to degrade.
D. A high Buffer Hit % indicates that required data blocks were found in the Buffer Cache memory, thereby avoiding expensive disk I/O.
Answer: AD