查看SQL执行计划_number of snapshot is over 5 in last hour, don't g-CSDN博客

本文链接：https://blog.csdn.net/zuiying_siebel/article/details/9359627

某些页面执行显示非常慢，需要查看执行计划进行调优，

此时需要用oracle用户登录。

执行
sqlplus "/as sysdba"
alter session set "_hash_join_enabled"=false;
alter session set optimizer_mode=first_rows_10;
alter session set "_optimizer_sortmerge_join_enabled"=false;

alter session set "_optimizer_join_sel_sanity_check"=true;

//相同的SQL,在经过siebel的优化和直接在PLSQL中运行，执行计划可能会不一样。执行上面的语句，两者的执行计划会保持一致

set pagesize 200;
set linesize 2000;
set timing on;
explain plan for $SQL
/
@?/rdbms/admin/utlxpls

exec dbms_stats.gather_table_stats(OWNNAME => 'SIEBEL',TABNAME => 'S_ACT_CONTACT',PARTNAME => NULL,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,BLOCK_SAMPLE => FALSE,METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254',DEGREE => 4,GRANULARITY => 'DEFAULT',CASCADE => true,NO_INVALIDATE => FALSE );

获取AWR

linuxxn:~ # su - oracle

oracle@linuxxn:~>

oracle@linuxxn:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 25 10:18:47 2019

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1470771342 U2KDB 1 U2KDB

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

* 1470771342 1 U2KDB U2KDB linuxxn

Using 1470771342 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 3

Listing the last 3 days of Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

U2KDB U2KDB 8686 23 May 2019 00:00 1

8687 23 May 2019 01:01 1

8688 23 May 2019 02:00 1

8689 23 May 2019 03:00 1

8690 23 May 2019 04:00 1

8691 23 May 2019 05:00 1

8692 23 May 2019 06:00 1

8693 23 May 2019 07:00 1

8694 23 May 2019 08:00 1

8695 23 May 2019 09:01 1

8696 23 May 2019 10:00 1

8697 23 May 2019 11:00 1

8698 23 May 2019 12:00 1

8699 23 May 2019 13:00 1

8700 23 May 2019 14:00 1

8701 23 May 2019 15:00 1

8702 23 May 2019 16:00 1

8703 23 May 2019 17:03 1

8704 23 May 2019 18:00 1

8705 23 May 2019 19:00 1

8706 23 May 2019 20:00 1

8707 23 May 2019 21:00 1

8708 23 May 2019 22:00 1

8709 23 May 2019 23:00 1

8710 24 May 2019 00:00 1

8711 24 May 2019 01:00 1

8712 24 May 2019 02:00 1

8713 24 May 2019 03:01 1

8714 24 May 2019 04:00 1

8715 24 May 2019 05:00 1

8716 24 May 2019 06:00 1

8717 24 May 2019 07:00 1

8718 24 May 2019 08:00 1

8719 24 May 2019 09:00 1

8720 24 May 2019 10:00 1

8721 24 May 2019 11:00 1

8722 24 May 2019 12:00 1

8723 24 May 2019 13:00 1

8724 24 May 2019 14:00 1

8725 24 May 2019 15:00 1

8726 24 May 2019 16:00 1

8727 24 May 2019 17:00 1

8728 24 May 2019 18:00 1

8729 24 May 2019 19:00 1

8730 24 May 2019 20:00 1

8731 24 May 2019 21:00 1

8732 24 May 2019 22:00 1

8733 24 May 2019 23:00 1

8734 25 May 2019 00:01 1

8735 25 May 2019 01:00 1

8736 25 May 2019 02:00 1

8737 25 May 2019 03:00 1

8738 25 May 2019 04:00 1

8739 25 May 2019 05:00 1

8740 25 May 2019 06:00 1

8741 25 May 2019 07:00 1

Snap

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

U2KDB U2KDB 8742 25 May 2019 08:00 1

8743 25 May 2019 09:00 1

8744 25 May 2019 10:00 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 8686

Begin Snapshot Id specified: 8686

Enter value for end_snap: 8744

End Snapshot Id specified: 8744

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_8686_8744.html. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_8686_8744.html