Oracle DBA face Test __oracle

ORACLE DBA face Test set
One: SQL tuning class
1 Enumerate several table connection ways
such as connection, non-equal connection, self connection, outer connection (left, right, full)
2 How to view SQL execution plan without the help of a third-party tool
(I) Use explain plan to query plan_table;
EXPLAIN Plan
SET statement_id= ' QUERY1 '
For
SELECT *
From a
WHERE aa=1;
SELECT operation, Options, object_name, object_type, IDs, parent_id
From plan_table
WHERE statement_id = ' QUERY1 '
Order BY ID;

II) The set TRACE in Sqlplus can see execution plan Statistics
SET autotrace on;

3: How to use the difference between CBO,CBO and rule
IF initialization parameter Optimizer_mode = CHOOSE THEN--(8I DEFAULT)
IF You have done table analysis
THEN optimizer OPTIMIZER=CBO (cost); /* High Efficiency *
ELSE
Optimizer optimizer=rbo (rule); /* High Efficiency *
End IF;
End IF;

Difference:
Rbo Select the best execution path according to the rule to run the query.
The CBO determines the execution plan by finding the lowest cost Access data based on table statistics.
Using the CBO requires attention:
(I) The need for regular analyze orders for analysis of statistics;
(II) The need for a stable implementation plan;
III) need to use hints (Hint);

Using rule requires attention:
I) Select the most efficient table name order
(II) Optimizing the writing of SQL;

4 How to locate the SQL that is important (consumes a lot of resources)
Use CPU-Multiple user sessions
SELECT A.sid, spid, status, SUBSTR (A.program, 1,) prog, A.terminal,a.sql_text,
Osuser, value/60/100 VALUE
From V$session A, v$process B, V$sesstat c
WHERE c.statistic# = C.sid = A.sid and a.paddr = b.addr
Order by VALUE DESC;

5 How to track the SQL of a session
Using Trace Tracking
ALTER session SET SQLTrace on;
COLUMN SQL format a200;
SELECT machine, Sql_text SQL
From V$sqltext A, v$session b
WHERE address = sql_address
and machine = ' &a '
Order by Hash_value, piece;
6 What is the most interesting about SQL tuning
Check the system for I/O issues
Sar-d can check the entire system's Iostat (IO statistics)
7 Talk about your knowledge of indexes (structure of indexes, impact on DML, impact on queries, why query performance is improved)
L Index has b-tree, BIT, cluster and other types. Oracle uses a complex, b-tree, self-balanced structure.
L Generally, the query can improve query performance by establishing an appropriate index on the table. However, when inserting, deleting and modifying, the indexes are modified at the same time, which has a certain effect on the performance.
l indexed and query conditions can use the index, the database will first index, according to the index content and query conditions, query out ROWID, and then according to ROWID out the required data. Because the index content is usually much less than the whole table content, so by reading the index first, can reduce I/O, improve query performance.
8 use of index query can improve the performance of the query. Why
L Typically, querying data by index is faster than full-table scans. But we must also pay attention to its cost.
n indexes require space for storage and regular maintenance, whenever a record is added or subtracted from a table or indexed columns are modified,
n the index itself will also be modified. This means that the insert,delete,update of each record will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, the unnecessary indexing slows down query response times.
L using an indexed query does not necessarily improve query performance, and the Index range query (indexed range SCAN) works in two situations:
N based on a range of searches, the general query returns the result set less than 30% of the number of records in the table should be adopted;
N retrieval based on a non-uniqueness index
9 What the binding variable is. What are the advantages and disadvantages of binding variables.
An L-bound variable refers to the use of a variable in an SQL statement, changing the value of a variable to alter the execution of the SQL statement.
L Advantages: Using binding variables can reduce the parsing of SQL statements and reduce the resources that the database engine consumes on SQL statement resolution. Improved programming efficiency and reliability. Reducing the number of accesses to the database can actually reduce Oracle's workload.
L Disadvantage: Often need to use dynamic SQL, because of the different parameters, may be the implementation of SQL different efficiency;
10 How to stabilize (fix) the execution plan
You can specify the execution plan in the SQL statement. Use of hints;
11 and sort related memory in 8i and 9i respectively how to adjust, the role of temporary table space is what
Sort_area_size in the sort operation, if the contents of the ordering is too much, the memory can not be all down, you need to do an external sort,
Temporary table spaces are needed to hold the intermediate results of the sort.
12 exists table T (a,b,c,d), to be sorted according to the field C after the 第21-30条 record display, please give the SQL
SELECT *
From (SELECT rownum as Row_num, tmp_tab.*
From (SELECT A, B, C, D
From T
Order by C) Tmp_tab
WHERE rownum <= 30)
WHERE Row_num >= 20
Order BY Row_num;
Two: Database basic concept Class
1 pctused and pctfree what it means to do something.
Pctused and Pctfree Control whether the block of data appears in the Freelist,
The space in the Pctfree control block is reserved for update, and when free spaces in the data block are less than the space set by the Pctfree,
The block of data is removed from the freelist, and when the block is larger because of the DML operation free space is greater than the pct_used set, the database block
is added to the Freelist list.
2 Simple description of the relationship between Tablespace/segment/extent/block
N tablespace: A database is divided into one or more logical units, the logical unit becomes a tablespace; each tablespace may contain one or more Segment;
n segments:segment refers to the space allocated for a particular logical storage structure in tablespace. Each segment is made up of one or more extent. Includes data segments, index segments, rollback segments, and temporary segments.
N Extents: A extent consists of a series of successive Oracle blocks. Oracle allocates space for segment through extent.
n Data blocks:oracle database smallest I/O storage unit, one data block corresponds to one or more operating system blocks assigned to Data file.
3 Describe the relationship between Tablespace and datafile
A table space can contain one or more data files.
Table space expands the table space by increasing or extending the data file, and the size of the table space is the size of the data file that makes up the table space.
A datafile can only belong to a table space;
4 Local Management table space and the characteristics of the Dictionary management table space, what is the characteristics of ASSM
N Local Admin table space: (9i default)
n the list of idle blocks is stored in the header of the data file in the table space.
N Features: Reduce the competition of data dictionary table, when allocating and shrinking space will produce rollback, do not need to merge
Table space managed by N-Dictionary: (8i Default)
n the list of idle blocks is stored in the dictionary table in the database.
N Feature: Slice is managed by data dictionary, which may cause contention of dictionary table. Each segment stored in the table space will have different storage words, and need to merge adjacent blocks;
5 What is the function of the rollback segment?
The rollback segment is used to hold the image before the data is modified, which is used to generate read-consistent database information, for database recovery, and for rollback. Only one rollback segment can be used for a transaction.
What is the role of the 6th journal?
Log files record all modifications to the database data, primarily to protect the database against failures, and to recover data. Its characteristics are as follows:
A each database contains at least two log file groups. Each log file group contains at least two log file members.
b The log file group writes in a circular fashion.
c Each log file member corresponds to a physical file.
7 SGA mainly has those parts, the main function is what
System global Area (SGA): A set of shared buffer storage areas allocated by Oracle for instance to store database data and control information for the management and operation of database data.
The SGA mainly includes:
U a) Shared pool: Data used to store recently executed SQL statements and recently used data dictionaries.
U b Data Buffer (database buffer cache): Used to store data that was recently read and written from a data file.
U c) Redo log buffers (redo log buffer): Used to record the operation of a service or background process on a database.
In addition, there are two optional memory structures in the SGA:
U d) Java pool: Used to store Java code.
U e) Large pool: Used to store large memory structures that are not directly related to SQL. Backup, restore use.
8 What are the main functions of Oracle system processes
U data write Process (DBWR): Responsible for writing changed data from the database buffer cache to the data file
U log Write Process (LGWR): Write changes in redo log buffers to online redo log files
U system Monitoring (Smon): Checking the consistency of the database if necessary, you will also start the recovery of the database when the database is open
U process Monitoring (Pmon): Responsible for cleaning up resources when an Oracle process fails
U checkpoint Process (CKPT): is responsible for updating database state information in control files and data files whenever changes in the buffer cache are permanently recorded in the database.
U archive Process (ARCH): Back up or archive a full log group for each log switch
U recovery Process (RECO): Ensure the consistency of distributed transactions, in distributed transactions, either commit at the same time, or simultaneously rollback;
U Job Scheduler (CJQ): is responsible for the scheduling and execution of the defined job in the system to complete a number of predefined work.
Three: Backup Recovery class
1 How to Categorize backups
N Physical Backup
U physical Backup is the most important way to backup. Used to ensure that the database is recovered with minimal database loss or no data loss.
N Cold Physics
U Cold Physical Backup provides the simplest and most direct way to protect your database from physical damage. It is recommended to use in the following situations.
U for a database that already has a large maximum amount of data, the database can be closed at night, when cold physical backup is applied.
You will need to upgrade the database server (such as replacing the hard drive), you should back up the database information, and restore the data information on the new hard disk, it is recommended to use a cold physical backup.
N Thermal Physics
U mainly refers to the backup process when the database is open and the user can use it. The cases where you need to perform a hot physical backup are:
You can only use hot-physical backups at this time because the nature of the database requires uninterrupted work.
U because the time required for backup is too long, and the database can only be closed for a short time.
n Logical Backup (EXP/IMP)
The U logical backup is used to restore database objects. But not based on a point-in-time, fully recoverable backup strategy. Only as a supplement to online and offline backups.
N Full Logical backup
A full logical backup is the export of the entire database to a database format file that can be ported between different database versions, operating systems, and hardware platforms.
n Specify logical backups of tables
With the Backup tool, you can back up the specified database tables to avoid the time and financial waste of a full logical backup.
2 What does it mean to archive?
The N archive is the archive of the current online redo log file.
N svrmgr> alter system archive log current;
n A database can be backed up online only if it is running in Archivelog mode and can be archived automatically. A full recovery is possible with an online backup.
3 If a table is 10:30:00 in 2004-08-04, how to restore it in the case of a perfect archive and backup
I) 9i added flash back should be OK;
II) Logminer should be able to find DML.
III) have a sound archiving and backup, archive the current data, and then restore to the deleted point of time before the drop table export, and then back to the final filing time;

However, I have not tried, are the theory of the said!!
4 What is Rman, what is the feature

n Rman can be used to back up and restore database files, archive logs, and control files. It can also be used to perform complete or incomplete database recovery.
N Rman initiates the Oracle server process on the database for backup or restore. Backup, restore, and recovery are driven by these processes.
N Note: Rman cannot be used to back up initialization parameter files and password files.
Has the following characteristics:
n functions like physical backups, but strong n times more powerful than physical backups;
n can compress empty blocks;
n can be incremental on a block level;
n You can package the output of the backup as a backup set, or you can split the backup set by a fixed size;
n the process of backup and recovery can be automatically managed;
n can use script (existing recovery catalog)
N can do bad block monitoring
5 Characteristics of Standby
Standby database: Oracle launches a high-availability (high AVAILABLE) database program, Standby
Synchronizing data between the primary node and the standby node through log synchronization, backup of the standby node as the primary node
Enables fast switching and catastrophic recovery, starting with 920 and supporting physical and logical standby servers.
The three types of data protection modes in 9i are:
N Maximize PROTECTION: Maximum data protection and no data differences, LGWR will be transferred to the standby node at the same time,
U the standby node must also receive full log data before the primary node transaction is confirmed. If the network is not good, causing LGWR can not transmit data, will cause serious performance problems, leading to the main node down machine.
N Maximize availability: No data loss mode, allow data divergence, allow asynchronous delivery.
U normally runs in maximum protection mode, automatically switches to maximum performance mode when the primary node is disconnected from the standby node or is not connected properly
The operation of the U master node can continue. In the case of bad network, there is a large performance impact.
N Maximize performance: This pattern should be said to be inherited from the 8i standby server mode, asynchronous transfer,
U No data synchronization check, may lose data, but can obtain the maximum performance of the master node. 9i When configuring data guard, the default is maximize performance
No practice, just list the answers.
6 How do you design a backup strategy for a system that requires a shorter recovery time (database 50G, daily archive 5G)
The database is relatively large logical backup is not necessary, daily archive 5G, weekly three/Saturday automatic archive 10G, every month Rman archive whole library.
There should be standby.
I lack the opportunity for backup and recovery, JUST PAPER.