Script:常用SQL语句优化脚本

 

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;

  1. 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)

  2. 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;

  3. Pingback: Oracle SQL性能优化专题 – ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN

  4. Pingback: Scripts-Oracle Performance | Steven Toolbox