Sort out dba interview questions

I. SQL tuning class
1: List several table Connection Methods
Hash join/merge join/nest loop (cluster join)/index join

2: How to view SQL Execution plans without using third-party tools
Set autot on

Explain plan set statement_id = & item_id for & SQL;
Select * from table (dbms_xplan.display );
Http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/ex_plan.htm

3: How to Use CBO? What is the difference between CBO and RULE?
When optimizer_mode = choose, if the table has statistical information (outside the Partition Table), the optimizer selects CBO. Otherwise, select RBO.

RBO follows a simple hierarchical methodology and uses 15 key points. When a query is received, the optimizer evaluates the number of key points used,
Then select the execution path of the optimal (minimum) level to run the query.
CBO tries to find the method to access data at the lowest cost. For the maximum throughput or the fastest initial response time, different
And select the lowest cost. Statistics on the table data content are used to determine the execution plan.

4: how to locate important (resource-consuming) SQL statements
Select SQL _text
From v $ SQL
Where disk_reads> 1000 or (executions> 0 and buffer_gets/executions> 30000 );

5: how to track the SQL of a session
Exec dbms_system.set_ SQL _trace_in_session (sid, serial #, & SQL _trace );

Select sid, serial # from v $ session where sid = (select sid from v $ mystat where rownum = 1 );
Exec dbms_system.set_ev (& sid, & serial #, & event_10046, & level_12 ,'');

6: What are the most important aspects of SQL adjustment?
View the response time of the SQL statement (db block gets/consistent gets/physical reads/sorts (disk ))

7: your understanding of indexes (index structure, impact on dml, And why query performance is improved)
B-tree index/bitmap index/function index/patitional index (local/global)
Indexes can usually improve the performance of select, update, and delete operations, which will reduce the insert speed,

8: Can indexed queries improve query performance? Why?
Indexes exist to improve query performance,
If the index does not improve the query performance,
It can only be said that the index is incorrect, or that it is not the case

9: What is variable binding? What are the advantages and disadvantages of variable binding?
Variable binding is relative to text variables. The so-called text variables refer to Directly Writing query conditions in SQL,
Such SQL statements need to be parsed repeatedly under different conditions. Variable binding means that variables are used to replace direct writing conditions,
The query bind value is passed at runtime, and then bound for execution.

The advantage is that hard Parsing is reduced, CPU contention is reduced, and shared_pool is saved.
The disadvantage is that histogram cannot be used, which is difficult for SQL optimization.

10: stable (fixed) Execution Plan
Query_rewrite_enabled = true
Star_transformation_enabled = true
Optimizer_features_enable = 9.2.0

Create and use stored outline
Http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/outlines.htm#26854

This post:
Http://www.cnoug.org/viewthread.php? Tid = 27598

11: how to adjust the 8i and 9i in the sorting relation, and what is the role of the temporary tablespace?
In 8i, sort_area_size/sort_area_retained_size determines the memory required for sorting.
If the sorting operation cannot be completed in sort_area_size, the temp tablespace is used.

In 9i, if workarea_size_policy = auto,
Sort in pga. Generally, 1/20 of pga_aggregate_target can be used for disk sort;
If workarea_size_policy = manual, the memory required for sorting is determined by sort_area_size.

When order by/group by/distinct/union/create index/index rebuild/minus operations are performed,
If it cannot be completed in pga or sort_area_size, the sorting will be performed in the temporary tablespace (disk sort ),
The temporary tablespace is mainly used to complete disk sort in the system.

12: The table T (a, B, c, d) exists. Sort the records according to field c and display the records from to 30.
Create table t (a number (8), B number (8), c number (8), d number (8 ));
/
Begin
For I in 1 .. 300 loop
Insert into t values (mod (I, 2), I/2, dbms_random.value (1,300), I/4 );
End loop;
End;
/
Select * from (select c. *, rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
/
Select * from (select * from test order by c desc) x where rownum <30
Minus
Select * from (select * from test order by c desc) y where rownum <20 order by 3 desc
Relatively poor performance compared with minus

Ii. Basic concepts of databases

1: pctused and pctfree
Pctused and pctfree control whether data blocks appear in freelist,
Pctfree controls the space reserved for update in the data block. When the free space in the data block is smaller than the space set by pctfree,
This data block is removed from freelist. When the free space of a block is greater than the space set by pct_used due to dml operations
Added to the freelist linked list.

2: Briefly describe the relationship between table/segment/extent/block
When a table is created, a data segment is created by default,
Each data segment contains the number of extents specified by min extents,
A certain number of blocks are allocated to the storage parameters of each extent data tablespace.

3: describes the relationship between tablespace and datafile.
One tablespace can have one or more datafiles. Each datafile can only be in one tablespace,
The data in table is distributed in each datafile in tablespace using the hash algorithm,
Tablespace is a logical concept, while datafile physically stores various database objects.

4: Features of local tablespace management and dictionary tablespace management. What are the features of ASSM?
Local Tablespace Management (LMT)
A new tablespace management mode that will appear after 8i. It uses bitmap to manage the space usage of the tablespace.
Dictionary table space (DMT)
A table space management mode that can be used before and after 8i. You can use the data dictionary to manage the space usage of the table space.
Segment space management (ASSM ),
It first appeared in Oracle920 with ASSM. The Link List freelist is replaced by a bitmap. It is a binary array,
It can quickly and effectively manage storage extensions and free blocks, so it can improve the nature of segmented storage,
The segment created on the ASSM tablespace is also called Bitmap Managed Segments (BMB segment ).

5: What is the role of rollback segments?
Transaction rollback: when the data in the transaction table is modified, the value before the data modification (that is, the previous image) will be stored in the rollback segment,
When you roll back a transaction, ORACLE uses the data pre-image in the ROLLBACK segment to restore the modified data to the original value.
　
Transaction recovery: when the transaction is being processed, the routine fails, and the rollback segment information is saved in the undo tablespace,
ORACLE will use rollback to restore uncommitted data the next time it opens the database.

Read consistency: When a session is modifying data, other sessions will not be able to see the modifications not submitted by the session.
When a statement is being executed, it will not be able to see the uncommitted modifications (statement-level read consistency) after the statement is executed)
When ORACLE executes the SELECT statement, ORACLE follows the current system change number (system change NUMBER-SCN)
To ensure that any changes not submitted before the current SCN are not processed by this statement. It can be imagined that when a long query is being executed,
If other sessions change a data block to be queried for this query, ORACLE constructs a read consistency view using the data pre-image of the rollback segment.
Http://www.itpub.net/showthread.php? S = & threadid = 10190 & highlight = % BB % D8 % B9 % F6 % B6 % CE % B5 % C4 % D7 % F7 % D3 % C3

6: What is the role of logs?
Records Database transactions to maximize data consistency and security

Redo log file: includes changes made to the database. In this case, you can enable data recovery in case of a fault. A database requires at least two redo log files.
Archive log file: an offline copy of the redo log file, which may be necessary to recover from a media failure.

Http://www.cnoug.org/viewthread.php? Tid = 17741 & highlight = % C8 % D5 % D6 % BE

7: What are the main functions of SGA?
SGA: db_cache/shared_pool/large_pool/java_pool
Db_cache:
The database cache (Block Buffer) plays a key role in the operation and performance of the Oracle database,
It occupies the main part of the Oracle database SGA (System Shared Memory zone. Oracle Database uses LRU
The algorithm stores recently accessed data blocks in the cache to optimize access to disk data.
Shared_pool:
The size of the Shared Pool is important for Oracle performance.
The Shared Pool stores data dictionary high-speed buffering and fully resolved or compiled PL/SQL blocks and SQL statements and control structures.
Large_pool:
When using MTS configuration, the Large_pool is used to maintain the session memory because UGA must be allocated in the SGA to maintain the user's session.
When using RMAN for backup, use the memory structure Large_pool for disk I/O caches.
Java_pool:
The memory area prepared for java procedure. If java proc is not used, java_pool is not required.

8: What are the main functions and functions of oracle system processes?
Data Writing Process (dbwr): responsible for writing changed data from the database buffer cache to the data file
Log write process (lgwr): writes changes in the redo log buffer to the online redo log file.
System Monitoring (smon): Checks Database Consistency and, if necessary, starts database recovery when the database is opened.
Process Monitoring (pmon): clears resources when an Oracle process fails.
Checkpoint Process (chpt): updates the database status information in control files and data files whenever changes in the buffer cache are permanently recorded in the database.
Archiving process (arcn): backs up or archives a full log group during each log switch.
Job scheduler (cjq): responsible for scheduling and execution of jobs defined in the system to complete some predefined work.
Restore process (reco): to ensure the consistency of distributed transactions. In distributed transactions, either commit or rollback at the same time;

Iii. Backup Recovery

1: how to classify backups
Logical backup: exp/imp
Physical backup:
RMAN backup
Full backup/incremental backup (accumulation/difference)
Hot backup: alter tablespace begin/end backup;
Cold backup: offline backup (database shutdown)

2: What does archive mean?
Archive logs: Archive logs (archived redo logs) must be created when Oracle wants to archive all online log File groups ).
It is useful for database backup and recovery:
<1> database backup and online and archive log files Ensure that all submitted items can be recovered in case of an operating system or disk failure.
<2> online backup can be used if the archived logs are permanently saved when the database is enabled and normally used by the system.
The database can run in two different ways:
NOARCHIVELOG or ARCHIVELOG
When the database is used in NOARCHIVELOG mode, online logs cannot be archived,
If the database runs in ARCHIVELOG mode, you can archive online logs.

3: If a table is dropped at 10:30:00, how can we restore it with comprehensive archiving and backup?
Manually copy back all backup data files
SQL> startup mount;
SQL> alter database recover automatic until time '2017-08-04: 10: 30: 00 ';
SQL> alter database open resetlogs;

4: What is rman and what are its characteristics?
RMAN is an important DBA tool used to back up, restore, and restore oracle databases,
RMAN can be used to back up and restore database files, archive logs, control files, and system parameter files. It can also be used to perform full or incomplete database recovery.
RMAN has three different user interfaces:
Command line, GUI (Backup manager integrated in OEM), and API (used for integration into third-party backup software ).
It has the following features:
1) functions are similar to physical backup, but N times more powerful than physical backup;
2) Empty blocks can be compressed;
3) increment can be achieved at the block level;
4) The backup output can be packaged into a backup set, or the backup set can be split by fixed size;
5) the backup and recovery process can be automatically managed;
6) scripts can be used (in Recovery catalog)
7) Monitoring of Bad blocks

5: Features of standby
Standby database: a high availability database solution launched by ORACLE,
Log synchronization is used between the master and slave nodes to ensure data synchronization. The slave node acts as the backup of the master node.
It enables fast switchover and catastrophic recovery. It also supports physical and logical backup servers starting from 920.
The three data protection modes in 9i are:
1) maximize protection: maximum data PROTECTION and no data difference, LGWR will be transferred to the slave node at the same time,
The slave node must also completely receive log data before the master node confirms the transaction. If the network is poor, LGWR cannot transmit data, which may cause serious performance problems and cause the master node to go DOWN.
2) maximize availability: No data loss mode. Data divergence is allowed and asynchronous transmission is allowed.
Normally, the instance runs in the maximum protection mode. When the network between the master node and the slave node is disconnected or the connection is abnormal, the instance is automatically switched to the maximum performance mode,
The operation on the master node can continue. Poor network performance has a greater impact.
3) maximize performance: This mode can be said to be an alternative server mode inherited from 8i, Which is asynchronously transmitted,
No data synchronization check, data may be lost, but the maximum performance of the master node can be obtained. 9i is maximize performance by default when configuring data guard.

6: How do you design a backup policy for a system with a short recovery time (Database 50 GB, archiving 5 GB every day)
Rman/No. 1 level 0 per month level 1 per weekend/Wednesday level 2 other daily level 2

Iv. System Management

1: For a system with system performance, describe your diagnosis and processing ideas.
1. Perform statspack to collect system information
Measure the test taker's knowledge about the system, determine whether the parameter settings are inappropriate, view top 5 events, and view top SQL.
2. Check v $ system_event/v $ session_event/v $ session_wait.
Determine the required resources (db file sequential read) from v $ system_event.
Study v $ session_event in depth to determine the session involved in the wait event
Determine detailed resource contention from v $ session_wait (p1-p3 value: file_id/block_id/blocks, etc)
3. Use the v $ SQL/v $ sqltext/v $ sqlarea table to determine the SQL statements with large disk_reads and (buffer_gets/executions) values.

2: lists several methods for diagnosing IO, CPU, and performance conditions.
Top/vmstat
Statspack
SQL _trace/tkprof
Check v $ system_event/v $ session_event/v $ session_wait
Check v $ sqlarea (disk_reads or buffer_gets/executions large SQL)

3: What do you know about statspack?
StapSpack is a software package provided by Oracle to collect database running performance indicators. This software package has been significantly enhanced in 9i and 10g since 8i.
The number of auxiliary tables for the software package (tables that store related parameters and collected performance indicators) has increased from 25 to 43.
The number of collection-level parameters increases from 3 (0, 5, 10) to 5 (0, 5, 6, 7, 10)
By analyzing the collected performance indicators, the database administrator can learn in detail the current running status of the database and optimize and adjust database instances, wait events, and SQL statements.
Snapshot collected by statspack can be used to generate statistical trend charts for various performance indicators of the database.

4: If the system needs to create an index on a large table, you will consider those factors and how to minimize the impact on the application.
When the system is idle
Nologging option (nologging cannot be used if there is a consumer uard)
Large sort_ared_size or pga_aggregate_target

5: What do you know about raid1 + 0 and raid5?
RAID 10 (or RAID 1 + 0) is different from RAID 0 + 1. It uses a hard drive to form a RAID 1 array and then a RAID 0 array between RAID 1 arrays.
RAID 10 has the same good data transmission performance as RAID 0 + 1, but has higher reliability than RAID 0 + 1. The actual RAID 10 array capacity is M × n/2,
Disk utilization is 50%. RAID 10 also requires at least four hard drives, which is expensive.
The reliability of RAID 10 is the same as that of RAID 1. However, the RAID 10 hard drive is separated by data, which improves data transmission performance.

RAID 5 is similar to RAID 3. The difference is that RAID 5's parity information is also separated and saved to all hard drives like data,
Instead of writing a specified hard drive, this eliminates the bottleneck of a single parity hard drive. The performance of the RAID 5 disk array is improved compared with that of RAID 3,
However, at least three hard drives are required. The actual capacity is M × (n-1), and the disk utilization is (n-1)/n.

V. Comprehensive and random class

1: Which part of oracle are you best?
Pl/SQL and SQL Optimization

2: Do you like oracle? Do you like forums? Or which part of oracle is preferred?
Like, SQL Optimization

3: Let's talk about the most interesting or difficult part of oracle.
Latch free processing

6. Interview topics

(1) Why check points must occur when the database switches logs? What is the significance of this checkpoint?
Trigger the execution of dbwr. dbwr writes all dirty queues related to this log to the data file, reducing the recovery time when the database crashes.
==> All you have to do is do the common checkpoint, but does not specify the checkpoint function when the Switch log is used.
Switch log checkpoint is mainly used to make the current redo log status from current ==> Active ==> Inactive for reuse.

(2) What are the advantages and disadvantages of table space management.
The dictionary management and automatic management modes use bitmap management extent to reduce competition between dictionaries and avoid fragments.
==> OK.

(3) differences between local indexes and global indexes and their applicability.
Local indexes are applicable to SQL statements that define a range of queries, such as time. Global indexes are applicable to queries in all records, such as querying a mobile phone number.
The global index may be unused and needs to be rebuilt.
==>
If the local index is suitable for conditions that contain the partition key, it is not absolute.
The global index may always be unused. What should I do?
9i contains the update global index clause.

(4) a table a varchar2 (1), B number (1), c char (2), with 100000 records. Create a B-Tree index on field, WHO is the largest table and index? Why?
This should take into account the number of bytes occupied by rwoid, assuming that char always occupies 2 bytes, compare rowid and 3
==> In addition, table and index are also different in segment free block management.

(5) There are several modes of 9i data guard. What are their differences.
Three Modes
Maxmize performance adopts asynchronous transmission
Maxmize availablity allows asynchronous transmission, swinging between the two
==> It is not called swing. Normally, the maxmize availablity transmission method is equivalent to maxmize protection, but the primary is allowed to continue working when the slave database Crash
Maxmize protection adopts synchronous transmission
==> Ensure absolute data consistency between Standby and primary
I personally think that using maxmize has better performance and has less impact on the primary database.

(6) What is the execution plan and how to view the execution plan.
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'
Pl/SQL developer is generally used. Others are rarely used and cannot be remembered.
==> Similar, add an Explain plan, v $ SQL _plan
(7) briefly describe the difference between nest loop and hash join.
Nest loop is applicable 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
This is a good explanation in the efficient database design and cannot be written at the moment.
==> A small table is called a driving result set.

Hash join is applicable when a large result set is returned.
==> Not necessarily a large result set

(8) What is the difference between db file sequential read and db file scattered read?
Db file sequential read refers to a block that needs to be read from the disk but is not in the sga. Db file scattered read requires multiple consecutive databases to wait.
Db file sequential read has a large number of waits, which may not be a problem. If the two events wait a lot, check the SQL statement based on p1, p2, p3, and sid for the possibility of tuning.
==> Db file scattered read can basically be defined as FTS/IFS
L (9) What are the waiting events of ibrary cache pin and library cache lock?
It usually appears when compiling package, procedure, and adding contraint.
==> Similar, meaning there are too many DDL

(10) For 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 (, b, c). You generally choose how to do this. Please explain the specific operation steps and statements.
Uncertain. Can I create a constraint after creating an index first?
Create index idx_w1 on w_1 (a, B, c) online;
Alter table w_1 add constraint uni_w1 unique (a, B, c) novalidate;
==>
In addition, you must consider non-busy hours.

(11) If an oracle database system on linux suddenly slows down, you generally find the cause.
First, let's look at the processes and see what these processes are doing.
Let's see v $ session_wait.
==>
Almost. With vmstat, iostat is better.

(12) give a brief description of RAID 5 and raid 01/10.
Raid5 uses verification information, hard disk utilization n-1/n, raid10 first uses the first image for striping, is the most efficient hard disk utilization, hard disk utilization of 50%
==> We usually mention the difference between redo log and redo log cannot be in RAID 5. There are also 01/10 differences and advantages and disadvantages.

(13) List 5 new 10 Gb features
1. rename tablespace
2. New em
3. asm
4. bigfile tablespace
5. Real incremental rman
6. flashback table
7. expdp, impdp

(1) Why check points must occur when the database switches logs? What is the significance of this checkpoint?
When the database switches logs, it will notify dbwr, write dirty data, ckpt, db_file, and redo to coordinate the scn numbers.

This problem was discussed earlier, mainly to understand why the log group may be active after the log switch.

(2) What are the advantages and disadvantages of table space management.
Table space management includes data dictionary and local table space management.
Local tablespace management: From oracle9r, local tablespace management is used by default,
In this way, bit (1 and 0) is directly used for management. Therefore, space shrinkage is fast.
For example, truncate or drop a very large table, which is very fast.
Data Dictionary tablespace management: oracle uses tables as its name suggests. This will cause overhead on the table management.
Therefore, it may take three hours or longer to drop a very large table in oracle 8i.

Differences between local indexes and global indexes and their applicability.
I usually create local indexes.
No global index used

(3) a table a varchar2 (1), B number (1), c char (2), with 100000 records. Create a B-Tree index on field, WHO is the largest table and index? Why?
On 100000 of the data, I will not create an index for field a in this table. Because a varchar2 (1) may have low performance after creating an index.
We all know that the index uses rowid in the end. If you use an index to query a large amount of data, it is not as good as full table scan, and the performance is very low.
If you do not know the data distribution of a, how do you determine that the index creation performance of a is not good? If the value of a is unique and a large number of queries use select * from t where a =, do you still think the index creation performance on a is lower than the full table scan?

(4) There are several modes of 9i data guard. What are their differences.

But I do 9i data guard. I use the primary database to restore the mongoard database by archiving logs,
There is only one log file difference in data. If a problem occurs on the slave database network or on the slave database, the master database reports an error. At the same time
Backup database write archive. Until it is written.
If you have configured these modes, you have no reason not to know them.

(5) What is the execution plan and how to view the execution plan.
The execution plan is the number of parse, execute, fetch, disk read/write, local read/write, and so on when oracle executes a statement.
Execution plan is an important tool for database optimization.

Are you talking about statistics or execution plans?

There are several methods:
1. explain
2. set autotrace on
Set autotrace traceonly
3. SQL _trace combined with pkprof
V $ SQL _plan

(6) briefly describe the difference between nest loop and hash join.
Nest loop: select * from a where id
Loop
Select id from B
End loop
Hash join:
This is an oracle hash connection. Table a and table B are connected using the oracle hash algorithm.
The key point is that hash join is only applicable to equal join conditions. What's more, how does the hash algorithm connect? Do both tables have hash functions? Haha

(7) What are the differences between db file sequential read and db file scattered read?

Db file sequential read (sequential reading of data files). There is a problem with the table connection sequence during multi-Table connections,
There is no correct driver table to read data from a data block
It may be because an index scan that should not be used is used.

Db file scattered read: discrete data file reading. Data is read from multiple data blocks.

If there are many waits, large-scale full table scans, fast index scans, and full index scans may occur. You need to add an appropriate index to eliminate this wait event.

(8) What are the waiting events of the library cache pin and library cache lock?
The library cache pin and library cache lock are waiting events in the share pool. Generally, the oracle latch waits,
Because multiple processes are grabbing the data field latch, pin is not obtained, so it is optimized according to the specific application needs.
This is basically not mentioned. The two latches mainly refer to the object Organization Method in the library cache. The first is a handle equivalent to the header information, and then points to the specific heap of the object, add the library cache lock to handle, and add the library cache pin to heap. You must first lock the handle before obtaining the pin. These two types of waits are usually caused by DDL, such as re-compiling objects and granting/Revoking permissions. This pin is not the pin in the cpu after the latch fails, this pin is a latch term.

(9) For 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 (, b, c). You generally choose how to do this. Please explain the specific operation steps and statements.
First create unqiue idx_1 on table (a, B, c)
Drop table common index (a, B)
In this way, your application is estimated to have enough memory, and the index does not know how long it will take to build.

(10) If an oracle database system on linux suddenly slows down, you generally find the cause.
(1) top it down, find the corresponding session, track the session, and combine it with application optimization tools such as autotrace, SQL _trace, dbms_system.set_ SQL _trace_in_session (sid, serial #, true)
(2) v $ session_wait to find the waiting event
(3) v $ SQL and v $ sqlarea to find SQL statements with poor performance
(4) perform statspack and Analysis

Alert not mentioned

(11) give a brief description of RAID 5 and raid 01/10.
Raid5 includes five disks, four data disks, and one redundant disk.

You are talking about Raid3. the checkpoint of raid5 is evenly distributed on each disk, and there is no separate disk.

(12) List 5 new 10 Gb features

Asm
Ash
Awr
Rman Enhancement
Flash back function
Ash/awr can basically be regarded as one, and the flash back function 9i is also available. To be precise, it should be flashback database. In fact, there are many new features of 10 Gb, such as RecycleBin, read/write of the physical standby database, Fast-start Failover of data guard, EM dbcontrol/grid control...