oracle 常用性能查询SQL分享

select count(1) from dictionary;
select * from dba_data_files;
select count(1) from dba_objects t where t.owner='BESTTONE';
select * from dba_tablespaces t where t.tablespace_name='BESTTONE';
select count(1) from dba_tables t where t.owner='BESTTONE';
select t.table_name,t.comments from dictionary  t where t.table_name like 'V$%';
select * from dba_constraints;
select * from dba_cons_columns;
select * from dba_tablespaces t where t.tablespace_name='SIM002';
CREATE TABLESPACE SS DATAFILE 'D:/ORACLE/ORADATA/BSTO/SS.DBF' SIZE 1M;
SELECT T.tablespace_name, T.status FROM DBA_TABLESPACES T;
SELECT T.TABLESPACE_NAME,T.FILE_NAME FROM DBA_DATA_FILES T;
ALTER TABLESPACE SIM002 OFFLINE;
ALTER TABLESPACE SIM002 ONLINE;
SELECT T.os_username,
       T.username,
       T.terminal,
       DECODE(T.returncode,
              '0',
              'Connected',
              '1005',
              'failedNull',
              '1017',
              'failed'),
       to_char(t.timestamp, 'dd-mon-yy hh24:mi:ss'),
       to_char(t.logoff_time, 'dd-mon-yy hh24:mi:ss')
  FROM DBA_AUDIT_SESSION T;--尝试登陆审计
select t.action,t.name  from audit_actions t;
select * from dba_audit_object;
select * from v$sesstat;
select a.sid,a.value "total cpu time" from v$sesstat a ,v$statname b
where a.STATISTIC#=b.STATISTIC# and b.NAME='cpu used by this session'
order by a.sid;--会话级服务时间值
select sum(time_waited) "total time waited"
  from v$system_event
 where event not in
       ('pmon timer', 'smon timer', 'rdbms ipc message',
        'parallel dequeue wait', 'virtual circuit',
        'SQL*Net message from client', 'client message', 'null event');
select count(*) from v$process;                        -- 取得数据库目前的进程数。
select value from v$parameter where name = 'processes'; --取得进程数的上限。
--计算总等待时间的实例级查询
select sid,sum(time_waited) "total time waited" from v$session_event
where event !='SQL*Net message from client'
group by sid;
--查看当前有哪些用户正在使用数据
SELECT osuser,
       a.username,
       cpu_time / executions / 1000000 || 's',
       sql_fulltext,
       machine
  from v$session a, v$sqlarea b
 where a.sql_address = b.address
 order by cpu_time / executions desc;
--计算总等待时间的会话级查询
SELECT * FROM V$LOG;
ALTER SYSTEM SWITCH LOGFILE;
SELECT * FROM V$LOG_HISTORY;
SELECT * FROM V$logfile;
select name,value from V$PARAMETER WHERE NAME='db_recover_file_dest';
select * from scott.dept;
select trunc(123.12,-1) from dual;
select round(1234.564,9) from dual;
select concat('','11') from dual;
select initcap('ww') from dual;
select instr('wwwweerr','w',-1,1) from dual;
select translate('d2q1wwr','d2','Dwr') FROM DUAL;
SELECT T.TABLESPACE_NAME,SUM(T.BYTES) FROM DBA_FREE_SPACE T GROUP BY T.TABLESPACE_NAME
--查询表空间的空闲大小
select sum(t.GETS),sum(t.GETMISSES) from v$rowcache t;--查询共享池中数据字典缓存的成功与失败次数
select * from user_extents;--查询用户数据段的存储情况
select * from user_indexes;--查看用户的索引段
SELECT * FROM DICTIONARY;
SELECT * FROM DBA_VARRAYS;
SELECT * FROM SYS.OBJ$;
SELECT * FROM DBA_VIEWS;
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM DBA_TS_QUOTAS;
SELECT * FROM DBA_ROLES;
SELECT * FROM DBA_PROFILES;
select t.TELPHONE,t.NAME,t.KEY,t.ORD,(case t.parent_id when 0  then '' else (SELECT S.NAME FROM T_PRIMENU S WHERE S.ID=T.PARENT_ID)  end), v.vox_name voxname
                          from t_primenu t, t_voxlab v
                         where t.product_id = '20071120004553169'
                            and t.vox = v.vox_num
                         order by t.key, t.ord asc
select id, key, telphone, vox, product_id, ord, name, vox_type, parent_id, areaid,DECODE(PARENT_ID,'0','',(SELECT S.NAME FROM T_PRIMENU S WHERE S.ID=T.PARENT_ID)) AS TNAME
from t_primenu T WHERE PRODUCT_ID='20071120004553169' ;
select level,sys_connect_by_path(T.NAME,'/') path
from T_PRIMENU T
start with T.PARENT_ID=0
connect by prior T.ID =T.PARENT_ID;
select connect_by_isleaf
from T_PRIMENU T
start with T.PARENT_ID=0
connect by prior T.ID =T.PARENT_ID;
select COUNT(1), area_id
  from t_area t
 GROUP BY area_id
HAVING COUNT(area_id) > 1;
死锁问题查看
SELECT * FROM V$SYSSTAT WHERE CLASS=4--查看数据库是否发生过死锁
select * from V$sysstat WHERE STATISTIC#=23 AND VALUE=0;--死锁会话是否还在连接中
select l.sid,
       serial#,
       username,
       decode(block, 0, 'NO', 'YES') blocker,
       decode(request, 0, 'N0', 'YES') waiter
  from v$lock l, v$session s
 where s.SID=l.SID and (request>0 or block>0)  order by block desc;--查看是否有死锁存在
 

select * from v$system_event where event='enquece';--查看会话等待enquece的时间
select owner, object_type, substr(OBJECT_NAME, 1, 30) OBJECT_NAME
  FROM DBA_OBJECTS
 WHERE STATUS = 'INVALID'
 ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME; --查看状态为失效的对象
select username,count(username) from v$session where username is not null group by username --查看死锁进程对应的SQL
SELECT /*+ PUSH_SUBQ */

Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,

Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,

Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,

Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,

SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status

FROM V$sqlarea

WHERE Address = (SELECT Sql_Address

FROM V$session

WHERE Sid =560 )
--查看数据库的连接数
--由上级向下遍历
select *
from lmss2_district d
start with d.id = '0200' --上级id
connect by prior d.id = d.parent_id

--由下级向上回朔
select *
from lmss2_district t
start with t.id='0200'
connect by prior t.parent_id=t.id
--查看正在执行的进程对应的SQL
select sql_text 
from v$sqlarea a, v$session s
where a.address = s.sql_address;
--查看系统的library cache命中率
SELECT SUM(PINS) "EXECUTIONS",
       SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
       1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
--查看shared pool的使用情况
select sum(bytes)/(1024*1024) from v$sgastat
where pool='shared pool'
and name != 'free memory';
 --专用服务模式下，查看cache在内存中的对象的大小，
select sum(sharable_mem) from v$db_object_cache;
--专用服务模式下，查看SQL占用的内存大小，
select sum(sharable_mem) from v$sqlarea;
--Oracle需要为保存每个打开的游标分配大概250字节的内存，以下语句可以计算这部分内存的占用情况，
select sum(250 * users_opening) from v$sqlarea;
-- ora-00600:内部错误，参数19004的解决办法：
execute dbms_stats.delete_schema_stats('hb2db');--hb2db为表空间名