SQLT 下载 https://www.askmaclean.com/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.askmaclean.com/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
Leave a Reply to Ask_Maclean_liu_Oracle Cancel reply