Free Oracle 1z0-064 Exam Dumps Questions & Answers

Examine the parameters set for your database instance:

To investigate the slow response time of queries on the TRANS table, you gather table and execute the query:

The table is stored in a tablespace with Automatic Segment Space Management (ASSM), and some rows of the TRANS table are migrated and chained.
Which two actions would you recommend to improve query response time?

• A.Create a bigger non-standard blocksize tablespace and move the TRANS table to that tablespace.
• B.Move the TRANS table to a tablespace with manual segment space management with a lower value set for the PCTUSED attribute.
• C.Move the TRANS table to a tablespace with manual segment space management with a higher value set for the PCTFREE attribute.
• D.Reorganize the TRANS table online by using the DBMS_REDEFINITION package.

You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.
You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.
Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)

• A.Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.
• B.Use bind variables for similar queries instead of literals.
• C.Configure the KEEP pool and cache the queried tables used in the KEEP pool.
• D.Use the result cache hint in the queries.
• E.Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.

Examine the query and its output:

Which two statements are true? (Choose two.)

• A.Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.
• B.Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.
• C.Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.
• D.Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.
• E.Session 4208 is definitely idle and should be killed to free network resources.

The CUSTOMERS table has 55,500 rows and 620 distinct values in the CUST_CITY_ID column. The number of popular values is 54 and less than 99% of the rows contain popular values.
Which type of histogram should you create to accurately determine the cardinality estimate on the CUST_CITY_ID column?

• A.frequency histogram
• B.height-balanced histogram
• C.high-frequency histogram
• D.hybrid histogram

Which three actions should you perform to reduce shared pool fragmentation and avoid the "ORA-04031: unable to allocate .... bytes of shared memory" error for the shared pool? (Choose three.)

• A.Identify the packages or procedures that are causing the "ORA-04031:unable to allocate....bytes of shared memory" error and use the DBMS_SHARED_POOL.KEEP to keep them in the shared pool.
• B.Standardize the type, size, and naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
• C.Configure the Server Result Cache.
• D.Use DBMS_SHARED_POOL.KEEP to keep the SYS.STANDARD, SYS.DBMS_STANDARD, and SYS.DIUTIL packages and frequently executed compiled triggers.
• E.Use more anonymous procedures.
• F.Configure shared server mode.