Free sharing of new updated Oracle 1Z0-054 exam practice test. If you are looking to get certified in short possible time, better try Flydumps latest new version Oracle 1Z0-054 with all new questions and answers added,visit Flydumps.com to free Oracle 1Z0-054 download vce and pdf files.
QUESTION 52
Examine the output of the following query:
SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,
2 a.immediate_gets,a.immediate_misses,b.pid
3 FROM v$latch a, v$latchholder b, v$latchname c
4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#
5 AND c.name LIKE ‘&latch_name%’ ORDER BY a.latch#;
LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES ———— ——– ——– ——- —— ————– —————- shared pool
20016544 8520540 14112 3137 0 0
Which two conclusions can you draw from this? (Choose two.)
A. The latch was requested in no wait mode.
B. The latch was requested in willing-to-wait mode.
C. The shared pool operations to allocate and free memory in it caused the misses.
D. Automatic Memory Management is not enabled because the IMMEDIATE_GETS and IMMEDIATE_MISSES columns have zero in them.
Correct Answer: BC
QUESTION 53
Examine the output of the query given below: SQL> SELECT mutex_type, location, sum(gets), sum(sleeps) FROM v$mutex_sleep_history GROUP BY mutex_type, location; MUTEX_TYPE LOCATION SUM(GETS) SUM(SLEEPS) ————– ————————————- ———- ———– Library Cache kglhdgn1 62 8669586 4538 Library Cache kglget2 2 2016618 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.
Correct Answer: C
QUESTION 54
Examine the query output shown below:
SQL> SELECT sum(pins), sum(reloads), sum(reloads)/sum(pins) FROM v$librarycache;
SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
16479 16000 0.9709327
Which statement describes the correct interpretation of the reloads-to-pin ratio and the action to be taken?
A. This ratio is reasonable. However, decrease the shared pool size for better performance.
B. This ratio is very high and the shared pool size should be increased immediately to reduce this ratio.
C. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio crosses 1.
D. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio falls below 0.1.
Correct Answer: B
QUESTION 55
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
Correct Answer: AE
QUESTION 56
You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report are shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.
View the Exhibit named TIMEMODEL and note what has contributed to % DB Time. View the Exhibit named EFFICIENCY and examine the various percentages shown.
Identify the option that has the correct answers for the questions given below:
1) Which factor indicates the main problem?
2) What is the main problem?
3) What solution would you recommend?
A. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time. 2) The CPU is very slow. 3) Increase the number of processors.
B. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. 2) This is due to inadequate Database Buffer Cache. 3) Increase the size of database buffer cache.
C. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. 2) There are too many soft parses. 3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.
D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache. 2) There are too many hard parses. 3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.
Correct Answer: D
QUESTION 57
You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report is shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.
View the Exhibit named TIMEMODEL and note what has contributed to % DB Time. View the Exhibit named EFFICIENCY and examine the various percentages shown.
Which option describes the correct understanding of the main problem?
A. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time, which indicates that the CPU is very slow.
B. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. This indicates too many soft parses.
C. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. This indicates inadequate database buffer cache.
D. % Non-Parse CPU: and Soft Parse %: are low in the EFFICIENCY Exhibit, which shows that very few statements are found in the cache. This indicates that there were too many hard parses.
Correct Answer: D
QUESTION 58
You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database
encountered performance-related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further.
View the Exhibits and examine the AWR report.
Which is the appropriate solution to the problem in this database?
A. increasing the size of the shared pool
B. adding one more CPU to the system
C. setting the CURSOR_SHARING parameter to EXACT
D. configuring Java pool because it is not configured
Correct Answer: A
QUESTION 59
You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows but only four distinct products are sold. View the Exhibit named HIST to check the distribution of values in the table and the histograms on the table.
View the Exhibits named QUERY-1 and QUERY-2 that show details in the V$SQL view for the queries executed on the SALES table.
You check the plan table and notice that both the queries that executed on the SALES table used index range scan. The second query retrieved most of the rows in the table but used index range scan.
Why would the second query use the same plan?
A. because the plan was bind aware
B. because the bind peeking never happened
C. because the OPEN_CURSORS parameter is set to a very low value
D. because the optimizer did not consider selectivity due to the use of bind variables
Correct Answer: D
QUESTION 60
You work on an online transaction processing (OLTP) database in which the SALES table has 10,000 rows but only four distinct products are sold. View the Exhibit named HIST to check data distribution in the table and the histograms on the table.
View the Exhibit named QUERY-1 that shows details in the V$SQL view for the query executed on the SALES table having product id 1.
Further, you query the SALES table thrice more in the following order:
–
query rows having product ID 4
–
query rows having product ID 2
–
query rows having product ID 4
View the Exhibit named QUERY-n to see the details about these queries.
What do you infer from this?
A. The second plan was created because the first plan was aged out.
B. The CURSOR_SHARING parameter value was changed to EXACT after the second query was executed.
C. The third plan was created because the first plan was aged out and the second plan had different selectivity.
D. The first two executions used the same plan because at parse time the optimizer did not consider selectivity as the cursor was not yet considered bind-aware.
Correct Answer: D
QUESTION 61
You are working for a company that uses huge applications to manage its customer details. During your regular performance checks, you executed the following query: SQL> show parameter shared_pool_reserved_size NAME TYPE VALUE —————————— ——————— —————– shared_pool_reserved_size big integer 6920601 SQL> SELECT free_space, used_space, request_misses, request_failures from V$SHARED_POOL_RESERVED; FREE_SPACE USED_SPACE REQUEST_MISSES REQUEST_FAILURES ———- ———- ————– —————- 6469776 555528 0 113 You observed that REQUEST_FAILURES is 113 but not increasing, and also that REQUEST_MISSES is not increasing. What would you interpret from this?
A. The reserved pool is very large. Consider reducing the size of the shared pool.
B. The reserved pool is large and has free space to satisfy requests. Consider reducing the size of the reserved pool.
C. The reserved pool is very small and is not finding enough memory to satisfy requests. Increase the size of the shared pool.
D. The reserved pool is very small and is not finding enough memory to satisfy requests. Increase the size of the reserved pool.
Correct Answer: B
QUESTION 62
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: SQL> SELECT name, type, sharable_mem, kept 2 FROM v$db_object_cache 3 WHERE sharable_mem > 4000 4 AND EXECUTIONS > 5 5 AND (type=’FUNCTION’ OR type=’PROCEDURE’) 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?
A. Enable the result cache if not already enabled.
B. Flush the shared pool to make space for this procedure.
C. Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.
D. Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.
E. Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.
Correct Answer: C
QUESTION 63
You work for a small manufacturing company as a DBA. The company has various applications to manage the details of its business and customer base. The company has only one sales outlet where the operator updates the SALES table in the sales application with the details when a product is sold. The company has many other applications for various other businesses that use the same database. The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted. During peak hours, you noticed the following: When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers: SQL> SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name=’SLNSEQ’; SEQUENCE_NAME LAST_NUMBER
SLNSEQ 1056400
You observed this behavior many times and only during peak hours.
What could be the problem and solution for this?
A. The reserved pool is not configured. Configure the reserved pool.
B. The keep buffer pool is not configured. Configure the keep buffer pool.
C. The sequence was created with a CACHE of 100 numbers. Re-create the sequence with a higher CACHE number.
D. The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.
Correct Answer: D
QUESTION 64
You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database. Many users in the company
say 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 /*+ RESULT_CACHE */ slnoq.currval as “SLNO”, prod_id, pdname, 2 cust_name FROM sales WHERE sl_date < sysdate;
View the Exhibit 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 table might have an index on the SL_DATE column
Correct Answer: AC QUESTION 65
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
Correct Answer: AC
QUESTION 66
In which case is the database buffer cache NOT used?
A. when physical I/Os are performed to read from data files
B. when the database writer process writes to the SYSTEM tablespace
C. when the server process writes sort data to the temporary tablespace
D. when the database writer process writes undo data to the undo tablespace
Correct Answer: C
QUESTION 67
Which two statements are true about the database buffer cache? (Choose two.)
A. A buffer can be written to by many processes at the same time.
B. A buffer containing a block that is identical to the block on disk is called free buffer.
C. A buffer containing a block that is being accessed by a process is called dirty buffer.
D. A buffer available to be overwritten with a different database block at any time is called a dirty buffer.
E. A buffer can contain a different version of a block that is available in a different buffer of the same cache.
Correct Answer: BE
QUESTION 68
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.
Correct Answer: D
QUESTION 69
You work as a DBA and 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
What can you conclude based on this ratio?
A. The database performance is very good because of reduced logical I/Os.
B. The database performance cannot be determined based only on this ratio.
C. Good database performance is guaranteed because very few physical I/Os are performed.
D. The database performance is very good because most of the requested data blocks are found in the buffer cache.
Correct Answer: B
QUESTION 70
You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used three queries to check the database performance as shown in the Exhibit.View the Exhibit and analyze the output.
What conclusion can you draw from this?
A. There are many physical I/Os happening.
B. There are many full table scans happening.
C. The data blocks are aging out of the buffer cache very fast.
D. There are many cursors trying to access the same data blocks.
E. The DBWn processes are not freeing sufficient buffers to meet the demand.
Correct Answer: D
QUESTION 71
Examine the following information from the AWR report: Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time ————————————– ———— ———– ——— CPU time 559 88.80 log file parallel write 2,181 28 4.42 SQL*Net more data from client 516,611 27 4.24 db file parallel write 13,383 13 2.04 db file sequential read 563 2 .27 Which could cause waits on the db file sequential read event?
A. too many full table scans
B. too large a database buffer cache
C. too many waits occurring while reading multiple blocks synchronously
D. too many index range scans occurring which refers to blocks not in the database buffer cache
Correct Answer: D
QUESTION 72
Through Oracle 1Z0-054 study materials, you can expect to learn to know how your test-writers imagine seeing the manner in which questions are generally enquired. Traveling to That certification message boards have been become a very good to your Oracle 1Z0-054 exam test. FLYDUMPS Oracle 1Z0-054, Mobile Application Development Certification Exam is good for all the Oracle CX-310-019 candidates. FLYDUMPS offer you model Oracle 1Z0-054 study materials for that lifetime of your current device’s license, and we promise your current Oracle 1Z0-054 after utilizing own Oracle 1Z0-054 Test.
Welcome to download the newest Pass4itsure PHR dumps: https://www.pass4itsure.com/phr.html