SQLT 下载 https://www.askmac.cn/wp-content/uploads/2011/01/sqlt.zip 

[oracle@PD009 xplore]$ pwd
/home/oracle/sqlt/utl/xplore
[oracle@PD009 xplore]$ ls



create_xplore_script.sql  drop_sys_views.sql  drop_user_objects.sql  install.sql  readme.txt  star.sql  sys_views.sql  uninstall.sql  user_objects.sql  xplore_2.zip  xplore.pkb  xplore.pks  xplore_script_1.log  xplore_script_1.sql


SQL> start install
Test Case User: SH
Password: oracle



Installation completed.
You are now connected as SH.

1. Set CBO env if needed
2. Execute @create_xplore_script.sql



SQL> @create_xplore_script.sql

Parameter 1:
XPLORE Method: XECUTE (default) or XPLAIN
"XECUTE" requires /* ^^unique_id */ token in SQL
"XPLAIN" uses "EXPLAIN PLAN FOR" command
Enter "XPLORE Method" [XECUTE]: 

Parameter 2:
Include CBO Parameters: Y (default) or N
Enter "CBO Parameters" [Y]: 

Parameter 3:
Include Exadata Parameters: Y (default) or N
Enter "EXADATA Parameters" [Y]: 

Parameter 4:
Include Fix Control: Y (default) or N
Enter "Fix Control" [Y]: 

Parameter 5:
Generate SQL Monitor Reports: N (default) or Y
Only applicable when XPLORE Method is XECUTE
Enter "SQL Monitor" [N]: 


Review and execute @xplore_script_1.sql




SQL>@xplore_script_1.sql
SH

Parameter 1:
Name of SCRIPT file that contains SQL to be xplored (required)
Note: SCRIPT must contain comment /* ^^unique_id */

Enter value for 1: star.sql

[oracle@PD009 xplore]$ cat star.sql


SELECT /* ^^unique_id */ c.cust_city,
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
  FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
 WHERE s.time_id = t.time_id
   AND s.cust_id = c.cust_id
   AND s.channel_id = ch.channel_id
   AND c.cust_state_province = 'FL'
   AND ch.channel_desc = 'Direct Sales'
   AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
 GROUP BY c.cust_city, t.calendar_quarter_desc;


awr trend

alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."

WITH sysstat AS
 (select sn.begin_interval_time begin_interval_time,
         sn.end_interval_time end_interval_time,
         ss.stat_name stat_name,
         ss.value e_value,
         lag(ss.value, 1) over(order by ss.snap_id) b_value
    from dba_hist_sysstat ss, dba_hist_snapshot sn
   where  
       ss.snap_id = sn.snap_id
     and ss.dbid = sn.dbid
     and ss.instance_number = sn.instance_number
     and ss.dbid = (select dbid from v$database)
     and ss.instance_number = (select instance_number from v$instance)
     and ss.stat_name = 'DB time')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/

生成的XPLORE Report  xplore_report_2

select /*+ dynamic_sampling(b 10) dynamic_sampling_est_cdn(b) gather_plan_statistics*/ count(*) from tvb b;
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

注意dynamic sampling used for this statement (level=2) 显示的level 2不是真的！ level 10在这里真的是LEVEL 10!

EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR
select count(*) from tvb ;

SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));

set linesize 200 pagesize 1400;

select /* FINDSQLID */ SQL_ID,SQL_FULLTEXT from V$SQL  where SQL_TEXT LIKE '%&SQLTEXT%'  and SQL_FULLTEXT NOT LIKE '%FINDSQLID%'
union all
select SQL_ID,SQL_TEXT FROM DBA_HIST_SQLTEXT where SQL_TEXT LIKE '%&SQLTEXT%'
and SQL_TEXT NOT LIKE '%FINDSQLID%';

alter session set events '10046 trace name context forever,level 12';
alter session set events '10053 trace name context forever,level 1';

alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
-- Execute the queries or operations to be traced here --

1.- Please provide AWR and ADDM report from each instance for interval of 30 minutes when the problem is present.
2.- Upload OS log file /var/log/messages
3.- Please upload background process trace files for each instance. LMD, LMS, LMON, DBWR, LGWR, diag, pmon, smon, etc.

有问题请去http://t.askmac.cn/forum-4-1.html提问， 会在一定时间内反馈给你
提问请写明 数据库版本、OS版本、问题类型
如果是性能问题请给出 AWR、ASH、ADDM及10046 TRACE
如果是ORA-600/7445错误请给出ALERT.LOG及其TRACE
如果是RAC CLUTERWARE问题请给出CRSD.LOG和CSSD.LOG

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm like '%disable%';

select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from test;

select spid,pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));

select name,value from v$system_parameter where ISDEFAULT!='TRUE'  order by 1;

set linesize 200 pagesize 1400
@?/rdbms/admin/utllockt

==========================================================================================>

执行计划历史

Want to Know if Execution Plan Changed Recently?

set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'

col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/

xp_awr.sql

select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));

SELECT to_char(TIME,'hh24:mi') , S.*
  FROM (SELECT NVL(WAIT_CLASS, 'CPU') ACTIVITY,
               TRUNC(SAMPLE_TIME, 'MI') TIME
          FROM GV$ACTIVE_SESSION_HISTORY) V   PIVOT(COUNT(*)  FOR ACTIVITY IN ('CPU' AS "CPU", 'Concurrency' AS "Concurrency", 'System I/O' AS "System I/O", 'User I/O' AS "User I/O", 'Administrative' AS "Administrative", 'Configuration' AS "Configuration", 'Application' AS "Application", 'Network' AS "Network", 'Commit' AS "Commit", 'Scheduler' AS "Scheduler", 'Cluster' AS "Cluster", 'Queueing' AS "Queueing", 'Other' AS "Other"))
 S
 WHERE TIME > SYSDATE - INTERVAL '500' MINUTE
 ORDER BY TIME

   SELECT *
    FROM (SELECT '1.v$sql'||'实例号:'||GV$SQL.inst_id source,
                 SQL_ID,
                 plan_hash_value,
                 TO_CHAR (FIRST_LOAD_TIME) begin_time,
                 '在cursor cache中' end_time,
                 executions "No. of exec",
                 (buffer_gets / executions) "LIO/exec",
                 (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec",
                 (disk_reads / executions) "PIO/exec",
                 (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM Gv$SQL 
           WHERE sql_id = '&A'
          UNION ALL
          SELECT '2.sqltuning set' source,
                 sql_id,
                 plan_hash_value,
                 'JUST SQLSET NO DATE' begin_time,
                 'JUST SQLSET NO DATE' end_time,
                 EXECUTIONS "No. of exec",
                 (buffer_gets / executions) "LIO/exec",
                 (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec",
                 (disk_reads / executions) "PIO/exec",
                 (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM dba_sqlset_statements
           WHERE SQL_ID = '&A'
          UNION ALL
          SELECT '3.dba_advisor_sqlstats' source,
                 sql_id,
                 plan_hash_value,
                 'JUST SQLSET NO DATE' begin_time,
                 'JUST SQLSET NO DATE' end_time,
                 EXECUTIONS "No. of exec",
                 (buffer_gets / executions) "LIO/exec",
                 (cpu_time / executions / 1000000) "CPUTIM/exec",
                 (elapsed_time / executions / 1000000) "ETIME/exec",
                 (disk_reads / executions) "PIO/exec",
                 (ROWS_PROCESSED / executions) "ROWs/exec"
            FROM dba_sqlset_statements
           WHERE SQL_ID = '&A'
          UNION ALL
          SELECT DISTINCT
                 '4.dba_hist_sqlstat' || '实例号:' || SQL.INSTANCE_NUMBER
                    source,
                 sql_id,
                 PLAN_HASH_VALUE,
                 TO_CHAR (s.BEGIN_INTERVAL_TIME ,'YYYY-MM-DD hh24:mi:ss') begin_time,
                 TO_CHAR (s.END_INTERVAL_TIME,'YYYY-MM-DD hh24:mi:ss') end_time,
                 SQL.executions_delta,
                 SQL.buffer_gets_delta
                 / DECODE (NVL (SQL.executions_delta, 0),
                           0, 1,
                           SQL.executions_delta)
                    "LIO/exec",
                 (SQL.cpu_time_delta / 1000000)
                 / DECODE (NVL (SQL.executions_delta, 0),
                           0, 1,
                           SQL.executions_delta)
                    "CPUTIM/exec",
                 (SQL.elapsed_time_delta / 1000000)
                 / DECODE (NVL (SQL.executions_delta, 0),
                           0, 1,
                           SQL.executions_delta)
                    "ETIME/exec",
                 SQL.DISK_READS_DELTA
                 / DECODE (NVL (SQL.executions_delta, 0),
                           0, 1,
                           SQL.executions_delta)
                    "PIO/exec",
                 SQL.ROWS_PROCESSED_DELTA
                 / DECODE (NVL (SQL.executions_delta, 0),
                           0, 1,
                           SQL.executions_delta)
                    "ROWs/exec"
            FROM dba_hist_sqlstat SQL, dba_hist_snapshot s
           WHERE     SQL.INSTANCE_NUMBER = s.INSTANCE_NUMBER
                 AND SQL.dbid = (SELECT dbid FROM v$database)
                 AND s.snap_id = SQL.snap_id
                 AND sql_id IN ('&A'))
ORDER BY source, begin_time DESC;


prompt 15 Most expensive SQL in the cursor cache


SELECT *
  FROM (SELECT SQL_ID,
               ELAPSED_TIME / 1000000 AS ELAPSED,
               SQL_TEXT
          FROM V$SQLSTATS
         ORDER BY ELAPSED_TIME DESC)
 WHERE ROWNUM <= 15;

prompt 15 Most expensive SQL in the workload repository


select * from (
select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed,
(select to_char(substr(st.sql_text,1,55))
from dba_hist_sqltext st
where st.dbid = stat.dbid and st.sql_id = stat.sql_id) as sql_text_fragment
from dba_hist_sqlstat stat, dba_hist_sqltext text
where stat.sql_id = text.sql_id and
stat.dbid = text.dbid
group by stat.dbid, stat.sql_id
order by elapsed desc
) where ROWNUM <= 15;

19c New Feature:Real-time SQL Monitoring for Developers

Mainly scenarios is following :

    1.Database users can generate and view SQL monitor report of the SQL statements issued by themself, without granting any additional privileges.
    2.If users have not been granted the SELECT_CATALOG_ROLE ,they can not generate and view SQL monitor report of SQL statements executed by other users.
    3.If users have been granted the SELECT_CATALOG_ROLE ,they can see SQL monitor report of SQL executed by other users .
And you can generate and view SQL monitoring report from the SQL*PLUS command line by DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST and DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST package or from Enterprise Manager (EM) just as 11g (see Doc ID 1229904.1 and Doc ID 1380492.1).

TEST CASE:


    --1.create users
    conn / as sysdba

    --1-1.create Low-privileged users without DBA privilege
    --dep1 for SQL Statement using Tables

    drop user dep1 cascade;
    create user dep1 identified by dep1;
    alter user dep1 quota unlimited on users;
    grant connect,resource to dep1;

    --2.prepare test data
    conn dep1/dep1

    drop table testa_dep1;
    drop table testb_dep1;
    create table testa_dep1(c1 number, c2 char(100));
    create table testb_dep1(c1 number, c2 char(100));

    begin
      for i in 1 .. 200 loop
        for j in 1 .. 100 loop
          insert into testa_dep1 values(i,'A');
          commit;
        end loop;
      end loop;
    end;
    /

    begin
      for i in 1 .. 200 loop
        for j in 1 .. 100 loop
          insert into testb_dep1 values(i,'A');
          commit;
        end loop;
      end loop;
    end;
    /

    --2.Executing SQL and check SQL Monitor Active Report 
    conn dep1/dep1

    select /*+ use_nl(a b) */ count(*)
     from testa_dep1 a, testb_dep1 b
     where a.c1=b.c1;


    --3.Generate and view SQL Monitor List and Active Report 
    --should be able to view SQL monitor report of the SQL statements issued by user-self, without granting any additional privileges.
    --should not be able to view SQL monitor report of SQL statements issued by other users.

    --REPORT_SQL_MONITOR_LIST
    SET LONG 1000000
    SET LONGCHUNKSIZE 1000000
    SET LINESIZE 1000
    SET PAGESIZE 0
    SET TRIM ON
    SET TRIMSPOOL ON
    SET ECHO OFF
    SET FEEDBACK OFF
    spool monitor_list_sql_dep1_active.html
    SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'ACTIVE',report_level => 'ALL') AS report FROM dual;
    spool off


    --REPORT_SQL_MONITOR
    set trimspool on
    set trim on
    set pages 0
    set linesize 1000
    set long 1000000
    set longchunksize 1000000
    spool monitor_sql_dep1.html
    select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
    spool off