表分析常用脚本

select * from dba_hist_active_sess_history where  sql_id ='0rns7aq7275wy' order by snap_id desc and snap_id ='63147';

select * from  v$sqlarea where sql_text like '%N_SYS_TERM%'  --0rns7aq7275wy

select * from DBA_HIST_SQL_PLAN where sql_id='0rns7aq7275wy'

select * from dba_hist_sqltext where sql_id='0rns7aq7275wy'

select * from dba_hist_sqlstat where sql_id='0rns7aq7275wy' order by snap_id desc;

--2133636016

select * from dba_hist_snapshot  where dbid ='2133636016' order by end_interval_time desc

   

   select sql_text from

(select sql_text,executions,buffer_gets,disk_reads

from v$sql

where buffer_gets > 100000

or disk_reads > 100000

order by buffer_gets + 100*disk_reads DESC)

where rownum <= 10;

--表分析

-- PENDCER_P_1

select 'T_VCH_USED' 表名,to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') 开始时间 from dual;

BEGIN

SYS.DBMS_STATS.GATHER_TABLE_STATS (

OwnName => 'HTYWDB'

,TabName => 'T_VCH_USED'

,Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'

,Degree => 4

,Cascade => TRUE

,No_Invalidate => FALSE);

END;

/

----------------------------------------------------------------------------------------------

select

round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",

        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",

        sa.*

  from v$sqlarea sa

where sa.sql_text like

       'SELECT count(*) FROM T_VCH_USED WHERE c_doc_id%'

       

       SELECT count(*) FROM T_VCH_USED

       

       select * from dba_hist_sqltext where sql_id='d4u866x7a2gh1'

       

      select * from DBA_HIST_SQL_PLAN where sql_id='d4u866x7a2gh1'

v$active_session_history  dba_hist_active_sess_history

SELECT round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",

        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",

        sa.*

  FROM ( SELECT *

                FROM v$sqlarea where  parsing_schema_name ='HTDOC'

                ORDER BY disk_reads DESC) sa

   WHERE ROWNUM<10  --3c9r1rz3pm9h8

  

   select * from dba_hist_active_sess_history where sql_id ='d4u866x7a2gh1'

  

      select * from DBA_HIST_SQL_PLAN where sql_id='d4u866x7a2gh1'

     

      select * from v$sqlarea where sql_id ='d4u866x7a2gh1'

     

      select *  FROM v$session_wait

     

     

     select

round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",sa.EXECUTIONS,

       round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",

        sa.*

  from v$sqlarea sa

  where  parsing_schema_name ='HTDOC'--sa.sql_id ='3c9r1rz3pm9h8'   and

  and sa.EXECUTIONS <>0

  select ELAPSED_TIME_DELTA / 1000000,c.*  from dba_hist_sqlstat c where sql_id ='d4u866x7a2gh1'

select a.sql_text         SQL语句,

       b.etime           执行耗时,

       c.user_id         用户ID,

       c.SAMPLE_TIME     执行时间,

       c.INSTANCE_NUMBER 实例数,

       u.username        用户名,

       a.sql_id          SQL编号

  from dba_hist_sqltext a,

       (select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime

          from dba_hist_sqlstat

         where ELAPSED_TIME_DELTA / 1000000 >= 1) b,

       dba_hist_active_sess_history c,

       dba_users u

 where a.sql_id = b.sql_id

   and a.sql_id = 'd4u866x7a2gh1'

   and c.user_id = u.user_id

   and b.sql_id = c.sql_id

--查询字段中含有字母的sql

   select * from t_vch_detail where regexp_like(C_PRN_NO, '[[:alpha:]]')