[Free] 2018(Jan) Latesttests Testking Oracle 1z0-054 Dumps with VCE and PDF Download 111-120

Latesttests 2018 Jan Oracle Official New Released 1z0-054
100% Free Download! 100% Pass Guaranteed!
http://www.Latesttests.com/1z0-054.html

Oracle Database 11g: Performance Tuning

Question No: 111

Which two statements are true about the database buffer cache? (Choose two.)

  1. A buffer can be written to by many processes at the same time.

  2. A buffer containing a block that is identical to the block on disk is called free buffer.

  3. A buffer containing a block that is being accessed by a process is called dirty buffer.

  4. A buffer available to be overwritten with a different database block at any time is called a

    dirty buffer.

  5. A buffer can contain a different version of a block that is available in a different buffer of the same cache.

Answer: B,E

Question No: 112

You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development databases. The company has recently upgraded this database to Oracle Database 11g from Oracle Database 10g.

You noticed that SGA is undersized as shown in Exhibit 1.

Latesttests 2018 PDF and VCE

To investigate further, you checked the related parameters as shown in Exhibit 2.

Latesttests 2018 PDF and VCE

You executed the following command to solve the problem: ALTER SYSTEM SET MEMORY_TARGET=800M;

What would happen?

  1. The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.

  2. It implements AMM by setting MEMORY_TARGET and MEMORY_MAX_TARGET parameters to 800M.

  3. It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M and the MEMORY_MAX_TARGET parameter to 800M.

  4. The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to a value less than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE values.

Answer: A

Question No: 113

In a database, the values for transactions per second are higher during working hours and lower during nonworking hours. You want to receive alerts in future time periods if the daytime performance varies compared to the captured daytime performance or the nighttime performance varies compared to the captured nighttime performance, over a repeating time period. This wouldpermit the DBA to perform additional performance tuning operations. Which feature would you use to record this variation in performance statistics?

  1. SQL tuning set

  2. SQL plan baselines

  3. the Automatic Workload Repository (AWR) baseline

  4. the Automatic Workload Repository (AWR) baseline template

Answer: D

Question No: 114

View the Exhibit and examine the Load Profile from the AWR report of a test database.The application must process an average of 10,000 very small transactions per day. The bulk of the processing activity is for DML operations with minimal to nil query processing.Which

two statements are correct about the values of the Physical reads events? (Choose two.)

Latesttests 2018 PDF and VCE

  1. The per second value indicates the total number of data blocks read from disk to only the buffer cache.

  2. The per transaction value includes both the db block gets and the consistent gets that occurred during the last sample period.

  3. The per transaction value indicates that write or update transactions have been committed or rolled back during the last sample period.

  4. The per second value indicates the total number of data blocks read from disk to the buffer cache including reads bypassing the buffer cache, and reads into process private buffers.

Answer: C,D

Question No: 115

View Exhibit1 to examine the description of the CUSTOMERS table.

Latesttests 2018 PDF and VCE

You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.

Latesttests 2018 PDF and VCE

The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table. What can you do to make the optimizer

detect the actual number of rows?

  1. Set the STATISTICS_LEVEL parameter to ALL.

  2. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.

  3. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.

  4. Increase the STALE_PERCENT value for the CUSTOMERS table by using the DBMS_STATS.SET_TABLE_PREFS procedure.

Answer: C

Question No: 116

You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure.

Further, you execute the following query to investigate: SQLgt; SELECT name, type, sharable_mem, kept

  1. FROM v$db_object_cache

  2. WHERE sharable_mem gt; 4000

  3. AND EXECUTIONS gt; 5

  4. AND (type=#39;FUNCTION#39; OR type=#39;PROCEDURE#39;)

6 /

NAME TYPE SHARABLE_MEM KEPT

———- ——- ——– —— GETEMKEY FUNCTION 13695 YES UPDATE_INV PROCEDURE 14766 NO

SETEMUSERCONTEXT PROCEDURE 13703 YES DECRYPT FUNCTION 17790 YES UPDATE_DEPT PROCEDURE 18765 NO

What would you do to improve the performance?

  1. Enable the result cache if not already enabled.

  2. Flush the shared pool to make space for this procedure.

  3. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.

  4. Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.

  5. Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.

Answer: C

Question No: 117

You plan to collect statistics for an important table in your database to help improved query performance. You are uncertain about the optimizer behavior after the statistics have been collected because currently there are no statistics for indexes or partitions on the table. You wish to test the performance effect of the newly collected statistics. How would you prevent the optimizer from using the newly collected statistics for other sessions immediately after statistics collection?

  1. Set the PUBLISH preference to FALSE for the table.

  2. Set the NO_VALIDATE preference to TRUE for the table.

  3. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.

  4. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter to FALSE for the session.

  5. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE for the session.

Answer: A

Question No: 118

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:

SQLgt; SELECT (1-((phy.value-phyd.value) / (cur.value con.value))) * 100

  1. quot;Cache Hit ratioquot;

  2. FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd 4 WHERE cur.name = #39;db block gets#39;

5 AND con.name = #39;consistent gets#39; 6 AND phy.name = #39;physical reads#39;

7 AND phyd.name = #39;physical reads direct#39;; Cache Hit Ratio

——– 98.43

Which understanding of this ratio is correct?

  1. 98.43% of times the requests have found free buffers.

  2. 98.43% of the total number of requests used the buffer cache.

  3. 98.43% of times the requests were satisfied by performing physical I/Os.

  4. 98.43% of times the requests have found the required data blocks in the buffer cache.

Answer: D

Question No: 119

Examine the output of the following query:

SQLgt; SELECT tablespace_name, tablespace_size, allocated_space, free_space 2 FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

————— ——– ——– —– TEMP_L 157286400 144048128 157220864

TEMP 105906176 105906176 0

After some time you executed the same command and observed a different output, as shown below:

SQLgt; SELECT tablespace_name, tablespace_size, allocated_space, free_space 2 FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

————— ——– ——– —– TEMP_L 131072 65536 65536

TEMP 105906176 105906176 0

Why has the output changed?

  1. because checkpoint occurred

  2. because the TEMP_L tablespace was shrunk

  3. because the database instance was restarted

  4. because no sort operations are running currently

Answer: B

Question No: 120

View the Exhibit and examine the content of the V$SGA_RESIZE_OPS view.

Latesttests 2018 PDF and VCE

You executed the following command to increase the size of the Large Pool: SQLgt; ALTER SYSTEM SET large_pool_size=8M;

System altered.

Which component would shrink to increase the size of the Large Pool?

  1. the Java Pool

  2. the Shared Pool

  3. KEEP Buffer Cache

  4. Default Buffer Cache

Answer: D

100% Free Download!
Download Free Demo:1z0-054 Demo PDF
100% Pass Guaranteed!
Download 2018 Latesttests 1z0-054 Full Exam PDF and VCE

Latesttests ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 Latesttests IT Certification PDF and VCE