Oracle Architecture Overview

Declaration: Version 11gR2, CentOS5.81 physical structure (File) main performance impact on IO (1) controlfileselect * fromv $ controlfile (2) datafileselect * fromdba_data_files (3) redo? (Onlinelogfile) select * fromv $ logfileselect * fromv $ log (4) passwordfile

Declaration: Version 11gR2, CentOS5.8 1 physical structure (File) main performance impact on IO (1) controlfile select * from v $ controlfile (2) datafile select * from dba_data_files (3) redo? (Online logfile) select * from v $ logfileselect * from v $ log (4) password file

Declaration: Version 11gR2, CentOS5.8

1. Physical Structure (file)

Major performance impact on IO

(1) controlfile

select * from v$controlfile

(2) datafile

select * from dba_data_files

(3) redo? (Online logfile)

select * from v$logfileselect * from v$log

(4) password file (sys)

select * from dba_users

(The password entry has changed from 11 GB to null to avoid cracking)

2. Logical Structure 2.1 instance

Instance (memory [sga] + process [background])

Instance = memory (sga) + background process (background)

View the memory. You can use the following view:

Select * from v $ sga_dynamic_components

? Background process, which can be viewed in the following view

  select * fromv$bgprocess where paddr!='00';

? We only care that paddr is not equal to 0. If paddr is equal to 0, it is not enabled.

2.2 instance Management

Parameter file spfilesid. ora, initsid. ora

Two files can be converted to each other.

create spfile from pfile;create pfile from spfile;

(1) enabling an instance involves three stages: nomount, mount, and open.

L?Nomount stage

The parameter file must be used when nomount is enabled. By default, the spfile parameter file is preferentially used from 9i. If the spfile file does not exist, the pfile file is used. If the pfile file does not exist, the pfile file cannot be found.

11G has a new feature. Maybe I just discovered it !!! First, when the database is enabled, if the parameter file is lost or damaged, the database will not be directly down, but if the database is restarted, an error will be reported, in addition, if there is no good backup, It is very troublesome. If the database finds that the parameter file is lost or damaged at runtime, you can use the following method to handle it:

create pfile from memory;

Therefore, the parameter file must have a good backup.

You can view the database status in the v $ instance view.

select status from v$instance;

You can view background processes in the v $ bgprocess view.

select * from v$bgprocess where paddr<>’00’;

L?Mount stage

The control file is required to enable the mount stage.

You can use the control_files parameter to view the location of the control file:

show parameter control_files

If the control file is corrupted, an error will be reported when the database is enabled, and you will be notified to check the specific error information in alert_sid.log. The location of the file can be viewed using the background_dump_dest parameter:

show parameter background

Note: alert_sid.log can be up to 2 GB in size. Therefore, backup is required when the service is busy or the file size changes.

L?Open stage

11gR2 version-tested, other versions are not sure

From the open stage to the mount stage to the nomount stage

alter database close;alter database dismount;shutdown immediate

(2) shut down the instance

1. normal

2. transactional

The system cannot be closed when there is a transaction.

You can view the v $ transaction view if there are any transactions in the system.

3. immediate

4. abort

2.3 brief introduction to parameters

Example of db_cache_size and processes Parameters

First, check the v $ parameter view to check whether it is a static parameter or dynamic

select name,issys_modifiablefromv$parameter wherename in ('db_cache_size','processes');NAME             ISSYS_MOD--------------------      ---------processes            FALSEdb_cache_size        IMMEDIATE

The ISSYS_MOD field shows that the db_cache_size parameter takes effect immediately. Therefore, we can see that the db_cache_size field is a dynamic parameter.

Note: If the processes parameter is too large, for example, when it is changed to 50000, the database will be suspended when it is restarted.

?

2.4 session (process)

A) process

select * from v$process;

Including v $ bgprocess

L? The following method can be used to trace the process of the OS system:

SQL> select distinct sid fromv$mystat;       SID----------        37SQL> select sid,paddr fromv$session where sid=37;       SID PADDR---------- --------        37 43BC365CSQL> select addr,pid,spidfrom v$process where addr='43BC365C';ADDR            PID SPID-------- ----------------------------------43BC365C         20 11633[root@elvis ~]# ps -ef | greporaroot      3762 3732  0 May30 ?        00:01:02 hald-addon-storage: polling/dev/hdcoracle    4604    1  0 May30 ?        00:00:02/u01/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inheritoracle    7624    1  0 06:22 ?        00:00:07 ora_pmon_elvisoracle    7628    1  0 06:22 ?        00:00:35 ora_vktm_elvisoracle    7634    1  0 06:22 ?        00:00:00 ora_gen0_elvisoracle    7638    1  0 06:22 ?        00:00:01 ora_diag_elvisoracle    7642    1  0 06:22 ?        00:00:00 ora_dbrm_elvisoracle    7646    1  0 06:22 ?        00:00:01 ora_psp0_elvisoracle    7650    1  0 06:22 ?        00:00:35 ora_dia0_elvisoracle    7654    1  0 06:22 ?        00:00:05 ora_mman_elvisoracle    7658    1  0 06:22 ?        00:00:03 ora_dbw0_elvisoracle    7662    1  0 06:22 ?        00:00:02 ora_lgwr_elvisoracle    7666    1  0 06:22 ?        00:00:16 ora_ckpt_elvisoracle    7670    1  0 06:22 ?        00:00:06 ora_smon_elvisoracle    7674    1  0 06:22 ?        00:00:00 ora_reco_elvisoracle    7678    1  0 06:22 ?        00:00:11 ora_mmon_elvisoracle    7682    1  0 06:22 ?        00:00:07 ora_mmnl_elvisoracle    7686    1  0 06:23 ?        00:00:00 ora_d000_elvisoracle    7690    1  0 06:23 ?        00:00:00 ora_s000_elvisoracle    9125    1  0 10:26 ?        00:00:00 ora_arc0_elvisoracle    9129    1  0 10:26 ?        00:00:00 ora_arc1_elvisoracle    9133    1  0 10:26 ?        00:00:00 ora_arc2_elvisoracle    9137    1  0 10:26 ?        00:00:00 ora_arc3_elvisoracle    9177    1  0 10:31 ?        00:00:00 ora_smco_elvisroot     11596 11564  0 16:36 pts/0    00:00:00 su - oracleoracle   11597 11596 0 16:36 pts/0    00:00:00 -bashoracle   11629 11597 0 16:36 pts/0    00:00:00 rlwrapsqlplus / as sysdbaoracle   11630 11629 0 16:36 pts/1    00:00:00sqlplus   as sysdbaoracle   11633 11630 0 16:36 ?        00:00:00oracleelvis (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))oracle   11637    1  0 16:36 ?        00:00:01 oracleelvis (LOCAL=NO)oracle   11678    1  0 16:38 ?        00:00:00 oracleelvis (LOCAL=NO)oracle   11682    1  0 16:39 ?        00:00:00 ora_w000_elvisroot     11735 11695  0 16:42 pts/2    00:00:00 grep ora

Note: In windows, the process ID needs to be viewed. In Windows, threads under a process cannot be viewed directly.

L? Trace process resource usage to session

In this case, you can find which session is occupying a large amount of resources, or even find

B) session

L? Resource view-important

select * from v$resource_limit;

L? Command type view, which is determined by the command column in the v $ session View

select * from audit_actions;

L? Use the SQL _id column in the session view to view the SQL statement

select * from v$sql where sql_id=’  ’;

Simulate an infinite loop statement that consumes Resources

declarenumber:=0;begin loopi:=1;end loop:end;

Kill the session after finding it.

alter system kill session ’67,23’;

Or Kill the process directly.

Window is the orakill command, and the server has

In Linux, kill, top, ps-ef | grep ora and other commands are used.

Kill usage

-- Kill-9 sid force kill

C) connection

Private connection

Shared connection

Not used. Just read it.

Pool connection

3. go deep into the logical structure 3.1 tablespace

Database-> tablespace-> segments-> extents-> osblocks

========================================================== ========

 selelct * from dba_tablespaces; select * from dba_data_files; select * from dba_temp_files; select * from dba_segment;

? -- A tablespace can contain a maximum of 1023 data files.

Note: The concept of a latency segment starts from 11g. If there is no data in the table, Oracle does not allocate space.

You can use the deferred_segment_creation parameter to check whether the delay segment is enabled.

-- Show parameterdeferred_segment_creation

========================================================== ========

Create a tablespace

 create tablespace orcl datafile 'E:\ORADATA\orcl\orcl01.dbf' size 10M;

Create a table and save it to the orcl tablespace.

 create table t(id int) tablespace orcl;

Table space allocation method (extent)

Autoallocate (default)

<= 1 m ??? 64 k ??? 8 blocks

<= 64 m ?? 1 m ??? 128 blocks

<= 1g ??? 8 m ???? 1024 blocks

> 1g ???? 64 m ??? 8096 blocks

Uniform [specify the growth size]

Always grow according to the specified size

In the allocation mode, you can see that the allocate_type field in the dba_tablespace view is system, which is the default value ??

Disable the tablespace.

 alter tablespace orcl offline;

-> Notes: Although tables cannot be accessed or modified, they can be deleted, equivalent to databases.

? Disabled, but it is partially disabled.

The recycle bin command show recyclebin can be queried, but the DDL/DML cannot be executed on the tables in the recycle bin.

 select * from " BIN$wHUg/RiVRxuXtwyypSxnTw==$0 TABLE" ;

From the recycle bin? Flash back

flashback table t to before drop;

?

========================================================== ==========

You can use EM to observe the table space usage.

? -- Logon-> administration-> tablespace

3.2 manage tablespaces and data files

Create a table

 create table tt tablespace orcl as select * from dba_objects;

Continuous Data insertion

insert into tt select * from tt;

When the tablespace size is insufficient, there are roughly the following ways to expand the tablespace:

1. directly expand the data file size

 alter database datafile file_id resize 20M;

2. automatically expand the data file size

 alter database datafile 5 autoextend on next 1M maxsize 1024M;

3. Manually expand the tablespace size

alter tablespace orcl add datafile 'D:\oradata\orcl\orcl02.dbf' autoextend on next 1M maxsize 1024M;

A 40 MB space is allocated to the tt table and it will be displayed, but no data in it is blank (insertion will follow the priority of spatial insertion)

alter table tt allocate extent(datafile'D:\oradata\orcl\orcl02.dbf' size 40M);

How to view the block usage in a table:

1. Package Method

execdbms_stats.gather_table_stats(‘table’,’TEST’);

2. Analysis table-more accurate

analyze table tt computestatistics;

The tt table can be compressed from 10 Gb.

alter table t shrink space;

However, row movement)

alter table t shrink space;

In this way, we can compress...

Check whether row movement can be moved.

select * from dba_tables where table_name='T';

========================================================== ======================================

Move Table t to table space 2 to observe the changes.

alter table t move tablespace orcl02;

Case 1:

? Optimize the problem and observe the disk read rate and the reading status of each file.

? View:

? -- DBAs are headers of static data from the data dictionary in the system.

?? -- V $ headers are dynamic, generally from control files and stored in the memory. Therefore, do not restart the server. After restart, the dynamic information will disappear, of course, from 10 Gb.

? Oracle stores dynamic information every other hour.

select * from v$filestat;

3.3 undo tablespace

Create an undo tablespace

create undo tablespace undotbs2 database'E:\oradata\orcl\undotbs02.dbf' size 10M;

Add a column to table t

alter table t add name varchar2(10) default 'a'; insert into t values(1,'elvis'); insert into t values(2,'elvis'); commit;

Then update the following values:

 updata t set name='c' where id=1; select * from t;

Do not submit to query and observe with the same user identity in another window.

? -- Of course not, because it is not submitted

Author: elvis_dataguru published on 15:07:40 Original article link

Read: 38 comments: 0 view comments

Original article address: Oracle Architecture Overview. Thank you for sharing it with me.