Oracle 1Z0-117 Exam Dumps

Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.

Which three statements are true regarding the execution plan?

A. The view operator collects all rows from a query block before they can be processed but higher operations in the plan.

B. The in-line query in the select list is processed as a view and then joined.

C. The optimizer pushes the equality predicate into the view to satisfy the join condition.

D. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.

E. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables.

View Exhibit1 and examine the structure and indexes for the MYSALES table.

The application uses the MYSALES table to insert sales record. But this table is also extensively used for generating sales reports. The PROD_ID and CUST_ID columns are frequently used in the WHERE clause of the queries. These columns have few distinct values relative to the total number of rows in the table. The MYSALES table has 4.5 million rows.

View exhibit 2 and examine one of the queries and its autotrace output.

Which two methods can examine one of the queries and its autotrace output?

A. Drop the current standard balanced B* Tree indexes on the CUST_ID and PROD_ID columns and re-create as bitmapped indexes.

B. Use the INDEX_COMBINE hint in the query.

C. Create a composite index involving the CUST_ID and PROD_ID columns.

D. Rebuild the index to rearrange the index blocks to have more rows per block by decreasing the value for PCTFRE attribute.

E. Collect histogram statistics for the CUST_ID and PROD_ID columns.

Your database has the OLTP_SRV service configured for an OLTP application running on a middle tier. This service is used to connect to the database by using

connection pools. The application has three modules. You enabled tracing at the service by executing the following command:

SQL exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE (`OLTP_SRV');

What is the correct method of consolidating the trace files generated by the procedure?

A. Use all trace files as input for the tkprof utility to consolidate the trace files for a module.

B. Use one trace file at a time as input for the trcess utility and use tkprof utility to consolidate all the output files for a module.

C. Use the trcess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility.

D. Use the tkprof utility to consolidate the trace files and create an output that can directly be used for diagnostic purposes.