DBA SQL 2019015

ANALYZE TABLE PROD_PARTS COMPUTE STATISTICS;
ANALYZE TABLE PROD_PARTS COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
ANALYZE TABLE PROD_PARTS COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

–Create Bar Chart
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(‘TPSDERIVQA1’, ‘RATES_TRADE’, method_opt => ‘FOR COLUMNS TRADE_ID’);
SQL> analyze table test2 estimate statistics;
SQL> analyze table test2 estimate statistics for columns empno;

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER,
TABNAME => ‘RATES_TRADE’,
ESTIMATE_PERCENT => 100,
METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,
degree => 8,
CASCADE => TRUE);
END;
/
/*
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME varchar2,
TABNAME varchar2,
PARTNAME varchar2,
ESTIMATE_PERCENT number,
BLOCK_SAMPLE PL/sql BOOLEAN,
METHOD_OPT varchar2,
degree number,
GRANULARITY varchar2,
cascade PL/sql BOOLEAN,
STATTAB varchar2,
STATID varchar2,
STATOWN varchar2,
NO_INVALIDATE PL/sql BOOLEAN,
STATTYPE varchar2,
force PL/sql BOOLEAN,
context varray)
*/

BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => USER,
OPTIONS => ‘GATHER AUTO’,
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, – 11G取样值比较大，可以使用SAMPLESIZE,10G好像是5~20%，可以根据需要调整
METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,
CASCADE =>TRUE,
DEGREE => 8 ) ;
END;
/
– 一般第一次收集的时候使用 ‘FOR ALL COLUMNS SIZE 1’ 删除所有列上的直方图，之后可以使用 SIZE AUTO , 逐渐调整，稳定后推荐使用 ‘FOR ALL COLUMNS SIZE REPEAT’

– Oracle利用直方图来提高非均匀数据分布的选择率和技术的计算精度。
– 但是实际上Oracle会采用另种不同的策略来生成直方图：
– 其中一种是针对包含很少不同值的数据集；
– 另一种是针对包含很多不同的数据集。
– Oracle会针对第一种情况生成频率直方图，针对第二种情况生成高度均衡直方图。
– 通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，
– 而当BUCTET = 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。
– 由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图。
– 在Oracle 10GR2之前如果使用dbms_stats包来创建直方图，那么如果指定需要创建的直方图的桶的数目与表的NUM_DISTINCT值相等，
– 那么几乎无法创建出一个FREQUENCY（频率）直方图，此时为了得到频率直方图只能使用analyze命令的“for all columns size表的NUM_DISTINCT值”，
– 这在某种程度上来说是一个退步，但这个问题在Oracle 10GR2后被修正。但是如果列中有180 - 200个不同值时，还是无法创建FREQUENCY（频率）直方图.
– 此时需要手工建立直方图,并写入数据字典方能完成FREQUENCY（频率）直方图的创建。

– 对于含有较少的不同值而且数据分布又非常不均匀的数据集来说，创建FREQUENCY（频率）直方图将会更加合适，因为它不存在会将低频出现的记录存入高频桶中的情况，
– 而HEIGHT BALANCED（高度平衡）直方图在存储桶(bucket)数分配不合理时就可能会出现这种情况。
– 因此一定要在创建直方图前确定使用何种直方图，并且要合理估计存储桶(bucket)数。

SELECT COLUMN_NAME AS “NAME”,
NUM_DISTINCT AS “#DST”,
LOW_VALUE,
HIGH_VALUE,
DENSITY AS “DENS”,
NUM_NULLS AS “#NULL”,
AVG_COL_LEN AS “AVGLEN”,
HISTOGRAM,
NUM_BUCKETS
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = ‘CREDIT_TRADE’;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(‘88ugj1t07d0u1’));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘88ugj1t07d0u1’,NULL,‘ADVANCED ALLSTATS LAST PEEKED_BINDS’));

–Query Bar Chart by Column
select column_name,num_distinct,num_buckets,histogram
from user_tab_col_statistics
where table_name=‘TEST2’ and column_name=‘EMPNO’;

–Query Cluster Factor
select t.table_name,t.NUM_ROWS,t.BLOCKS,t.AVG_ROW_LEN,i.index_name,i.CLUSTERING_FACTOR
from dba_tables t,dba_indexes i
where t.table_name=i.table_name
and t.owner=‘SCOTT’
and t.table_name=‘TEST2’;

–Query Total through Sample
SELECT COUNT(*) FROM big_table SAMPLE(sample_percent) [SEED(seed_value)]

–Running I/O Calibration
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
–DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, <MAX_LATENCY>, iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

DBMS_OUTPUT.PUT_LINE ('max_iops = ’ || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ’ || lat);
dbms_output.put_line('max_mbps = ’ || mbps);
end;
/

V$IO_CALIBRATION_STATUS
DBA_RSRC_IO_CALIBRATE

– SQL Trace
Enable SQL trace:
SQL> alter session set SQL_TRACE=true;
for other session:
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);

Disable SQL trace:
SQL> alter session set SQL_TRACE=false;
for other session:
SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);

– 使用10046 事件跟踪
SQL> alter session set events ‘10046 trace name context forever, level 28’; --启动10046事件
SQL> alter session set tracefile_identifier = ‘WW60031’;

SQL> alter session set events ‘10046 trace name context off’; – 关闭10046事件

SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true); – 启动trace

SQL> exec dbms_monitor.session_trace_disable(267,996); – 关闭trace

– tkprof .trc .txt sys=no

SQL> ALTER SESSION SET EVENTS ‘10053 trace name context forever,level 1’;
SQL> ALTER SESSION SET EVENTS ‘10053 trace name context off’;

EXPLAIN PLAN FOR SELECT
SQL> select * from table(dbms_xplan.display);
SQL> select * from table(dbms_xplan.display_cursor(‘5mu9n4f3fqxtt’));

select table_name,num_rows,to_char(last_analyzed,‘YYYY-MM-DD HH24:MI:SS’) AS LAST_ANALYZED
from user_tables
where table_name = ‘TPS_FAV_CONTACT_COVERAGE’;

select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

–Query the usage of Tablespace
SELECT F.TABLESPACE_NAME,
D.TOT_GROOTTE_MB “Total(M)”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “Used(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),‘990.99’) || ‘%’ “Rate”,
F.TOTAL_BYTES “Free(M)”,
F.MAX_BYTES “MaxExtent(M)”
FROM (SELECT T.TABLESPACE_NAME,
ROUND(SUM(NVL(FS.BYTES,0)) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(NVL(FS.BYTES,0)) / (1024 * 1024), 2) MAX_BYTES
FROM DBA_FREE_SPACE FS,DBA_TABLESPACES T
WHERE T.TABLESPACE_NAME = FS.TABLESPACE_NAME(+)
GROUP BY T.TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
UNION ALL
SELECT F.TABLESPACE_NAME,
D.TOT_GROOTTE_MB “Total(M)”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “Used(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),‘990.99’) || ‘%’ “Rate”,
F.TOTAL_BYTES “Free(M)”,
F.MAX_BYTES “MaxExtent(M)”
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) TOTAL_BYTES,
NULL MAX_BYTES
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM DBA_TEMP_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

–Query the used of Datafile by Tablespace
SELECT
D.TABLESPACE_NAME,
D.FILE_NAME,
D.TOT_GROOTTE_MB “Total(M)”,
D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES,0) “Used(M)”,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - NVL(F.TOTAL_BYTES,0))
/ D.TOT_GROOTTE_MB * 100,2),‘990.99’) || ‘%’ “Rate”,
NVL(F.TOTAL_BYTES,0) “Free(M)”,
NVL(F.MAX_BYTES,0) “MaxExtent(M)”
FROM (SELECT TABLESPACE_NAME,FILE_ID,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME,FILE_ID) F,
(SELECT DD.TABLESPACE_NAME,DD.FILE_NAME,FILE_ID,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME,DD.FILE_NAME,FILE_ID) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND D.FILE_ID = F.FILE_ID(+)
ORDER BY 1,2;

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES, MAXBYTES, INCREMENT_BY
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = ‘TPS_TS’
ORDER BY FILE_ID DESC;

alter database datafile ‘/oradata/DERIVD5/derivd5/tpsts_28.dbf’ autoextend on maxsize 34084864000;
alter database datafile ‘/oradata/DERIVD5/derivd5/tpsts_28.dbf’ resize 34084864000;
alter database datafile ‘/oradata/DERIVD5/derivd5/tpsts_28.dbf’ autoextend off;

SQL> ALTER TABLESPACE TEMP SHRINK SPACE; – KEEP 8G
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE ‘/u01/app/oracle/oradata/GSP/temp02.dbf’

SELECT DISTINCT TU.USERNAME,MACHINE,SID,TABLESPACE,BLOCKS*8/1024 USE_MB,SEGTYPE,TU.SQL_ID,SQL_TEXT
FROM
(SELECT USERNAME,SESSION_ADDR,SQLADDR,SQLHASH,SQL_ID,TABLESPACE,SUM(BLOCKS) BLOCKS,SEGTYPE,CONTENTS
FROM V$TEMPSE{G}_{U}SAGEGROUPBYUSERNAME,SQLADDR,SQLHASH,SQ{L}_{I}D,TABLESPACE,SEGTYPE,CONTENTS,SESSIO{N}_{A}DDR)TU,(SELECTSQ{L}_{T}EXT,SQ{L}_{I}D,ADDRESS,HAS{H}_{V}ALUEFROMV$SQL
GROUP BY SQL_TEXT,SQL_ID,ADDRESS,HASH_VALUE) SQ,
(SELECT USERNAME,MACHINE,SADDR,SID
FROM V$SESSION) SE
WHERE TU.SQLADDR=SQ.ADDRESS AND TU.SQL_ID=SQ.SQL_ID
AND TU.SQLHASH=SQ.HASH_VALUE AND TU.USERNAME=SE.USERNAME
AND TU.SESSION_ADDR=SE.SADDR
ORDER BY USE_MB DESC;

SELECT SID,SERIAL#,USERNAME,STATUS,OSUSER,PROCESS,MACHINE,PROGRAM FROM VKaTeX parse error: Expected 'EOF', got '#' at position 49: …S.SID, S.SERIAL#̲,P.SPID,DECODE(…SESSION S,GV$PROCESS P
WHERE S.PADDR = P.ADDR AND S.INST_ID = P.INST_ID
AND S.USERNAME IS NOT NULL AND S.USERNAME NOT IN (‘SYS’,‘SYSTEM’)
AND S.MACHINE NOT IN (‘tpsps1u.nam.nsroot.net’,‘tpsps2u.nam.nsroot.net’,‘tpsps4u.nam.nsroot.net’,‘tpsps5u.nam.nsroot.net’);

–Query the SQL lock
SELECT SESS.SID,
SESS.SERIAL#,
LO.ORACLE_USERNAME,
LO.OS_USER_NAME,
AO.OWNER,
AO.OBJECT_NAME,
LO.LOCKED_MODE,
SESS.MACHINE
FROM V$LOCKE{D}_{O}BJECTLO,DB{A}_{O}BJECTSAO,V$SESSION SESS
WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID
ORDER BY AO.OWNER;

– for RAC:
SELECT SESS.INST_ID,
SESS.SID||’, '||SESS.SERIAL# SID_SERIAL#,
LO.ORACLE_USERNAME,
LO.OS_USER_NAME,
AO.OBJECT_NAME,
LO.LOCKED_MODE,
SESS.MACHINE,
SESS.PROGRAM
FROM GV$LOCKE{D}_{O}BJECTLO,DB{A}_{O}BJECTSAO,GV$SESSION SESS
WHERE AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = SESS.SID
AND SESS.INST_ID = LO.INST_ID
ORDER BY AO.OWNER;

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type,block,ctime
FROM GV$LOCKWHERE(id1,id2,type)IN(SELECTid1,id2,typeFROMGV$LOCK WHERE request>0)
ORDER BY id1, request;

– Check the block session from RAC instance
select b.SID||’, ‘||b.SESS_SERIAL# BLOCKED_SID_SERIAL#,
bs.SECONDS_IN_WAIT,
bs.PROGRAM BLOCKED_PROGRAM,
bs.MACHINE BLOCKED_MACHINE,
bs.OSUSER BLOCKED_USER,
bs.PROCESS BLOCKED_PID,
s.SID||’, '||s.SERIAL# BLOCKING_SID_SERIAL#,
s.PROGRAM BLOCKING_PROGRAM,
s.MACHINE BLOCKING_MACHINE,
s.OSUSER BLOCKING_USER,
s.PROCESS BLOCKING_PID
from GV$SESSIO{N}_{B}LOCKERSb,GV$SESSION s,
GV$SESSION bs
where s.SID = bs.FINAL_BLOCKING_SESSION
and bs.SID = b.SID
and bs.SERIAL# = b.SESS_SERIAL#
and bs.SECONDS_IN_WAIT > 10;

SELECT ‘SID ’
|| l1.sid
||’ is blocking SID ’
|| l2.sid blocking
FROM gv$lockl1,gv$lock l2 ,
gv$session s
WHERE l1.block =1
AND l2.request > 0
AND s.sid =l1.sid
AND l1.id1 =l2.id1
AND l1.id2 =l2.id2;

SELECT DISTINCT S1.USERNAME || ‘@’ || S1.MACHINE
|| ’ ( INST=’ || S1.INST_ID || ’ SID=’ || S1.SID || ’ ) IS BLOCKING ’
|| S2.USERNAME || ‘@’ || S2.MACHINE || ’ ( INST=’ || S1.INST_ID || ’ SID=’ || S2.SID || ’ ) ’ AS BLOCKING_STATUS
FROM GV$LOCKL1,GV$SESSION S1, GV$LOCKL2,GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

–Tune SQL statement
SELECT * FROM USER_ADVISOR_TASKS T WHERE TASK_NAME=‘SQL_TUNING_TEST_20150204’;
SELECT * FROM DBA_SQLTUNE_STATISTICS;
SELECT * FROM DBA_SQLTUNE_BINDS;
SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID=13009;

DECLARE
MY_TASK_NAME VARCHAR2(30);

BEGIN
MY_TASK_NAME := dbms_sqltune.create_tuning_task(SQL_ID => ‘a17fgaj4xyb07’,
PLAN_HASH_VALUE => 1239362567,
SCOPE => ‘COMPREHENSIVE’,
TIME_LIMIT => 60,
TASK_NAME => ‘SQL_TUNING_TEST_20150204’,
DESCRIPTION => ‘TUNING TASK TEST’
);
END;
/

BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘SQL_TUNING_TEST_20150204’); END;

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name =‘SQL_TUNING_TEST_20150204’;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘SQL_TUNING_TEST_20150204’) FROM DUAL;

BEGIN dbms_sqltune.drop_tuning_task(‘SQL_TUNING_TEST_20150204’); END;

SELECT b.sql_text, b.sql_id, a.sid, a.serial#, p.pid, p.spid, a.paddr, a.osuser, a.machine
FROM v$sessiona,v$sql b, v$process p
WHERE a.sql_address = b.address
AND p.addr = a.paddr
AND b.sql_text like ‘%dbms_stats.gather_schema_stats%’;

select tp.platform_name,tp.endian_format,d.platform_name
from v$transportabl{e}_{p}latformtp,v$database d
where tp.platform_name = d.platform_name(+);

select COUNT(*),username from gv$sessionwhereusernameisnotnullGROUPBYusername;selectCOUNT(\ast ),usernamefromv$session where username is not null GROUP BY username;

select COUNT(*),STATUS from gv$sessionwhereusernameisnotnullGROUPBYSTATUS;selectCOUNT(\ast ),STATUSfromv$session where username is not null GROUP BY STATUS;

– Query SESSION/PROCESS ID for SQL
SELECT S.SQL_ID,S.SID,S.SERIAL#,P.SPID,S.USERNAME,S.MACHINE,S.PROGRAM
FROM V$SESSIONS,V$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SQL_ID = ‘1kbqzzjxv1fmm’;

SELECT S.SQL_ID,S.SID,S.SERIAL#,P.SPID,S.USERNAME,S.MACHINE,S.PROGRAM
FROM V$SESSIONS,V$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.SID = 1142 AND S.SERIAL# = 45;

1.CPU占用最多的前10个进程：
ps auxw|head -1;ps auxw|sort -rn -k3|head -10

2.内存消耗最多的前10个进程
ps auxw|head -1;ps auxw|sort -rn -k4|head -10

3.虚拟内存使用最多的前10个进程
ps auxw|head -1;ps auxw|sort -rn -k5|head -10

SELECT P.SPID, S.SID, S.SERIAL#, S.USERNAME, S.STATUS, S.SADDR, S.SQL_ID, S.PREV_SQL_ID, Q.SQL_FULLTEXT
FROM V$PROCESSP,V$SESSION S, V$SQL Q
WHERE P.ADDR = S.PADDR AND S.SQL_HASH_VALUE = Q.HASH_VALUE
AND P.SPID = 10430;

– List Index Columns
SELECT AI.OWNER,AI.TABLE_NAME,AI.INDEX_NAME,LISTAGG(IC.COLUMN_NAME,’,’) WITHIN GROUP (ORDER BY IC.COLUMN_POSITION) AS COLUMN_NAMES
FROM ALL_INDEXES AI,
ALL_IND_COLUMNS IC
WHERE AI.OWNER = IC.INDEX_OWNER
AND AI.TABLE_NAME = IC.TABLE_NAME
AND AI.INDEX_NAME = IC.INDEX_NAME
AND AI.OWNER = ‘TPSCORP_M’
AND AI.TABLE_NAME = ‘TPS_TRADE’
GROUP BY AI.OWNER,AI.TABLE_NAME,AI.INDEX_NAME
ORDER BY AI.INDEX_NAME;

– Enable Index Monitoring
SET SERVEROUTPUT ON
BEGIN
FOR IDX IN (SELECT * FROM USER_INDEXES WHERE INDEX_TYPE NOT IN (‘LOB’,‘IOT - TOP’) ORDER BY TABLE_NAME)
LOOP
BEGIN
EXECUTE IMMEDIATE ‘ALTER INDEX ‘|| IDX.INDEX_NAME ||’ MONITORING USAGE’;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(IDX.INDEX_NAME||SQLERRM);
END;
END LOOP;
END;
/

– Check Index Monitor Result
SELECT
u.name OWNER,
io.name INDEX_NAME,
t.name TABLE_NAME,
DECODE(bitand(i.flags, 65536), 0, ‘NO’, ‘YES’) MONITORING,
DECODE(bitand(ou.flags, 1), 0, ‘NO’, ‘YES’) USED,
ou.start_monitoring,
ou.end_monitoring
FROM
sys.obj$ io,
sys.obj$ t,
sys.ind$ i,
sys.object_usage ou,
sys.user$ u
WHERE i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# = i.bo#
AND io.owner# = u.user#;

SELECT * FROM v$object_usage;

ALTER INDEX INDEX_NAME NOMONITORING USAGE;

– Rebuild Index
ALTER INDEX ii REBUILD ONLINE NOLOGGING TABLESPACE test PARALLEL 2;

– Unix Command
– netstat -an | grep 15210 | awk ‘/^tcp/{++S[$NF]}END{for (key in S) print key,S[key]}’

– 找出哪个数据库用户用什么程序在最近三天执行过delete或truncate table的操作

SELECT c.username,
a.program,
b.sql_text,
b.command_type,
a.sample_time
FROM dba_hist_active_sess_history a
JOIN dba_hist_sqltext b
ON a.sql_id = b.sql_id
JOIN dba_users c
ON a.user_id = c.user_id
WHERE a.sample_time BETWEEN SYSDATE - 3 AND SYSDATE
AND b.command_type IN (7, 85)
ORDER BY a.sample_time DESC;

– Check Cursors
show parameter session_cached_cursor
drop view user_cursors;
create view user_cursors as
select
ss.username||’(’||se.sid||’) ’ user_process, sum(decode(name,‘recursive calls’,value)) “Recursive Calls”,
sum(decode(name,‘opened cursors cumulative’,value)) “Opened Cursors”, sum(decode(name,‘opened cursors current’,value)) “Current Cursors”
from v$sessionss,v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and ( name like ‘%opened cursors current%’
OR name like ‘%recursive calls%’
OR name like ‘%opened cursors cumulative%’)
and se.sid = ss.sid
and ss.username is not null
group by ss.username||’(’||se.sid||’) ';

title 'Per Session Current Cursor Usage ’
column USER_PROCESS format a25;
column “Recursive Calls” format 999,999,999;
column “Opened Cursors” format 99,999;
column “Current Cursors” format 99,999;

select * from user_cursors
order by “Recursive Calls” desc;

–####################################################
WITH a AS (SELECT VALUE init_open_cursors
FROM v$parameterWHEREname{=}^{\prime }ope{n}_{c}ursor{s}^{\prime }),bAS(SELECTMAX(ma{x}_{c}ursors)cur{r}_{m}a{x}_{c}ursorsFROM(SELECTMAX(a.VALUE)ma{x}_{c}ursorsFROMv$sesstat a, v$statnameb,v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = ‘opened cursors current’
GROUP BY s.sid))
SELECT b.curr_max_cursors,a.init_open_cursors FROM a, b;

– Query Oracle Hidden Parameters
set linesize 200
col NAME for a40
col VALUE for a10
col DEFAULT_VALUE for a20
col DESCRIPTION for a60
select a.ksppinm name, b.ksppstvl value, b.KSPPSTDVL DEFAULT_VALUE, a.ksppdesc description
from x$ksppia,x$ksppcv b
where a.indx = b.indx and a.ksppinm like ‘_optimizer_cost_based_transformation’;

BEGIN
DBMS_PROFILER.START_PROFILER (‘WW60031 Test’);
–PLSQL Put Here

–DBMS_PROFILER.PAUSE_PROFILER
–DBMS_PROFILER.RESUME_PROFILER

DBMS_PROFILER.STOP_PROFILER;
END;
/

SELECT * FROM PLSQL_PROFILER_RUNS ;
SELECT * FROM PLSQL_PROFILER_UNITS ;
SELECT * FROM PLSQL_PROFILER_DATA;

– Cardinality Feedback
select c.child_number, c.use_feedback_stats , s.sql_text from v$sq{l}_{s}hare{d}_{c}ursorc,v$sql s
where s.sql_id=c.sql_id and c.sql_id = ‘an4zdfz0h7513’
and s.child_number= c.child_number;

SQL> alter session set “_optimizer_use_feedback”=false;

select /+ opt_param(’_optimizer_use_feedback’ ‘false’)/ count(*) from jy;

select /+ cardinality(p 1) / count() from jy p;
/+ materialize /
/+ PARALLEL(s, 16)*/
create index index_name on table_name(col_name) nosegment;

– Enable the Explain Plan to use Index
alter session set “_use_nosegment_indexes” = true;

set serveroutput on
VAR rc REFCURSOR;
EXECUTE RATES_FXLM_TRADE_SVC_NEW.GET_EVENT_BY_GENERIC_TRADE_ID(p_trade_ids=>RATES_QUERY_TRADE_ARRAY(RATES_QUERY_TRADE_OBJECT(‘48022273’,‘Oasys.DealId’)),p_cursor=>:rc);
PRINT rc;

alter table HIST_TPS_TRADE truncate partition PART_2009;
alter table HIST_TPS_TRADE drop partition PART_2009;

select owner,extents,segment_name,blocks from dba_segments where segment_name=‘TPS_TRADE’ and owner=‘TPSCORP_M’;
SELECT prerid, COUNT (rid) rid
FROM (SELECT SUBSTR (ROWID, 1, 15) prerid, ROWID rid FROM TPS_PM_PRODUCT)
GROUP BY prerid;

select extent_id,block_id,blocks from dba_extents where owner=‘TPSCORP_M’ and segment_name=‘TPS_TRADE’;

select ‘alter index ‘||index_name||’ rebuild tablespace TPS_TSI;’ FROM user_indexes where index_name not like ‘SYS%$$’;

alter index MESSAGE_HEADER_INDEX_TS rebuild partition SYS_P13746 online nologging parallel 4;

– Data Pump Jobs
select * from dba_datapump_jobs where state=‘EXECUTING’;
DECLARE
hdl number;
begin
hdl := dbms_datapump.attach(‘JOB_NAME’,‘OWNER’);
DBMS_DATAPUMP.STOP_JOB(hdl,8,0);
end;
/

– Query INDEX
SELECT T1.INDEX_NAME,T1.TABLE_NAME,T1.UNIQUENESS,
LISTAGG(T2.COLUMN_NAME,’,’) WITHIN GROUP (ORDER BY T2.COLUMN_POSITION) COLUMN_GROUP
FROM ALL_INDEXES t1,ALL_IND_COLUMNS t2
WHERE t1.INDEX_NAME = t2.INDEX_NAME
AND t1.OWNER = T2.INDEX_OWNER AND t1.OWNER = ‘TPSDERIV’
AND t1.TABLE_NAME IN (‘TRADE’,‘COMMON_TRADE_XML_DATA’)
GROUP BY T1.INDEX_NAME, T1.TABLE_NAME, T1.UNIQUENESS;

– Get the time distribution of log file sync, log file parallel write
select event, wait_time_milli,wait_count
from v$event_histogram
where event in (‘log file sync’, ‘log file parallel write’);

– 巧用dba_hist_active_sess_history跟踪某个时间段内SQL
select to_char(b.begin_interval_time,‘yyyy-mm-dd hh24:mi:ss’) update_time,c.sql_text sql_text,a.session_id
from dba_hist_active_sess_history a,DBA_HIST_SNAPSHOT b,v$sqlarea c
where a.snap_id=b.snap_id
and a.sql_id=c.sql_id
and b.begin_interval_time>=TO_DATE(‘24-Jul-15 21:00:00’,‘DD-Mon-YY HH24:MI:SS’) and b.begin_interval_time < TO_DATE(‘24-Jul-15 22:00:00’,‘DD-Mon-YY HH24:MI:SS’)
–and lower(c.sql_text) like ‘%tps_regulatory_status%’
–and a.session_id = ‘206096868’
order by update_time;

– DROP SYNONYMS
SET SERVEROUTPUT ON
BEGIN
FOR SYN IN (SELECT OWNER,SYNONYM_NAME,TABLE_OWNER FROM DBA_SYNONYMS WHERE TABLE_OWNER LIKE ‘TPSD%’) LOOP
IF SYN.OWNER = ‘PUBLIC’ THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||SYN.SYNONYM_NAME;
END IF;
END LOOP;
END;
/

SELECT * FROM TPSCORP_M.TPS_CORP_ARCHIVE_JOB AS OF TIMESTAMP(TO_DATE(‘07-12-16 23:00:00’,‘DD-MM-YY HH24:MI:SS’));

– Query the Undo Usage for each Session
SELECT s.USERNAME,s.SID,s.SERIAL#,s.SQL_HASH_VALUE,t.UBAFIL “UBA filenum”, t.UBABLK
“UBA Block number”,t.USED_UBLK “Number os undo Blocks Used”,
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName
FROM v$sessions,v$transaction t,v$rollnamerWHEREs.SADDR=t.SE{S}_{A}DDRANDt.XIDUSN=r.usnANDr.NAME{=}_S^{\prime }YSSMU{4}_{4}131258603$’;

– Reset Tab Stat
exec dbms_stats.set_table_stats(‘TEST1’,‘T1’,numrows=>49953);

EXEC DBMS_UTILITY.COMPILE_SCHEMA(USER);
SELECT OBJECT_TYPE, OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE NOT STATUS = ‘VALID’;

– Flashback Query
SELECT * FROM CGMI_FOREIGN_REF C

– Get the Real Execution Plan
ALTER SESSION SET STATISTICS_LEVEL = ALL;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘716qw6t48c9p2’, NULL, 'ALLSTATS LAST '));

–#######################################################################################
– SQL Advisor
– SQL_TEXT
DECLARE
MY_TASK_NAME VARCHAR2(30);
MY_SQLTEXT CLOB;
BEGIN
MY_SQLTEXT :=‘SELECT * FROM TEST_OBJECT_TTX WHERE OBJECT_ID = 856’;
MY_TASK_NAME :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
–BIND_LIST => SQL_BINDS(ANYDATA.CONVERTNUMBER(9)),
USER_NAME => ‘NOAP’,
SCOPE => ‘COMPREHENSIVE’,
TIME_LIMIT => 60,
TASK_NAME => ‘SQL_TUNING_TEST’,
DESCRIPTION => ‘TUNING TASK’
);
END;
/

– SQL_ID
BEGIN
DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => ‘451ay2w5ws0jv’,
plan_hash_value => NULL,
USER_NAME => ‘NOAP’,
SCOPE => ‘COMPREHENSIVE’,
TIME_LIMIT => 60,
TASK_NAME => ‘SQL_TUNING_TEST’,
DESCRIPTION => ‘TUNING TASK’
);
END;
/

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(‘SQL_TUNING_TEST’);
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name =‘SQL_TUNING_TEST’;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘SQL_TUNING_TEST’) FROM DUAL;

–#######################################################################################
– Running SQL
SELECT COUNT(*) RUNNING_SQL
FROM (
SELECT b.sid oracleID,
b.username,
b.serial#,
spid,
paddr,
sql_text,
b.machine
FROM v$processa,v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value
–AND sql_text like ‘BEGIN CONFIG.PUBLISH_STATS%’
);

– Sessions Distribution
SELECT I.INSTANCE_NAME,
I.HOST_NAME,
S.USERNAME,
COUNT(*) CONNECTIONS
FROM GV$SESSIONS,GV$INSTANCE I
WHERE S.INST_ID = I.INST_ID
AND (USERNAME LIKE ‘TPS%’ OR USERNAME = ‘CMOSTONLINE’)
GROUP BY S.USERNAME, I.HOST_NAME, I.INSTANCE_NAME
ORDER BY USERNAME;

SELECT I.INSTANCE_NAME,
I.HOST_NAME,
COUNT() CONNECTIONS,
ROUND((COUNT() / P.VALUE * 100),2) AS Percent_used
FROM GV$SESSIONS,GV$INSTANCE I, GV$PARAMETER P
WHERE S.INST_ID = I.INST_ID AND I.INST_ID = P.INST_ID
AND (USERNAME LIKE ‘TPSDERIV%’ OR USERNAME LIKE ‘STPDRV%’)
AND P.NAME = ‘sessions’
GROUP BY I.HOST_NAME, I.INSTANCE_NAME, P.VALUE
ORDER BY I.HOST_NAME;

SELECT * FROM USER_INDEXES WHERE INDEX_NAME = ‘TPS_TRADE_LASTMOD_TIMESTAM_IDX’;
SELECT SYS_OP_COUNTCHG(SUBSTRB(T.ROWID,1,15),5) AS CLF FROM TPS_TRADE T WHERE TRADE_ID IS NOT NULL;
EXEC DBMS_STATS.SET_INDEX_STATS(USER,‘TPS_TRADE_LASTMOD_TIMESTAM_IDX’,CLSTFCT=>42134476);