Jump to content

nongxia1

Members
  • Content count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About nongxia1

  • Rank
    Newbie
  • Birthday 03/03/1991

Profile Information

  • Gender
    Male
  • Location
    Thailand
  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,
  2. Strange Performance problem on certain time

    Hi Burleson, Thank you very much for your valued suggestion. I already order you book wait for reading your book.
  3. Strange Performance problem on certain time

    Hi Burleson, Great suggestion Thank you very much . and i have another question about gv$active_session_history as i collect this view on problem period second by second and found that sid=54 was block all application session (as attachement file) but i cann't see sid=54 on this view i question that sid=54 is "inactive" session due to event = "sql*net message from client" on that time ? can i investigate what sid=54 waiting event on that period from this view or another view? Please suggest and thank you for your time .
  4. Hi Burleson, I am a new DBA and love to read your website to research and understand oracle database. I have strange performance problem on my oracle 11gR2 database.My application team found that on every 9:00 application will slow down and timeout some transaction. They collect response time of query that application sent to database and return result to application, very high response time between 9:00 - 9:02 on every day. I investigate Scheduler Job and found nothing. I manage to collect AWR every 2 minute on that period and found event "enq: TX - row lock contention" and "SQL*Net break/reset to client". I already check event "enq: TX - row lock contention" that come from sql statement that "SELECT FOR UPDATE" on the same row, as we investigate that on another period they also use this statement to update same row but not high response time so we focus on event "SQL*Net break/reset to client". I don't understand that what "SQL*Net break/reset to client" mean ? why they are appear on every 9:00 - 9:02 AM. are these event make performance of database slow down or not ? I research on Oracle Doc that show they maybe have some invalid statement from application to database but why there are appear only on 9:00AM. please suggest options for me how to investigate this Thank you very much. 9:00 - 9:02 AM Snap Id Snap Time Sessions Cursors/Session Begin Snap: 40121 12-Sep-17 09:00:00 617 11.9 End Snap: 40122 12-Sep-17 09:02:00 728 10.6 Elapsed: 2.00 (mins) DB Time: 16.86 (mins) Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) % DB time Wait Class enq: TX - row lock contention 387 508 1314 50.25 Application SQL*Net break/reset to client 160 195 1219 19.28 Application DB CPU 127 12.55 log file sync 9,386 49 5 4.85 Commit db file sequential read 1,523 22 15 2.22 User I/O 9:06 - 9:08 AM Snap Id Snap Time Sessions Cursors/Session Begin Snap: 40124 12-Sep-17 09:06:00 708 10.9 End Snap: 40125 12-Sep-17 09:08:00 698 11.0 Elapsed: 2.01 (mins) DB Time: 4.27 (mins) Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 105 40.98 log file sync 10,131 34 3 13.20 Commit db file sequential read 1,621 23 14 8.91 User I/O reliable message 823 3 3 1.03 Other control file sequential read 2,805 2 1 0.89 System I/O
  5. Hi Burleson,

    i have ordered oracle tuning pack at 2 of aug,ORDER #1755 any update for this? can you email or send private message for this order status. Thank you.

×