| 1. | Which statement is true with reference to resource plans? |
| a. | | Resource plans are set using profiles |
| b. | | Only one resource plan can be stored in the database at one time |
| c. | | The database can have many resources plans, but only one can be active at any one time |
| d. | | The database can have many resources plans, and each user chooses which plan to belong to
|
|
|
|
|
|
|
| 2. | What is the main reason for creating a reverse key index on a column? |
| a. | | The column is populated using a sequence |
| b. | | The column contains many different values |
| c. | | The column is mainly used for value range scans |
| d. | | The column implementing an inverted list attribute
|
|
|
|
|
|
|
| 3. | There is a table with a million rows. An index has to be built on a column in the table that has a low cardinality. The table is part of a Decision Support System. The goal is to build an index that would be efficient for queries using AND/OR predicates. Which type of index would be most suitable? |
| a. | | B-Tree Index |
| b. | | Reverse Key Index |
| c. | | Bitmap Index |
| d. | | Compresses Indexes
|
|
|
|
|
|
|
| 4. | A table called ORDER is created with the following SQL query in a database:
CREATE TABLE order (
order_id NUMBER(5) not null,
product_name VARCHAR2(4) NOT NULL)
NOLOGGING
PCTFREE 0
storage (initial 32K next 32K pctincrease 0);
2 indexes have been created, one on the ORDER_ID column and the other on the PRODUCT_NAME column. Evaluate these 4 SQL statements, assuming use of the Rule-Bases Optimizer:
1. SELECT * FROM order where order_id = 40;
2. SELECT * FROM order where order_id+0 = 40;
3. SELECT * FROM order where product_name = 'Oracle';
4. SELECT * FROM order where product_name||'' = 'Oracle';
What is the valid conclusion about index usage in the above 4 SQL statements? |
| a. | | Statements 1, 2 & 3 will use an index, and in statement 4 the index will be ignored |
| b. | | Statements 1, 3 & 4 will use an index, and in statement 2 the index will be ignored |
| c. | | None of the SQL statements will use an index |
| d. | | Statements 1 & 3 will use an index, and in statement 2 & 4 the index will be ignored
|
|
|
|
|
|
|
| 5. | The DBA's task of building a well performing database often begins with selecting proper data storage structures. DBAs should be aware of what types of storage structures are appropriate for various data access methods. Which data access methods will enhance database performance when combined with the appropriate types of application? |
| a. | | Cluster |
| b. | | Advanced Queue |
| c. | | Advanced Replication |
| d. | | Index-organized table
|
|
|
|
|
|
|
| 6. | In an index-organized table, what type of segment is used to store row data that exceeds the index's PCTTHRESHOLD? |
| a. | | DATA segment |
| b. | | INDEX segment |
| c. | | OVERFLOW segment |
| d. | | TABLE segment
|
|
|
|
|
|
|
| 7. | What is a potential reason for a "SNAPSHOOT TOO OLD" error message? |
| a. | | The snapshots were not refreshed in time |
| b. | | An ITL entry in a data block has been reused |
| c. | | Rollback segment extent sizes are too large |
| d. | | The online redo log files are not big enough to snap the largest transactions
|
|
|
|
|
|
|
| 8. | Consider the following Sql Query:
SELECT sum(waits) * 100 / sum(gets) "Ratio",
sum(waits) "Waits", sum(gets) "Gets"
FROM v$rollstat;
What should be done if the number of waits for any class is greater than 1% of the total number of requests? |
| a. | | Create more rollback segments |
| b. | | Check for WAITS in v$rollstat and create more extents |
| c. | | Increase the number of extents in the rollback segments |
| d. | | Check for WRAPS in v$rollstat and create larger extents
|
|
|
|
|
|
|
| 9. | Which OPTIMIZER_MODE should be used to minimize total response time? |
| a. | | RULE |
| b. | | ALL_ROWS |
| c. | | FIRST_ROWS_n |
| d. | | FIRST_ROWS
|
|
|
|
|
|
|
| 10. | Which of the following statements are true about the Oracle block size? |
| a. | | Large block size is good for DSS |
| b. | | Large block size may increase index reads |
| c. | | Large block size decreases contention |
| d. | | Microsoft SQL Server is better than Oracle by far |
| e. | | Small block size may decrease index reads
|
|
|
|
|
|
|
| 11. | Examine the following query:
SELECT DECODE(SUM(totalq),0,'No Responses',
SUM(wait)/SUM(totalq)) "Average wait time"
From v$queue q,v$dispatcher d
WHERE q.type = 'DISPATCHER'
AND q.paddr = d.paddr;
Which of the following statement is true? |
| a. | | A steadily increasing value indicates a possible need for fewer dispatchers |
| b. | | A steadily increasing value indicates a possible need for more dispatchers |
| c. | | This query does not indicate whether user processes are waiting for dispatchers |
| d. | | The query returns the percentage of time the dispatcher process of each protocol are busy
|
|
|
|
|
|
|
| 12. | Poor performance is seen for inserts into a new table. V$WAITSTAT and V$SYSTEM_EVENT were queried and it was determined that there is free list contention in the database. Next, the segments were identified by joining the DBA_SEGMENTS table and V$SESSION_WAIT view to identify the file and block where waits are occurring. What should be the next step? |
| a. | | If the file and block identify a data block within a table, consider increasing the number of free lists on the table using the ALTER TABLE command and specifying the FREELISTS clause |
| b. | | If the file and block identify a data block within a table, consider increasing the number of free lists on the table using the ALTER TABLESPACE command and specifying the FREELISTS clause in the default storage clause |
| c. | | If the file and block identify a segment header for a table, consider increasing the number of free lists on the table using the ALTER TABLESPACE command and specifying the FREELIST clause in the default storage clause |
| d. | | If the file and block identify a segment header for a table, consider increasing the number of free lists on the table using the ALTER TABLE command and specifying the FREELISTS keyword in the storage clause
|
|
|
|
|
|
|
| 13. | Which resource consumer plan must be included in the resource directive plan to avoid validation errors? |
| a. | | OTHER |
| b. | | SYS_GROUP |
| c. | | DEFAULT_GROUPS |
| d. | | OTHER_GROUPS
|
|
|
|
|
|
|
| 14. | Where can the information about deadlocks occurring in the database be found? |
| a. | | Audit trail |
| b. | | Alert log and foreground server trace files |
| c. | | Online redo log |
| d. | | LGWR trace files
|
|
|
|
|
|
|
| 15. | There is a parent table (DEPT) and a child table (EMP). Most of the queries use joins of these tables. How can the access be optimized by foreign key between parent and child table? |
| a. | | Minimize space and time overhead for indexes associated with constraint |
| b. | | Set constraint deferred |
| c. | | Build UNIQUE constraint on foreign key |
| d. | | Nothing is required, because the matching unique or primary key is never updated or deleted
|
|
|
|
|
|
|
| 16. | Which description below describes OTHER_GROUPS consumer group? |
| a. | | This consumer group cannot be explicitly assigned to a user. It must have a resource directive specified in the schema of any active plan. This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan schema, including DEFAULT_CONSUMER_GROUP |
| b. | | This consumer group is the initial consumer group for all users /sessions that have not been explicitly assigned an initial consumer group. It has switch privileges granted to PUBLIC; therefore, all users are automatically granted switch privilege for this consumer group |
| c. | | It is the initial consumer group for the users SYS and SYSTEM |
| d. | | It provides a group having lower priority than SYS_GROUP and OTHER_GROUPS in this plan
|
|
|
|
|
|
|
| 17. | Why should statistics be gathered with the DBMS_STATS package instead of the ANALYZE command? |
| a. | | The ANALYZE command doesn't support HISTOGRAMS |
| b. | | DBMS_STATS enables gathering statistics in parallel |
| c. | | The ANALYZE command does not compute statistics on indexes |
| d. | | ANALYZE does not enable row-level sampling |
| e. | | All of the above
|
|
|
|
|
|
|
