A dba question

A dba question
Note: You can select and answer the following questions based on your own situation. You do not have to answer all the questions. You can also directly contact the interviewer to answer the questions without answering them.

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.