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
/*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */
begin :q := dbms_sqltune.prepare_awr_statement( begin_snap => :begin_snap, end_snap => :end_snap, basic_filter => :basf, stmt_filter => TRUE, object_filter => NULL, rank1 => :rank1, rank2 => :rank2, rank3 => :rank3, result_percentage => :perc, result_limit => :lmt, attribute_list => :attrList, attribute_selected => :attrSel, flags => dbms_sqltune.FLAG_PREPAWR_WRAPCTOR + dbms_sqltune.FLAG_PREPAWR_NOCKBINDS + dbms_sqltune.FLAG_PREPAWR_INCLBID); end; SELECT sql_id, plan_hash_value, bucket_id, begin_snap, end_snap FROM (SELECT dbid, sql_id, bucket_id, plan_hash_value, begin_snap, end_snap, cpu_plus_io FROM (SELECT dbid, sql_id, bucket_id, plan_hash_value, begin_snap, end_snap, cpu_plus_io, ROW_NUMBER () OVER (PARTITION BY bucket_id ORDER BY cpu_plus_io DESC) AS within_bucket_rnk FROM ( SELECT dbid, sql_id, bucket_id, MAX (plan_hash_value) KEEP (DENSE_RANK LAST ORDER BY cpu_plus_io) plan_hash_value, MAX (begin_snap) KEEP (DENSE_RANK LAST ORDER BY cpu_plus_io) begin_snap, MAX (end_snap) KEEP (DENSE_RANK LAST ORDER BY cpu_plus_io) end_snap, MAX (cpu_plus_io) cpu_plus_io FROM (SELECT dbid, sql_id, plan_hash_value, begin_snap, end_snap, bucket_id, cpu_time + buffer_gets * 10000 AS cpu_plus_io FROM (SELECT dbid, begin_snap, end_snap, sql_id, plan_hash_value, CASE WHEN bucket_id = 0 THEN cpu_time / executions ELSE cpu_time END AS cpu_time, CASE WHEN bucket_id = 0 THEN buffer_gets / executions ELSE buffer_gets END AS buffer_gets, bucket_id FROM ( SELECT dbid, MIN (snap_id) begin_snap, MAX (snap_id) end_snap, sql_id, plan_hash_value, SUM (cpu_time) cpu_time, SUM (buffer_gets) buffer_gets, DECODE ( SUM (executions), 0, 1, SUM (executions)) executions, DECODE ( GROUPING_ID ( snap_id, hour_id, day_id), 3, 0, 5, 1, 6, 2, 3) bucket_id FROM (SELECT f.dbid, f.snap_id, sql_id, plan_hash_value, TO_NUMBER ( TO_CHAR ( begin_interval_time + 0, ‘DDD’)) day_id, (TO_NUMBER ( TO_CHAR ( begin_interval_time + 0, ‘DDD’)) – 1) * 24 + TO_NUMBER ( TO_CHAR ( begin_interval_time + 0, ‘HH24’)) hour_id, cpu_time, buffer_gets, executions FROM (SELECT v1.* FROM (SELECT snap_id, dbid, min_inst_num, sql_id, plan_hash_value, cpu_time, buffer_gets, executions FROM (SELECT snap_id, dbid, min_inst_num, sql_id, plan_hash_value, cpu_time, buffer_gets, executions, ROW_NUMBER () OVER ( PARTITION BY dbid, snap_id ORDER BY (cpu_time + buffer_gets * 10000) DESC) AS within_snap_rnk FROM ( SELECT /*+ cardinality(d 1) */ sq.dbid, sq.snap_id, MIN ( sq.instance_number) min_inst_num, sq.sql_id, plan_hash_value, SUM ( cpu_time_delta) cpu_time, SUM ( buffer_gets_delta) buffer_gets, SUM ( executions_delta) executions FROM dba_hist_sqlstat sq, v$database d, dba_hist_sqltext st WHERE sq.dbid = d.dbid AND st.dbid = sq.dbid AND st.sql_id = sq.sql_id AND sq.snap_id BETWEEN &bid AND &eid AND st.command_type IN (2, 3, 6, 7, 189) AND BITAND ( NVL ( sq.flag, 0), 1) = 0 GROUP BY sq.dbid, sq.snap_id, sq.sql_id, plan_hash_value)) WHERE within_snap_rnk <= 150) v1, ( SELECT sq.sql_id FROM dba_hist_sqlstat sq, v$database d WHERE sq.dbid = d.dbid AND sq.snap_id BETWEEN :bid AND :eid GROUP BY sq.dbid, sq.sql_id HAVING SUM ( NVL ( executions_delta, 0)) >= 2) v2 WHERE v1.sql_id = v2.sql_id) f, dba_hist_snapshot s WHERE f.snap_id = s.snap_id AND f.dbid = s.dbid AND s.instance_number = f.min_inst_num) GROUP BY GROUPING SETS ( (dbid, sql_id, plan_hash_value, snap_id), (dbid, sql_id, plan_hash_value, day_id), (dbid, sql_id, plan_hash_value, hour_id), (dbid, sql_id, plan_hash_value))))) GROUP BY dbid, sql_id, bucket_id) WHERE cpu_plus_io >= DECODE (bucket_id, 0, (10 * 1000000), 1, (60 * 1000000), 2, (240 * 1000000), (480 * 1000000))) WHERE within_bucket_rnk <= 150)ORDER BY bucket_id ASC, cpu_plus_io DESCSELECT sqlset_row (sql_id, force_matching_signature, sql_text, object_list, bind_data, parsing_schema_name, module, action, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env, priority, command_type, first_load_time, stat_period, active_stat_period, other, plan_hash_value, sql_plan, bind_list) FROM (SELECT /*+ first_rows(1) */ sql_id, force_matching_signature, sql_text, CAST (NULL AS SQL_OBJECTS) object_list, bind_data, parsing_schema_name, module, action, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, executions, end_of_fetch_count, optimizer_cost, optimizer_env, NULL priority, command_type, NULL first_load_time, NULL stat_period, NULL active_stat_period, XMLELEMENT (“other_attrs”, XMLELEMENT (“parsing_user_id”, parsing_user_id)).getClobVal () other, plan_hash_value, CAST (NULL AS SQL_PLAN_TABLE_TYPE) sql_plan, CAST (NULL AS SQL_BINDS) bind_list FROM ( (SELECT t1.sql_id, t1.force_matching_signature, sql_text, module, action, selap AS ELAPSED_TIME, scpu AS CPU_TIME, sbgets AS BUFFER_GETS, swrites AS DIRECT_WRITES, sdreads AS DISK_READS, srow AS ROWS_PROCESSED, sfetches AS fetches, sexec AS EXECUTIONS, seofc AS end_of_fetch_count, optimizer_cost, optimizer_env, command_type, parsing_schema_name, parsing_user_id, T1.snap_id, T1.plan_hash_value, T1.dbid, loaded_versions, bind_data FROM (SELECT sql_id, force_matching_signature, action, module, snap_id, dbid, loaded_versions, instance_number, sql_text, command_type, optimizer_env, bind_data, parsing_schema_name, parsing_user_id, plan_hash_value, optimizer_cost FROM (SELECT s.snap_id, s.dbid, s.instance_number, s.sql_id, s.force_matching_signature, sql_text, t.command_type, module, action, fetches_delta AS FETCHES, executions_delta AS EXECUTIONS, end_of_fetch_count_delta AS END_OF_FETCH_COUNT, disk_reads_delta AS DISK_READS, direct_writes_delta DIRECT_WRITES, buffer_gets_delta AS BUFFER_GETS, optimizer_env, rows_processed_delta AS ROWS_PROCESSED, cpu_time_delta AS CPU_TIME, elapsed_time_delta AS ELAPSED_TIME, optimizer_cost, s.parsing_schema_name, s.parsing_user_id, plan_hash_value, loaded_versions, bind_data FROM DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t, DBA_HIST_OPTIMIZER_ENV e, V$DATABASE d WHERE s.sql_id = t.sql_id AND s.dbid = t.dbid AND s.dbid = e.dbid(+) AND s.optimizer_env_hash_value = e.optimizer_env_hash_value(+) AND s.dbid = d.dbid AND /* only capture sqls with the full set of execution stats */ BITAND (NVL (s.flag, 0), 1) = 0)) T1, ( SELECT sql_id, plan_hash_value, SUM (CPU_TIME) AS scpu, SUM (BUFFER_GETS) AS sbgets, SUM (DISK_READS) AS sdreads, SUM (DIRECT_WRITES) AS swrites, SUM (ROWS_PROCESSED) AS srow, SUM (FETCHES) AS sfetches, SUM (EXECUTIONS) AS sexec, SUM (END_OF_FETCH_COUNT) AS seofc, SUM (ELAPSED_TIME) AS selap, MAX (SNAP_ID) KEEP (DENSE_RANK LAST ORDER BY ELAPSED_TIME) AS snap_id, MAX (INSTANCE_NUMBER) KEEP (DENSE_RANK LAST ORDER BY ELAPSED_TIME) AS instance_number FROM (SELECT s.snap_id, s.dbid, s.instance_number, s.sql_id, s.force_matching_signature, sql_text, t.command_type, module, action, fetches_delta AS FETCHES, executions_delta AS EXECUTIONS, end_of_fetch_count_delta AS END_OF_FETCH_COUNT, disk_reads_delta AS DISK_READS, direct_writes_delta DIRECT_WRITES, buffer_gets_delta AS BUFFER_GETS, optimizer_env, rows_processed_delta AS ROWS_PROCESSED, cpu_time_delta AS CPU_TIME, elapsed_time_delta AS ELAPSED_TIME, optimizer_cost, s.parsing_schema_name, s.parsing_user_id, plan_hash_value, loaded_versions, bind_data FROM DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t, DBA_HIST_OPTIMIZER_ENV e, V$DATABASE d WHERE s.sql_id = t.sql_id AND s.dbid = t.dbid AND s.dbid = e.dbid(+) AND s.optimizer_env_hash_value = e.optimizer_env_hash_value(+) AND s.dbid = d.dbid AND /* only capture sqls with the full set of execution stats */ BITAND (NVL (s.flag, 0), 1) = 0) WHERE snap_id >= &bid AND snap_id <= &eid AND command_type IN (1, 2, 3, 6, 7, 189) AND sql_id = ‘&sqlid’ AND plan_hash_value = &phv GROUP BY sql_id, plan_hash_value) T2 WHERE T1.sql_id = T2.sql_id AND T1.plan_hash_value = T2.plan_hash_value AND T1.snap_id = T2.snap_id AND T1.instance_number = T2.instance_number)) S)
promptprompt 15 Most expensive SQL in the cursor cacheprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select * from ( select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,:newl,’ ‘),1,55) as sql_text_fragment from V$SQLSTATS order by elapsed_time desc) where ROWNUM <= 15;promptprompt 15 Most expensive SQL in the workload repositoryprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~select * from ( select stat.sql_id as sql_id, sum(elapsed_time_delta) / 1000000 as elapsed, (select to_char(substr(replace(st.sql_text,:newl,’ ‘),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;
xplore 11.4.3.5 2011/08/10 carlos.sierraToggles CBO init.ora and fix control parameters to discover plansWhen to use:~~~~~~~~~~~Use xplore only when ALL these conditions are met:1. SQL performs poorly or returns wrong results while using a bad plan.2. The bad plan can be reproduced on a test system (no data is preferred).3. A good plan can be reproduced on the test system by switching OFE.4. You need to narrow reason to specific parameter or bug fix control.5. You have full access to the test system, including SYS access.When NOT to use:~~~~~~~~~~~~~~~Do not use xplore when ANY of these conditions is true:1. The SQL statement may cause corruption or update data.2. There is high volume of data in tables referenced by SQL.3. The execution of the SQL may take longer than a few seconds.Install:~~~~~~~1. Connect as SYS and execute install script: # sqlplus / as sysdba SQL> START install.sql Notes: a. You will be asked for the test case user and password. b. Test case user should exist already. Include suffix if any. c. XPLORE has no dependencies on SQLT.Use:~~~1. Set the CBO environment ONLY if needed to reproduce the bad plan. Notes: You may need to issue some ALTER SESSION commands. For example: STATISTICS_LEVEL or “_COMPLEX_VIEW_MERGING”. The CBO enviornment will be then captured into a baseline. The baseline is executed before each test. # sqlplus SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL; — example2. Generate the xplore_script on same session than 1. SQL> START create_xplore_script.sql Notes: It will ask for 5 inline parameters. You can specify XECUTE or XPLAIN method. You will be asked if you want to include parameters for CBO, Exadata and/or Fix Control. If using XECUTE you may also request SQL Monitor Reports with each test.3. Execute generated xplore_script. It will ask for two parameters: P1. Name of the script to be executed. Notes: When using XECUTE method, your SQL must include comment /* ^^unique_id */ in the first few lines of your sql text. Example: SELECT /* ^^unique_id */ t1.col1, etc. P2. Password for 4. After you are done using XPLORE you may want to bounce the database since it executed some ALTER SYSTEM commands: # sqlplus / as sysdba SQL> shutdown immediate SQL> startupUninstall:~~~~~~~~~1. Connect as SYS and execute uninstall script: # sqlplus SQL> START uninstall.sql Note: You will be asked for the test case user.Feedback:~~~~~~~~carlos.sierra@oracle.com
BUG 13582535 – _FIX_CONTROL” = “4887636:OFF——— The customer is having multiple 11.2.0.2. The performance degradation ishappening on two or three environment.- The three environment are completely different database,servers andapplication.- The workaround that the customer was using is to setoptimizer_features_enable to 10.2.0.3 or 9.2.0.8. Anything less than 11g foroptimizer feature enable is working fine.- We build SQLT test case and i found out that after disabling the fixcontrol”_fix_control” = “4887636:OFF”, everything is working fine on anyenvironment.Poor Performance in 11G for Query with ROWNUM predicate and NESTED LOOPS.(Doc ID 551749.1)- According to the bug # 6438892, this issue is fixed in 11gR2. The questionis ‘why we see this same issue in 11.2.0.2?’ Are we hitting different bug?- This is related only to the SQL where ‘group by’ is being used.- The execution plan is too long to fit here. I have SQLT test case in thefollowing location:System Name: celcaix3@ Username: bugmnt (password “my1offtst”)SID: TARABDirectory: /bugmnt3/am/celcaix3/SR3.5114760001/app/oracle/product/11.2.0.2.3- Everything will be located under ‘/bugmnt3/am/celcaix3/SR3.5114760001’- I will upload to this bug the following:1- SQLT without hint (optimizer_features_enable=10.2.0.4).2- SQLT with hint (optimizer_features_enable=10.2.0.4).3- SQLT xplore.- The execution plan the customer is getting using OFE (10.2.0.4) is the bestplan. The execution goes from 8 hours to just 4 or 5 minutes.- We get the same execution plan by setting _fix_control” = “4887636:OFF7/15/13 Bug 13582535 – _FIX_CONTROL” = “4887636:OFF- Are we hitting the same issue in the bug # 6438892 or this is differentbug? Please, know that the bug # 6438892 is fixed in 11.2.0.2- If this is the same bug # 6438892, shall we get a backport for 11.2.0.2?DIAGNOSTIC ANALYSIS:——————–Please, see the problem descriptionWORKAROUND:———–1- optimizer_features_enable= –> anything less than 11g.2- “_fix_control” = “4887636:OFF”