100% Guarantee of 1z0-054 test question materials and ebook for Oracle certification for IT candidates, Real Success Guaranteed with Updated 1z0-054 pdf dumps vce Materials. 100% PASS Oracle Database 11g: Performance Tuning exam Today!
2021 Oct 1z0-054 exam cram
Q31. You work as a database administrator to manage an online transaction processing (OLTP) system.The application code was recently tuned for optimum performance and users acknowledged the performance improvement. However, the users are now again complaining that the application response time has gone down considerably. You used a few queries to investigate the problem and a couple of important queries are shown in the Exhibit. View the Exhibit and analyze the output shown.
Which actions would improve the performance? (Choose all that apply.)
A. increasing the number database writers
B. decreasing the number of database writers
C. increasing the size of the database buffer cache
D. decreasing the size of the database buffer cache
E. decreasing the value set to the FAST_START_MTTR_TARGET parameter
Q32. 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
Q33. Examine the output of the query given below:
SQL> SELECT mutex_type, location, sum(gets), sum(sleeps)
GROUP BY mutex_type, location;
MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS)
-------------- ------------------------------------- ---------- -----------
Library Cache kglhdgn1 62 8669586 4538
Library Cache kglget2 2 2021618 24
Cursor Stat kkocsStoreBindAwareStats [KKSSTALOC8] 2975 1
Cursor Pin kkslce [KKSCHLPIN2] 666831 678
Library Cache kgllkdl1 85 3369224 110
Library Cache kglpnal1 90 224199 13
Library Cache kglic1 49 42068 10
Library Cache kglpin1 4 9620087 374
Library Cache kglpndl1 95 2065089 79
9 rows selected.
Which statement is true?
A. Each row in the output represents a SQL statement that had to wait for mutexes.
B. The Cursor Stat and Cursor Pin SLEEPS indicate that the CURSOR_SHARING parameter is set to EXACT.
C. The GETS column shows the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
D. The sum of numbers in the GETS and SLEEPS columns indicates the number of times a mutex/location was requested by the requesting session while being held by the blocking session.
Q34. You enabled tracing for a session by executing the following command:
SQL>SET AUTOTRACE TRACEONLY;
You issued a SQL query in the same session. Which two statements are true? (Choose two.)
A. It generates only a report on the execution path used by the SQL optimizer.
B. Trace for the statement would be created without displaying the query output.
C. It generates only the statement execution statistics without executing the query.
D. It generates a report on the execution path used by the SQL optimizer and the statement execution statistics.
Q35. View the Exhibit. Examine the query on the v$servicemetric view and the output.You wish to define a service-level threshold based on wall-clock time to monitor the performance of the service.
Which metric should be used to set the appropriate threshold?
Refresh 1z0-054 test:
Q36. View the Exhibit and examine the content of the V$MEMORY_RESIZE_OPS view.Which two conclusions can you draw? (Choose two.)
A. Automatic Memory Management (AMM) is enabled.
B. Automatic Shared Memory Management (ASMM) is enabled but not AMM.
C. The Shared Pool grew in IMMEDIATE mode to avoid an out-of-memory error.
D. The Default Buffer Cache grew in DEFERRED mode to avoid an out-of-memory error.
E. The memory is transferred in IMMEDIATE mode only if there are entirely empty granules available in other components.
Q37. The database application developers are planning to make some major schema changes such as creating new indexes and materialized views. They want to check the net impact of these changes on the workload performance. This activity has to be performed in the production database, so they want only the query part of the data manipulation language (DML) statements to be executed so that the side effects to the database or user data can be prevented.
What should they use to achieve this?
A. Database Replay
B. SQL Tuning Advisor
C. SQL Access Advisor
D. SQL Performance Analyzer
Q38. You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database.
View the Exhibit named SETTING and note the result cache settings.
Many users in the company state that performance has improved on the queries they use but some users complain that they have not got any performance benefit on the queries they use.
You checked all the queries they use and the following is one of them:
SQL> SELECT slnoq.currval as "SLNO", prod_id, pdname,
2 cust_name FROM sales WHERE sl_date < sysdate;
View the Exhibit named TEST and examine the testing performed to check this.
Why is the result cache not used? (Choose all that apply.)
A. because the query uses SYSDATE
B. because the query uses an alias for a column
C. because the query uses the SLNOQ.CURRVAL sequence
D. because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0
Q39. You are working as a DBA in ABC Corp. You are working on online transaction processing (OLTP) system. The database uses ASM storage. One of the ASM disk goes offline because of hardware failure. When the disk is added back again after the fix, because the database is active, rebalance operations impact the I/O subsystem.
Which two recommendations would you give to lower the impact on the I/O subsystem? (Choose two.)
A. Increase the number of ASMB processes.
B. Increase the number of DBWR_IO_SLAVES.
C. Decrease the value for the ASM_POWER_LIMIT parameter.
D. Increase the value for the ASM_POWER_LIMIT parameter.
E. Set the DISK_REPAIR_TIME disk attribute to a lower value.
F. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
Q40. You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.
You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings:
SQL> show parameter sga
NAME TYPE VALUE
------------------------------ --------------- -----------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 0
SQL> show parameter target
NAME TYPE VALUE
------------------------------ --------------- ----------------- .....
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 100M
sga_target big integer 0
You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA).
Which action would solve this problem?
A. Set the SGA_TARGET parameter to 300M.
B. Set the SGA_MAX_SIZE parameter to 400M.
C. Set the MEMORY_TARGET parameter to 100M.
D. Set the MEMORY_MAX_TARGET parameter to 300M.