Oracle theoretical knowledge

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 );

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, dml impact, query impact, and why query performance is improved)
B-tree index/bitmap index/function index/patitional index (local/global) index usually improves the performance of select/update/delete and reduces 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?
Sort_area_size/sort_area_retained_size in 8i determines the memory required for sorting. If the sorting operation cannot be completed in sort_area_size, temp tablespace is used. If workarea_size_policy = auto in 9i, 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 the 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, B number, c number, d number );
/
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 table space management (LMT) is a new table space management mode that will appear after 8 I. It uses bitmap to manage the space usage of the table space.
Dictionary-Managed table space (DMT) is a table space management mode that can be used before and after 8 I. 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 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, the statement cannot see uncommitted modifications (statement-level read consistency) after execution ).
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_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. 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 very 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 must 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 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:
Database backup and online and archive log files Ensure that all submitted items can be recovered in the case of operating system and disk faults.
If the archive log is permanently saved when the database is opened and used by the normal system, it can be used online backup.
The database can run in two different ways:
When a NOARCHIVELOG or ARCHIVELOG database is used in NOARCHIVELOG mode, online log archiving cannot be performed. If the database runs in ARCHIVELOG mode, online log archiving can be implemented.

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
Startup mount;
Alter database recover automatic until time '2017-08-04: 10: 30: 00 ';
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. Data synchronization between the master and slave nodes ensures data synchronization, backup of the slave node as the master node can achieve fast switchover and catastrophic recovery. Starting from 920, physical and logical backup servers are also supported.
The three data protection modes in 9i are:
1) maximize protection: maximum data PROTECTION and no data difference, LGWR will be transmitted to the slave node at the same time, before the master node transaction confirmation, the slave node must also completely receive log data. 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, it 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 system automatically switches to the maximum performance mode. The operations on the master node can continue. Poor network performance has a greater impact.
3) maximize performance: This mode can be said to be a backup server mode inherited from 8i. It is asynchronously transmitted without data synchronization check and may lose data, but it can achieve the maximum PERFORMANCE of the master node. 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. Collection-level parameters increase from 3 (0, 5, 10) to 5 (0, 5, 6, 7, 10) by analyzing collected performance indicators, the database administrator can learn in detail the current running status of the database, and optimize and adjust the database instance, wait event, and SQL. 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 relatively idle; nologging option (nologging cannot be used if there is a consumer uard) Large sort_ared_size or pga_aggregate_target is large

5: What do you know about raid 10 and RAID 5?
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 capacity of the RAID 10 array is M × n/2, and the 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 drive, rather than being written to 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 RAID 3, but at least three hard drives are required. The actual capacity is M × (n-1), and the disk utilization is (n-1)/n.