目录
- 一、关于实例级别
- 二、关于表操作：
- 三、关于权限和角色
- 四、关于约束和索引
- 五、关于锁
- 六、关于进程
- 七、关于日志挖掘——关于补充日志和logminer数据挖掘
- 八、关于rman及脚本
- 九、关于日志
- 十、关于表空间查询脚本
- 十一、关于SQL 调优顾问的使用
- 十二、跟踪SQL语句
- 十三、关于游标
- 十四、关于死锁
- 十五、关于自动优化器
- 十六、其他
一、关于实例级别
1、查看oracle进程用户信息
SELECT * FROM v$session WHERE paddr IN (SELECT addr FROM v$process WHERE spid = 423565);
2、关于系统级别
SQL> spool /data/out.log --spool 执行后，会输出spool之后所有的sql语句，到操作系统中的指定文件 SQL> spool off; --关闭spool输出 SQL> show error; --上一次执行sql，出现的错误 SQL> host ls -lt --查看系统操作 SQL> @1.sql --执行脚本
3、set命令
SQL>set time on; SQL>set timing on; SQL>set autotrace on; SQL>set autotrace off; SQL>set linesize 200; SQL>set pagesize 10000; SQL>set long 10000;
4、关于时间格式
SQL> select sysdate from dual; --dual是一张神奇的表
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual; --将系统时间以年月日方式显示
SQL> select to_char(systimestamp,'yyyymmdd hh24:mi:ssxff') from dual; --将系统时间以年月日展示且秒显示到小数点后6位
SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; --2020-02-26——把数值转换为字符
SQL> select to_number('123') from dual; --123——字符转换为数值
SQL> select to_date('20211112','yyyymmdd') from dual; --12-NOV-21——把数值转换为日期
二、关于表操作：
1、基本表操作
SQL> create table student(sno number(6),sname varchar2(10),birthday date); --创建表 SQL> alter table student add telephone varchar2(11); --增加表中的列 SQL> alter table transcript3 modify grade char(10); --修改表中列的属性值 SQL> alter table student drop column telephone; --删除表某一列 SQL> alter session set nls_date_format='yyyymmdd'; --更改当前会话的时间显示格式（30-AUG-19)为20190830，退出当前sql会话后，自动回复30-AUG-19格式 SQL> select * from student where sname is null;— --查询当前表中空值的列，不能使用sname = null，需要使用sname is null SQL>create table student2 as select * from student; --复制表（复制student表所有结构及内容到student2） SQL>create table student3 as select * from student where 1>2; --复制表结构，不复制内容 SQL>truncate table student4; --清空表数据，表结构不变，清空时不写入日志，所以无法还原，慎用 SQL>delete table student4; --删除表数据，清空时，写入日志，可以还原 SQL>select * from student2 where upper(sname)='A'; --查找sname为大写的值 SQL>select * from student2 where sname like 'A%'; --查询列sname中以A开头的 SQL>select * from student2 where length(sname)=4; --查询sname中长度为4的行， SQL>select * from student2 order by sno desc; --按sno降序插叙 SQL>select * from student2 order by sno asc,birthday; --别名使用，如果中间没有空格，可以直接使用别名；如果别名中间有空格，需要加双引号 SQL>select sno,sum(score) from Transcript group by sno; --查出每个学生的成绩总和 SQL>select km,avg(score) from Transcript group by km; --查询每门课程（KM）的平均分数 SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>60); --查询（按照sno分组，即针对每个人），平均分数大于60的学生的学号和平均成绩 SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>(select avg(score) from Transcript)); --查询（按照sno分组，即针对每个人），平均分数大于所有学生平均成绩的学生的学号和平均分数 SQL>select sno,avg(score) from Transcript group by sno having(avg(score)>60) order by sno desc; --查询（按照sno分组，即针对每个人），平均分数大于60的学生的学号和平均成绩，并按照学号的降序排列（说明了几个谓词的顺序） SQL>insert into student(sno,sname) values(4,'sunliu');
2、关于多表查询：
SQL> select s.sno,s.sname,a.sno,a.zz from student s inner join address a on s.sno=a.sno; --连接表方法1 SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno; --连接表方法2 SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno=a.sno(+); --左外连接1 SQL> select s.sno,s.sname,a.sno,a.zz from student s left outer join address a on s.sno=a.sno; --左外连接2 SQL> select s.sno,s.sname,a.sno,a.zz from student s,address a where s.sno(+)=a.sno; 右 --外连接1 SQL> select s.sno,s.sname,a.sno,a.zz from student s right outer join address a on s.sno=a.sno; --右外连接2 SQL>select s.sno,s.sname,a.sno,a.zz from student s full outer join address a on s.sno=a.sno; --全连接 SQL> rename student to newstudent; --命名表 SQL> alter table newstudent rename column sno to newsno; --重命名一张表的某个列
3、复杂SQL:
from——where——group by——having——select——order by
SQL>select * from student where exists(select * from address where zz='zhengzhou'); --EXISTS的使用 SQL>select xm,count(*) from student2 group by xm; --group by QL>select * from student2 where xm in(select xm from student2 group by xm having(count(*)>1)); --having SQL>select a.*,rowid from student6 a; --rowid的应用
三、关于权限和角色
1、权限--用户及参数：
SQL> create user test1 identified by test1; --创建用户，参数为默认文件 SQL>create profile pro1 limit failed_login_attempts 2 password_lock_time 2; --创建参数文件（用于设置用户 SQL> alter user test1 profile pro1; --把用户的参数文件改为新的pro1; SQL> create user test2 identified by test2 profile pro1; --创建用户，并定义参数文件 SQL> alter user test1 account unlock; --解锁用户 SQL> alter profile pro1 limit password_life_time 10 password_grace_time 2; --更改参数文件
2、权限--系统权限：
SQL>GRANT CONNECT TO test; --授予用户connect权限 SQL>GRANT RESOURCE TO test; --授予用户resource权限 SQL>GRANT create table to test； --授予用户创建表权限 SQL>GRANT UNLIMITED TABLESPACE to test; --授予用户无限制表空间访问 SQL>grant create any table to test; --授予创建任何表的用户 SQL>select * from dba_sys_privs d where d.grantee='TEST1'; --查看用户拥有的权限 SQL> grant create session to test2 with admin option; --权限继承(GRANT 最后增加with admin option） SQL>REVOKE create session FROM test1; --收回权限
3、权限--对象权限：
SQL>revoke select on scott.student from test2 --收回对象权限 SQL>GRANT SELECT ON SCOTT.EMP TO test1; --允许用户查询 EMP 表的记录 SQL>GRANT UPDATE ON SCOTT.EMP TO test1; --允许用户更新 EMP 表中的记录 SQL>GRANT ALL ON SCOTT.EMP TO test1; --允许用户插入、删除、更新和查询EMP 表中的记录 SQL>GRANT SELECT ON SCOTT.EMP TO test1 with grant option; --继承grant 对象权限（9i以及之前是无此功能的）
4、角色管理：
SQL>CREATE ROLE role_name; --创建角色 SQL>grant role1 to test2; --将角色给用户 SQL>grant create session ,create any table, drop any table to role1; --授予角色权限 SQL>select * from dba_sys_privs d where d.grantee='CONNECT'; --查询用户具有的系统权限 SQL>select * from dba_roles; --查看有多少角色 SQL>select * from role_sys_privs; --查看当前用户的角色具有的系统权限 SQL>select * from dba_sys_privs d where d.grantee='TEST1'; --查看用户具有的系统权限：（system或sys用户查询） SQL>select * from dba_tab_privs where rownum<20 and grantee='DBA'; --查看角色具有的对象权限； SQL>select * from dba_tab_privs where rownum<20 and grantee='TEST1'; --查看用户具有的对象权限； SQL>select * from dba_tab_privs where rownum<20 and grantee='TEST1'; --更改用户口令，删除用户 SQL> alter user test1 identified by test1； --更改用户口令 SQL> drop user test1 cascade; --删除用户
四、关于约束和索引
1、完整性约束：
SQL> create table yg2(bh number not null,xm varchar2(8)); --查询约束（只有一个not null约束，名称是系统根据规则随机产生 SQL> create table yg3(bh number(4) not null check (bh>0 and bh<10000), xm varchar2(8)); --check约束 SQL> create table yg4(bumenID number not null,bumenneiID number,xm varchar2(8),unique(bumenID,bumenneiID)); --unique约束（会自动创建索引） SQL> create table gz_newYG(bh number(4),xm varchar2(8),gz number(10) default 1000 ); --default约束 SQL>create table student(xh number(6) constraint code_pk primary key , xm varchar2(20)); --primary key示例(此列不允许为空，而且ORACLE会自动为主键列创建索引，这里的 primary key约束由用户显式起名)： SQL>create table address (xh number(8) ,zz varchar2(50) , foreign key(xh) references student(xh)); --foreign key示例(实现两个表之间参照与被参照的关系，外键只能取主键已经有的值，这里的foreign key由系统自动起名): SQL>alter table user7 disable primary key; --禁用主键约束 SQL>alter table user7 enable primary key; --启用主键约束
2、查询是否有外键约束：
select * from user_constraints where constraint_type <>'C' and constraint_type <>'P' and constraint_type <>'U' ;
3、查看此表的表索引占据空间大小，以及索引高度：
select index_name,index_type,table_owner,table_name,compression,tablespace_name,blevel,leaf_blocks,num_rows,partitioned,visibility from user_indexes where table_owner='MESPRD' and table_name='SFC_BROOKNER_PARAM_LOG' order by num_rows;
五、关于锁
1、锁-行级锁：
SQL>select * from emp for update; --锁定emp表，不可以update,但可以insert into; SQL>select * from v$lock; --查看视图，TM表级锁，TX行级锁 SQL>select ADDR,KADDR,SID,TYpe,ID1 from v$lock where type='TM'or type='TX'; SQL>select ... for update wait 5； --锁定一张表或某几行，只等待5秒钟，如果超过5秒钟仍被其他人占用锁定，就不进行等待了 SQL>select ... for update nowait ； --锁定一张表或某几行，等待，被其他人占用锁定，就返回错误。 SQL>rollback; --解锁
2、锁-表级锁：
SQL>lock table emp in row share mode; --行级共享 SQL>lock table emp in share mode; --共享锁 SQL>lock table epm in row exclusive mode; --行级排他锁 SQL>lock table emp in share row exclusive mode; --共享航机排他锁 SQL>lock table emp in exclusive mode --排他锁
六、关于进程
1、会话，进程：
SQL> select username from v$session where username='SCOTT'; --查看用户SCOTT的会话数 SQL>select * from v$procession; --sys或system可以查看 SQL>disconnect --断开连接；
2、ORACLE实例的后台进程
SQL>show paramter db_wr; --查看DBWR的数量【只有sys用户可以查看】 SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%DBW%'; --查看DBWR的进程 SQL> select PROGRAM, PGA_USED_MEM from v$process where Program like'%LGW%'; --查看LGWR的进程 SQL> show parameter statistics;
七、关于日志挖掘——关于补充日志和logminer数据挖掘
1、开启补充日志
在需要分析的数据库中开启补充日志。
select SUPPLEMENTAL_LOG_DATA_MIN from v$database; alter database add supplemental log data;
2、设置一个字典文件路径（需要重启 DB）：
show parameter utl_file_dir alter system set utl_file_dir='/oracle' scope=spfile;
3、创建一个数据字典文件
exec dbms_logmnr_d.build('dict.ora','/oracle');
4、建立日志分析列表
（1）添加第一个日志
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.new);
（2）继续添加日志
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile(logfilename=>'日志文件'，options=>dbms_logmnr.addfile);
execute dbms_logmnr.add_logfile('日志文件'，dbms_logmnr.addfile);
（3）移除日志
execute dbms_logmnr.remove_logfile(logfilename=>'日志文件');
5、启动分析
（1）无条件分析
exec dbms_logmnr.start_logmnr(DictFileName =>'/oracle/dict.ora');
（2）有条件分析
exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',startscn=>123,endScn => 124);
exec dbms_logmnr.start_logmnr(DictFileName=>'/oracle/dict.ora',starttime=> to_date('2016-08-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),endtime=> to_date('2016-08-15 01:00:00','YYYY-MM-DD HH24:MI:SS');
（3）可以使用COMMITTED_DATA_ONLY参数来只显示那些被提交的事务：
EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);
（4）有条件分析的选项：
scn: startscn,endScn time: starttime,endtime
6、查看日志分析结果
select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents; select username,scn,timestamp,sql_redo,sql_undo from v$logmnr_contents where seg_owner='TEST' and seg_name='TEST01';
7、结束分析
dbms_logmnr.end_logmnr;
八、关于rman及脚本
1、rman备份功能
（1）RMAN> backup tag itpux_db01_fullbak format '/backup/full/itpux_db01_full_%s_%p_%t' (database); --手动备份数据库全库 （2）RMAN> backup tag itpux_db01_arch format '/backup/arch/itpux_db01_arch_%s_%p_%t' archivelog all; --手动备份归档日志 （3）备份脚本：rman备份脚本 （4）备机各种单句 backup tag LLL1_full format '/oracle/backup/full/LLL1_full_db01_%s_%p_%t_%c' (database); backup tag LLL1_file format '/oracle/backup/full/LLL1_datafile_db01_%s_%p_%t_%c' (datafile 1,2,3); backup tag LLL1_ctl format '/oracle/backup/full/LLL1_ctl_db01_%s_%p_%t_%c' (current controlfile); backup tag LLL1_pfile format '/oracle/backup/full/LLL1_pfile_db01_%s_%p_%t_%c' (spfile);
2、脚本
见：https://www.topunix.com/post-6082.html
九、关于日志
1、查看近来数据库生成的撤销数据量（undo)：
ALTER SESSION SET nls_date_format = 'dd-mm-yy hh24:mi:ss';
SELECT begin_time, end_time, (undoblks * (SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size')) undo_bytes FROM v$undostat;
2、闪回日志恢复功能、还原点创建功能
（1）SQL>archive log list; --闪回前提，必须要归档模式
（2）SQL> alter database flashback on/off --闪回启停
（3）SQL> select flashback_on from v$database; --闪回查询：
（4）SQL> CREATE RESTORE POINT restorepoint1; --正常还原点的创建：
（5）SQL> select * from dictionary where table_name like '%RESTORE%'; --从字典查看还原点信息
（6）SQL> select * from V$RESTORE_POINT; --查看还原点：
（7）SQL> FLASHBACK database TO RESTORE POINT restorepoint1; --闪回值还原点：
（8）SQL> FLASHBACK DATABASE [<database_name>] TO SCN <system_change_number> --sqlplus下基于SCN 闪回
（9）SQL> FLASHBACK DATABASE [<database_name>] TO TIMESTMP <system_timestamp_value> --sqlplus下基于时间戳闪回
（10）SQL> FLASHBACK DATABASE [<database_name>] TO RESTORE POINT <restore_point_name> --sqlplus下基于还原点闪回
（11）RMAN> FLASHBACK DATABASE TO SCN=1121679; --RMAN 下基于SCN闪回
（12）RMAN> FLASHBACK DATABASE TO TIME ="TO_DATE('2013-10-10 19:25:21','YYYY-MM-DD HH24:MI:SS')"; --RMAN 下基于时间闪回
（13）RMAN> FLASHBACK DATABASE TO SEQUENCE=56 THREAD=1; --RMAN 下基于归档序号
（14）SQL> create restore point before_drop guarantee flashback database; --担保还原点的创建（建议mount下）
（15）SQL>alter database open read only; --建议更改数据库先为只读，确认好了后，在resetlogs打开，否则前面的保存点就无法再使用了。
十、关于表空间查询脚本
1、查询大字段的表，查询用户下最大表占用空间
select a.owner,a.table_name,a.column_name,a.tablespace_name,bytes/1024/1024/1024 from all_lobs a,dba_segments b where a.owner='NCC' and a.tablespace_name='USERS' and a.segment_name=b.segment_name order by b.bytes desc;
2、常用表空间查询语句（LLL总结）
查询最大表空间容量、已扩展在用容量、已使用容量、空闲容量、使用百分比
解析：表空间容量定义
--表空间的总容量等于所有数据文件的maxbytes（自动扩展)+bytes（非自动扩展) ，定义为maxtotal(数据来源：dba_data_files)
--表空间的扩展的容量等于所有数据文件的bytes之和，定义为total (数据来源:dba_data_files)
--表空间的已扩展的容量中有一部分空闲容量，定义为free（数据来源:dba_free_space中的free容量）
--表空间的实际空闲容量等于maxtotal-total+free的容量
select a.tablespace_name, round(maxtotal,4) as maxtotal_GB, round(total,4) as extended_Total_GB, round((total-free),4) as usage_GB, round((maxtotal-total+free),4)as free_GB,round((1-(total-free)/maxtotal)*100,4)||'%' as free_percent from (select tablespace_name, SUM(bytes)/1024/1024/1024 as total,SUM(case autoextensible when 'YES' then maxbytes else bytes+maxbytes end )/1024/1024/1024 as maxtotal from dba_data_files group by tablespace_name) a, (select tablespace_name,sum(bytes)/1024/1024/1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by free_percent desc;
3、对某个用户授予某个表空间的访问权限
alter user mes quota unlimited on test_tbsp;
4、创建表空间格式常用（LLL总结）
create tablespace MES_PRD datafile '/oracle/app/oracle/oradata/mesorcl/MES_PRD_DATA.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
十一、关于SQL 调优顾问的使用
1、手动执行sys下的SQL_TUNING_TASK任务并调优
（1）查看clientname为sql tuning advisor是否开启
select * from dba_autotask_client;
如果开启，就会随下述窗口执行
select * from dba_scheduler_windows;
（2）查看sql优化任务，对应上述优化建议
select * from dba_advisor_tasks where task_name = 'SYS_AUTO_SQL_TUNING_TASK';
（3）查看自动优化任务的参数
select * from dba_advisor_parameters where task_name like 'SYS_AUTO_SQL_TUNING%' and (parameter_name like '%TIME_LIMIT%' or parameter_name like '%TIME_LIMIT%');
默认自动优化任务是1200秒（20分钟）。如果超时了，就会自动停止。
（4）修改优化执行时间
①每条sql最大时间--Time limit per statement in a SQL Tuning Set
设置10800s=3小时。
begin
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 10800);
end;
②整个分析任务执行时间--The maximum time that an analysis can execute
设置36000s=10小时。
begin
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 36000);
end;
（5）手动执行sql优化任务
SQL> exec dbms_sqltune.execute_tuning_task(task_name=>'SYS_AUTO_SQL_TUNING_TASK');
（6）取消正在进行的sql_tuning任务
exec dbms_sqltune.cancel_tuning_task('SYS_AUTO_SQL_TUNING_TASK');
（7）查看优化建议
set linesize 80 pagesize 0 long 100000 --注意，此句必须要加上，否则下面显示不出来，
select dbms_auto_sqltune.report_auto_tuing_task from dual;
或:
select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
（8）生成自动调优建议的sql
set linesize 132 pagesize 0 long 100000
select dbms_sqltune.script_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
2、SQL自动优化步骤(手动执行单个sql语句或id）
登录访问用户账号，如:mes_prd
（1）获取sql_id
从v$sql或awr中（通过开发提供的开发语句，或awr慢的语句）
select SQL_TEXT, SQL_FULLTEXT, SQL_ID from v$sql where SQL_TEXT like 'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%';
或者通过如下方法：
①获取确定登录用户的系统进程，
ps -ef|grep LOCAL=NO --比如新登录的，则进程号最大，如：
②再通过如下语句得到sql_id
select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid,s.sql_id
from v$session s,v$process p
where s.paddr=p.addr and p.spid=17408;
（2）创建调优任务
①方法1：根据sql_id
declare tune_task varchar2(30); tune_sql clob; begin tune_task :=DBMS_SQLTUNE.create_tuning_task( sql_id =>'26vvbvs4mawf1', task_name => 'test_sql_tuning' ); end;
②方法2：根据sql_text
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select b.logtime, a.name, a.cardid, b.amount, b.goods,b.score
from lll_member a, lll_sales b
where a.region = ''SDS''
and a.recommend = ''LX''
and b.logtime > to_date(''2016-12-15'', ''yyyy-mm-dd'')
and a.cardid = b.cardid
order by b.logtime';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text =>my_sqltext,
user_name => 'MES_PRD',
scope =>'COMPREHENSIVE',
time_limit => 600,
task_name =>'test_sql_tuning',
description => 'Task to tune a query on a specified table');
END;
/
--SQL 单引号变双引号
（3）登录sql，获取当前会会话的跟踪文件
oradebug setmypid oradebug tracefile_name 获取到文件:/oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_ora_75586.trc
（4）手动运行SQL调优顾问
exec dbms_sqltune.execute_tuning_task(task_name=>'test_sql_tuning');
（5）查看任务执行状态（完成/执行中/异常）
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_sql_tuning';
（6）关闭跟踪
oradebug close_trace;
（7）查看调优建议内容
set long 1000000 longchunksize 1000000
set linesize 300 pagesize 0
select dbms_sqltune.report_tuning_task('test_sql_tuning') from dual;
或查看上述文件
less /oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_ora_75586.trc
（8）删除任务
BEGIN DBMS_ADVISOR.DELETE_TASK( task_name => 'test_sql_tuning' ); END;
（9）查看执行进度
-- 查看SQL Tuning Advisor的进展（task执行很久）---好像执行完成才能看到？ col opname for a20 col ADVISOR_NAME for a20 SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK FROM V$ADVISOR_PROGRESS WHERE USERNAME = 'TEST';
3、SQL自动优化步骤--方法2-流程和2一样，但多了profile建议
（1）根据sql查询sqlid
select SQL_TEXT, SQL_FULLTEXT, SQL_ID from v$sql where SQL_TEXT like 'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%';
（2）为SQL_id创建一个STA（SQL Tuning advisor）分析任务（使用SYS用户执行）
variable sta_task VARCHAR2(100); exec :sta_task:=dbms_sqltune.create_tuning_task(sql_id=>'2k22yt1p1ckbr',time_limit=>72000,task_name=>'MyTask1');
（3）执行上面创建的STA（需要一定的时间）
exec dbms_sqltune.execute_tuning_task(task_name=>'MyTask1');
（4）查询优化任务创建与执行的情况
select * from dba_advisor_tasks a, dba_advisor_log b where a.task_id = b.task_id and a.task_name = 'MyTask1';
（5）--查看任务优化报告（优化的详细内容）
select dbms_sqltune.report_tuning_task('MyTask1') from dual;
---select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK') from dual;
（6）接受建议的 SQL 概要文件,即创建SQL_Profle
execute dbms_sqltune.accept_sql_profile( task_name => 'MyTask1',task_owner =>'sys', replace => TRUE);
（7）查看第5步创建起来的SQL_Profile信息
select a.name, a.task_id, a.created from dba_sql_profiles a, dba_advisor_log b where a.task_id = b.task_id and b.task_name = 'MyTask1';
（8）再次执行SQLID为‘dza1z9nnup2wb’的语句
--执行时间由原来的50秒降为1秒
--查看执行计划，执行计划中包含下面信息，说明是使用了创建的SQL_Profile及索引所起到的效果
（9）删除SQL_Profile（删除执行计划）
exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_017acc4af5ea0001');
（10）删除优化任务
exec dbms_sqltune.drop_tuning_task(task_name => 'MyTask1');
----------------------------------------------利用提前建立的存储过程-------------------------------------------
create or replace procedure p_create_sqltuning_task(p_sql_id varchar2) is
v_tuning_task varchar2(30);
（STA）根据sql语句查询sqlid后进行STA分析
2021年7月22日 13:33
分区 数据库性能调整 的第 1 页
v_tuning_task varchar2(30);
v_sql_id v$session.sql_id%type;
begin
v_sql_id := p_sql_id;
v_tuning_task := dbms_sqltune.create_tuning_task(sql_id => v_sql_id);
dbms_sqltune.execute_tuning_task(v_tuning_task);
dbms_output.put_line('This Tuning task name is : ' || v_tuning_task);
dbms_output.put_line('-------------Please using follow command query SQL tuning report!------------');
dbms_output.put_line('set linesize 200 pagesize 9999');
dbms_output.put_line('set long 100000');
dbms_output.put_line('select dbms_sqltune.report_tuning_task(''' ||
v_tuning_task || ''') from dual;');
end;
--查询sqlid
select SQL_TEXT, SQL_FULLTEXT, SQL_ID
from v$sql
where SQL_TEXT like
'SELECT substr(ic_purchasein_b.dbizdate, 1, 10) dbizdate%'
--执行存储过程获取STA建议
SYS@racdb> exec p_create_sqltuning_task(' 2k22yt1p1ckbr')
This Tuning task name is : TASK_76380
-------------Please using follow command query SQL tuning report!------------
set linesize 200 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_76380') from dual;
PL/SQL procedure successfully completed.
--查看STA建议
set linesize 500 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_76556') from dual;
--删除任务
exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_76380');
exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_%');
十二、跟踪SQL语句
1、根据根据sid和serial#跟踪语句
（1）获取当前用户登录mes时，操作系统的PID，通过新增查看。
ps -ef|grep LOCAL=NO --24696
（2）获取登录用户的sid和searial#
select * from v$session where username='MES_PRD';
（3）进一步根据pid确认sid和searial#--sid=191,serial#=319
select s.SADDR,SID,s.serial#,s.paddr,s.username,s.status,s.machine,s.program,s.prev_SQL_ID,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=25608;
（4）开启SQL跟踪
execute dbms_monitor.session_trace_enable(session_id =>191,serial_num => 319,plan_stat => 'all_executions');
（5）确认会话跟踪
select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=24696;
（6）跟踪目录$LOG获取
mesorcl_ora_24696.trc
（7）前端执行查看--记工查询
（8）tkprof分析trc
tkprof mesorcl_ora_24696.trc test.log sys=no
（9）关闭跟踪
exec dbms_monitor.session_trace_disable(session_id =>191 , serial_num =>319 );
也可以关闭所有会话跟踪
exec dbms_monitor.session_trace_disable();
（10）确认关闭跟踪
select s.SID,s.serial#,s.paddr,s.username,s.status,s.sql_trace,p.program,p.spid from v$session s,v$process p where s.paddr=p.addr and p.spid=24696;
2、设置系统跟踪查看
（1）登录sys，设置追踪打开
alter system set sql_trace=true;
（2）查看目录
$ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace
（3）找到最新的
mesorcl_ora_8452.trc等
（4）生成可读文档
tkprof mesorcl_ora_25624.trc test2.log sys=no
3、获取最新的sql执行语句
（1）方法1：
select a.machine,a.osuser,b.sql_text,b.sql_id,b.LAST_LOAD_TIME,a.prev_exec_start from v$session a,v$sql b where a.prev_sql_id=b.sql_id order by b.last_load_time desc;
十三、关于游标
1、关于游标及游标缓存
（1）当前所有session使用的游标数和可以打开的最大游标打开数
select sum(a.value), b.name,c.value max_open_cursor from v$sesstat a, v$statname b, v$parameter c where a.statistic# = b.statistic# and b.name = 'opened cursors current' and c.name= 'open_cursors' group by b.name ;
（2）当前session中，使用游标数最大色session会话
select max(a.value),b.name from v$sesstat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current' group by b.name;
（3）V$OPEN_CURSOR，
当前Session缓存的游标，可能包含已经打开的缓存和关闭的缓存。但不是打开的正在使用的游标 V$OPEN_CURSOR中显示的当前Session游标缓存中游标，如果要精确查询当前Session打开的游标总数，需要从V$ SESSTAT中查询。
（4）$SESSION_CACHED_CURSOR
当前Session已经关闭并被缓存的游标。
（5）关于参数open_cursor和session_cached_cursor
open_cursors：每个session（会话）最多能同时打开cursor（游标）数
session_cached_cursor：每个session（会话）最多可以缓存已关闭cursor（游标）数
若要理解参数open_cursors和session_cached_cursor的作用，最好先弄清楚oracle是如何执行每个sql语句的。
通过上图，可以看出两点：
两个参数之间没有任何关系，相互之间不会有任何影响。
两个参数有着相同的作用：让后续相同的sql语句不再打开游标，从而避免软解析过程来提供应用程序的效率。
十四、关于死锁
1、死锁无法杀掉问题解决
（1）用dba用户执行以下语句，可以查看到被死锁的语句
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
（2）查找死锁的进程：
sqlplus "/as sysdba" select s.username, l.object_id, l.session_id sid, s.serial# serial#, l.oracle_username, l.os_user_name, l.process from v$locked_object l, v$session s where l.session_id = s.sid;
（3）通过SQL kill 进程：
SQL>alter system kill session 'sid,serial#';
（4）如果还不能解决（直接杀系统进程）：查到这个进程
SQL>select pro.spid from v$session ses, v$process pro where ses.sid=<sid号> and ses.paddr=pro.addr;
$kill -9 pro.spid
十五、关于自动优化器
1、查看自动维护任务
SQL>select * from dba_autotask_client;
auto optimizer stats collection --自动优化器统计收集 auto space advisor --自动分段顾问 sql tuning advisor --自动SQL调整顾问
2、启用或禁用所有自动维护任务的所有窗口
（1）禁用
begin EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE; end; /
（2）启用
begin EXECUTE DBMS_AUTO_TASK_ADMIN.enable; end; /
3、启用或禁止某个自动维护任务的所有窗口
（1）如：禁用所有的sql tuning advisor任务
BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END;
（2）启用所有的sql tuning advisor任务（如：sql tuning advisor）
BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
4、启用或禁用某个自动维护任务的某个维护窗
缺省情况下，所有维护任务在所有预定义的维护窗口都运行。可以对某一个维护窗口启用或禁用自动化任务。
（1）禁用（如：sql tuning advisor的周一窗口）
BEGIN dbms_auto_task_admin.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW'); END;
（2）启用（如：sql tuning advisor的周一窗口）
BEGIN dbms_auto_task_admin.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => 'MONDAY_WINDOW'); END;
5、维护窗口属性：
可是使用DBMS_SCHEDULER 包来修改窗口属性。
（1）修改维护窗口的属性
步骤1—首先禁用维护窗口
BEGIN
dbms_scheduler.disable
(
name => 'SATURDAY_WINDOW'
);
END;
/
步骤2：修改
--举例：修改周六的窗口属性（修改value,间隔4小时进行一次）
begin
dbms_scheduler.set_attribute
(
name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(4, 'hour')
);
end;
/
--举例：修改周五的窗口属性（修改value值，每周五11点40开始）
begin
dbms_scheduler.set_attribute
(
name => 'FRIDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'freq=daily;byday=FRI;byhour=11;byminute=40; bysecond=0'
);
end;
/
（2）启用维护窗口：
BEGIN
dbms_scheduler.enable
(
name => 'SATURDAY_WINDOW'
);
END;
/
对于当前打开的窗口，你需要首先禁用，然后修改再启用，配置立即生效，如果你不通过这三个过程来修改属性，属性是不会生效的，直到下一次窗口打开。
（3）创建新窗口：
BEGIN
dbms_scheduler.create_window(
window_name => 'EARLY_MORNING_WINDOW',
duration => numtodsinterval(1, 'hour'),
resource_plan => 'DEFAULT_MAINTENANCE_PLAN',
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
dbms_scheduler.add_window_group_member(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
（4）删除窗口：
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
group_name => 'MAINTENANCE_WINDOW_GROUP',
window_list => 'EARLY_MORNING_WINDOW');
END;
/
6、立即停止正在运行的任务
（1）查看运行的job
SQL>SELECT * FROM USER_SCHEDULER_RUNNING_JOBS; SELECT * FROM DBA_JOBS_RUNNING;
（2）停止job
dbms_job.broken(jobid,true);
（3）停止job2
SQL>exec dbms_scheduler.stop_job('job_name名称'，force=>true);
十六、其他
1、orapwd使用方式
orapwd file=/oracle/app/oracle/product/12.2/db_1/dbs/orapwkssyorclprd password=1qaz%2WSX force=y
其中owrpwkssyorclprd中的kssyorclprd是实例名。如果数据库名是kssyorcl，实例名是kssyorclprd，则此处是kssyorclprd。
2、百度直接搜索orcle文档
site:docs.oracle.com v$database site:docs.oracle.com dba_scheduler_windows等
3、查看表、索引等统计信息最新的统计时间
注：查看表的统计信息收集时间
SQL>select LAST_ANALYZED from dba_tables; SQL>select LAST_ANALYZED from dba_indexes;
文章评论