It is more faster and easier to pass the Oracle 1z0-054 exam by using Precise Oracle Oracle Database 11g: Performance Tuning questuins and answers. Immediate access to the Up to the minute 1z0-054 Exam and find the same core area 1z0-054 questions with professionally verified answers, then PASS your exam with a high score now.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1z0-054 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Q51. You work as a DBA for a company and manage an online transaction processing (OLTP) system. You received complaints about the performance degradation of SQL statements executed by the application that uses this database.View the Exhibit and examine the output of the queries executed to investigate the performance issues.
Which two actions can improve performance? (Choose two.)
A. increasing the size of the shared pool
B. setting the CURSOR_SHARING parameter to EXACT
C. decreasing the value set for the OPEN_CURSORS parameter
D. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1337874392
E. replacing literals with bind variables in SQL statements with the PLAN_HASH_VALUE 1445457117
Q52. View Exhibit1 to examine the description of the CUSTOMERS table.
The CUSTOMERS table has been updated heavily today. In a frequently used SQL statement, you notice that estimated rows and the actual number of rows fetched differ greatly. The COUNTRY_ID column has an index.
View Exhibit2 and examine the query execution plan.
What would you recommend to improve the optimizer's estimation?
A. setting the STATISTICS_LEVEL parameter to ALL
B. setting the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE
C. creating extended statistics for the CUST_LAST_NAME, CUST_ID, and CUST_TOTAL columns
D. updating the statistics for the CUSTOMERS table by using the DBMS_STATS.GATHER_TABLE_STATS procedure
Q53. View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.
The Shared Pool was shrunk when the Default Buffer Cache required more memory. Why did the Java Pool not shrink when the Default Buffer Cache grew?
A. because it is not an auto-tuned component
B. because it uses memory available outside the total SGA size
C. because it always requires three granules and cannot be reduced
D. because it has less free memory than the Database Buffer Cache
E. because it cannot be shrunk below the value set to the JAVA_POOL_SIZE parameter
Q54. You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity:
SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100
2 "Cache Hit ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads'
7 AND phyd.name = 'physical reads direct'
Cache Hit Ratio
Which understanding of this ratio is correct?
A. 98.43% of times the requests have found free buffers.
B. 98.43% of the total number of requests used the buffer cache.
C. 98.43% of times the requests were satisfied by performing physical I/Os.
D. 98.43% of times the requests have found the required data blocks in the buffer cache.
Q55. View the Exhibit and examine the content of the V$PGA_TARGET_ADVICE_HISTOGRAM view.
What would you infer from this?
A. If the PGA size is set to 4 MB, then all the work areas would execute in optimal mode.
B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.
C. If the PGA size is set to 32 MB, then the number of work areas would reduce. So it is the appropriate size for PGA.
D. If the size of the Program Global Area (PGA) is set to more than 64 MB, then all the work areas would execute in optimal mode. So it is the appropriate size for PGA.
Q56. Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor as part of automatic SQL tuning task?
A. recommendation for SQL profiles
B. recommendation about effective indexing
C. recommendation about stale or no statistics
D. recommendation about the structure of SQL statements
Q57. As part of a proactive tuning activity, you have the Automatic Workload Repository (AWR) and ADDM enabled for your database with the default setting of snapshot retention as 8 days and snapshot interval as 60 minutes.
Which performance issue does NOT appear in the ADDM and needs alternative tools for investigation?
A. suboptimal parameter settings
B. application-level block contention
C. performance issues related to the Resource Manager
D. short-lived or intermittent performance issues lasting for less than 5 minutes
Q58. You observed in your database that the queries formerly executed in 5 seconds now take longer time.View the Exhibit and analyze the output of the query that you executed to find the problem.
Which is the primary concern?
A. the number of disk sorts
B. the number of full table scans
C. the number of chained and migrated rows
D. the number of rows fetched based on rowid
Q59. You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables.
What would happen to the SQL profile that is created for the SQL statement?
A. It becomes outdated immediately after the tables change.
B. It is maintained automatically along with the changes in the tables.
C. The profile will be used for the changed plans of the SQL statement.
D. The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.
Q60. You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the database performance.
What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?
A. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
B. Increase the value of the STALE_PERCENT preference for these tables.
C. Mark those associated indexes that are less frequently used as invisible.
D. Increase the value of the ESTIMATE_PERCENT preference for these tables