ORACLE隐参查询

set linesize 200;
col name for a30;
col value for a10;
col ismodified for a10;
col isdefault for a10;
col description for a75;

select
i.ksppinm name,
i.ksppdesc description,
cv.ksppstvl value,
cv.ksppstdf isdefault,
decode(bitand(cv.ksppstvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,’FALSE’) ismodified,
decode(bitand(cv.ksppstvf,2),2,’TRUE’,’FALSE’) isadjusted from
sys.x$ksppi i,
sys.x$ksppcv cv
where
i.inst_id=userenv(‘Instance’) and
cv.inst_id=userenv(‘Instance’) and
i.indx=cv.indx and
i.ksppinm like ‘%&GetHidPar%’;

优化脚本

==>手工创建一个快照
Exec dbms_workload_repository.create_snapshot;

==>手工创建awr基线
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id ,baseline_name);

==>查询shared_pool各组件的情况，注意此语句对性能影响很大,危险
SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),’999,999.00′)||’k’ “AVG SIZE”
FROM X$KSMSP GROUP BY ksmchcls;

==>删除指定列统计信息
exec dbms_stats.gather_table_stats(user,’EYGLE’,method_opt=>’for columns owner size 1′);

==>做数据文件的收缩脚本，注意与实际数据库应用相关联一下
spool /home/oracle/resize_dbf.txt
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading “Smallest|Size|Poss.”
column currsize format 999,990 heading “Current|Size”
column savings format 999,990 heading “Poss.|Savings”
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = ‘db_block_size’
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) –
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select ‘alter database datafile ”’||file_name||”’ resize ‘ ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) –
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
spool off;

==>10G recyclebin清理查询
SHOW RECYCLEBIN
删除指定
PURGE TABLE “BINjR8PK5HhrrgMK8KmgQ9nw==”;
清除全部
purge recyclebin

==>收集非分区表的统计信息：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => ”,
tabname => ”
estimate_percent => 30,
method_opt => ‘for all columns size repeat’,
no_invalidate => FALSE,
degree => 30,
cascade => TRUE);
END;
/

==>对分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => ”,
tabname => ”,
estimate_percent => 5,
method_opt => ‘for all columns size repeat’,
no_invalidate => FALSE,
degree => 30,
granularity => ‘ALL’,
cascade => TRUE);
END;
/

==>针对分区表某一个分区收集统计信息
exec dbms_stats.GATHER_table_STATS(ownname=>”,tabname=>”partname=>’PAR_2014_05_01′, ESTIMATE_PERCENT=>10,cascade=>true,no_invalidate=>false,degree=>32);

==>查看执行计划的脚本
1、通过sql_id 去以下视图中找到该sql的child_number,多个值代表不同的执行计划
select * from v$sql_plan where sql_id=’5p1sb4ys1cnh9′;
2、用sql_id 和child_number 去查找当前内存中的sql执行计划
select * from table(dbms_xplan.display_cursor(‘5p1sb4ys1cnh9′,0));
3、通过sql_id 去内存中查找执行计划的绑定变量对于的值
select * from v$sql_bind_capture where sql_id=’5p1sb4ys1cnh9’;

==>查看等待事件脚本
set pagesize 2000
set linesize 120
col event format a30
col username for a10
col osuser for a15
col p1 for 9999999
col sid for 9999
col p2 for 999999
col p3 for 9999999999999
col program format a30
select substr(a.event,1,25) event,substr(b.program,1,30) program ,b.sid,b.username,b.osuser
from v$session_wait a,v$session b
where a.sid=b.sid and a.event not like ‘%SQL%’
and a.event not like ‘%message%’
and a.event not like ‘%time%’
and a.event not like ‘PX Deq:%’
and a.event not like ‘jobq slave%’;

==>查询并行度session，一般长时间，有规律的查询无非就放在crontab,后台,job里
select p.DEGREE,p.REQ_DEGREE,s.SQL_ADDRESS,s.SQL_ID,s.MACHINE,s.PROGRAM,s.USERNAME,s.PADDR
from v$px_session p,v$session s where p.SADDR=s.SADDR;
select * from v$sql q where q.SQL_ID in (‘&1’);

DML锁检查

–用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 WHERE object_id=’107181′))

–查找锁的进程：
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS,D.object_name
FROM V$LOCKED_OBJECT l,V$SESSION S,DBA_OBJECTS D WHERE l.SESSION_ID=S.SID AND D.object_id=L.OBJECT_ID;

GPS_PARK_RECORD

SELECT l.SESSION_ID
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
–查询锁对应的os进程
select pro.spid from v$session ses,v$process pro where ses.sid=439 and ses.paddr=pro.addr;

–kill掉这个锁的进程：

alter system kill session ‘286,24060’;
SELECT ‘alter system kill session ”’|| S.SID||’,’||s.SERIAL#||”’;’
FROM V$LOCKED_OBJECT l,V$SESSION S,dba_objects o

WHERE l.SESSION_ID=S.SID and o.OBJECT_ID=l.OBJECT_ID and o.OBJECT_NAME=’GPS_PARK_RECORD’;

–kill相关链接此对此对象session
select /*+rule */ ‘kill -9 ‘||p.SPID from gv$access a ,gv$session s ,gv$process p
where a.INST_ID=2
and a.SID=s.SID
and s.PADDR=p.ADDR
and a.OBJECT=’PKG_EXP_OP_TASK’
and a.OWNER=’EXP’ ;