oracle 维护、监控、优化常用sql

1) sql监控

SELECT SE.sid, schemaname, SE.serial#, PR.spid, SE.status, SUBSTR(SE.program, 1, 10) PROG,
   SUBSTR(SE.machine, 1, 10) MACH,
   SQ.sql_text
FROM v$session SE, v$sqlarea SQ, v$process PR
WHERE SE.paddr = PR.ADDR(+)
   AND SE.sql_address = SQ.address(+)
   AND schemaname ='KING'

ORDER BY SE.sid
  
---面查出来的sqlid
select * from table(dbms_xplan.display_cursor('ddzktdys2nz3w'))

select count(*) from v$session


---查看各个状态的sql
select se.status,sq.sql_text from v$session se,v$sqlarea sq   where se.sql_address=sq.address(+)


--正在执行的

select a.username, a.sid,b.SQL_TEXT, b.SQL_FULLTEXT
  from v$session a, v$sqlarea b
where a.sql_address = b.address
---执行过的
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2013-03-01/13:30:47' and
       '2013-03-01/13:50:47' order by b.FIRST_LOAD_TIME
--（此方法好处可以查看某一时间段执行过的sql，并且 SQL_FULLTEXT 包含了完整的 sql 语句）

--其他
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_TEXT
from V$SESSION A LEFT JOIN V$SQL B ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE order by b.cpu_time desc


select address, sql_text, piece
  from v$session, v$sqltext
where address = sql_address
-- and machine = < you machine name >
order by address, piece

---查找前十条性能差的sql.

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea
order BY disk_reads DESC )where ROWNUM<10 ;

---查看占io较大的正在运行的session

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,
se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.
p1text,si.physical_reads,
si.block_changes FROM v$session se,v$session_wait st,
v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.
sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.
wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC

--
alter system set processes = 400 scope = SPFILE
show processes PARAMETERS
----查看实例名
select instance_name from V$instance


2）表空间大小查看
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b                 
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name

3）索引查看 删除 
---查询某张表的索引
select * from user_indexes where TABLE_Name = 'CASE_ACLINE_TEMP'
select * from user_indexes where TABLE_Name = 'CASE_ACLINE_PARTITION'
---删除某个索引
DROP INDEX CASE_ACLINE_PARTITION_IDX
DROP INDEX FK_CASE_ACLINE_TEM_CASE_ID

-- 索引占用大小
select a.segment_name,a.tablespace_name,b.table_name,a.bytes/1024/1024 mbytes,a.blocks 
from user_segments a, user_indexes b 
where a.segment_name = b.index_name 
and a.segment_NAME = 'CASE_ACLINE_PARTITION_IDX' --索引 
and a.tablespace_name='TEST_TEMP' --表空间 
and b.table_name = 'CASE_ACLINE_PARTITION' --索引所在表 
order by table_name,a.bytes/1024/1024 desc

---查看分区表索引
SELECT * FROM USER_IND_PARTITIONS T
WHERE T.INDEX_NAME='CASE_ACLINE_PARTITION_IDX'


4）连接数查看  修改

--连接数
select count(*) from v$session

--并发连接数
Select count(*) from v$session where status='ACTIVE'　
---最大连接
show parameter processes
---修改连接数 ，重启生效
alter system set processes = 400 scope = spfile;