ORACLE系统查询语句(五)
sleep4 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep10 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep4,
TO_CHAR (a.sleep5 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep11 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep5
FROM v$latch a
WHERE a.misses <> 0
ORDER BY 2 DESC
--DBMS_METADATA.GET_DDL
1.得到一个表或索引的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;
2.得到一个用户下的所有表，索引，存储过程的ddl
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');
3.得到所有表空间的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
4.得到所有创建用户的ddl
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;
和处理AWR快照相关
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
END;
/
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(56, 57);
END;
/
SQL> @ /rdbms/admin/awrrpt.sql
-- 查询无效索引
SELECT INDEX_NAME FROM USER_INDEXES WHERE STATUS <> 'VALID' AND PARTITIONED = 'NO';
SELECT INDEX_NAME, PARTITION_NAME FROM USER_IND_PARTITIONS WHERE STATUS <> 'USABLE';
-- 生成重建索引的语句
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD' FROM USER_INDEXES WHERE STATUS <> 'VALID' AND PARTITIONED = 'NO';
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME FROM USER_IND_PARTITIONS WHERE STATUS <> 'USABLE';
-- 收集统计信息
cascade设置为true is also gather columns and index’s statistics
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SA' , tabname=>'T_PRODUCT_SUBSCRIBE',estimate_percent=>100, degree=>1, cascade=> TRUE);
exec DBMS_STATS.SET_TABLE_STATS(ownname=> 'SA' , tabname=>'T_PRODUCT_SUBSCRIBE', numrows=>1000000);
11g上自动统计信息相关
select client_name,status from Dba_Autotask_Client;
select log_date,status from dba_scheduler_job_run_details
where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date desc;
select job_name,state from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB' ;
BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL); END;
ps -ef | grep LOCAL | grep -v grep | awk '{print $2}' | xargs kill -9
跟踪会话
SELECT P.PID, P.SPID
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
AND S.SID = 154
-- 使用V$PROCESS中的列SPID
ORADEBUG SETOSPID XX
-- 使用V$PROCESS中的PID
ORADEBUG SETORAPID XX
-- 开启跟踪
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
ORADEBUG TRACEFILE_NAME
-- 关闭跟踪
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
tkprof C:\01\orcl_ora_652.trc C:\01\orcl_ora_652.txt aggregate=yes sys=no sort=prscnt
--
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('4ba4mf4mknjvr', 0, 'ALL'));
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('4ba4mf4mknjvr', 0, 'iostats last'));