Q. Which component will NEVER allocate memory from the large pool?
A. Oracle Library Cache.
B. Oracle Parallel Query.
C. Oracle Recovery Manager.
D. Oracle Multithreaded Server.
Answer: A
Explanation:
Oracle Library Cache will NEVER allocate memory from the Large Pool.
B:Oracle Parallel Query (PQ) can cause the Shared Pool to cache additional items not related to application SQL. That’s why it can allocate memory from the Large Pool.
C:Oracle Recovery Manager (RMAN) utility can use the Large Pool to process requests for optional features.
D:UGA information for the Shared Sever option is cached in the Large Pool if it’s defined. Otherwise, it uses the Shared Pool.
Q. Database Resource Manager uses resource plans to determine resource limits for the set of users.Which statement is true in 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.
Answer: C
Explanation:
Oracle8i introduced a new feature, Resource Manager, which was designed to improve the allocation and management of server resources needed by application users. In Oracle 9i, it is possible to control numerous aspects of application processing via Resource Manager. The database can have many resources plans, but only one can be active at any one time.
A:Resource plans are not set using profiles.
B:The database can have many resources plans.
D:Each user cannot choose which plan to belong to because only one plan can be active at any one time.
Q. Which three actions will cause queries to place a table’s blocks at the most-recently-used end of the LRU list for the DEFAULT pool? (Choose three)
A. Creating a table with the CACHE option.
B. Querying the table by using a CACHE hint.
C. Ensuring the query performs a full table scan.
D. Defining the table without the option for caching.
E. Altering an existing table to set the CACHE option.
F.Ensuring the query does not retrieve data through index lookup.
G. Creating a separate database buffer cache to hold cached table.
Answer: A, B, E
Explanation:
If you are creating a table with the CACHE option or altering an existing table to set the CACHE option will place a table’s blocks at the most-recently-used end of the LRU list. Querying the table by using a CACHE hint will cause queries to place a table’s blocks at the most-recently-used end of the LRU list.
C:It does not mean that blocks will be placed at the most-recently-used end of the LRU list if the query performs a full table scan.
D:Defining the table without the option for caching will not place blocks in the LRU list.
F:This statement will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.
G:You don’t need to create a separate database buffer cache to hold cached table.
Q. What is the main reason to create a reverse key index on a column?
A. The column is populates 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.
Answer: A
Explanation:
The Reverse Key Index (RKI) is a special type of B-Tree index. The RKI is useful when an index is built on a column that contains sequential numbers.
B:B-Tree index will be appropriate choice for the column with many different values.
C:The column is mainly used for value range scans is not good candidate for the Reverse Key Index. Reverse Key indexes are only useful for equality and non-equality searches. Queries that perform range scans (e.g., using BETWEEN, >, <) on columns that are Reverse Key indexed will not be able to use the index and will cause full table scans.
D:The RKI does not work for the column implementing an inverted list attribute.
Q. Which type of table is the best candidate to be cached?
A. Small table rarely retrieved with a full table scan.
B. Large table rarely retrieved with a full table scan.
C. Small table frequently retrieved with a full table scan.
D. Large table frequently retrieved with a full table scan.
Answer: C
Explanation:
Small tables frequently retrieved with a full table scan can be cached.
A:If table is rarely retrieved there is no sense to cache it.
B:Large table cannot be easy cached because CACHE option works mostly for the small tables that can be loaded in the memory. Also if table is rarely retrieved there is no sense to cache it.
D:Large table is not good candidate to be cached.
Q. Which initialization parameter specifies the location of the alert log file?
A. UTL_FILE_DIR
B. USER_DUMP_DEST
C. LOG_ARCHIVE_DEST
D. BACKGROUND_DUMP_DEST
Answer: D
Explanation:
BACKGROUND_DUMP_DEST initialization parameter specifies the location of the alert log file.
A:UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O.
B:USER_DUMP_DEST is used to specify the location of user trace files.
C:LOG_ARCHIVE_DEST shows the directory for the archived redo logs.
Q. The NOLOGGING mode in SQL statements is a tool used to reduce redo operations, but NOLOGGING does not apply to every operation for which the attribute is set. Which three SQL statements can use the NOLOGGING mode to reduce redo operations? (Choose three)
A. UPDATE
B. CREATE INDEX
C. ALTER INDEX…REBUILD
D. Conventional Path INSERT
E. CREATE TABLE…. AS SELECT
Answer: B, C, E
Explanation:
You can use the NOLOGGING mode to create index, to rebuild index and to create table as select from the other table.
A:It cannot be used for DML operations itself. Only table can be switched to this mode and than UPDATE command can be performed for the table.
D:Conventional Path INSERT cannot use it. Once the NOLOGGING attribute is set on a table, redo entry generation will be suppressed for all subsequent DML on the table only when that DML is of the following types: Direct Path loads using SQL*Loader,direct load inserts using the /*+ APPEND*/ hint.
Q. Which two statements about database blocks are true? (Choose two)
A. DSS environment prefer a large block size.
B. Small block sizes result in more block contention.
C. Random access to large object favours a large block size.
D. You can reduce the number of block visits by packing rows as closely as possible into blocks.
E. To change the database block size, you must shot down the instance and perform a STARTUP RESETLOGS after you make the change.
Answer: A, D
Explanation:
DSS database will work better with a large database block size due to lot of full scans. Users of these systems are concerned with response time, which is the time it takes to get the results from their queries, so data need to be packed as closely as possible into blocks.
B:Small block sizes will cause overload in the DSS system.OLTP systems works better with small block sizes.
C:Random access to large object does not prefer a large block size.
E:You cannot change a database block size with the STARTUP RESETLOGS command. There is no STARTUP RESETLOGS command in Oracle. The STARTUP and ALTER DATABASE OPEN RESETLOGS commands exist.
Q. The ORDERS table has millions of rows and is accessed very often with an index (ORDID_NDX) on a primary key (ORD_ID). Where should ORDERS and ORDID_NDX be stores?
A. Same tablespace
B. Different tablespace on the same disk.
C. Tablespace containing a rollback segment.
D. Different tablespaces on different disks.
Answer: D
Explanation:
To avoid I/O contention you need to store different tablespaces on different disks.
A:Storing data and indexes on the same tablespace can cause performance degradation.
B:Different tablespace on the same disk will not fix I/O contention problem.
C:Rollback tablespace should be stored way far from the data and indexes.
Q. Which two statements about row migration are true? (Choose two)
A. Row migration is caused by a PCTREE value set too low.
B. Row migration can be resolved using the ANALYZE command.
C. Row migration can be reduced by choosing a larger block size.
D. Row migration means that row pieces are stored in different blocks.
E. Queries that use an index to select migrated rows perform additional I/O.
Answer: A, B
Explanation:
Row migration occurs when a previously inserted row is updated. If the update to the row causes the row to grow larger than the space available in the block specified by PCTFREE, Oracle moves (or migrates) the row to a new block. The ANALYZE command just populates the CHAIN_CNT column of DBA_TABLES, which is otherwise null. This column will indicate how many of a table’s rows are using more than one block to store data. However, no distinction is made between the number of chained rows and the number of migrated rows.
C:Row migration cannot be reduced by choosing a larger block size. Rebuilding the database with a larger block size can give you a significant performance benefit because of row chaining,but not row migration problem elimination.
D:Row migration is migration the row to a new block.Whenever a row split over two or more multiple blocks it is referred to as a chained row.
E:Queries that use an index to select migrated rows do not perform additional I/O.
Q. What are three indications of contention for this rollback segment header?
A. A nonzero value in the WAITS column of the V$ROLLSTAT view.
B. A nonzero value in the UNDO HEADER column of the V$WAITSTAT view.
C. A nonzero value in the ROLL_SEG_WAIT column of the V$ROLLSEGS view.
D. A nonzero value in the UNDO_HEADER_WAITS columns of the V$ROLLBACK_SEGS view.
E. A nonzero value in the Undo Segment TX Slot event of the V$SYSTEM_EVENT view.
Answer: A, B, E
Explanation:
The ROLLSTAT view contains detailed information regarding the behavior of the rollback segments in the database. In particular, the columns USN, GETS and WAITS are particularly useful for measuring contention for the rollback segment’s header. WAITS column shows number of times a user Server Process needed to access the rollback segment header and experienced a wait. The V$WAITSTAT view contains information on block contention statistics. A nonzero value in the UNDO_HEADER column indicates a contention problem. The V$SYSTEM_EVENT view tracks performance related information on rollback segments via the Undo Segment TX Slot statistic. Ideally, the value in the Undo Segment TX Slot event should be consistently at or near zero.
C: There is no V$ROLLSEGS data dictionary view in the Oracle.
D: There is no V$ROLLBACK_SEGS data dictionary view in the Oracle.
Q. When tables are stored in locally managed tablespaces, where is extent allocation information stored?
A. Memory
B. Data dictionary.
C. Temporary tablespace.
D. Within the locally managed tablespace
Answer: D
Explanation:
The extent allocation information is stored in the corresponding tablespace if tables are stored in locally managed tablespaces.
A: This information is stored in the corresponding tablespace, not in the memory.
B: Only for tables in data dictionary managed tablespaces extent allocation is stored in the data dictionary.
C: Temporary tablespaces are not used to store extent allocation information.
Q. What is one difference between I/O slaves and DBWn processes for the DB Writer?
A. In Oracle8i, I/O slaves are not available; only DBWn processes are available.
B. I/O slaves perform the write function only, while DBWn processes also perform date-gathering activity.
C. I/O slaves will work only with synchronous I/O, whereas DBWn processes are available only within asynchronous I/0.
D. I/O slaves will work only with asynchronous I/O, whereas DBWn processes are available only within synchronous I/0.
Answer: B
Explanation:
Database Writer slave processes are similar to the actual DBW0 process itself, except they can only perform write operations, not move buffers from the LRU List to the Dirty List in the Database Buffer Cache as DBW0 does. The purpose of these slaves is to simulate asynchronous I/O on systems that only support synchronous I/O.
A: I/O slaves are available in the Oracle8i.
C: I/O slaves will work not only with synchronous I/O, they can simulate asynchronous I/O operations.
D: I/O slaves work with synchronous I/O and it can simulate asynchronous I/O operations.
Q. With reference to Oracle data storage structures, a cluster is defined as?
A. A group of table that each have more then 2 low cardinality columns.
B. A data structure where a group of one or more tables have their own dedicated tablespaces.
C. A group of one or more tables which resides in a tablespace that is striped across multiple disks.
D. A group of one or more tables that share the same data blocks because they share common columns and are often used together in join queries.
Answer: D
Explanation:
Clusters are used to store the data from one or more tables in the same physical Oracle blocks. In general, clustered tables should have these attributes: always be queried together and only infrequently on their own, have little or no DML activity performed on them after the initial load, have roughly equal numbers of child records for each parent key.
A:Cluster definition is not related with columns’ cardinality.
B:Clusters’ tables do not have their own dedicated tablespaces. They use the same physical Oracle blocks.
C:Tablespace can be stripped across multiple disks (via its datafiles), but tables in side this tablespace can or cannot be a cluster.
Q. You have a table with a million rows. You want to build an index on a column in the table that has a low cardinality. The table is part of a Decision Support System.Your 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. Bitmap Index.
C. Reverse Key Index.
D. Compresses Indexes.
Answer: B
Explanation:
Because of low cardinality of column in the table of DSS system bitmap index will be useful only to perform queries using AND/OR predicates.
A:B-tree index works better for the OLTP systems with high cardinality columns.
C:Reverse Key Index is useful when an index is built on a column that contains sequential numbers.
D:There is no compressed index type in Oracle.
Q. What are two main benefits of index-organized tables? (Choose two)
A. More concurrency.
B. Faster full table scans.
C. Fast primary key-based access.
D. Less contention on the segment header.
E. Less storage is required because there is no duplication of primary key values.
Answer: C, E
Explanation:
Oracle provides Index Organized Tables (IOTs) to store a table’s data in a specific order. Instead of storing a row ID pointer to where the rest of the row data is stored, the row data is actually stored in its entirety in the index itself. The table rows are stored in index order. If you access the table using its primary key, an IOT will return the rows more quickly than a traditional table. The extra free space is available because there is no duplication of primary key values in an IOT.
A: IOTs do not provide more concurrency.
B: They will not be helpful for full table scans.
D: Less contention on the segment header is not a main benefit of index-organized tables.
Q. Which four statements are true regarding materialized views? (Choose four)
A. Materialized views cannot be partitioned, nor can they be defined on partitioned tables.
B. Materialized views are often used in data warehouses to increase the speed of queries on very large datatables.
C. Queries that benefit from the use of materialized views often involve joins between tables or aggregations such as SUM.
D. A materialized view stores both the definition of a view and the rows resulting from the execution of the views.
E. Materialized views can be used to replicate data, which was formerly achieved using the CREATE SNAPSHOT statement.
Answer: B, C, D, E
Explanation:
Stored outlines help speed up queries by telling the optimizer how to tackle the query execution associated with a particular SQL statement. Materialized views are also designed to speed up queries by storing data from queries in a pre-joined, pre-summarized format. Unlike a traditional view, which is merely stored in the data dictionary as a SELECT statement that is executed when the view is accessed, a materialized view stores the physical results of the view in its own segment, separate and distinct from the underlying table on which the view is based. Materialized views are intended primarily for use in data warehouses and Decision Support Systems where large volumes of data are accessed and summarized using queries.
A: Materialized view segment can be stored in its own tablespace and can be indexed and partitioned.
Q. Which statement is valid regarding index clusters?
A. Index clusters can only be used for tables with low cardinality columns.
B. Index clusters are generally well suited for tables that have many full table scans.
C. Normal B-Tree indexes do not store null key values, whereas cluster indexes store null keys.
D. A cluster index always takes up much more storage space than a normal index for the same set of key values.
Answer: C
Explanation:
Index clusters can store null keys. B-Tree indexes do not.
A:Index clusters cannot only be used for tables with low cardinality columns. They are used to store the data from one or more tables in the same physical Oracle blocks.
B:They are not very good suited for tables with many full table scans.
D:A cluster index does not use much more storage space than a normal index.
Q. You have a table called COMPANY created with the following SQL in your database:CREATE TABLE company (
company_id NUMBER(5) not null,
company_name VARCHAR2(4)NOT NULL
)
NOLOGGING
storage (initial 32K next 32K pctincrease 0)
;You have created 2 indexes, one on the COMPANY_ID column and the other on the COMPANY_NAME column. Evaluate these 4 SQL statements, assuming use of the Rule-Bases Optimizer:
1. SELECT * FROM company where company_id = 40;
2. SELECT * FROM company where company_id+0 = 40;
3. SELECT * FROM company where company_name = ‘Oracle’;
4. SELECT * FROM company where company_name||’’ = ‘Oracle’;
What is a valid conclusion about index usage in the above 4 SQL statements?
A. All 4 SQL statements will use an index.
B. Statements 1, 2 & 3 will use an index, and in statement 4 the index will be ignored.
C. Statements 1, 3 & 4 will use an index, and in statement 2 the index will be ignored.
D. None of the SQL statements will use an index.
E. Statements 1 & 3 will use an index, and in statement 2 & 4 the index will be ignored.
F.Only statement 1 will use an index, and in statement 2, 3 & 4 the index will be ignored.
Answer: E
Explanation:
Statements 1 & 3 will use an index, and in statement 2 & 4 the index will be ignored.
A:In statement 2 & 4 the index will be ignored.
B:In statement 2 the index will be ignored.
C:In statement 4 the index will be ignored.
D:Statements 1 & 3 will use an index.
F:Statements 1 & 3 will use an index.
Q. What is the main reason for a row overflow area when creating index-organized tables?
A. To avoid row chaining and migration.
B. To speed up full table scans and fast full index scans.
C. To improve performance when the index-organized table is clustered.
D. To keep the B-Tree structure densely clustered to allow more rows per leaf block.
Answer: D
Explanation:
The main reason to use a row overflow for IOT is the need to keep the B-Tree structure densely clustered to allow more rows per leaf block. During IOT table creation you can specify the OVERFLOW tablespace where the second half of the row data will be stored when the row’s length exceeds the size set aside in PCTTHRESHOLD.
A:The row overflow will not help you to avoid row chaining because it’s used exactly to
keep chained rows for better balance of the B-Tree structure.
B:It will not speed up full table scans and fast full index scans because it will be
required additional time to read data from the overflow area.
C:The row overflow area will not improve an overall performance of IOT.
Continue.....