精华内容
下载资源
问答
  • oracle慢sql跟踪oracle慢sql跟踪查询执行最慢的sql查询当天执行慢的SQL:查询次数最多的SQL完整跟踪仅对增删改跟踪查找最近一分钟内,最消耗CPU的SQL语句:查找最近一分钟内,最消耗CPU的会话:查找最近一分钟内,最...

    oracle慢sql跟踪

    查询执行最慢的sql

    select *
    from (select
    sa.FIRST_LOAD_TIME,
    sa.LAST_ACTIVE_TIME,
    sa.SQL_TEXT,
    sa.SQL_FULLTEXT,
    sa.EXECUTIONS “执行次数”,
    round(sa.ELAPSED_TIME / 1000000, 2) “总执行时间”,
    round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) “平均执行时间”,
    sa.COMMAND_TYPE,
    sa.PARSING_USER_ID “用户ID”,
    u.username “用户名”,
    sa.HASH_VALUE
    from v$sqlarea sa
    left join all_users u
    on sa.PARSING_USER_ID = u.user_id
    where sa.EXECUTIONS > 0
    and u.username=‘ZHSCJG’ --用户名按实际填写
    order by sa.LAST_ACTIVE_TIME desc,(sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
    where rownum <= 50;

    查询当天执行慢的SQL:

    SELECT S.SQL_TEXT,
    S.SQL_FULLTEXT,
    S.SQL_ID,
    ROUND(ELAPSED_TIME / 1000000 / (CASE
    WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
    1
    ELSE
    EXECUTIONS
    END),
    2) “执行时间’S’”,
    S.EXECUTIONS “执行次数”,
    S.OPTIMIZER_COST “COST”,
    S.SORTS,
    S.MODULE, --连接模式(JDBC THIN CLIENT:程序)
    – S.LOCKED_TOTAL,
    S.PHYSICAL_READ_BYTES “物理读”,
    – S.PHYSICAL_READ_REQUESTS “物理读请求”,
    S.PHYSICAL_WRITE_REQUESTS “物理写”,
    – S.PHYSICAL_WRITE_BYTES “物理写请求”,
    S.ROWS_PROCESSED “返回行数”,
    S.DISK_READS “磁盘读”,
    S.DIRECT_WRITES “直接路径写”,
    S.PARSING_SCHEMA_NAME,
    S.LAST_ACTIVE_TIME
    FROM GV$SQLAREA S
    WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE
    WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
    1
    ELSE
    EXECUTIONS
    END),
    2) > 5 --100 0000微秒=1S
    AND S.PARSING_SCHEMA_NAME = USER
    AND TO_CHAR(S.LAST_LOAD_TIME, ‘YYYY-MM-DD’) =
    TO_CHAR( SYSDATE, ‘YYYY-MM-DD’ )
    AND S.COMMAND_TYPE IN (2 , 3, 5, 6 , 189)
    ORDER BY “执行时间’S’” DESC;


    /* SQL中 COMMAND_TYPE意义:
    2:INSERT
    3:SELECT
    6:UPDATE
    7:DELETE
    189:MERGE

    查询次数最多的SQL

    select *
    from (select s.SQL_TEXT,
    s.EXECUTIONS “执行次数”,
    s.PARSING_USER_ID “用户名”,
    rank() over(order by EXECUTIONS desc) EXEC_RANK
    from v$sql s
    left join all_users u
    on u.USER_ID = s.PARSING_USER_ID) t
    where exec_rank <= 100;

    完整跟踪

    select
    a.FIRST_LOAD_TIME
    , a.LAST_ACTIVE_TIME
    ,a.*
    from v

    $

    sqlarea a
    where 1=1
    and a.LAST_ACTIVE_TIME >= to_date(‘2014-12-24 14:12:00’,‘yyyy-MM-dd HH24:mi:ss’)
    – and a.LAST_ACTIVE_TIME < to_date(‘2014-11-25 09:41:59’,‘yyyy-MM-dd HH24:mi:ss’)
    AND (a.MODULE is null or lower(nvl(a.MODULE,’’)) <> ‘pl/sql developer’ )
    –and sql_text like ‘%insert%’
    and not (lower(sql_fulltext) like ‘%qdi_modules%’
    or lower(sql_fulltext) like ‘%qdi_host %’
    or lower(sql_fulltext) like ‘%sm_operatelog%’
    or lower(sql_fulltext) like ‘%sys.scheduler j o b o r l o w e r ( s q l f u l l t e x t ) l i k e ′ _job%' or lower(sql_fulltext) like '%sys.scheduler joborlower(sqlfulltext)like_job_run_details%’
    or lower(sql_fulltext) like ‘%sys.scheduler$_event_log%’
    )
    order by a.last_active_time asc

    仅对增删改跟踪

    select
    a.FIRST_LOAD_TIME
    , a.LAST_ACTIVE_TIME
    ,a.*
    from v$
    sqlarea a
    where 1=1
    and a.LAST_ACTIVE_TIME >= to_date(‘2014-12-24 14:12:00’,‘yyyy-MM-dd HH24:mi:ss’)
    – and a.LAST_ACTIVE_TIME < to_date(‘2014-11-25 09:41:59’,‘yyyy-MM-dd HH24:mi:ss’)
    AND (a.MODULE is null or lower(nvl(a.MODULE,’’)) <> ‘pl/sql developer’ )
    and ( lower(sql_fulltext) like ‘%insert%’ or lower(sql_fulltext) like ‘%update%’ or lower(sql_fulltext) like ‘%delete %’ )
    and not (lower(sql_fulltext) like ‘%qdi_modules%’
    or lower(sql_fulltext) like ‘%qdi_host %’
    or lower(sql_fulltext) like ‘%sm_operatelog%’
    or lower(sql_fulltext) like ‘%sys.scheduler j o b o r l o w e r ( s q l f u l l t e x t ) l i k e ′ _job%' or lower(sql_fulltext) like '%sys.scheduler joborlower(sqlfulltext)like_job_run_details%’
    or lower(sql_fulltext) like ‘%sys.scheduler$_event_log%’
    )
    order by a.last_active_time asc

    查找最近一分钟内,最消耗CPU的SQL语句:

    SELECT ASH.INST_ID,
    ASH.SQL_ID,
    (SELECT VS.SQL_TEXT
    FROM GV S Q L A R E A V S W H E R E V S . S Q L I D = A S H . S Q L I D A N D A S H . I N S T I D = V S . I N S T I D ) S Q L T E X T , A S H . S Q L C H I L D N U M B E R , A S H . S Q L O P N A M E , A S H . S E S S I O N I N F O , C O U N T S , P C T L O A D ∗ 100 ∣ ∣ ′ F R O M ( S E L E C T A S H . I N S T I D , A S H . S Q L I D , A S H . S Q L C H I L D N U M B E R , A S H . S Q L O P N A M E , ( A S H . M O D U L E ∣ ∣ ′ − − ′ ∣ ∣ A S H . A C T I O N ∣ ∣ ′ − − ′ ∣ ∣ A S H . P R O G R A M ∣ ∣ ′ − − ′ ∣ ∣ A S H . M A C H I N E ∣ ∣ ′ − − ′ ∣ ∣ A S H . C L I E N T I D ∣ ∣ ′ − − ′ ∣ ∣ A S H . S E S S I O N T Y P E ) S E S S I O N I N F O , C O U N T ( ∗ ) C O U N T S , R O U N D ( C O U N T ( ∗ ) / S U M ( C O U N T ( ∗ ) ) O V E R ( ) , 2 ) P C T L O A D , D E N S E R A N K ( ) O V E R ( O R D E R B Y C O U N T ( ∗ ) D E S C ) R A N K O R D E R F R O M G V SQLAREA VS WHERE VS.SQL_ID = ASH.SQL_ID AND ASH.INST_ID = VS.INST_ID) SQL_TEXT, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, ASH.SESSION_INFO, COUNTS, PCTLOAD * 100 || '%' PCTLOAD FROM (SELECT ASH.INST_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, (ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' || ASH.SESSION_TYPE) SESSION_INFO, COUNT(*) COUNTS, ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD, DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER FROM GV SQLAREAVSWHEREVS.SQLID=ASH.SQLIDANDASH.INSTID=VS.INSTID)SQLTEXT,ASH.SQLCHILDNUMBER,ASH.SQLOPNAME,ASH.SESSIONINFO,COUNTS,PCTLOAD100FROM(SELECTASH.INSTID,ASH.SQLID,ASH.SQLCHILDNUMBER,ASH.SQLOPNAME,(ASH.MODULEASH.ACTIONASH.PROGRAMASH.MACHINEASH.CLIENTIDASH.SESSIONTYPE)SESSIONINFO,COUNT()COUNTS,ROUND(COUNT()/SUM(COUNT())OVER(),2)PCTLOAD,DENSERANK()OVER(ORDERBYCOUNT()DESC)RANKORDERFROMGVACTIVE_SESSION_HISTORY ASH
    WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
    AND ASH.SESSION_STATE = ‘ON CPU’
    AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
    GROUP BY ASH.INST_ID,
    ASH.SQL_ID,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    (ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
    ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
    ASH.SESSION_TYPE)) ASH
    WHERE RANK_ORDER <= 10

    查找最近一分钟内,最消耗CPU的会话:

    SELECT SESSION_ID,
    COUNT()
    FROM V$ACTIVE_SESSION_HISTORY V
    WHERE V.SESSION_STATE = ‘ON CPU’
    AND V.SAMPLE_TIME > SYSDATE - 10/ (24 * 60)
    GROUP BY SESSION_ID
    ORDER BY COUNT(
    ) DESC;

    查找最近一分钟内,最消耗I/O的SQL语句:

    SELECT ASH.INST_ID,
    ASH.SQL_ID,
    (SELECT VS.SQL_TEXT
    FROM GV$
    SQLAREA VS
    WHERE VS.SQL_ID = ASH.SQL_ID
    AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    ASH.SESSION_INFO,
    COUNTS,
    PCTLOAD * 100 || ‘%’ PCTLOAD
    FROM (SELECT ASH.INST_ID,
    ASH.SQL_ID,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    (ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
    ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
    ASH.SESSION_TYPE) SESSION_INFO,
    COUNT() COUNTS,
    ROUND(COUNT(
    ) / SUM(COUNT()) OVER(), 2) PCTLOAD,
    DENSE_RANK() OVER(ORDER BY COUNT(
    ) DESC) RANK_ORDER
    FROM GV$ACTIVE_SESSION_HISTORY ASH
    WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
    AND ASH.SESSION_STATE = ‘WAITING’
    AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
    AND ASH.WAIT_CLASS = ‘USER I/O’
    GROUP BY ASH.INST_ID,
    ASH.SQL_ID,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    (ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
    ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
    ASH.SESSION_TYPE)) ASH
    WHERE RANK_ORDER <= 10
    ORDER BY COUNTS DESC;

    查找最近一分钟内,最消耗资源的SQL语句:

    SELECT ASH.INST_ID,
    ASH.SQL_ID,
    (SELECT VS.SQL_TEXT
    FROM GV$
    SQLAREA VS
    WHERE VS.SQL_ID = ASH.SQL_ID
    AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    ASH.SESSION_INFO,
    COUNTS,
    PCTLOAD * 100 || ‘%’ PCTLOAD
    FROM (SELECT ASH.INST_ID,
    ASH.SQL_ID,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    (ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
    ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
    ASH.SESSION_TYPE) SESSION_INFO,
    COUNT() COUNTS,
    ROUND(COUNT(
    ) / SUM(COUNT()) OVER(), 2) PCTLOAD,
    DENSE_RANK() OVER(ORDER BY COUNT(
    ) DESC) RANK_ORDER
    FROM GV$ACTIVE_SESSION_HISTORY ASH
    WHERE ASH.SESSION_TYPE <> ‘BACKGROUND’
    AND ASH.SESSION_STATE = ‘WAITING’
    AND ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
    AND ASH.WAIT_CLASS = ‘USER I/O’
    GROUP BY ASH.INST_ID,
    ASH.SQL_ID,
    ASH.SQL_CHILD_NUMBER,
    ASH.SQL_OPNAME,
    (ASH.MODULE || ‘–’ || ASH.ACTION || ‘–’ || ASH.PROGRAM || ‘–’ ||
    ASH.MACHINE || ‘–’ || ASH.CLIENT_ID || ‘–’ ||
    ASH.SESSION_TYPE)) ASH
    WHERE RANK_ORDER <= 10
    ORDER BY COUNTS DESC;

    查找最近一分钟内,最消耗资源的会话:

    SELECT ASH.SESSION_ID,
    ASH.SESSION_SERIAL#,
    ASH.USER_ID,
    ASH.PROGRAM,
    SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 0)) “CPU”,
    SUM(DECODE(ASH.SESSION_STATE, ‘WAITING’, 1, 0)) -
    SUM(DECODE(ASH.SESSION_STATE,
    ‘WAITING’,
    DECODE(ASH.WAIT_CLASS, ‘USER I/O’, 1, 0),
    0)) “WAITING”,
    SUM(DECODE(ASH.SESSION_STATE,
    ‘WAITING’,
    DECODE(ASH.WAIT_CLASS, ‘USER I/O’, 1, 0),
    0)) “IO”,
    SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 1)) “TOTAL”
    FROM V$ACTIVE_SESSION_HISTORY ASH
    WHERE ASH.SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
    GROUP BY ASH.SESSION_ID, ASH.USER_ID, ASH.SESSION_SERIAL#, ASH.PROGRAM
    ORDER BY SUM(DECODE(ASH.SESSION_STATE, ‘ON CPU’, 1, 1));

    查看值得怀疑的SQL

    select substr(to_char(s.pct, ‘99.00’), 2) || ‘%’ load,
    s.executions executes,
    p.sql_text
    from (select address,
    disk_reads,
    executions,
    pct,
    rank() over(order by disk_reads desc) ranking
    from (select address,
    disk_reads,
    executions,
    100 * ratio_to_report(disk_reads) over() pct
    from sys.v_KaTeX parse error: Expected group after '_' at position 108: …s, sys.v_̲sqltext p
    where s.ranking <= 5
    and p.address = s.address
    order by 1, s.address, p.piece;

    查看消耗内存多的sql

    select b.username ,a. buffer_gets ,a.executions,
    a.disk_reads/decode(a.executions,0,1,a.executions),a.sql_text SQL
    from v$sqlarea a,dba_users b
    where a.parsing_user_id = b.user_id
    and a.disk_reads >10000
    order by disk_reads desc;

    查看逻辑读多的SQL

    select *
    from (select buffer_gets, sql_text
    from v$sqlarea
    where buffer_gets > 500000
    order by buffer_gets desc)
    where rownum <= 30;

    查看执行次数多的SQL

    select sql_text, executions
    from (select sql_text, executions from v$sqlarea order by executions desc)
    where rownum < 81;

    查看读硬盘多的SQL

    select sql_text, disk_reads
    from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
    where rownum < 21;

    查看排序多的SQL

    select sql_text, sorts
    from (select sql_text, sorts from v$sqlarea order by sorts desc)
    where rownum < 21;

    展开全文
  • oracle慢sql查找 调优

    2020-12-18 16:28:55
    一、进入sql命令行,找出排队的sql语句 select inst_id,sid,serial#,username uname, status, program, sql_id, SQL_HASH_VALUE sqlhv, sql_child_number chnum, event, blocking_session blks from gv$session ...

    一、进入sql命令行,找出排队的sql语句

    select inst_id,sid,serial#,username uname, status, program, sql_id, SQL_HASH_VALUE sqlhv, sql_child_number chnum, event, blocking_session blks 
    from gv$session 
    where wait_class<>'Idle';
    

    将上面sql执行的结果sql_id,放入下列查询

    select sql_id, sql_fulltext, hash_value
    from gv$sqlarea
    where sql_id= '&sql_id';
    

    二、优化器优化

    以PLSQL为例,按F5进入优化器

    将需要优化的sql输入,并执行
    在这里插入图片描述
    table access full发现全表扫描,增加索引后,再次执行,耗时大大减少

    在这里插入图片描述

    展开全文
  • 线上Oracle准备实现类似MySQL slow query的监控脚本,把查询时间超出定值的SQL定时的发送邮件告警,实现过程记录如下: 主要思路是通过DBA_HIST的几个视图来获取每小时...

    线上Oracle准备实现类似MySQL slow query的监控脚本,把查询时间超出定值的SQL定时的发送邮件告警,实现过程记录如下:

     

    主要思路是通过DBA_HIST的几个视图来获取每小时快照中慢SQL的情况,为了不影响线上环境,这里把脚本部署在了自己的监控端,通过DBLINK定期的抓取线上生产库的数据到监控数据库,并简单的处理后获得csv格式的报表,发送报表至邮箱。

     

    定时脚本 每小时查询一次

    00 * * * *  /opt/scripts/oracle/get_slow_query.sh

     

    脚本内容如下

    [oracle@59-Mysql-Test ~]$ cat /opt/scripts/oracle/get_slow_query.sh

    #!/bin/bash

    errlog="/opt/scripts/oracle/sqlerror.log"

    sq_data="/opt/scripts/oracle/slow_query_data.xls"

    check_file="/opt/scripts/oracle/slowsql_check.log"

    send_mail_check="/opt/scripts/oracle/send_mail.chk"

    export ORACLE_BASE=/u01/app/oracle

    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

    export ORACLE_SID=oramon

    export PATH=/usr/sbin:$PATH

    export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:$PATH

    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

    export CLASSPATH=/u01/app/oracle/product/11.2.0/db_1/JRE:/u01/app/oracle/product/11.2.0/db_1/jlib:/u01/app/oracle/product/11.2.0/db_1/rdbms/jlib

     

    cd /opt/scripts/oracle/

    $ORACLE_HOME/bin/sqlplus -S sqmon/oracle @main  > ${errlog}

    cat ${errlog} | grep -v 'Call completed.' | grep -v '' > ${check_file}

    [ -s ${check_file} ] && /bin/mail -s "Oracle slow query check error" xxx@xxx.com < ${check_file}

    cat ${sq_data} | grep -v '<' >${send_mail_check}

    [ -s ${send_mail_check} ] && /bin/mail -a ${sq_data} -s "OracleDB find slow query,please check" xxx@xxx.com,xxx@xxx.com

     

    [oracle@59-Mysql-Test oracle]$ cat main.sql

    call  pro_get_slow_query();

    set linesize 5000

    set term off verify off feedback off pagesize 999

    set markup html on entmap ON spool on preformat off

    spool slow_query_data.xls

    @get_tables.sql

    spool off

    exit

     

    [oracle@59-Mysql-Test oracle]$ cat get_tables.sql

    select sql_id,elapsed_time,cpu_time,iowait_time,gets,reads,rws,clwait_time,execs,elpe,machine,username,dbms_lob.substr(sqt,4000) from DBA_ORA_SLOW_QUERY where elpe > 10 and machine not in ('rac01','rac02');

     

    存储过程pro_get_slow_query内容如下

    CREATE OR REPLACE PROCEDURE SQMON.pro_get_slow_query

    AS

    BEGIN

    /**********delete old data on sqltext*************/

    delete from local_dba_hist_sqltextas;

    commit;

    insert into local_dba_hist_sqltextas select * from dba_hist_sqltext@dg2;

    commit;

    insert into DBA_ORA_SLOW_QUERY_HISTORY select a.*,sysdate from DBA_ORA_SLOW_QUERY;

    commit;

    delete from DBA_ORA_SLOW_QUERY;

    commit;

    /*

    select * from DBA_ORA_SLOW_QUERY;

    select * from DBA_ORA_SLOW_QUERY_HISTORY;

    */

    /************insert new date ********************/

    insert into  DBA_ORA_SLOW_QUERY

    select v_1.sql_id,

           v_1.elapsed_time,

           v_1.cpu_time,

           v_1.iowait_time,

           v_1.gets,

           v_1.reads,

           v_1.rws,

           v_1.clwait_time,

           v_1.execs,

           v_1.elpe,

           v_2.machine,

           v_2.username,

           v_1.sqt

      from (select s.sql_id,

                   elapsed_time / 1000000 elapsed_time,

                   cpu_time / 1000000 cpu_time,

                   iowait_time / 1000000 iowait_time,

                   gets,

                   reads,

                   rws,

                   clwait_time / 1000000 clwait_time,

                   execs,

                   st.sql_text sqt,

                   elapsed_time / 1000000 / decode(execs, 0, null, execs) elpe

              from (select *

                      from (select sql_id,

                                   sum(executions_delta) execs,

                                   sum(buffer_gets_delta) gets,

                                   sum(disk_reads_delta) reads,

                                   sum(rows_processed_delta) rws,

                                   sum(cpu_time_delta) cpu_time,

                                   sum(elapsed_time_delta) elapsed_time,

                                   sum(clwait_delta) clwait_time,

                                   sum(iowait_delta) iowait_time

                              from dba_hist_sqlstat@HUBSDG2

                             where snap_id >=

                                   (select max(snap_id) - 1

                                      from dba_hist_snapshot@DG2)

                               and snap_id <=

                                   (select max(snap_id)

                                      from dba_hist_snapshot@DG2)

                             group by sql_id

                             order by sum(elapsed_time_delta) desc)

                     where rownum <= 20) s,

                   local_dba_hist_sqltextas st

             where st.sql_id = s.sql_id) v_1

      left join (select distinct a.sql_id, a.machine, b.username

                   from dba_hist_active_sess_history@DG2 a

                   left join dba_users@DG2 b

                     on a.user_id = b.user_id

                  where a.snap_id >=

                        (select max(snap_id) - 1 from dba_hist_snapshot@DG2)

                    and a.snap_id <=

                        (select max(snap_id) from dba_hist_snapshot@DG2)) v_2

        on v_1.sql_id = v_2.sql_id

     order by elpe desc;

    commit;

    END;

    /

     

    上面标绿加粗部分的表不再列出,可以直接通过CTAS格式去创建即可。

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-2134819/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/29320885/viewspace-2134819/

    展开全文
  • oracle慢查询sql

    2021-01-19 12:45:21
    详细主题:通过sql查询出数据库有哪些sql语句执行时长比较 转载:https://www.cnblogs.com/asker009/p/10768298.html 一、查询执行最sql select * from (select sa.SQL_TEXT, sa.SQL_FULLTEXT, sa....

    通过sql查询出数据库有哪些sql语句执行时长比较慢

     

    一、查询执行最慢的sql

    select *
     from (select sa.SQL_TEXT,
            sa.SQL_FULLTEXT,
            sa.EXECUTIONS "执行次数",
            round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
            round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
            sa.COMMAND_TYPE,
            sa.PARSING_USER_ID "用户ID",
            u.username "用户名",
            sa.HASH_VALUE
         from v$sqlarea sa
         left join all_users u
          on sa.PARSING_USER_ID = u.user_id
         where sa.EXECUTIONS > 0
         order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
     where rownum <= 50;

     

    二、查询次数最多的 sql

    select *
     from (select s.SQL_TEXT,
            s.EXECUTIONS "执行次数",
            s.PARSING_USER_ID "用户名",
            rank() over(order by EXECUTIONS desc) EXEC_RANK
         from v$sql s
         left join all_users u
          on u.USER_ID = s.PARSING_USER_ID) t
     where exec_rank <= 100;
    

     

     

    查询次数最多的 sql
    查询执行最慢的sql
    返回数据量过大的Sql

    数据库层面的诊断:慢SQL

    数据库层面的诊断:应用层面,日志和异常和反向分析(线程),数据包

    网络层面:网络流量,本机和远程对比,第三方工具和应用对比。数据包

    配置层面:开发层面,网络层面,应用层面,系统层面

    展开全文
  • oracle慢sql优化思路

    千次阅读 2020-07-04 09:59:18
    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-performance-fundamentals.html#GUID-DD9CAA74-3E0B-48C9-8770-AADB614BC992 Oracle Database 2 Day + Performance Tuning Guide ...
  • oracle-sql查询异常

    2012-07-30 14:29:28
    oracle-sql查询异常,分析缓慢的原因
  • oracle 分析慢sql 并创建索引

    千次阅读 2018-09-04 10:02:18
    第一步:explain plan for SELECT * from table //执行计划(此处的表查询由于隐私,所以没拿出来) 第二步:select * from table(dbms_xplan.display)//查询分析 select * from user_indexes where table_name= '...
  • ORACLE分页查询SQL语句(最有效的分页)

    万次阅读 多人点赞 2018-11-01 16:18:24
    (有ORDER BY写法) (语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。) WITH partdata AS ( SELECT ROWNUM AS rowno, tt.* FROM ( SELECT * FROM k_task t WHERE flight_date ...
  • 为提高oraclesql执行效率,优化性能,通过oracle表分析功能进行调整,能加快sql查询效率2倍以上
  • 实用toad抓oracle中较sql
  • 并发100的情况下响应时间达到2秒多,这对于需求来说是不能接受的,通过分析日志发现有两个表的select语句耗时很高,100-500ms之间,但是sql就是简单的根据主键字段去查询,将sql拿出直接在plsql中执行还是很。...
  • oracle 查看执行最 sql

    万次阅读 2016-12-30 14:57:54
    查询执行最sql select * from (select sa.SQL_TEXT, sa.SQL_FULLTEXT, sa.EXECUTIONS "执行次数", round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间", round(sa.ELAPSED_TIME / 1000000 / sa
  • OracleSQL优化建议

    千次阅读 2018-06-14 13:49:34
    OracleSQL优化建议 下述为34条OracleSQL的优化建议,仅供参考。 (1)选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的...
  • oracle常用查询慢SQL的语句

    千次阅读 2020-10-25 16:37:07
    SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS, EVENT, PROGRAM FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID IN (147877, 147878) AND SESSION_TYPE = 'FOREGROUND' AND SQL_ID = 'da90pycpdy62b' --查看查询sql ...
  • 查询ORACLE执行SQL

    2020-06-09 14:33:41
    SELECT S.SQL_TEXT, S.SQL_FULLTEXT, S.SQL_ID, ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 2) “执行时间’S’”, S.EXECUTIONS ...
  • Oracle 高效SQL

    千次阅读 2005-03-10 20:45:00
    No2:共享SQL为了不重复解析相同的SQL语句,在第一次解析之后, ORACLESQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当...
  • oracle sql语句大全

    千次阅读 多人点赞 2019-08-06 17:16:54
    oracle常用sql语句函数 sql执行顺序 •FROM •WHERE •GROUP BY •HAVING •SELECT •DISTINCT •UNION •ORDER BY 一、Oracle数据库操作 1、创建数据库 create database databasename 2、删除数据库 ...
  • oracle数据库sql语句优化

    千次阅读 2016-07-21 14:30:48
    但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则...
  • oracle常用sql语句

    万次阅读 热门讨论 2016-08-09 08:53:35
    -- 首先,以超级管理员的身份登录oracle  sqlplus sys/bjsxt as sysdba    --然后,解除对scott用户的锁  alter user scott account unlock;  --那么这个用户名就能使用了。  --(默认全局数据库名orcl) ...
  • oracle的10条sql

    千次阅读 2018-07-16 02:40:55
    SELECT *FROM ( SELECT v.sql_id, v.child_number, v.sql_text, v.elapsed_time, v.cpu_time, v.disk_reads, RANK () OVER (ORDER BY v.elapsed_time DESC) elapsed_rank FROM v$sql v ) AWHERE e...
  • Oracle SQL

    千次阅读 2006-10-26 16:38:00
    Oracle SQL关键字: ORACLE的简单介绍 ORACLE公司是一家提供综合技术产品、方案、服务的数据库公司ORACLE数据库设计成可大量存储数据,快速查询数据,保证数据的安全和一致性,跨网络的分布式管理及客户-服务器的...
  • oracle sql查询效率

    2016-05-03 08:20:33
    select * from (select my_table.*,rownum as my_rownum from( select a.iccid, a.batch_id, a.imsi_c as imsi_x, a.state_date as create_date, 'IMSL' as imsi_x_type from recycle_imsic a ...
  • oracle sql加排序变

    2015-02-07 08:34:04
    select vrowid rid from cux_mtl_system_items_v e where e.ORGANIZATION_CODE = '100' ... 如上SQL,去掉ORDER BY 1秒内能查出来,如果加上ORDER BY 至少要20秒以上,这是为啥啊 我是在PLSQL上面操作的
  • ORACLE_SQL性能优化(这个很全的)
  • 最近接到组里的小朋友问我说,开发给他提的一个问题,...通过Oracle慢sql查询定位到2点左右的慢sql,由于我们系统我设定的snap_id是每小时刷一次,所以昨晚两点的snapid就能获取到,通过snap_id找到2-3点左右的慢sql.
  • ORACLEsql

    千次阅读 2013-11-19 21:26:12
    自学ORACLE的笔记,希望有所帮助
  • 查找oracle比较的session和SQL

    千次阅读 2012-08-15 10:51:55
    一、如何找到消耗资源大的Oracle的session及其执行的SQL语句  HP-UX可以用glance,top、IBM- AIX可以用topas、另外可以使用PS命令查看进程。 通过这些程序我们可以找到用系统资源特别大的这些进程的进程号,我们就...
  • oracle数据库执行sql

    千次阅读 2018-04-24 17:49:00
    一条sql突然执行变,耗时9秒,应用是不能改的,只能从数据库方面下手解决步骤思路:1:查看sql是否走索引2:查看索引是否失效3:hint 强制走索引(只是用来查看hint状态下,查询是否更改,应用是不能改的)4:收集...
  • 基于oraclesql优化方法论

    千次阅读 2015-06-19 14:41:17
    Oracle数据库里SQL优化的终极目标就是要缩短目标SQL语句的执行时间。要达到上述目的,我们通常只有如下三种方法可以选择: 1、降低目标SQL语句的资源消耗; 2、并行执行目标SQL语句; 3、平衡系统的资源消耗。 ...
  • oracle 常用sql语句

    千次阅读 2017-09-24 20:08:21
    -- 首先,以超级管理员的身份登录oracle  sqlplus sys/bjsxt as sysdba    --然后,解除对scott用户的锁  alter user scott account unlock;  --那么这个用户名就能使用了。  --(默认全局数据库名orcl) ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 80,450
精华内容 32,180
关键字:

oracle慢sql