1) Oracle Architectural Components
a)oracle server
*oracle database
Is a collection of data that is treated as a unit.
Consists of three file types.(controlfile,datafile,redo)
*oracle instance
Is a means to access an Oracle database.
Always opens one and only one database.
Consists of memory and background process structures.
b)oracle instance
memory structure
process structure
*memory structure
Automatic Shared Memory Management=ASMM
sga
pga
sga
shraed pool
database buffer cache
redo log buffer
large pool user for 1:UGA 2:RMAN I/O 3:Parallel query messaging
java pool
streams pool
SQL>show parameter sga
SQL>show sga
SQL>select * From v$sga;
lock_sga:
LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.
pre_page_sga:
PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.
sga_max_size:
sga_target:
sga_target = shraed pool+ database buffer cache+ redo log buffer+large pool+java pool
SGA_MAX_SIZE >= SGA_TARGET
SQL> alter system set sga_max_size=160M scope=spfile;
SQL> alter system set sga_target=160M scope=spfile;
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 152M
sga_target big integer 152M
SQL> alter system set sga_target=160M;
alter system set sga_target=160M * ERROR at line 1: ORA-02097: parameter cannot be
modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size
pga
The Program Global Area or Process Global Area (PGA) is a memory region
that contains data and control information for a single server process
or a single background process.
The PGA is allocated when a process is created and
deallocated when the process is terminated.
the PGA is an area that is used by only one process.
*process structure
1,user process run at client
2,server process
dedicated server
shared server
3,background processes
dbwn pmon ckpt lgwr smon arcn
Optional background processes:
LMDn QMNn CJQ0 LMON RECO Dnnn LMS Snnn LCKn Pnnn
V$PROCESS
GV$PROCESS
c)oracle database
*physical structure
control files
v$database
v$log
select * from v$controlfile;
data files
online redo logfiles
Parameter file
Password file
Archive log files
Backup files
Alert and trace log files
*logical structure
tablespace
Tablespaces consist of one or more data files.
Data files belong to only one tablespace.
dba_tablespaces
dba_data_files
user_tablespaces
segment 段
Segments exist within a tablespace.
Segments are made up of a collection of extents.
dba_segments
user_segments
extent 区
Extents are a collection of data blocks.
dba_extents
user_extents
oracle data block 块
Data blocks are mapped to disk blocks.
d)Establishing a Connection and Creating a Session
Before users can submit SQL statements to an Oracle database,
they must connect to an instance.
e)Processing SQL Statements
Processing a Query
Parse:
Search for identical statement
Check syntax, object names, and privileges
Lock objects used during parse
Create and store execution plan
Bind: Obtain values for variables
Execute: Process statement
Fetch: Return rows to user process
Processing a DML Statement
Parse: Same as the parse phase used for processing a query
Bind: Same as the bind phase used for processing a query
Execute:
If the data and undo blocks are not already in the Database Buffer Cache,
the server process reads them from the data files into the Database Buffer Cache.
The server process places locks on the rows that are to be modified.
The undo block is used to store the before image of the data,
so that the DML statements can be rolled back if necessary.
The data blocks record the new values of the data.
The server process records the before image to the undo block and updates the data block.
Both of these changes are made in the Database Buffer Cache.
Any changed blocks in the Database Buffer Cache are marked as dirty buffers.
That is, buffers that are not the same as the corresponding blocks on the disk.
The processing of a DELETE or INSERT command uses similar steps.
The before image for a DELETE contains the column values in the deleted row,
and the before image of an INSERT contains the row location information
2)databse adminstration tools
Oracle Universal Installer(OUI)
Database Configuration Assistant(DBCA)
Database Upgrade Assistant
Oracle Net Manager
Oracle Enterprise Manager(OEM)
SQL*Plus and iSQL*Plus
Recovery Manager
Oracle Secure Backup
Data Pump(EXP/IMP)
SQL*Loader
Command-line tools
3)managing an oracle instance
a) To start an instance, the database must read instance configuration parameters
*initialization parameter files
In order to start an instance and open the database,
you must connect as SYSDBA and enter the STARTUP command.
the Oracle server reads the initialization parameter file.
*Two types of initialization parameter files :
Static parameter file, PFILE, commonly referred to as initSID.ora.
Persistent server parameter file, SPFILE, commonly referred to as spfileSID.ora.
*Initialization Parameter File Contents
A list of instance parameters.
The name of the database the instance is associated with.
Allocations for memory structures of the System Global Area (SGA).
What to do with filled online redo log files.
The names and locations of control files.
Information about undo segments.
*PFILE:
The PFILE is a text file that can be modified with
an operating system editor.
Modifications to the file are made manually.
Changes to the file take effect on the next startup.
Its default location is $ORACLE_HOME/dbs.
*SPFILE:
Binary file with the ability to make changes
persistent across shutdown and startup.
Maintained by the Oracle server.
Records parameter value changes made with the
ALTER SYST
oracle中的SGA和PGA
需积分: 37 109 浏览量
2019-03-23
01:03:23
上传
评论
收藏 4KB RAR 举报
weixin_38669628
- 粉丝: 383
- 资源: 6万+
最新资源
- Docker容器配置进阶
- tensorflow-gpu-2.7.4-cp37-cp37m-manylinux2010-x86-64.whl
- 多段线、 圆、弧转多段线(仅我可见)
- tensorflow-2.7.2-cp38-cp38-manylinux2010-x86-64.whl
- yeyue-p8Yi4-ve4a83792.apk
- tensorflow-gpu-2.7.3-cp38-cp38-manylinux2010-x86-64.whl
- 五相感应电机矢量控制模型MATLAB
- RGLED (1) (1).circ
- IMG_20240427_215747.jpg
- python下前端WEB学习笔记
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈