查看SGA/PGA开辟多大空间,查看日志缓冲区分配大小
$sqlplus "/as sysdba"
SQL>show parameter sga
...
SQL>show parameter pga
SQL>show parameter log_buffer
oracle 10g中:一般情况下,oracle设置为sga自动管理,共享池喝数据缓存区的大小分配由之前的SGA_MAX_SIZE和SGA_TARGET决定,若需要手工分配,设置SGA_TARGET=0,SHADE_POOL_SIZE和DB_CACHE_SIZE设置为非0即可。
修改sga大小(scpoe=spfile方式),scope=xxx可以不写,默认both
SQL>alter system set sga_target=2000M scope=spfile;
SQL>alter system set sga_target=2000M scope=memory;
SQL>alter system set sga_target=2000M scope=both;
注:修改log_buffer大小,必须重启才会生效,且 scope=memory或者 scope=both会报错,只能是 scope=spfile;
oracle 11g中:自动化更彻底,只需设置MEMORY_TARGET参数,连PGA都不需要设置,MEMORY_TARGET参数指定的内存会自动分配内存给SGA与PGA
ipcs -m查看共享内存的命令:
$ipcs -m
查看oracle进程:
进入Oracle用户
#su oracle
$ps -ef |grep oracle
查看实例的进程
$ps -ef |grep instance_name
查看实例名:
SQL>show parameter instance_name
查看归档进程:
$ps -ef |grep arc
查看数据库归档是否开启
SQL>archive log list;
更改数据库归档模式,需要重启数据库,先将数据库至于mount状态,再更改,再open数据库:
SQL>shutdown immediate
SQL>startup mount;
SQL>alter database archivelog;(SQL>alter databese noarchivelog)
SQL>alter database open;
SQL>archive log list;
数据库的开启与关闭:
开启分三个步骤:
nomount与参数文件(pfile/spfile)相关 oracle 9i起 引进了spfile
mount 与 控制文件相关
open 与 数据文件/日志文件相关
SQL>startup nomount
SQL>alter database mount;
SQL>alter database open;
SQL>shutdown immediate;
各种文件所在位置:
SQL>show parameter spfile;
SQL>show parameter control
SQL>select file_name from dba_data_files;
SQL>select group#,member from v$logfile;
SQL>show parameter recovery
oracle监听
如果想在远程A机器上通过网络访问本地B机器上的数据库,B机器上的数据库必须开启监听,远程的A机器只需安装数据库客户端,然后通过读取A机器上数据库客户端配置的TNSNAMES.ORA的配置文件,即可连接并访问B机器的数据库。详细可参考oracle官方文档的concept说明。
lsnrctl status 命令是查看监听状态的民工,其中Listener Parameter File 和 Listener Log File定位了监听文件LIstener.ora以及对应的日志;
lsnrctl stop命令是关闭监听的命令
lsnrctl start 命令是开启监听的命令
$lsnrctl status
$lsnrctl stop
$lsnrctl start
清空共享池
alter system flush shared_pool;
观察数据库体系结构中的逻辑结构
块BLOCK–>区EXTENT–>段SEGMENT–>表空间TABLESPACE–>数据库DATABASE
逻辑结构之BLOCK
SQW>show paramenter db_block_size
--也可以通过观察表空间视图dba_tablespaces的block_size值获取 8192=8k
select block_size from dba_tablespaces where tablespace+name='SYSTEM';
逻辑结构之TACBLESPACES
sqlplus "/ as sysdba"
--删除表空间 including contents and datafiles 表示删除表空间的数据和对应数据文件
drop tablespace TBS_CLC including contents and datafiles;
--普通数据表空间
SQL> create tablespace TBS_CLC
blocksize 16K --指定最小块为16k 最小快默认为8k的
datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_01.DBF' size 100M
autoextend on --开启自动扩展
next 64k --每次都以64k扩展 等同于 uniform 64k的功能
maxsize 5G
extent management local--10g以上可以取消
segment space management auto;--10g以上可以取消
表空间已创建
select file_name, tablespace_name, autoextentsible, bytes from dba_data_files where tablespace_name='TBS_CLC';
--临时表空间(语法有些特别,有temporary及tempfile的关键字)
CREATE TEMPORARY TABLESPACE TEMP_CLC TEMPFILE 'D:\ORADATA\ORA10\DATAFILE\TEMP_CLC.DBF' SIZE 100M;
表空间已创建
select file_name, bytes, autoextentsible from dba_temp_files where tablespace_name='TEMP_CLC';
--回滚表空间(语法有些特别,有undo的关键字)
CREATE UNDO TABLESPACE UNDOTBS_CLC DATAFILE '' SIZE 100M;
表空间已创建
select file_name, tablespace_naem, autoextentsible, bytes/1024/1024 from dba_data_files where tablespace_name='UNDOTBS_CLC';
--系统表空间
select file_name, tablespace_name, autoextentsible, bytes/1024/1024 from dba_data_files where tablespace_name like 'SYS%';--sysaux作为辅助系统表空间
--系统表空间和用户表空间都属于永久保留内容的表空间
select tablespace_name, contents from dba_tablespaces where table_space_name in ('TBS_CLC','UNDOTBS_CLC','SYSTEM','SYSAUX');
逻辑结构之user
--sysdba用户登录,假设clc用户存在,先删除
sqlplus "/as sysdba"
drop user clc cascade;
--建用户,并将先前建的表空间tbs_clc和临时表空间temp_clc作为clc用户的默认使用空间。
create user clc
identified by clc000000
default tablespace tbs_clc
temporary tablespace temp_clc;
--授权,暂且将最大权限给clc用户
grant dba to clc;
--可以登录用户了
connect clc/clc000000
逻辑结构之EXTENT
oracle的最小单位是BLOCK,最小扩展单位是EXTENT
--构造t(如果没有知名表空间,就是用户clc的默认表空间)
sqlplus clc/clc000000
drop table t purge;
create table t (id int) tablespace tbs_clc;
--查询数据字典获取extent相关信息
select segment_name, extent_id,tablespace_name, byts/1024/1024, blocks from suer_extents where segment_name = 'T';
--插入数据后继续观察,发现
查看表空间剩余空间,原始表空间=剩余表空间=已使用表空间
--原始表空间-剩余表空间=已使用表空间
select sum(byts)/1024/1024 from dba_free_space where tablespace_name='TBS_CLC';--查看剩余多少
select sum(byts)/1024/1024 from dba_data_files where tablespace_name='TBS_CLC';--原始表空间
表空间扩展:
1、增加数据文件:
SQL>alter tablespace TBS_CLC add datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_02.DBF' size 100M;
2、修改为表空间自动扩展方式
SQL>alter database datafile 'D:\ORADATA\ORA10\DATAFILE\TBS_CLC_02.DBF' autoextend on;
回滚表空间可以新建多个,并且自由切换,但是数据库当前使用的回滚表空间却只能有一个(RAC数据库会有多个)
--查看数据库当前在用回滚段
SQL>show parameter undo
--查看数据库有几个回滚端
select tablespace_name, status from dba_tablespaces where contents ='UNDO';
--切换回滚表空间
SQL>alter system set undo_tablespace=undotbs2 scope=both;
临时表空间可以建多个,也可以同时被使用。
分析数据库产生多少日志:
select a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
该脚本是利用V s t a t n a m e 和 v statname 和 v statname和vmystat两个动态性能视图来跟踪当前session操作产生的日志量,使用方法很简单:首次先执行该脚本,查看日志大小,随即执行你的更新语句,再执行该脚本返回的日志大小,两者相减就是你此次更新语句产生的日志大小。增删改都会产生日志,删除产生的undo量最多,redo也最多
sqlplus "/as sysdba"
--其中该视频需要先sqlplus /as sysdba 登录授权如下后方可执行
SQL>grant all on v_$mystat to clc;
SQL>grant all on V_$statname to clc;
CONNECT CLC/CLC000000
create or replace view v_redo_size as select a.name, b.value from v$statname a, v$mystat b where a.statistic#=b.statistic# and a.name='redo size';
分区表可以带条件truncate:
alter table t truncate partition '分区名';
全局临时表分为两种:一种是基于会话的全局临时表(commit preserve rows),一种是基于事务的全局临时表(on commit delete rows)
全局临时表DML操作(增删改)都会产生日志,且会话临时日志量大于事务临时,但是都远小于普通临时产生的日志量
create global temporary table temp_test on commit preserve rows as select * from dba_projects where 1=2;
create global temporary table temp_test on commit delete rows as select * from dba_projects where 1=2;
全局事务临时表,在commit或者session链接退出后,临时表记录自动删除,且都不会产生日志(140可以忽略不计);
全局会话临时表commit之后,记录还在。
不同会话独立。
分区表(排名按使用频率)
范围分区range;
列表分区list;
组合分区;
HASH分区;
1.范围分区(最常见是是按照时间分区)
SQL>drop table range_part_tab purge;
SQL>create table rang_part_tab (id number,deal_date date, area_code number, contents varchar2(4000))
partition by rang(deal_dsate)
(partition p1 values less than (to_date('2018-02-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2018-03-01','yyyy-mm-dd')),
partition p12 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)
);
insert into range_part_tab (id,deal_date,area_code,contents)
select rownum,to_date( to_char(sysdata-365,'j')+trunc(dbms_random.value(0,365)),'j'), ceil(dbms_random.value(590,599)),--ceil向上取整,floor向下取整
rpad('*,400,'*') from dual connect by rownum<=10000;
SQL>commit;
value less than 是范围分区的特定语法,用于指明具体的范围,比如partition p2 values less than (to_date(‘2018-03-01’,‘yyyy-mm-dd’)),表示小于3月份的记录。
partition p_max values less than (maxvalue)表示超出范围的记录全部落在这个区中
分区表的分区可分别指定在不同的表空间里,如果不写即为都在同一默认表空间里。
2.列表分区
SQL>drop table list_part_tab purge;
SQL>create table list_part_tab (id number,deal_date date, area_code number, contents varchar2(4000))
partition by list(area_code)
(partition p_591 values (591),
partition p_592 values (592),
partition p_599 values (599),
partition p_other values (default)
);
插入语法同范围分区
列表分区仅需values即可确定范围,可以写为多个如 partition p_union values(591,592,594).
partition p_other values(default) 表示不在591-599范围的记录全部落在这个默认分区中。
表空间可以指定也可以不指定。
3.散列分区(HASH)
SQL>drop table hash_part_tab purge;
SQL>create table hash_part_tab(id number,deal_date date, area_code number, contents varchar2(4000))
partition by hash(deal_date)
partitions 12
;
插入语法同范围分区
散列分区与之前两种分区的明显差别在于,没有指定分区名,而仅仅指定了分区个数 如partitions 12.
散列分区的分区个数设置为偶数个。
可以指定散列分区的分区表空间,比如增加下以小段,store in (ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8,ts9,ts10,ts11,ts12)表示分别再12个不同的表空间里,不屑即在同一默认表空间。
表空间可以指定也可以不指定。
4.1组合分区-范围列表(range-list)
SQL>drop table range_list_part_tab purge;
SQL>create table range_list_part_tab(id number,deal_date date, area_code number, contents varchar2(4000))
partition by range(deal_date)
subpartition by list(area_code)
subpartition template
(subpatition p_591 values (591),
subpatition p_599 values (599)
subpatition p_other values (default)
)
(partition p1 values less than (to_date('2018-02-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2018-03-01','yyyy-mm-dd')),
partition p12 values less than (to_date('2019-01-01','yyyy-mm-dd')),
partition p_max values less than (maxvalue)
)
;
插入语法同范围分区
组合分区是由主分区和从分区组成的,如范围-列表分区,就表示主分区是范围分区,从分区是列表分区,从分区关键字为subpartition。
为了避免在每个分区中都写相同的从分区,可以考虑用模板凡是,如subpartition template关键字。
设计子分区模块,都要有subpartition关键字。
表空间可以指定也可以不指定。
分区消除:消除了除指定分区外的其他分区,使之查询的代价cost小、逻辑读小;分区越多,cost越大。
delete无法释放空间,truncate可以释放空间却不能带条件,只能全表清除数据,分区表可以truncate某一个分区而不影响其他分区:
`alter table range_part_tab truncate partition p9;`
分区交换即备份转移,瞬间完成:
alter table range_part_tab exchange partition p8 with newtable;
--注意是交换,再执行一次,则又交换回来了。
分区分割:
SQL>alter table range_part_tab split partition p_max at (to_date('2019-02-01','yyyy-mm-dd')) into (partition p201902,partition p_max);
SQL>alter table range_part_tab split partition p_max at (to_date('2019-03-01','yyyy-mm-dd')) into (partition p201903,partition p_max);
三个关键字:split\as\into
at部分说明了具体范围,小于某个指定的值
into部分说明分区被分割成两个分区,表示p_max被分割成partition p201902 和 partition p_max两部分,其中括号里的p_max可以改为新的名字,也可以保留原来的名字。
有分割就有合并:
SQL>alter table range_part_tab merge partition p201902, p_max into partition p_max;
SQL>alter table range_part_tab merge partition p201903, p_max into partition p_max;
两个关键字:merge、into
merge后买你跟着的是需要合并的两个分区名
into部分 为合并后的分区名,可以是新的,也可以沿用已存在的
分区的增删:
如果追加的分区界限比p_max还低,是不被允许的。在最后一个分区是less than(maxvalue)的情况下,是不能追加分区的,只能split分割。或者可以先删除p_max,再追加。
SQL>alter table range_part_tab drop partition p_max;
SQL>alter table range_part_tab add partition p201902 values less than (to_date('2019-02-01','yyyy-mm-dd'));
SQL>alter table range_part_tab add partition p201903 values less than (to_date('2019-03-01','yyyy-mm-dd'));
分区索引:
全局索引:即普通索引,与普通的建索引方式一样
局部索引:需要增加local关键字,相当于每个分区都分别建一个索引
全局索引好比一个大索引,局部索引好比多个小索引
--range_part_tab表的deal_date列建全局索引
SQL>create index idx_part_tab_date on range_part_tab(deal_date);
--对area_code列建局部索引
SQL>create index idx_part_tab_area on range_part_tab(area_code) local;
select segment_name,partition_name,segment_type,bytes/1024/1024,tablespace_name from user_segments where segment_name in ('IDX_PART_TAB_DATE');
索引状态查看
status为N/A表示是局部索引
表USER_IND_PARTITIONS进一步分析索引的状态
SQL>select index_name,status from user_indexs where index_name in ('IDX_PART_TAB_DATE','IDX_PART_TAB_AREA');
SQL>select index_name ,partition_name,status from user_ind_partitions where index_name in ('IDX_PART_TAB_DATE','IDX_PART_TAB_AREA');
--索引重建
SQL>alter index IDX_PART_TAB_DATE rebulid;
--oracle自己执行索引重建:update global indexes
--分区转移、合并、分割、增删,都可以用这个关键字
SQL>alter table range_part_tab truncate parttion p2 update global indexes;
假设索引高度为3,有100万条记录:
查询返回一条记录 ,需要3到4个IO;
查询返回100w条记录,需要100w乘3到4个IO,就是三四百万个IO,不如全表扫描;
如果表字段很多(上百个),占用的BLOCK就多,查询速度就慢下来了。
select count() from t ; --id 列建有索引
要使用到索引:
1.加不为空条件:select count() from t where id is not null;
2.给id列设为不允许为空:alter table t modify id not null;
3.给id列建立主键:alter table t add constraint pk1_id primary key (id);
若t表只有一个id字段,索引因为还有rowid,效率反而不如全表扫描。
select min(id),max(id) from t 用不到索引,一次执行只能从一个方向读索引数据,oracle无法用index full scan(min/max)这个算法同时在最左边和最右边读取;
select (select min(id) from t ) min,(select max(id) from t) max from dual;
distinct 会产生排序,建立索引可取消排序;
index fast full scan(可让逻辑读减少,但无法消除排序);
index full scan (可消除排序,但逻辑读比索引快速全扫描多);
union去除重复数据 union all未做筛选 (某些业务场景下,两个表不会重复,所以不用union 改为union all)
union all 需要排序
索引无法消除union all排序(index fast full scan);–这是两个不同的结果集的筛选,各自的索引无法奏效
创建主从表
--删除表
drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
--创建主表
create table t_p (id number, namevarchar2(20));
--创建主键
alter table t_p add constraint t_p_id_pk primary key (id);
--创建子表
create table t_c(id number,fid number, name varchar2(30));
--创建外键
alter table t_c add constraint fk_t_c foreing key(fid) references t_p(id);
--创建子表索引
create index ind_t_c_fid on t_c(fid);
主表子表级联删除:
先删除外键,然后根据on delete cascade关键字重建外键
alter table t_c drop constraint FK_T_C;
alter table t_c add constraint FK_T_C foreign key(fid) references t_p(id) on delete cascade;
改造主键:
若一个表某字段符合主键条件没有重复记录,但却只有一个普通索引,要改为主键该如何操作?
直接建主键即可。
drop table t cascade constraints purge;
create table t (id number, name varchar2(20));
create index idx_t_id on t(id);
--直接创建主键即可
alter table t add constraint t_id_pk primary key (id);
在等值查询情况下,组合索引的列无论哪一列在前,性能都一样;
组合索引的两列,当一列是范围查询,一列是等值查询的情况下,等值查询列在前,范围查询列在后,这样的索引才最高效。
索引过多对DML语句的影响:
1对insert语句负面影响最大,有百害无一利,只要有索引,插入就越慢,越多越慢。
2对delete语句来说,有好有坏,在海量数据库定位删除少数记录时,这个条件列时索引列显然是必要的,但是过多列有索引还是会影响明显,因为其他列的索引也要因此被更新。在经常需要删除大量记录的时候,危害加剧。
3对update语句的负面影响最小,快速定位少量记录并更新的场景和delete类似,但是具体修改某列时却有差别,不会触及其他索引列的维护。