일반
2012.02.16 14:32

about SPA testing

댓글 0조회 수 19960
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print
?

단축키

이전 문서

다음 문서

+ - Up Down Comment Print

http://gavinsoorma.com/2011/02/upgrade-to-11g-and-ensure-optimizer-plan-stability-using-sql-plan-baselines/


SQL> select SQL_HANDLE,SQL_TEXT,ENABLED, ACCEPTED from dba_sql_plan_baselines

2 where sql_text like '%10G%';

SQL_HANDLE SQL_TEXT ENA ACC

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

SQL_1c8e20c5c8fa7f6a select /*10G_STS*/ count(*) from myobjects where object_type='VIEW' YES YES

SQL_388c048277282b1f select /*10G_STS*/ count(*) from myobjects where object_type='SYNONYM' YES YES

SQL_8fca037405ff8ce7 select /*10G_STS*/ count(*) from myobjects where object_type <> 'INDEX' YES YES

SQL_dda6e70a26d74f1b select /*10G_STS*/ count(*) from myobjects where object_type like 'PACKAGE%' YES YES

SQL_f915be3f43387642 select /*10G_STS*/ count(*) from myobjects YES YES

SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES NO

SQL_fc83e34606a4b991 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE' YES YES


7 rows selected.


SQL> SET SERVEROUTPUT ON

SQL> SET LONG 10000

SQL> DECLARE report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE( sql_handle => 'SQL_fc83e34606a4b991'); DBMS_OUTPUT.PUT_LINE(report);

END;

/


Inputs:

-------

SQL_HANDLE = SQL_fc83e34606a4b991

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

Plan: SQL_PLAN_gt0z38s3a9fcj44cebc6b

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

Plan was verified: Time used 3.027 seconds.

Plan passed performance criterion: 224.68 times better than baseline plan.

Plan was changed to an accepted plan.


SQL> explain plan for
2 select /*10G_STS*/ count(*) from myobjects where object_type='TABLE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2605150876

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 46 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| MYOBJECTS_IND | 15332 | 134K| 46 (3)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='TABLE')

Note
-----
- SQL plan baseline "SQL_PLAN_gt0z38s3a9fcj44cebc6b" used for this statement





번호 분류 제목 글쓴이 날짜
8 RAC Testing for Changing hostname via "hostname" cmd on Uinux  secret 김형조 2013.09.12
7 일반 ps 에서 보여주는 priority의 의미  secret 김형조 2013.09.12
6 일반 10060 trace name context forever   김형조 2012.02.21
5 일반 Reference Link - Active Data Guard   김형조 2012.02.16
» 일반 about SPA testing   김형조 2012.02.16
3 일반 DBA HIST desc all   김형조 2012.02.16
2 일반 event trace - method   관리자 2012.02.16
1 일반 Known schemas in Oracle   관리자 2012.02.15
Board Pagination ‹ Prev 1 Next ›
/ 1
Designed by hikaru100

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

SketchBook5,스케치북5

O R A C L E L A B 로그인 :)