Oracle Database pen questions (with answers)

1. Why check points must occur when the database switches logs? What is the significance of this checkpoint? A: The execution of dbwr is triggered. dbwr writes all dirty queues related to this log to the data file to shorten the time required for instance recovery. 2. What are the advantages and disadvantages of table space management. A: dictionary management and local management

1. Why check points must occur when the database switches logs? What is the significance of this checkpoint? A: The execution of dbwr is triggered. dbwr writes all dirty queues related to this log to the data file to shorten the time required for instance recovery. 2. What are the advantages and disadvantages of table space management. A: dictionary management and local management

1. Why check points must occur when the database switches logs? What is the significance of this checkpoint?
A: The execution of dbwr is triggered. dbwr writes all dirty queues related to this log to the data file to shorten the time required for instance recovery.

2. What are the advantages and disadvantages of table space management.
A: dictionary management and local management. Local Management uses bitmap management extent to reduce competition between dictionaries and avoid fragmentation.

The local tablespace management has the following advantages over the dictionary tablespace management:
1). Reduced recursive space management;
2) the system automatically manages the extents size or uses the unified extents size;
3). Reduced competition between data dictionaries;
4). No rollback information is generated;
5). the adjacent remaining space does not need to be merged;
6). Space fragments are reduced;
7). provides better management of temporary tablespace.

3. Differences and applicability between local indexes and global indexes.
A: For a local index, each table partition corresponds to an index partition. When the table partition changes, the index maintenance is automatically performed by Oracle. For a global index, you can select whether to partition, and the index partition does not correspond to the table partition. During partition maintenance, the global index is usually INVALDED and must be rebuilt after the operation is completed. Oracle9i provides the update global indexes statement, which allows you to re-create a GLOBAL index while maintaining partitions.

4. a table a varchar2 (1), B number (1), c char (2) has 100000 records, and B-Tree indexes are created on field, WHO is the largest table and index? Why?
A: considering the number of bytes occupied by rowid, assume that char always occupies 2 bytes and compare rowid. In addition, table and index are managed differently in segment free block.

5. There are several data guard modes for Oracle9i. What are their differences.
Answer: There are three modes:
Maximize performance: This is the default protection mode of data guard. You do not need to receive feedback from standby before the transaction commit on primay. This mode may cause data loss when the primary fails, but standby has the least impact on the performance of the primary.
Maximize availability: under normal circumstances, the maximum available mode is the same as the maximum protection mode. When standby is unavailable, the maximum available mode is the maximum automatic performance mode, therefore, the standby fault will not cause primay to be unavailable. If there is at least one standby available, even if the primarydown machine does not lose data.
Maximize protection: the highest protection mode. Transactions on primay must confirm that the redo has been passed to at least one standby before commit. If all standby is unavailable, primary will be suspended. This mode ensures zero data loss.

6. What is an execution plan and how to view it.
A: The Execution Plan is the internal execution step of the database:
Set autotrace on
Select * from table
Alter session set event '10046 trace name context forever, level 12 ';
SYS. DBMS_SYSTEM.SET_ SQL _TRACE_IN_SESSION = (SID, SERIAL #, TRUE );
Pl/SQL developer is generally used, and an explain plan, v $ SQL _plan is added.

7. Briefly describe the difference between nest loop and hash join.
A: nest loop applies when the returned results are small.
For in 1... n loop
Traverse small tables
Traverse a large table based on the results of a small table (index required for a large table)
End loop
Hash join is applicable when a large result set is returned.

8. What is the difference between db file sequential read and db file scattered read?
A: db file sequential read-DB files are read sequentially. This event usually displays read operations (such as index reading) related to a single data block ). If this wait event is significant, it may indicate that there is a problem with the table connection sequence in the Multi-Table connection, and the driver table may not be properly used; or it may indicate that the table is indexed without selection. In most cases, we say that records can be obtained more quickly through indexes. Therefore, it is normal to wait for a database with good coding specifications and adjustments. However, in many cases, using indexes is not the best choice. For example, if you read a large amount of data from a large table, full table scanning may be significantly faster than index scanning, therefore, during development, we should note that such queries should be avoided using index scanning.
Db file sequential read-DB sort distributed reading, which usually shows waiting related to full table scan. When the database performs a full table scan, the data will be distributed (scattered) into the Buffer Cache based on performance considerations. If the wait event is significant, it may indicate that no index is created or a proper index is not created for some tables scanned by the full table, we may need to check whether these data tables are correctly set. However, this wait event does not necessarily mean low performance. In some conditions, Oracle will take the initiative to use full table scanning to replace index scanning to improve performance, which is related to the amount of data accessed, in CBO, Oracle performs more intelligent selection. In RBO, Oracle prefers to use indexes. When the wait event is significant, you can use the v $ session_longops dynamic performance view to diagnose the event. This view records the tasks that run for a long time (more than 6 seconds, most of the operations may be full table scan (in any case, this part of information is worth our attention ).

9. What are the waiting events of the library cache pin and library cache lock?
A: "library cache pin" usually occurs when objects such as PL/SQL, VIEW, and TYPES are compiled or re-compiled.

10. in a 24*7 Application, you need to change the common index (a, B) of a table with a large access volume of more than 10 million of the data level to a unique constraint (a, B, c). You generally choose how to do this. Please explain the specific operation steps and statements.
A: it cannot be determined whether the following constraints can be applied after the index is created:
Create index idx_w1 on w_1 (a, B, c) online;
Alter table w_1 add constraint uni_w1 unique (a, B, c) novalidate;

11. If an oracle database system on linux suddenly slows down, you can find the cause from where.
A: 1) run the vmstat and iostat commands to check the system load (whether I/O reads and writes are serious and whether the CPU is idle ).
2) use the top tool to check which processes have high CPU usage;
3) query the data dictionaries of v $ session_wait and v $ system_event to find the serious wait events and try to optimize the SQL statements that affect the performance.

12. Describe your understanding of RAID 5 and raid 01/10.
Answer: raid5 uses verification information, and the hard disk utilization is n-1/n. In the example of RAID5 (4D + P), four disks are used to store data bits and one disk is used to store the verification bits. The basic principle is as follows: one-digit verification information is generated based on the band-based data 4D (using four-digit data) and stored in the fifth disk.
Raid10 is the most efficient hard disk exploitation method by using first-image striping. The hard disk utilization rate is 50%, which is a combination of Raid 0 and Raid1, it uses the parity check to implement the striped set image, so it inherits the fast speed of RAID 0 and the security of RAID 1.

13. List 5 new 10 Gb features.
A:
1). Automatic shared memory (SGA) Management
2). Automatic Storage Management (ASM)
3). ADDM and Query Optimizer
4). flashback table)
5). Data Pump (expdp, impdp)