2022年性能优化常用sql归类 .pdf

《2022年性能优化常用sql归类 .pdf》由会员分享，可在线阅读，更多相关《2022年性能优化常用sql归类 .pdf（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、天天好心情留下我的路迹，偶或停下身来看看。挡档灰尘，啪啪土，明天在路途。登录注册空间博客好友相册留言 oracle 常用命令收藏1. 增加主键 alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN); 2. 增加外键 alter table TABLE_NAME addconstraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME; 3. 使主键或外键失效、生效 alter table TABLE_NAME disable(

2、enable) constraint KEY_NAME; 4、查看各种约束 select constraint_name,table_name,constraint_type,status from user_constraints; 5、删除主键或外键 alter table TABLE_NAME drop constraint KEY_NAME; 6、在 initorcl.ora中加入 rollback_segments=(rb0,rb1,.) 其中 rb0,rb1为自己定义的回滚段，可使这些回滚段在启动时生效7、svgmgrshow sga 显示全局区8、copy 命令名师资料总结 -

3、- -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 38 页 - - - - - - - - - 格式 : copy from to () using 其中 :database string ；e.g:scott/tigeroracle 9、客户端注册表修改 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE NLS_LANG_BAK -语言字符集简体中文 :SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280 美国英文 :AMERICA.WE8IS

4、O8859P1 10、在 win95/win98中在注册表中 : HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE 填加一个字符串键值 local=oracle （可根据情况而定, 指数据库别名 ) 即可以在sql*plus中不用输入连接串或在 autoexec.bat 中添加 set local=alias_name 11、在 sqlplus快捷方式中， 在属性窗口中， 在目标中， 在d:orawin95binplus33w.exe后面加空格和 scott/tigeroracle 即可快速进入12、修改数据库的字符集在表 props$ 中 update props$ se

5、t value$=ZHS16CGB231280 13、 oracle 安全与审计 user_sys_privs,user_tab_privs; 配置文件主要参数 session_per_user 每个用户可同时进行几个会话 cpu_per_session 每个用户可用多少个(cpu 的) 百分之一秒 cpu_per_call 语法分析、执行、取数可用多少个百分之一秒 connect_time 用户连接数据库的时间( 分钟 ) idle_time 用户不调用数据库的时间( 分钟 ) logical_reads_per_session 整个会话过程中用户访问oracle的块数 logical_re

6、ads_per_call 一次调用用户访问oracle的块数 private_SGA 一个用户进程所用SGA的内存数量 composite_limit 复合限制数 failed_login_attempts 连续多次注册失败引起一个帐户死锁 password_life_time 一个口令在其终止前可用的天数 password_reuse_time 一个口令在其n 天才能重新使用 password_reuse_max 一个口令在重新使用之前必须改变多少次 password_lock_time 一个口令帐户被锁住的天数14、管理初始化文件 show parameters 经常修改的项目有 v$pa

7、rameter shared_pool_size 分配给共享的字节数 rollback_segments 回滚段的个数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 38 页 - - - - - - - - - sessions 会话个数 processes 进程个数15、管理控制文件控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。增加控制文件，在initorcl.ora中，找到control_file项, 增加一项即可删除控制文件，

8、在initorcl.ora中去掉，然后删除物理文件建立新的控制文件 create controlfile reuse set database 数据库名 logfile group 整数 文件名 ,group 整数 文件名 ,. 对于现有的数据库，可以间接地通过 alter database backup controlfile to trace命令生成控制文件，即可在orantrmb7trace 下有 ora00289.trc文件，其内容为文本16、管理回滚段: 存放事务的恢复信息建立回滚段 create public rollback segment SEG_NAME tabelspace

9、 TABLESPACE_NAME; alter rollback segment SEG_NAME online; 删除回滚段首先改变为offline状态直接使用回滚段 sqlset transaction use rollback segment SEG_NAME; 17、管理日志建立日志组 sqlselect * from v$logfile; sqlalter database add logfile group 3 (f:orantdatabaselog1_g3.ora f:orantdatabaselog2_g3.ora) size 100k; sqlselect * from v$

10、logfile; 删除日志组 alter database drop logfile group 1; 但是其物理文件并没有被删除掉系统至少需要2 个日志组，如果只有2 个，就不能删除不能删除正活动的日志组手工归档通过 alter system 的 archive log 子句来实现 archive log thread 整数 seq 整数 change 整数 currentgroup 整数 logfile 文件名 nextallstartto 位置 18 系统控制 alter system . alter system enable restricted session; 只允许具有rest

11、ricted系统特权的用户登录名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 38 页 - - - - - - - - - alter system flush shared_pool 清除共享池 alter system checkpoint 执行一个检查点 alter system set license_max_session=64,license_session_warning=54 会话限制为64，会话的警界为54 alter system set licens

12、e_max_session=0 会话数为无限制 alter system set license_max_users=300 用户限制为300 个 alter system switch logfile 强制执行日志转换19 会话控制 alter session alter session set sql_trace=true 当前会话具有sql 跟踪功能 alter session set NLS_language=French 出错信息设为法语 alter session set NLS_date_format=YYYY MM DD HH24:MI:SS;缺省日期格式 alter sess

13、ion set optimizier_goal=first_row改变优化方法为基于开销方法，具有快速响应速度 update studentteach set sold=sold+1 where sno=98010; commit; alter session close database link teach; 关闭远程链路20、封锁机制数据封锁 : 保护表数据，在多个用户并行存取数据时候，保证数据的完整性。 DML 操作又在两个级别获取数据封锁：指定记录封锁和表封锁表封锁可以有下列方式：行共享（RS ），行排他（RX ），共享封锁 （S），共享行排他 （SPX)和排他封锁 (X) 行共享表

14、封锁(RS), 允许其他事务并行查询、插入，修改和删除及再行封锁 select .from 表名 . for update of .; lock table 表名 in row share mode; 行排他表封锁(RX) 对该行有独占权利 insert into 表名 .; update 表名 .; delete from 表名 .; lock table 表名 in row exclusive mode; 允许并行查询、 插入、 删除或封锁其他行，但禁止其他事务使用下列命令进行并发封锁： lock table 表名 in share mode; lock table 表名 in share

15、 exclusive mode; lock table 表名 in exclusive mode; 共享表封锁 (S) lock table 表名 in share mode; 允许其他事务可在该表上做查询和再实现共享表操作，但不能修改该表，同时也不能做如下封锁： lock table 表名 in share row exclusive mode; lock table 表名 in exclusive mode; lock table 表名 in row exclusive mode; 共享排他表封锁(SRX) lock table 表名 in share row exclusive mode

16、; 排他表封锁 (SRX) lock table 表名 in exclusive mode; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 38 页 - - - - - - - - - 21、通常 oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务在 NT 上至少要启动两个服务 oraclestartID和 oracleserverID 22、每个数据库都有一个系统标识符(SID) ，典型安装

17、的数据库使用的系统标识符是ORCL 23、删除带约束的表 Drop table 表名 cascade constraints; 24、设置事务 set transaction read onlyread writeuse rollback segment 回滚段名 25、建外键单字段时： create table 表名 (col1 char(8), cno char(4) REFERENCE course); 多个字段时 , 在最后加上 Foreign Key (字段名 ) REFERENCE 表名 ( 字段 ) 连带删除选项 (on delete cascade 当指定时，如果父表中的记录被

18、删除，则依赖于父表的记录也被删除 REFERENCE 表名（） on delete cascade; 26、启动关闭数据库关闭： svrmgrconnect internal/oracle shutdown -正常关闭数据库 svrmgrshutdown immediate -立即关闭数据库 svrmgrshutdown abort -一种最直接的关闭数据库的方式，执行之后，重新启动需要花 6-8 小时启动： $svrmgrl svrmgrstartup -正常启动 -等价于 :startup nomount; alter database mount; alter database open

19、; svrmgrstartup mount; -安装启动：用于改变数据库的归档或执行恢复状态 svrmgrstartup nomount; -用于重建控制文件或重建数据库 svrmgrstartup restrict; -约束启动，能启动数据库，但只允许具有一定特权的用户访问如果希望改变这种状态，连接成功后 alter system disable restricted session; svrmgrstartup force;当不能关闭数据库时，可采用强制启动数据库来完成数据库的关闭操作。 svrmgrstartup pfile=d:orantdatabaseinitorcl.ora -带初

20、始化参数文件的启动27、缺省用户和密码 . Oracle安裝完成后的初始口令? internal/oracle sys/change_on_install system/manager scott/tiger 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 38 页 - - - - - - - - - sysman/oem_temp . ORACLE9IAS WEB CACHE的初始默认用户和密码？ administrator/administrator28、对公共授予访

21、问权 grant select on 表名 to public; create public synonym 同义词名 for 表名 ; 29、填加注释 comment on table 表名 is 注释 ; comment on column 表名 . 列名 is 注释 ; 30 oracle loader 控制文件的格式 load data infile 数据文件名 into table 表名 (first_name position(01:14) char, surname position(15:28) char, clssn position(29:36) char, hire_da

22、ta position(37:42) date YYMMDD) 31、程序中报错:maxinum cursor exceed! 应该调整数据库初始化文件加如一项 open_cursors=200 32、生成用户时指定缺省表空间 create user 用户名 identified by 口令 default tablespace 表空间名 ; 33、重新指定用户的缺省表空间 alter user 用户名 default tablespace 表空间名34、约束条件 create table employee (empno number(10) primary key, name varchar

23、2(40) not null, deptno number(2) default 10, salary number(7,2) check salaryexec my_execute(select * from tab); sqlexec my_execute(insert into test value|(|ddd|); sqlexec my_execute(commit); 对于查询方面的可以如下方式：比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。 create or replace procedure proc_test as v_curid integer; v_

24、result integer; v_strSql varchar2(255); v_userid okcai.userid%type; v_username okcai.username%type; begin v_strSql := select * from okcai_|to_char(sysdate,yyyymm); v_curid := dbms_sql.open_cursor; dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7); dbms_sql.define_column(v_curid,1,v_userid); dbms_sql.defi

25、ne_column(v_curid,2,v_username,10); -必须指定大小 v_result := dbms_sql.execute(v_curid); loop if dbms_sql.fetch_rows(v_curid) = 0 then exit; -没有了，退出循环 end if; dbms_sql.column_value(v_curid,1,v_userid); dbms_sql.column_value(v_curid,2,v_username); dbms_output.put_line(v_userid); 名师资料总结 - - -精品资料欢迎下载 - - -

26、- - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 38 页 - - - - - - - - - dbms_output.put_line(v_username); end loop; dbms_sql.close(v_curid); end;46.1 用 EXECUTE IMMEDIATE . 在 PL/SQL 运行 DDL语句 begin execute immediate set role all; end; . 给动态语句传值(USING 子句 ) declare l_depnam varchar2(20) := te

27、sting; l_loc varchar2(10) := Dubai; begin execute immediate insert into dept values (:1, :2, :3) using 50, l_depnam, l_loc; commit; end; . 从动态语句检索值(INTO 子句 ) declare l_cnt varchar2(20); begin execute immediate select count(1) from emp into l_cnt; dbms_output.put_line(l_cnt); end; . 动态调用例程 . 例程中用到的绑定

28、变量参数必须指定参数类型. 黓认为 IN 类型 , 其它类型必须显式指定 declare l_routin varchar2(100) := gen2161.get_rowcnt; l_tblnam varchar2(20) := emp; l_cnt number; l_status varchar2(200); begin execute immediate begin | l_routin | (:2, :3, :4); end; using in l_tblnam, out l_cnt, in out l_status; if l_status != OK then dbms_outp

29、ut.put_line(error); end if; end;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 38 页 - - - - - - - - - . 将返回值传递到PL/SQL记录类型 ; 同样也可用 %rowtype 变量 declare type empdtlrec is record (empno number(4), ename varchar2(20), deptno number(2); empdtl empdtlrec; begin execu

30、te immediate select empno, ename, deptno | from emp where empno = 7934 into empdtl; end; . 传递并检索值 .INTO 子句用在USING子句前 declare l_dept pls_integer := 20; l_nam varchar2(20); l_loc varchar2(20); begin execute immediate select dname, loc from dept where deptno = :1 into l_nam, l_loc using l_dept ; end; .

31、 多行查询选项 . 对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾 . declare l_sal pls_integer := 2000; begin execute immediate insert into temp(empno, ename) | select empno, ename from emp | where sal :1 using l_sal; commit; end; . 完成 update 的 returning功能 update可以用 returning返回修改以后的值。比如: UPDATE employee

32、s SET job_id = SA_MAN, salary = salary + 1000, department_id = 140 WHERE last_name = Jones RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3; 用 execute immediate来完成的时候，可以用 declare l_sal pls_integer; begin 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -

33、- - - 第 12 页，共 38 页 - - - - - - - - - execute immediate update employees SET salary = salary + 1000 where last_name=okcai RETURNING INTO :1 returning into v_sql; commit; end; 47、用 ref cursor来完成动态游标的功能declare type ct is ref cursor; cc ct; v_notify acc_woff_notify%rowtype; begin open cc for select * f

34、rom acc_woff_notify; loop fetch cc into v_notify; exit when cc%notfound; dbms_output.put_line(v_notify.done_code); end loop; close cc; end; 48、重新编译 sqlexec dbms_pile_schema(schema); 如： sqlexec dbms_pile_schema(scott); 49、去除数据库中特殊字符 .字符串字段中含有,如果用来组合sql 语句，会造成语句不准确。比如： replace(f1,) .字符串字段中含有t n, 如果用来在

35、c 或者 c程序中输出到文件，格式无法保证。比如： replace(f2,t,) .清除换行和回车比如 : replace(f2,chr(13)|chr(10),) 50. 如果希望用aimtzmcc 用户连接数据库，访问aicbs 用户的表，不在表名前缀aicbs.，可以在建立数据库连接后发下面的命令 alter session set current_schema = aicbs;51. 查询锁的原因如果进程被死锁，可以按下面方式查询 从 v$session或者 v$locked_object找到此 session 如果有 lockwait，查询 v$lock, select * from

36、 v$lock where kaddr = C00000024AB87210 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 38 页 - - - - - - - - - 如果没有，根据sid select * from v$lock where sid = 438 查看 v$lock lmode 0，表示已经得到此锁 request 0 表示正在请求此锁根据 id1 和 id2 的值可以判断请求哪个锁的释放。 select * from v$lock where id

37、1=134132 and id2 = 31431 52.oracle8中扩充了group by rollup和 cube 的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); 下面的语句可以按照rollup不同的字段进行小计 select region_code,write_status,count(*) from aicbs.acc_woff_notify group by region_code,rollup(writ

38、e_status); select region_code,write_status,count(*) from aicbs.acc_woff_notify group by rollup(region_code,write_status);53. 查询正在执行语句的执行计划( 也就是实际语句执行计划) select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111); 其中 id 和 parent_id表示了执行数的结构, 数值最大的为最先执行比如ID PAR

39、ENT_ID - 0 1 0 2 1 3 2 4 3 5 4 6 3 -则执行计划树为 0 1 2 3 6 4 554.alter table 语句详细说明 1 修改表的属性 物理属性： PCTFREE,PCTUSED,INITRANS,MAXTRANS和存储特征。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 38 页 - - - - - - - - - logging 数据段压缩 2 修改字段 增加字段 修改字段 删除字段 字段改名 modify collectio

40、n retrieval modify LOB storage alter varray col 3 修改约束 4 修改表的分区 5 alter external table 6 move table 7 enable disable55. oracle中的裸设备指的是什么? 裸设备就是绕过文件系统直接访问的储存空间56. oracle如何区分 64-bit/32bit 版本？$ sqlplus / AS SYSDBA SQL*Plus: Release 9.0.1.0.0 - Production on Mon Jul 14 17:01:09 2003 (c) Copyright 2001 O

41、racle Corporation. All rights reserved. Connected t Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production SQL select * from v$version; BANNER - Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production PL/SQL Release 9.0.1.0.0

42、 - Production CORE 9.0.1.0.0 Production TNS for Solaris: Version 9.0.1.0.0 - Production NLSRTL Version 9.0.1.0.0 - Production SQL57. 怎样计算一个表占用的空间的大小？select owner,table_name, NUM_ROWS, BLOCKS*AAA/1024/1024 Size M, EMPTY_BLOCKS, LAST_ANALYZED from dba_tables where table_name=XXX; 名师资料总结 - - -精品资料欢迎下载

43、- - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 38 页 - - - - - - - - - Here: AAA is the value of db_block_size ; XXX is the table name you want to check58. 如何查看最大会话数？ SELECT * FROM V$PARAMETER WHERE NAME LIKE proc%; SQL SQL show parameter processes NAME TYPE VALUE - - - aq_tm_proce

44、sses integer 1 db_writer_processes integer 1 job_queue_processes integer 4 log_archive_max_processes integer 1 processes integer 200 这里为 200 个用户。 select * from v$license; 其中 sessions_highwater纪录曾经到达的最大会话数59. 如何以 archivelog的方式运行oracle 。init.ora log_archive_start = true RESTART DATABASE60. 数据表中的字段最大数是

45、多少? 表或视图中的最大列数为 100061. 怎样查得数据库的SID ? select name from v$database; 也可以直接查看 init.ora文件63. 如何在 Oracle 服务器上通过SQLPLUS 查看本机IP 地址 ? select sys_context(userenv,ip_address) from dual; 如果是登陆本机数据库，只能返回127.0.0.164. unix 下怎么调整数据库的时间？su -root date -u 08010000 65. 在 ORACLE TABLE 中如何抓取MEMO 類型欄位為空的資料記錄? select rema

46、rk from oms_flowrec where trim( from remark) is not null ;66. P4 電腦安裝方法將 SYMCJIT.DLL改為 SYSMCJIT.OLD 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 38 页 - - - - - - - - - 67. 如何查詢SERVER 是不是 OPS? SELECT * FROM V$OPTION; 如果 PARALLEL SERVER=TRUE則有 OPS能68. 何查詢每個用戶的

47、權限? SELECT * FROM DBA_SYS_PRIVS; 69. 如何將表移動表空間? ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME; 70. 如何將索引移動表空間? ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME; 71. 在 LINUX,UNIX 下如何啟動DBA STUDIO? OEMAPP DBASTUDIO 72. 查詢鎖的狀況的對象有? V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA, V$PROCESS ; 查詢鎖的表的

48、方法:SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, TO_CHAR(LMODE) MODE_HELD, DECODE(REQUEST, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, TO_CHAR(REQUEST) MODE_RE

49、QUESTED, O.OWNER|.|O.OBJECT_NAME| (|O.OBJECT_TYPE|), S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;73. SQLPLUS 下如何修改編輯器? DEFINE _EDITOR= - 必须加上双引号来定义新的编辑器，也可以把这个写在$ORACLE_HOME/sqlplus/admin/glogin.sql里面使它永久

50、有效。74. ORACLE產生隨機函數是? DBMS_RANDOM.RANDOM75. LINUX 下查詢磁盤競爭狀況命令? Sar -d 76. LINUX 下查詢 CPU競爭狀況命令? sar -r 77. 查看數據庫字符狀況? SELECT * FROM NLS_DATABASE_PARAMETERS; SELECT * FROM V$NLS_PARAMETERS; 78. 查詢表空間信息? SELECT * FROM DBA_DATA_FILES; 79. 如何查看各个表空间占用磁盘情况？名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - -