Jump to content

Search the Community

Showing results for tags 'performance'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type



Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests

Found 1 result

  1. Hi Burleson, I have POC on my lab about SQL Plan Management and found some thing that not make sense. My Oracle Database Version is "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production" with a single node, parameter cursor_sharing = FORCE. I created table that skew data with below information which have TEST_SPM_IDX1 on ID1 column and gather stats 4 bucket to make sure oracle create histogram and know they are skewed data on this column. SQL> SELECT ID1,COUNT(*) FROM DEMO.TEST_SPM GROUP BY ID1; ID1 COUNT(*) ---------- ---------- 1 1 2 1000 3 2000 SQL> exec dbms_stats.gather_table_stats(OWNNAME => 'DEMO',TABNAME => 'TEST_SPM',ESTIMATE_PERCENT =>null,METHOD_OPT => 'FOR COLUMNS SIZE 4 ID1'); I simulate query to aspect optimizer create execution plan to use index scan on id1=1 SQL> ALTER SYSTEM FLUSH SHARED_POOL; SQL> select /*TEST_SPM*/ * from demo.test_spm where id1=1; SQL> SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE '%/*TEST_SPM*/%' AND LOWER(SQL_TEXT) NOT LIKE '%HASH%'; SQL_TEXT SQL_ID CHILD# HASH_VALUE PLAN_HASH_VALUE ----------------------------------------------------------------- ------------- ---------- ---------- --------------- select /*TEST_SPM*/ * from demo.test_spm where id1=:"SYS_B_0" dfb3nvbbw2urx 0 3619777277 863343145 SQL> SET PAGESIZE 0 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dfb3nvbbw2urx',NULL,'ALL')); Plan hash value: 863343145 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 241 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | 1000 | 983K| 241 (0)| 00:00:03 | |* 2 | INDEX RANGE SCAN | TEST_SPM_IDX1 | 1000 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- I capture these cursor to SQL Plan baseline by use below statement SQL> variable pls number; exec :pls := dbms_spm.load_plans_from_cursor_cache(SQL_ID => 'dfb3nvbbw2urx'); -- and then i execute with different bind variable with id1=2 (aspect to use full table scan) SQL> select /*TEST_SPM*/ * from demo.test_spm where id1=2; -- view current SQL Plan baseline for 'dfb3nvbbw2urx' SQL> select t.* from (select distinct sql_handle from dba_sql_plan_baselines where SIGNATURE = '10288309437548151870') pb, table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle, null,'basic')) t; -------------------------------------------------------------------------------- SQL handle: SQL_8ec76aec745b443e SQL text: select /*TEST_SPM*/ * from demo.test_spm where id1=:"SYS_B_0" -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_8xjvaxju5qj1yeb1890ae Plan id: 3944255662 Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 1145642998 -------------------------------------- | Id | Operation | Name | -------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_SPM | -------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_8xjvaxju5qj1yf5a65c55 Plan id: 4121320533 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 863343145 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_SPM | | 2 | INDEX RANGE SCAN | TEST_SPM_IDX1 | ----------------------------------------------------- SQL> DECLARE 2 report clob; 3 BEGIN 4 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle =>'SQL_8ec76aec745b443e',COMMIT=>'NO'); 5 DBMS_OUTPUT.PUT_LINE(report); 6 END; 7 / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SQL_8ec76aec745b443e PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = NO Plan: SQL_PLAN_8xjvaxju5qj1yeb1890ae ------------------------------------ Plan was verified: Time used .01 seconds. Plan failed performance criterion: 333 times worse than baseline plan. Baseline Plan Test Plan Stats Ratio ------------- --------- ----------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): .011 .34 .03 CPU Time(ms): 0 .333 0 Buffer Gets: 442 .01 Physical Read Requests: 0 0 Physical Write Requests: 0 0 Physical Read Bytes: 0 0 Physical Write Bytes: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of plans verified: 1 Number of plans accepted: 0 I try to use evolve function and aspect that this function will compare performance for sql_id with match bind variable but as upper result they not as my aspect? so that full table scan execution plan will never accepted plan in this case even full table scan more suit with id1=2 and id1=3 than index scan ? please suggest and thank you very much. Xia,
×