2011年1月6日

Script:常用SQL语句优化脚本

mac

Author

32 min

Read Time

Views

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

Ask_Maclean_liu_Oracle 2013-06-01

/*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */

Ask_Maclean_liu_Oracle 2013-06-28

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)

maclean 2013-07-08

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;

Oracle SQL性能优化专题 – ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN 2013-08-16

[…] Script:常用SQL语句优化脚本 […]

Scripts-Oracle Performance | Steven Toolbox 2014-02-27

[…] Script:常用SQL语句优化脚本 […]

Ask_Maclean_liu_Oracle 2014-10-29

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

Ask_Maclean_liu_Oracle 2014-10-29

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"

评论 (7)