DBA classic interview questions (1)

 
2: How to view SQL Execution plans without using third-party tools
Set autotrace on
 
If the following error occurs:
SP2-0613: unable to verify PLAN_TABLE format or entity
SP2-0611: An error occurred while enabling EXPLAIN report
Run oraclehome \ ora92 \ rdbms \ admin \ utlxplan. SQL
 
Explain plan set statement_id = & item_id for & SQL;
Select * from table (dbms_xplan.display );
 
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 and then selects the optimal level (minimum) to run the query.
CBO tries to find the method to access data at the lowest cost, calculate the cost of using different execution plans for the maximum throughput or the fastest initial response time, and select the one with the lowest cost, statistics on 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/partitional 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?
The index exists to improve the query performance. If the index does not improve the query performance, it can only be said that the index is incorrect, or it may be different.
 
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 the SQL statement that directly writes query conditions. Such SQL statements need to be parsed repeatedly under different conditions, to bind a variable is to use a variable to replace the direct writing condition, query the bind value and pass it at runtime, and then bind it 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
 
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.
If order by/group by/distinct/union/create index/index rebuild/minus operations 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 control data blocks retain the space for update. When the free space in the data block is less than the space set by pctfree, this data block is removed from freelist. When the free space in the dml operation is greater than the space set by pct_used, this database block is 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, and each extent allocates a certain number of blocks according to the storage parameters of the 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 the table is distributed in each datafile in tablespace through the hash algorithm. tablespace is a logical concept, datafile stores various database objects physically.
 
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 that can quickly and effectively manage storage extensions and remaining blocks (free blocks ), therefore, 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 transaction modifies the data in the table, the value before the data modification (that is, the previous image) will be stored in the ROLLBACK segment. When the user rolls back the transaction (ROLLBACK) ORACLE will use 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 the database is opened.
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 a SELECT statement, ORACLE ensures that any uncommitted changes earlier than the current SCN are not processed by the current system change NUMBER-SCN. It can be imagined that when a long query is being executed, if other sessions change the data block to be queried for this query, ORACLE uses the data pre-image of the rollback segment to construct a read consistency view.
 
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.
 
7: What are the main functions of SGA?
 
SGA: db buffer cache/redo log buffer/shared pool/large pool/java pool
Db_cache:
The database cache (Block Buffer) plays a key role in the operation and performance of Oracle databases. It occupies the main part of the Oracle database SGA (System Shared Memory zone. The Oracle database uses the LRU algorithm to store recently accessed data blocks in the cache to optimize disk data access.
 
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 MTS is used for configuration, because UGA needs to be allocated in SGA to maintain user sessions, Large_pool is used to maintain the session memory. When RMAN is used for backup, the memory structure Large_pool is used as the disk I/O cache.
 
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;