sql多表联合检查实例下边提供五款sql多表关联合检查询的实例,个个功效分歧等。

杀掉僵死会话:

方法二

2.查询数据库job语句

select * from order_info as a where a.saleorder=(select b.ext1_skill
from ivrlog4ivrdlvinst as b where b.start_date=@date1 and se_id=’55’
and b.ext1_skill!=”) and convert(varchar(10),a.instime,112)=@date2 and
max(a.instime)

11.询问大事务以致相应的sql

方法三

 

declare @date1 varchar(20), @date2 varchar(20) set @date1=’20100812′ set
@date2=’2010-08-12′

 

方法四

经常表空间:

select * from order_info as a where a.saleorder=( select b.ext1_skill
from ivrlog4ivrdlvinst as b where b.start_date=@date1 and se_id=’55’
and b.ext1_skill!=”)and convert(varchar(10),max(a.instime),112)=@date2

不经常表空间:

sql多表联合检查实例上面提供七款sql多表关联合检查询的实例,个个成效不等同。select
* from order_info as a ,ivrlog4ivrdlvinst as b where
(a.saleorder=b.ext1_skill and b.start_date=@date1 and se_id=’55’ and
b.ext1_skill!=”) and convert(varchar(10),a.instime,112)=@date2 and
max(a.instime)

SELECT l.session_id sid,
s.serial#,l.oracle_username,l.os_user_name,s.machine,o.object_name,
s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE
l.object_id = o.object_id AND l.session_id = s.sid and
s.status<>’ACTIVE’ ORDER BY sid, s.serial#;

select b.caller, b.start_date, b.start_time, b.ext1_skill,
c.deliveryno, c.destroyresult, c.deliverydate, c.deliverytime,
c.arrangetime, c.driverphone, c.drivermobile, a.servicedate,
a.servicetime, a.workertelfrom order_info as a ,ivrlog4ivrdlvinst as b
,delivery_info as cwhere a.saleorder in (select b.ext1_skill from
ivrlog4ivrdlvinst where b.start_date=@date1 and b.se_id=’55’ and
b.ext1_skill!=”) and convert(varchar(10),a.instime,112)=@date2 order
by b.start_date desc, b.start_time desc

12.查询pga和uga使用量

8.查询隐含参数

select a.name,b.value from v$statname a,v$sesstat b where
a.statistic#=b.statistic# and b.sid=(select sid from v$mystat where
rownum=1) and (a.name like ‘%ga %’ or a.name like ‘%direct temp%’)
union all
select ‘total: ‘||a.name,sum(b.value) from v$statname a,v$sesstat
b,v$session c where a.statistic#=b.statistic# and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’) and b.sid=c.sid and c.username is not
null group by ‘total: ‘||a.name;

3.查询表空间使用率和数据文件碎片

set feedback off
set linesize 120
col grp_name for a10
col grp_num for a10
col used_rate for a10

 

select s.sid,s.serial#,s.status,t.start_time,
t.xidusn||’.’||t.xidslot||’.’||t.xidsqn xid,
s.username,decode(s.sql_id,null,s.prev_sql_id,s.sql_id) sqlid
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by t.start_time;

SELECT A.tablespace_name tablespace,
D.mb_total,
SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM(A.used_blocks * D.block_size) / 1024 / 1024
mb_free
FROM v$sort_segment@ncslave.com A,
(SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace@ncslave.com B, v$tempfile@ncslave.com C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name GROUP by A.tablespace_name,
D.mb_total;

select ‘grp’||group_number grp_num,name
grp_name,type,round(total_mb/1024,2) total_gb,round(free_mb/1024,2)
free_gb,round(REQUIRED_MIRROR_FREE_MB/1024,2)
req_mir_free_gb,round(USABLE_FILE_MB/1024,2)
usable_gb,round((total_mb-free_mb)/total_mb*100,2)||’%’ used_rate
from v$asm_diskgroup_stat;

 

select sql_id,event,count(*) from v$active_session_history where
sql_id is not null and event is not null and sample_time between
to_date(‘20161125150000′,’yyyy-mm-dd hh24:mi:ss’) and
to_date(‘20161125160000′,’yyyy-mm-dd hh24:mi:ss’) group by
sql_id,event order by count(*) desc;

 

 

col total_mb for 999999
col free_mb for 99999
col free_rate for a15
col used_rate for a15

select * from (select
name,gets,misses,immediate_gets,immediate_misses,sleeps from v$latch
order by misses desc) where rownum<10;

select d.tablespace_name,
d.mb total_mb,
f.mb free_mb,
round((f.mb / d.mb), 4) * 100 || ‘%’ free_rate,
round((d.mb – f.mb) / d.mb, 4) * 100 || ‘%’ used_rate
from (select tablespace_name, bytes / 1024 / 1024 mb from
dba_data_files) d,
(select tablespace_name, sum(bytes) / 1024 / 1024 mb
from dba_free_space
group by tablespace_name) f
where d.tablespace_name = f.tablespace_name(+)
order by (d.mb – f.mb) / d.mb desc;

alter session set nls_date_format=’YYYY-MM-DD HH24:MI:SS’;
col owner for a20
col job_name for a30
col LAST_START_DATE for a20
col NEXT_RUN_DATE for a20
col job_action for a40
col what for a40
col status for a10
set linesize 200
set pages 100

alter system kill session ‘sid,serial#’ immediate;

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注