T test database

Apr 14 posted in Oracle (RSS), 134 views.

1: The table lists several ways to connect
hash join / merge join / nest loop (cluster join) / index join

2: do not use third-party tools and how to view sql of the implementation plan
set autot on
explain plan set statement_id = & item_id for &sql;
select * from table (dbms_xplan.display);

3: how to use the CBO, CBO and the distinction RULE
In optimizer_mode = choose when, if there is statistical information table (partition table outside), optimizer will choose CBO, unless the election RBO. RBO followed a simple classification methodology, the use of 15-level elements, when receiving the inquiry, will optimize the use of the number of points, and then choose the best level (the least amount) to run the query execution path.
CBO try to find the lowest cost method of access to data, to the largest or the fastest throughput of the initial response time, using a different calculation of the cost of an implementation plan, and select one of the lowest cost, the data content on the table statistics are used to determine an implementation plan.

4: how important (and more consumption of resources) of the SQL
select sql_text
from v $ sql
where disk_reads> 1000 or (executions> 0 and buffer_gets / executions> 30000);

5: How do I track a session of the SQL
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: SQL adjustment are most concerned about what <br /> view the SQL of response time (db block gets / consistent gets / physical reads / sorts (disk))

7: talk about your understanding of the index (the index structure, the impact of dml, the impact of inquiry, why increase the query performance)
b-tree index / bitmap index / function index / patitional index (local / global) are usually able to raise the index select / update / delete performance, will reduce the speed of insert

8: the use of the index will improve the query performance query it? <br /> Why is the index to improve query performance in order to exist, if there is no index in the query to improve performance, can only be described as using the wrong index, or talk about is the occasion different

9: What is bind variables? What are the advantages and disadvantages of bind variables?
Bind variables is relatively variable in terms of text, the so-called variable text refers to the direct writing SQL query, the SQL to do under different conditions require repeated analysis, refers to the use of bind variables instead of directly writing the variable conditions, bind value inquiry In the run-time delivery, and then bind the Executive. Analysis of the advantages is to reduce the hardware, the struggle to reduce CPU use, save shared_pool; drawback is can not use histogram, sql more difficult to optimize

10: How stable (fixed) Plan of Implementation
query_rewrite_enabled = true
star_transformation_enabled = true
optimizer_features_enable = 9.2.0
To create and use the stored outline

11: and the sort of memory-related, respectively, in 8i and 9i how to adjust, temporary table space What is the role of
8i in sort_area_size / sort_area_retained_size needed to determine the sort of memory, if the sorting operation can not be completed sort_area_size, temp table space will be used; 9i if workarea_size_policy = auto, the sort carried out in the pga, usually pga_aggregate_target 1 / 20 can be used for disk sort; if workarea_size_policy = manual, the sort of memory needed by the sort_area_size decision, in the implementation of order by / group by / distinct / union / create index / index rebuild / minus such operation, or if the pga in sort_area_size can not be completed, sorting will be carried out in the temporary table space (disk sort), temporary table space is a major role in the completion of system disk sort.

12: the existence of table T (a, b, c, d), sorted according to c field after the first 21-30 records check, please give sql
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 (1300), 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
Poor performance compared to the minus

Second: the basic concepts of database categories

1: pctused and pctfree that what the meaning of what the role of
pctfree control data and pctused block appears in the freelist in, pctfree control data block space reservations for the update, when the data blocks in the free space is less than the space pctfree set, the data blocks removed from the freelist, when the block dml operation as a result of free space is greater than the space set pct_used, the database will be added in the block list in the freelist.

2: a brief description of table / segment / extent / block the relationship between
create table when creating a default data segment, each data segment containing min extents specified number of extents, each extent, according to the storage space, according to the parameters form a certain number of blocks allocated

3: tablespace and datafile described the relationship between a tablespace can have one or more datafile, each datafile in a tablespace with only, table of data, through the hash algorithm in each datafile in the tablespace in, tablespace is a logical concept, datafile physically stored in the database all the objects.

4: the local management of the dictionary table space and table space management features, ASSM What are the characteristics of the local management Tablespace (Locally Managed Tablespace referred to LMT), 8i after the table space for a new management model to manage through the form bitmap the use of the space space.
Dictionary-managed tablespace (Dictionary-Managed Tablespace referred to as DMT), 8i included after all can also use a table space management model, through the data dictionary space management tablespace to use.
Dynamic segment space management (ASSM), it is the first time in years Oracle920 With ASSM, link list bitmap freelist be replaced, it is a binary array that can quickly and effectively to manage the storage expansion and the remaining blocks (free block) and therefore to improve the nature of sub-storage, ASSM tablespace created that paragraph there is another call Bitmap Managed Segments (BMB above).

5: What is the role of Rollback Segment Rollback Services: Services to amend the table when the time data, the data value before the amendment (that is, before the image) will be stored in the rollback segment, when the user rollback Affairs (ROLLBACK) when , ORACLE will use the data in the rollback segment to be modified before the image data back to the original value.
Service Recovery: When the services are being processed, the routine fails, rollback segment information stored in the undo table space, ORACLE will use next time you open the database is not rolled back to restore the data submitted.
Read consistency: when a conversation in the process of amending the data, the other will not see the conversation did not submit the conversation changes.
When a statement is being implemented, the statement will not see from the statement after the beginning of the implementation of the changes did not submit (statement-level read consistency).
When the ORACLE implementation of SELECT statement, ORACLE changed in accordance with its current system (SYSTEM CHANGE NUMBER-SCN) to ensure that any current SCN did not submit the change would not be dealt with by the statement. Imagine: When a query is running a long time, if the other conversation changed the query to query a data block, ORACLE rollback segment data will be used to construct a pre-image view of time consistency.

6: What is the role of the log records of database services, to guarantee maximum data consistency and security of redo log files: the database containing records of the changes, so that failure of the event can enable data recovery, a database of at least two a redo log file archiving log file: Yes, the offline redo log files copy, these copies may have failed from the media is essential in recovery.

7: SGA who are mainly part of what a major role
SGA: db_cache / shared_pool / large_pool / java_pool
db_cache: database cache (Block Buffer) for the operation of Oracle databases and performance play a key role, it occupied Oracle database SGA (system shared memory area) of the main part. Oracle database by using the LRU algorithm, the recent visit of the data blocks stored in the cache to optimize data access on disk.
shared_pool: the size of shared pool performance for Oracle is very important to preserve sharing pool data dictionary cache or compile and complete resolution of the PL / SQL blocks and SQL statements and control structures
large_pool: the use of MTS configuration, due to the UGA in the SGA allocated to maintain the user's session is used to keep the conversation Large_pool memory, use RMAN to back up when done, it is necessary to use the memory structure Large_pool to do disk I / O cache browser.
java_pool: for java procedure to prepare the memory region, if not used java proc, java_pool is not necessary

8: oracle system What are the main process, What is the role of the data write process (dbwr): responsible for the change of the data from the database buffer cache to write data files to write the process of log (lgwr): the redo log buffer Change into the online redo log file system Monitor (smon): checks the consistency of the database, if necessary, will be opened in the database recovery process to start the database monitor (pmon): responsible for the failure of a Oracle process checkpoint when the process of cleaning up resources (chpt): responsible for the buffer cache when changes in the permanent record in the database, the update control files and data files in the database state information.
Archiving process (arcn): log in every time when switching to full backup of the log group or archive operation scheduler (cjq): responsible for scheduling and execution system has been the definition of a good job, the completion of some predefined task.
Recovery process (reco): to ensure the consistency of distributed transactions in the distributed transaction in, or at the same time commit, either at the same time rollback;

C: backup and recovery category

1: How to backup logical backup Category: exp / imp
Physical backup:
RMAN backup full backup / incremental backup (cumulative / difference)
Hot backup: alter tablespace begin / end backup;
Cold Backup: offline backup (database shutdown)

2: What is the meaning of archiving on the archived log: Oracle to fill up the online archive log file group, the archive log will be set up (archived redo log).
Their backup and recovery on the database use the following:
Database backup, as well as online and archive log files, disk failure in the operating system and can be submitted to ensure that all things can be restored.
Open and normal in the database system, if the archive log is permanently preserved, online backup can be carried out and use.
Database can be run in two different ways:
NOARCHIVELOG way or the database in ARCHIVELOG mode to use NOARCHIVELOG can not archive online log, if the database is running in ARCHIVELOG mode can be implemented online archive log.

3: If a table in the 2004-08-04 10:30:00 by drop, in a sound archive and backup, how to restore?
Manual backup copy back all the data files
startup mount;
alter database recover automatic until time''2004-08-04:10:30:00'';
alter database open resetlogs;

4: rman is, What are the characteristics of
RMAN (Recovery Manager) is an important tool for DBA for backup, restore and recovery oracle database, RMAN can be used to backup and restore database files, archive logs, control file, the system parameter files may also be used to perform fully or not complete database recovery.
RMAN has three different user interfaces: COMMAND LINE mode, GUI mode (integrated in the backup OEM Manager), API way (for integration into third-party backup software).
With the following characteristics:
1) similar to the physical backup, but stronger than the physical backup N times;
2) can be compressed air block;
3) be able to achieve incremental block level;
4) the output can be packaged into a backup backup set can also be divided by the fixed-size backup set;
5) Backup and Restore can automatically manage the process;
6) can use the script (there is in the Recovery catalog)
7) can be done to monitor the bad block

5: standby database of the characteristics of the reserve (standby database): ORACLE launched a high-availability (HIGH AVAILABLE) database program, in the main nodes and inter-node through the backup log to ensure the synchronization of data synchronization, backup node as a backup master node can be fast switching and catastrophic recovery, from 920 onwards, has also started to support the physical and logical standby server.
9i of the three data protection modes are:
1), MAXIMIZE PROTECTION: No maximum data protection and data differences, LGWR will be sent to the standby node, the node Affairs confirmed in the main before the standby node also log data must be fully received. If the network properly, LGWR can not be caused by transmission of data, will cause serious performance problems, leading to the main node DOWN machine.
2), MAXIMIZE AVAILABILITY: no data loss mode, allowing data differences, and to allow asynchronous transmission. Under normal circumstances to run in maximum protection mode, in the main nodes of the network and the standby node abnormal disconnect or connect automatically switch to maximum performance mode, the main node of the operation can continue. In the case of poor network have a greater impact on performance.
3), MAXIMIZE PERFORMANCE: This model can be said to be inherited from the 8i backup server mode, asynchronous transmission, no data synchronization check, you may lose data, but can be the greatest performance of the main node. 9i in the configuration when the default DATA GUARD is MAXIMIZE PERFORMANCE

6: For a relatively short time to restore the system (database 50G, daily archiving 5G), how to design your backup strategy
rman / month on the 1st weekend of every level 0 / level 1 the other day Wednesday level 2

IV: System Management

1: the existence of system performance for a system to deal with what you thought of the diagnosis
1) do statspack information collection systems to understand the system more or less the situation / set up parameters to determine the existence of improper places / view the top 5 event / See top sql and so on
2) check v $ system_event / v $ session_event / v $ session_wait
V $ system_event from the beginning to determine what resources (db file sequential read) in-depth study, such as v $ session_event, determined to wait for the events involved in the conversation from v $ session_wait the detailed situation is a scramble for resources (p1-p3 value: file_id / block_id / blocks, etc.)
3) v $ sql / v $ sqltext / v $ sqlarea table to determine disk_reads, (buffer_gets / executions) the larger the value of SQL

2: Diagnosis cited several IO, CPU, performance status of the method
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 is awareness of statspack
Oracle provides StapSpack is a collection of performance indicators database package, the package from 8i onwards, 9i, 10g has significantly increased. The support package table (storage-related parameters and performance indicators collected by the table) from the initial growth of 25 to 43. Collection-level parameters from the original three (0,5,10) to 5 (0,5, 6,7,10) collected through the analysis of performance indicators, the database administrator can understand in detail the operation of the current database, on the database instance, waiting for events, SQL, etc. to optimize the adjustment. Statspack collected using snapshot, the database can produce a variety of statistical indicators of the performance of statistical trend charts.

4: If the system is now a great need to create an index table, you will consider those factors, what to do in order to minimize the impact on the application of relatively idle in the system; nologging options (if not you can use dataguard nologging) great sort_ared_size or larger pga_aggregate_target

5: What is awareness of raid10 and raid5
RAID 10 (or RAID 1 +0) and RAID 0 +1 different, it is composed of hard disk drives to RAID 1 array, RAID 1 array and then re-formed between the RAID 0 array. RAID 10 mode with the same RAID 0 +1 mode of data transfer with good performance, but higher than RAID 0 +1 has a higher reliability. RAID 10 arrays the actual capacity of the M × n / 2, the disk utilization of 50%. RAID 10 also requires at least four hard disk drive structure, and therefore very expensive. The reliability of RAID 10 and RAID 1, but due to the hard drive RAID 10 data between divided and thus data transmission performance.
RAID 5 and RAID 3 is very similar, the only difference lies in RAID 5 parity data with the same information can also be saved to the partition of the hard drives of all, rather than write a specified hard disk drives, thereby eliminating a single hard disk parity bottlenecks drive. RAID 5 disk array of RAID 3 performance improved, but still need at least three hard drives. The actual capacity of M × (n-1), the disk utilization for the (n-1) / n.