Tidying up some DBA's face questions [go]

One: 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 plan without 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 the difference between CBO,CBO and rule
In Optimizer_mode=choose, if the table has statistics (outside the partitioned table), the optimizer chooses the CBO, otherwise it chooses Rbo.

Rbo follows a simple grading methodology, using 15 levels of points, when a query is received, the optimizer evaluates the number of points used,
Then select the optimal level (minimum quantity) of the execution path to run the query.
The CBO tries to find the lowest-cost method of accessing the data, in order to maximize throughput or the fastest initial response time, the calculation uses a different
The cost of the execution plan and select one of the lowest cost, the statistics about the data content of the table are used to determine the execution plan.

4: How to locate important (resource-intensive) SQL
Select Sql_text
From V$sql
where disk_reads > or (Executions > 0 and buffer_gets/executions > 30000);

5: How to track SQL for 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, ");

What is the most concerned about 6:sql adjustment
View the SQL response time (db block gets/consistent gets/physical reads/sorts (disk))

7: Talk about your understanding of indexes (structure of indexes, impact on DML, why query performance is improved)
B-tree index/bitmap index/function index/patitional Index (Local/global)
Indexes often improve the performance of select/update/delete, reducing the speed of inserts,

8: Does using index queries improve the performance of queries? Why
Indexes exist to improve query performance.
If the index does not improve performance in the query,
Can only say the wrong index, or the occasion is different

9: What is a binding variable? What are the pros and cons of binding variables?
The binding variable is relative to the text variable, so-called text variable refers to the SQL directly write query conditions,
This kind of SQL needs to be resolved repeatedly under different conditions, the binding variable refers to the use of variables instead of direct writing conditions,
Query bind value is passed at run time, and then the binding executes.

The advantage is to reduce hard parsing, reduce CPU contention, and save Shared_pool
Disadvantage is that it is difficult to use Histogram,sql optimization

10: How to Stabilize (fix) the 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 posting:
http://www.cnoug.org/viewthread.php?tid=27598

11: How to adjust the memory of the sort related to the 8i and 9i, what is the function of temporary table space
Sort_area_size/sort_area_retained_size in 8i determines the amount of memory required for sorting
If the sort operation cannot be completed in Sort_area_size, the temp table space is used

If the Workarea_size_policy=auto is in 9i,
The sort is performed within the PGA, typically 1/20 of the pga_aggregate_target can be used for disk sort;
If workarea_size_policy=manual, the required memory for sorting is determined by sort_area_size

When performing operations such as order By/group by/distinct/union/create Index/index Rebuild/minus,
If the PGA or Sort_area_size cannot be completed, the sort will be done in the temporary tablespace (disk sort),
The primary function of the temporal tablespace is to complete the disk sort in the system.

12: Table T exists (a,b,c,d), to be sorted according to the field C, take 第21-30条 record display, please give SQL
CREATE TABLE T (a number (8), b number (8), c number (8), D number (8));
/
Begin
For I in 1.. 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 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 < ORDER BY 3 DESC
Poor performance compared to the minus

Second: Basic concept of the database class

1:pctused and pctfree mean what does that mean?
Whether the pctused and Pctfree control data blocks appear in the Freelist,
Pctfree the space in the control data block for update, when the free space in the data block is less than the space set by Pctfree,
The block is removed from the freelist, and when the block is free space is larger than the pct_used setting due to DML operation, the database block will
is added to the freelist linked list.

2: A brief description of the relationship between Table/segment/extent/block
When table is created, a data segment is created by default.
Each data segment contains the number of extents specified by min extents,
Each extent allocates a certain number of blocks for the storage parameters of the data table space

3: Describe the relationship between Tablespace and datafile
A tablespace can have one or more datafile, each datafile only within one tablespace,
The data in table is distributed by hash algorithm in each datafile of tablespace,
Tablespace is a logical concept, and datafile physically stores objects of the database.

4: Local Management table space and dictionary management table space features, what are the characteristics of ASSM
Local Management table space (locally Managed tablespace abbreviated LMT)
A new management mode for tablespaces that appears after 8i, using bitmaps to manage space usage of tablespaces.
Dictionary Management table Space (dictionary-managed tablespace called DMT)
8i previously included a table-space management model that could be used later, using a data dictionary to manage the spatial use of tablespaces.
Dynamic segment Space Management (ASSM),
It first appeared in the Oracle920 with the ASSM, the link list freelist replaced by the bitmap, which is a binary array,
The ability to quickly and efficiently manage storage extensions and remaining chunks (free block), thereby improving the nature of segmented storage,
The segment created on the ASSM table space also has another salutation called Bitmap Managed segments (BMB segment).

5: What is the role of the rollback segment
Transaction rollback: When the transaction modifies the data in the table, the value before the data modification (that is, the former image) is stored in the rollback segment.
When a user rolls back a transaction (ROLLBACK), Oracle uses the pre-data image in the rollback segment to restore the modified data to its original value.
　
Transaction recovery: When the transaction is being processed, the routine fails and the rollback segment information is saved in the Undo table space.
Oracle will use rollback to recover uncommitted data the next time the database is opened.

Read consistency: When a session is modifying data, other sessions will not see changes that were not committed by that session.
When a statement is executing, the statement will not see uncommitted modifications from the beginning of the statement (statement-level read consistency)
When Oracle executes a SELECT statement, Oracle is based on the current system change number (NUMBER-SCN)
To ensure that any uncommitted changes prior to the current SCN are not handled by the statement. As you can imagine: When a long query is being executed,
If another session changes a block of data that the query is querying, Oracle constructs a read-consistent view using the pre-data 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 function of the log
Record database transactions to maximize data consistency and security

Redo log files: Contains a record of the changes made to the database so that if a failure can enable data recovery, a database requires at least two redo log files
Archived log files: An offline copy of the redo log files that may be necessary to recover from media failure.

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

7:sga mainly have those parts, the main role is what
Sga:db_cache/shared_pool/large_pool/java_pool
Db_cache:
The database cache (Block buffer) plays a critical role in the operation and performance of the Oracle database.
It occupies the main part of the Oracle database SGA (System shared memory area). Oracle database by using LRU
Algorithm to optimize access to disk data by storing recently accessed chunks in the cache.
Shared_pool:
The size of the shared pool is important for Oracle performance.
Saving data dictionary cache and fully parsed or compiled PL/SQL statements and control structures in a shared pool
Large_pool:
When using MTS configuration, because you want to allocate UGA in the SGA to keep the user's session, you use Large_pool to keep the session memory
When using Rman for backup, use the Large_pool memory structure to do the disk I/O buffers
Java_pool:
Memory area prepared for Java procedure, not required if Java Proc,java_pool is not used

What are the main 8:oracle system processes and what are the functions?
Data write 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): Check database consistency If necessary also initiates recovery of the database when the database is opened
Process monitoring (Pmon): Responsible for cleaning up resources when an Oracle process fails
Checkpoint process (CHPT): Updates the database state information in the control files and data files whenever changes in the buffer cache are permanently recorded in the database.
Archive process (ARCN): Backs up or archives a full log group at each log switch
Job Scheduler (CJQ): Responsible for scheduling and executing a defined job in the system to complete some predefined tasks.
Recovery process (RECO): To ensure the consistency of distributed transactions, in the distributed transaction, either commit at the same time, or simultaneously rollback;

Three: Backup Recovery class

1: How backups are categorized
Logical Backup: Exp/imp
Physical Backup:
Rman Backup
Full Backup/incremental Backup (cumulative/differential)
Hot backup: Alter tablespace begin/end backup;
Cold backup: Offline backup (Database shutdown)

2: What does archiving mean
About archive logs: When Oracle is archiving a filled online log filegroup, an archive log (archived redo log) is created.
It has the following uses for database backup and recovery:
<1> database fallback as well as online and archived log files to ensure that all committed things can be restored in the operating system and disk failures.
<2> under Database open and normal system use, if archive log is saved permanently, online fallback can be made and used.
The database can be run in two different ways:
Noarchivelog Way or Archivelog way
When the database is used in Noarchivelog mode, the online log cannot be archived.
If the database is running in Archivelog mode, an archive of online logs can be implemented.

3: If a table is 10:30:00 in 2004-08-04, how can it be recovered in the case of a well-documented and backed-up?
Manually copy back all backed-up data files
Sql>startup Mount;
Sql>alter database recover automatic until time ' 2004-08-04:10:30:00 ';
Sql>alter database open resetlogs;

What is the

4:rman and what are the characteristics?
Rman (Recovery Manager) is an important tool for DBAs to back up, restore, and restore Oracle databases, and
Rman can be used to back up and recover database files, archive logs, control files, system parameter files, can also be used to perform a full or incomplete database recovery.
Rman has three different user interfaces:
COMMAND line, GUI (integrated with the backup manager in the OEM), API mode (for integration into third-party backup software). The
has the following characteristics:
1) function similar to physical backup, but more powerful than physical backup n times,
2) can compress empty blocks,
3) can be implemented at block level,
4) can be the output of the backup package into a backup set, you can also split the backup set by a fixed size;
5) The process of backup and recovery can be automatically managed;
6) can use script (exists in recovery catalog)
7) can do bad block monitoring

Features of 5:standby
Standby (Standby database): Oracle's High Availability (AVAILABLE) database solution
Synchronization of the data between the primary and standby nodes is ensured, and the standby node is the backup of the primary node.
Enables fast switching and catastrophic recovery, starting with 920 and starting to support both physical and logical standby servers.
The three types of data protection modes in 9i are:
1), Maximize PROTECTION: Maximum data protection and no data divergence, LGWR will be transmitted to the standby node simultaneously,
The standby node must also fully receive the log data before the primary node transaction acknowledgement. If the network is not good, cause the LGWR can not transmit data, will cause serious performance problems, leading to the main node down machine.
2), maximize availability: No data loss mode, allow data divergence, allow asynchronous transmission.
Normally runs in maximum protection mode, automatically switches to maximum performance mode when the network of the primary and standby nodes is disconnected or is not connected properly.
The operation of the master node can still be continued. In the case of poor network has a large performance impact.
3), maximize performance: This mode should be said to be inherited from the 8i standby server mode, asynchronous transfer,
Countless data synchronization checks may be lost, but the maximum performance of the master node can be obtained. 9i When configuring data guard, the default is maximize performance

6: How do you design a backup strategy for a system that requires less recovery time (database 50G, archive 5G per day)
rman/Monthly Level 0 per weekend/Wednesday Level 1 other daily level 2

Four: System Management class

1: For a system that has system performance, say your diagnostic approach
1 Do statspack collection system related information
Understand the system in general/determine if there is an inappropriate parameter setting/view Top 5 event/View top SQL, etc.
2 Check V$system_event/v$session_event/v$session_wait
Starting with v$system_event, determine what resources are needed (DB file sequential read), etc.
In-depth study of v$session_event to determine the sessions involved in waiting events
Determine detailed resource contention from v$session_wait (value of P1-P3: File_id/block_id/blocks, etc.)
3 using V$sql/v$sqltext/v$sqlarea table to determine Disk_reads, (buffer_gets/executions) Large SQL

2: List Several methods for diagnosing IO, CPU, performance status
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 larger SQL)

3: What do you know about Statspack?
Stapspack is a software package provided by Oracle to collect database performance metrics from 8i, with significant enhancements in 9i and 10g
The software package's auxiliary table (the table that stores the relevant parameters and the collected performance indicators) grew from 25 to 43
The collection level parameters are increased from the original 3 (0, 5, 10) to 5 (0, 5, 6, 7, 10)
By analyzing the collected performance metrics, the database administrator can understand the current operation of the database in detail, and optimize the database instance, wait events, SQL, etc.
Using the snapshot collected by statspack, statistical trend graphs of various performance indexes of the database are compiled.

4: If the system now needs to create an index on a very large table, you will consider those factors, how to do to minimize the impact on the application
When the system is relatively idle
Nologging option (nologging is not available if Dataguard is available)
Large sort_ared_size or Pga_aggregate_target larger

5: What do you know about raid1+0 and RAID5?
Unlike raid 0+1, RAID 10 (or RAID 1+0) is a RAID 1 array that consists of a hard disk drive and then a RAID 0 array between RAID 1 arrays.
The RAID 10 mode has the same good data transfer performance as the raid 0+1 mode, but is more reliable than the raid 0+1. The actual capacity of the RAID 10 array is MXN/2,
Disk utilization is 50%. RAID 10 also requires a minimum of 4 hard drive components, and is therefore expensive.
RAID 10 is as reliable as RAID 1, but because of the data partitioning between RAID 10 hard drives, it is a good performance.

RAID 5 is similar to RAID 3, except that the parity information for RAID 5 is also split into all hard drives as data.
Instead of writing to a specified hard drive, the bottleneck of a single parity hard drive is eliminated. RAID 5 disk array has improved performance over RAID 3.
But you still need at least 3 hard drives. Its actual capacity is MX (n-1) and the disk utilization is (n-1)/n.

Five: Comprehensive Random class

1: What part of Oracle are you best at?
PL/SQL optimization

2: Do you like Oracle? Do you like the forum? Or what part of Oracle do you prefer?
Like, the optimization of SQL

3: Feel free to say what you think is the most interesting part of Oracle or the hardest part.
Processing of latch FREE

Six. Interview topics

(1) When the database switches the log, why must checkpoints occur? What is the point of this checkpoint?
Triggering DBWR execution, DBWR writes all dirty queues associated with this log to the data file, reducing the recovery time when the database crashes.
==> you are just ordinary checkpoint to do, and not specifically the switch log when the role of checkpoint.
Switch Log checkpoint is most important to make the current redo log state ==> active ==> Inactive for reuse

(2) Table space management methods have what kinds, each has what merits.
Dictionary management mode and automatic management mode, automatic management mode using bitmap management extent, reduce the competition between dictionaries, while avoiding fragmentation
==>ok.

(3) Difference and applicability of local index and global index.
Local indexes apply to SQL statements to qualify a range of queries such as time, the global index applies to queries in all records, such as querying a mobile phone number.
Global indexes can always be unused and need to be rebuilt
==>
The local index is appropriate for the condition that contains partition key, and of course not absolute
Global indexes can always be unused, and I usually ask what should I do?
9i with UPDATE global index clause

(4) A Table a VARCHAR2 (1), b number (1), C char (2), there are 100,000 records, create B-tree index on field A, then the table and index who big? Why?
This takes into account the number of bytes that the rwoid occupies, assuming that char always occupies 2 bytes, comparing ROWID and 3
==> also, table and index are managed differently in segment free block.

(5) The 9i data guard has several modes, each of which is different.
Three different modes
Maxmize Performance with asynchronous transfer
Maxmize availablity allows asynchronous transfer to swing between the two
==> is not called rocking, normal situation maxmize availablity transmission is equivalent to maxmize protection, just allow crash to continue working when from the library primary
Maxmize Protection with synchronous transfer
==> ensure standby is consistent with primary absolute data
Personally think the use of maxmize performance better, the impact on the primary database is relatively small

(6) What is the execution plan, and how do you see the execution plan in general?
Execution plan is the execution step inside the database
Set Autotrace on
SELECT * FROM table
Alter session set event ' 10046 Trace name context forever,level 12 '
General use of PL/SQL developer, other less use, can't remember
==> the same, add a explain plan, V$sql_plan
(7) Briefly describe the difference between Nest Loop and hash join.
The nest loop is suitable for cases where the results are relatively small.
For in 1...N Loop
Iterating over a small table
Traversing large tables based on the results of a small table (large tables need to be indexed)
End Loop
This is a good explanation for the efficient design of the database, but it can't be written.
==> A small table called a driven result set is more appropriate

Hash join applies when returning a large result set
==> may not necessarily be a big result set

(8) DB file sequential read and DB file scattered read wait for the difference, if the above wait more, prove what problem?
DB file sequential read refers to a block that needs one but is not currently in the SGA, waiting to be read from disk. DB file scattered read requires multiple successive databases to cause a wait.
DB file sequential read there is a lot of waiting, maybe not a problem. If the two events wait more, the SQL statements are checked against P1,P2,P3 and Sid, and there is a possibility of tuning
==>db file scattered read basic can be qualitative as fts/ifs
L (9) Ibrary cache pin and library cache lock where is the wait event, general description of what the problem?
Generally occurs when Package,procedure is compiled, add contraint.
==>, which means too much DDL.

(10) In a 24*7 application, you need to have a large number of access to 10 million of the data level of the table's Ordinary index (a, B) to modify the unique constraint (A,B,C), you will generally choose how to do, please say the specific steps and statements.
Not sure whether the constraint can be established after first building the index
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, be sure to consider non-peak hours

(11) If an Oracle database system on Linux suddenly slows down, where do you usually find the reason?
Top to see what processes are going on and see what these processes are doing
Look at v$session_wait.
==>
Almost, it's better to add Vmstat, Iostat.

(12) Explain the understanding of RAID5 and RAID01/10.
RAID5 the use of calibration information, hard disk utilization n-1/n, RAID10 first mirror in the strip, is the most efficient way to use hard disk, the utilization of hard disk 50%
==> usually mention that redo log is not in RAID5. There are 01/10 differences and pros and cons.

(13) Listing 5 new features of 10g
1.rename tablespace
2. The New EM
3.asm
4.bigfile tablespace
5. True incremental Rman
6.flashback table
7.EXPDP, IMPDP

(1) When the database switches the log, why must checkpoints occur? What is the point of this checkpoint?
Database switch log when, because he wants to notify DBWR, write dirty data, ckpt,db_file, redo SCN number coordination

This issue was discussed earlier, primarily to understand why log groups may also be active after log switching

(2) Table space management methods have what kinds, each has what merits.
The management of tablespaces includes both data dictionaries and local table space management methods.
Local surface space management: Starting from oracle9r, basically the default is the management of the native table space,
This approach is directly used by the bit (1 and 0) mode to manage. So the space shrinks fast.
For example, we truncate or drop off a very large table, very fast
Data dictionary table Space management: As the name implies, Oracle is managed with a table. This can result in overhead on the table management.
So, in Oracle 8i, we drop a very large table that may take 3 hours or longer.

Differences and applicability of local and global indexes.
I create indexes that are generally local indexes.
The global index has not been used

(3) A Table a VARCHAR2 (1), b number (1), C char (2), there are 100,000 records, create B-tree index on field A, then the table and index who big? Why?
On 100000 of the data, this table alone will not create an index on field a. Because a VARCHAR2 (1) may have a low performance after creating an index.
As we all know, the index is ultimately used ROWID, if you use an index to query large amounts of data, it is not as good as full table scan, performance is very low.
If you do not know the data distribution of a, how do you decide that the index performance of A is not good? If the value of a is unique, and a large number of queries use the SELECT * from T where a=, do you also think that the index performance on a is lower than the full table scan?

(4) The 9i data guard has several modes, each of which is different.

Specifically can't say, need to query the document, but I do 9i data guard I am the main library through the archive log to restore the Datagard database,
Data only one log file differences, if the network from the library problems, or the standby library problems, the main library will be error. At the same time constantly to
Prepare a library to write archives. Until it is written.
If it's actually configured, there's no reason why several modes don't know

(5) What is the execution plan, and how do you see the execution plan in general?
The execution plan is how much parse,execute,fetch is used by Oracle when executing a statement, how many disk read/write are used, local read/write, and so on
Execution planning is an important tool for database optimization.

Are you talking about statistics, or are you talking about the execution plan?

There are several ways to do this:
1. Explain for
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, from a table, B table using the Oracle hashing algorithm to connect
The key point is that the hash join is only appropriate for the equals join condition. Moreover, the hash algorithm is connected, specifically how to do? Do two tables have to be hashed? Oh

(7) DB file sequential read and DB file scattered read wait for the difference, if the above wait more, prove what problem?

DB file sequential read (data file sequential reading). In a multiple-table connection, there is a problem with the table's connection order,
There is no correct driver table to read data from a data block
May be using an index scan that should not be used

DB file scattered read data file discrete reads from multiple blocks of data

If the above wait is more, there may be a large-scale full-table scan, fast index Scan, full index scan. You need to add an appropriate index to eliminate the wait event.

(8) What is the waiting event for the library cache pin and the library cache lock, and what is the general description of the problem?
The library cache pin and library cache lock are waiting events in the share pool, which generally indicate that Oracle latch waits,
Because more than one process is robbing data field latch, can't get pin, so optimize according to specific application needs
That's basically not the point. The two latch are organized primarily as object in the library cache, first a handle that is equivalent to the header information, and then point to the specific heap,library cache of the object. Lock is added to the handle, the library cache pin is added to the heap, and the handle must be locked before obtaining a pin. These two wait more, generally due to DDL, such as the Recompile object, Grant/revoke permissions, etc., this pin is not a pin in the CPU after latch failure, this pin is latch, is a noun

(9) In a 24*7 application, you need to have a large number of access to 10 million of the data level of the table's Ordinary index (a, B) to modify the unique constraint (A,B,C), you will generally choose how to do, please say the specific steps and statements.
First create Unqiue idx_1 on table (A,B,C)
Normal index of DROP table (A, B)
In doing so, your application is estimated to be closure, and index does not know how long it will take to build it.

(10) If an Oracle database system on Linux suddenly slows down, where do you usually find the reason?
(1) Top, find the corresponding session, follow the session, combined with Autotrace, Sql_trace, Dbms_system.set_sql_trace_in_session (sid,serial#,true) and other application optimization tools
(2) v$session_wait, find the waiting event
(3) V$sql,v$sqlarea find poor performance of SQL, etc.
(4), for Statspack, analysis

No mention of alert

(11) Explain the understanding of RAID5 and RAID01/10.
RAID5 such as 5 disk, 4 disk for the data disk, a disk as redundant disk.

You're talking about the RAID3,RAID5. The check digit is evenly distributed across the disks, there is no separate check disk

(12) Listing 5 new features of 10g

Asm
Ash
Awr
Rman Enhanced
Flash back function
Ash/awr basically can calculate a, flashback function 9i also have, accurate say should be flashback database. In fact, a lot of new features of 10g, such as RecycleBin, the physical reserve Read/write,data guard Fast-start Failover,em Dbcontrol/grid control ...

Tidying up some DBA's face questions [go]