ORACLE脚本。。。。。

建合理的索引、索引碎片整理、表碎片整理、调整表pct及索引pct、调整表init、利用keep池将表缓存、将表放到更小的块中 exec dbms_system.set_sql_trace_in_session(&sid,&serial#,&sql_trace); alter session set events '&event trace name context forever,level &level'; alter session set events '&event trace name context off'; exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,''); oradebug event 10046 trace name context forever,level 12 select * from table(dbms_xplan.display(null,null,'outline')); select client_name,status from dba_autotask_client; --扩字段 ALTER TABLE HSS.TB_PRD_PRD MODIFY PRD_ID NUMBER(12); --日期转换 select count(*) from abp_int.account a, abp_int.customer b where a.cust_id = b.cust_id and b.party_id ='410000110140' and to_date(a.exp_date, 'YYYY-MM-DD HH24:MI:SS') > sysdate; select * from abp_int.tb_int_order_ab a where a.arrive_date>= to_date('201801010000', 'yyyymmddhh24mi'); --查看字符集 select * from nls_session_parameters; select * from nls_database_parameters; --优化相关 --查看当前执行的语句 select distinct --' alter system kill session '''||s2.sid||','||s2.SERIAL# ||''';', s1.sql_text, s2.SERVER, s2.sid, s2.serial#, s1.SQL_ID, s2.SQL_CHILD_NUMBER,s2.EVENT, s2.username, s2.lockwait,s2.BLOCKING_SESSION_STATUS, s2.BLOCKING_INSTANCE, s2.BLOCKING_SESSION, s2.status, s2.osuser, s2.machine, s2.module, s2.terminal, s2.PROGRAM, s5.spid, s2.LOGON_TIME, s2.ACTION, s2.process from v$sql s1, v$session s2, v$process s5 where s1.hash_value(+) = s2.sql_hash_value and s2.PADDR = s5.ADDR(+) -- and s5.SPID=3105 and s2.sql_id='d49gm4w1rnj47' --and s2.OSUSER='w_tydk_zhongyx' --and s2.sid=11909 --and s2.MACHINE='abp-app-007' --and s2.OSUSER='abpadm' order by 1,terminal, status; --查看sql的执行进度 select sid, opname, target, start_time, elapsed_seconds, sofar, totalwork, trunc(sofar / totalwork * 100, 2) || '%' as perwork from v$session_longops t where sofar != totalwork /*and target like '%TB_RTB_LXJ_TASK%'*/ and t.SQL_ID='a9z5wabyhguzu'; --查看sql已插入值 select * from v$sql_bind_capture t where sql_id='d49gm4w1rnj47'; --根据sql_id查看sql及执行计划 select * from v$session ss where ss.USERNAME='NOSS_OP' AND ss.STATUS!='INACTIVE' AND ss.PROGRAM='sqlplus@abp-app-007 (TNS V1-V3)'; select * from v$session ss where ss.sql_id='04zaa2bqazpz4' and ss.SQL_EXEC_START>to_timestamp('2019/04/30 00:30', 'yyyy/mm/dd hh24:mi'); select * from v$sql_plan where sql_id='b40y04gyun0ux'; --看执行计划更准确 select to_char(t1.SQL_FULLTEXT) from v$sql t1 where t1.SQL_ID='4fkhphkpjauct'; select * from gv$sql_plan t where t.SQL_ID='7qj8bxmdhfsyr'; select * from dba_hist_sqlbind where sql_id='aw7bfzbyc43aw' order by snap_id desc, position asc; select * from v$sqlarea where sql_id='0gb6nymk2hhn9'; select to_char(sql_text) from dba_hist_sqltext where sql_id='5z4bsnrwna3m2'; --explain plan for select * from table(DBMS_XPLAN.DISPLAY_CURSOR('ckvu1mannj1jy',0, 'advanced')); select * from table(dbms_xplan.display_awr('aw7bfzbyc43aw')); select * from table(dbms_xplan.display(null,null,'outline')); select * from table(dbms_xplan.display); --查看AWR和CURSOR中的执行计划 select * from table(dbms_xplan.display_awr('35q8hm55fdcbb')); select * from table(dbms_xplan.display_cursor('35q8hm55fdcbb')); --查看sql的历史执行计划 select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan where SQL_ID='59rrva3asy1jz' order by TIMESTAMP; select * from table(dbms_xplan.display_awr('59rrva3asy1jz',null,null,'ADVANCED')); --查看真实执行计划 alter session set statistics_level=all; select count(0) from abp_Int.tb_prd_ofr_inst_21 a where ofr_inst_cd = REGEXP_REPLACE('2-1K9TVKXE', '^', 'SY-') and exists (select 1 from abp_int.tb_prd_ofr_detail_inst_21 b where a.ofr_inst_id = b.ofr_inst_id and b.ofr_detail_inst_ref_id = 22819198 and b.ofr_id = 6100002800 and b.exp_date >= sysdate); select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); --查看内存中的执行计划 select * from table(dbms_xplan.display_cursor('21wxc7qa3cc5v',null,'TYPICAL PEEKED_BINDS')); select '| Operation |Object Name | Rows | Bytes| Cost |' as "Explain Plan in library cache:" from dual union all select rpad('| '||substr(lpad(' ',1*(depth-1))||operation|| decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'|| rpad(decode(id, 0, '----------------------------', substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name) ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ', decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M', trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' || lpad(decode(bytes,null,' ', decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M', trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' || lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M', trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan" from v$sql_plan sp where sp.hash_value=&hash_value or sp.sql_id='&sqlid'; --sql当前执行情况---次数统计 select sql_id, plan_hash_value plan_hash, child_number, sql_profile, decode(executions, 0, 1, executions) executions, round(elapsed_time / 1000000 / decode(executions, 0, 1, executions), 2) avg_etime_s, round(buffer_gets / decode(executions, 0, 1, executions)) avg_lio, round(disk_reads / decode(executions, 0, 1, executions)) avg_pio, round(cpu_time / 1000000 / decode(executions, 0, 1, executions), 2) avg_cputime_s, round(rows_processed / decode(executions, 0, 1, executions)) avg_row, last_load_time from v$sql where sql_id = trim('7gprcytpddth3') order by plan_hash_value, child_number; --sql历史执行情况---次数统计 select * from (select distinct s.snap_id, to_char(s.begin_interval_time, 'mm/dd/yy_hh24mi') || to_char(s.end_interval_time, '_hh24mi') date_time, sql.plan_hash_value plan_hash, sql.executions_delta executions, (sql.elapsed_time_delta / 1000000) / decode(sql.executions_delta, null, 1, 0, 1, sql.executions_delta) avg_etime_s, sql.buffer_gets_delta / decode(sql.executions_delta, null, 1, 0, 1, sql.executions_delta) avg_lio, sql.disk_reads_delta / decode(sql.executions_delta,