DBA classic interview questions (1)

DBA classic interview questions (1) 1: List several table join Methods: hash join, merge join, nest loop join (cluster join), and index join 2: no third-party tools, how to view the SQL Execution Plan set autotrace on if the following error occurs: SP2-0613: unable to verify PLAN_TABLE format or entity SP2-0611: Error executing oraclehome \ ora92 \ rdbms \ admin \ utlxplan when the EXPLAIN report is enabled. SQL explain plan set statement_id = & item_id for & SQL; select * from table (dbms_xplan.display); 3: How to Use CBO, the difference between CBO and RULE is in 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 SQLselect SQL _textfrom v $ sqlwhere disk_reads> 1000 or (executions> 0 and buffer_gets/executions> 30000) that is important (consumes many resources); 5: how to track SQLexec dbms_system.set_ SQL _trace_in_session (sid, serial #, & SQL _trace) of a session; 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 tim of the SQL statement E (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) index usually improves the performance of select/update/delete, which will reduce the insert speed: can indexed queries improve query performance? Why does the index exist 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 the case is different. 9: What is the variable bound? 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, and SQL optimization is difficult. 10: How to stabilize (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, respectively, 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 9i will be used if workarea_size_policy = auto, sorting is performed within pga. Generally, 1/20 of pga_aggregate_target can be used for dis. K sort; If workarea_size_policy = manual, the memory required for sorting is determined by sort_area_size when you perform operations such as order by/group by/distinct/union/create index/index rebuild/minus, 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 the disk sort in the system. 12: The table T (a, B, c, d) must be sorted by field c and displayed in records 21-30, please provide SQL 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_ran Dom. 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 phase comparison minus performance poor II. Basic Database concept class ================== ========================================================== ============================ 1: pctused and pctfree indicate what is the meaning? 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 smaller than the space set by pctfree, the data block is removed from freelist. When the free space in the block is larger than the space set by pct_used due to dml operations, the database block will be added to the freelist linked list. 2: a simple description of the relationship between table/segment/extent/block when creating a table, a data segment is created by default. Each data segment contains the number of extents specified by min extents, A certain number of blocks 3 are allocated for each extent data tablespace storage parameter: describes the relationship between tablespace and datafile. One tablespace can have one or more datafiles, and each datafile can only be in one tablespace, table data is distributed in datafile in tablespace through 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? A new Tablespace management mode will appear after the local management Tablespace (Locally Managed Tablespace (LMT) is 8i, use bitmap to manage the space usage of a tablespace. A table space management mode that can be used before and after the Dictionary management table space (DMT) 8i. It is used to manage the space usage of the table space through the data Dictionary. The dynamic segment space management (ASSM) first appeared in Oracle920 with ASSM. The Link List freelist is replaced by the 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 a rollback segment? Transaction rollback: when the transaction modifies the data in the table, the value before the modification (that is, the image before the modification) 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 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, the statement cannot see uncommitted modifications (statement-level read consistency) after the statement is executed. When ORACLE executes the SELECT statement, ORACLE ensures that any uncommitted changes earlier than the current SCN are not processed by this statement according to 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 to record database transactions and ensure data consistency and security to the maximum extent? redo log files: include changes made to the database. In this case, data recovery can be enabled in case of a fault, A database must archive at least two redo log files: offline copies of the redo log files, which may be necessary for recovery from media failures. 7: What are the main functions of SGA? SGA: db buffer cache/redo log buffer/shared pool/large pool/java pool db_cache: database cache (Block Buffer) it 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. 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, SQL statements, and control structures. large_pool: When MTS is configured, because UGA should be allocated in SGA to maintain user sessions, Large_pool should be 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 processes of the Oracle system? What is the role of the Data Writing Process (dbwr): responsible for writing changed data from the database buffer cache to the data file log writing process (lgwr ): write changes in the redo log buffer to the online redo log file system monitoring (smon): Checks Database Consistency and, if necessary, starts the database recovery process monitoring (pmon) when the database is opened ): responsible for clearing the resource Checkpoint Process (chpt) When an Oracle process fails: Responsible for permanently recording changes in the buffer cache in the database, update the database status information in the control file and data file. Archive process (arcn): backs up full log groups or archive job schedulers (cjq) during each log switch: schedules and executes jobs defined in the system, 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;