Re: SQL Profile

From: Karl Arao <karlarao_at_gmail.com>
Date: Mon, 27 Jul 2009 19:14:32 +0800
Message-ID: <12ee65600907270414y78d96dfbve2b51571b334b9a7_at_mail.gmail.com>



Hi Kerry,

Thanks for that info, sorry for the late response. I was swamped with work and got sick this weekend.

Yes I have verified that the SQL is still using the profile,

SQL_TEXT
          SQL_ID HASH_VALUE ADDRESS CHILD_NUMBER PLAN_HASH_VALUE SQL_PROFILE


  • ---------- ---------------- ------------ ---------------
    SELECT WRK.EMPLID ,WRK.PRC_ORD_TS ,WRK.EMPL_RCD ,WRK.CAL_ID ,WRK.RSLT_SEG_NUM ,P g0pbvgnt34jfn 842155476 00000001A1472C48 0 158280707 SYS_SQLPROF_0148a97b2445c001

1 row selected.

but I can't really tell if there was a prior plan because the AWR retention was only set to 7 days. So I can only tell from the data that I have that there's only one plan_hash_value and it's giving them a sudden headache...

SQL_ID PLAN_HASH_VALUE BEGIN_INTERVAL_TIM SNAP_ID HOURS

------------- --------------- ------------------ ---------- ----------
g0pbvgnt34jfn       158280707 16-JUL-09 13:00:28        443 .963948959
g0pbvgnt34jfn                 16-JUL-09 14:00:30        444 1.94081776
g0pbvgnt34jfn                 16-JUL-09 15:00:31        445 2.26967715
g0pbvgnt34jfn                 17-JUL-09 19:00:30        473 .472475268
g0pbvgnt34jfn                 17-JUL-09 20:00:31        474  1.4477344
g0pbvgnt34jfn                 17-JUL-09 21:00:33        475 2.42456821
g0pbvgnt34jfn                 17-JUL-09 22:00:34        476  3.4029314
g0pbvgnt34jfn                 17-JUL-09 23:00:35        477 4.37847449
g0pbvgnt34jfn                 18-JUL-09 00:00:36        478  5.3553517
g0pbvgnt34jfn                 18-JUL-09 01:00:37        479 6.33208665
g0pbvgnt34jfn                 18-JUL-09 02:00:39        480  7.3090762
g0pbvgnt34jfn                 18-JUL-09 03:00:40        481 8.28600562
g0pbvgnt34jfn                 18-JUL-09 04:00:41        482 9.26283679
g0pbvgnt34jfn                 18-JUL-09 05:00:42        483 10.2413281
g0pbvgnt34jfn                 18-JUL-09 06:00:43        484 11.2182852
g0pbvgnt34jfn                 18-JUL-09 07:00:45        485 12.1935523
g0pbvgnt34jfn                 18-JUL-09 08:00:46        486 13.1704089
g0pbvgnt34jfn                 18-JUL-09 09:00:47        487 14.1472895
g0pbvgnt34jfn                 18-JUL-09 10:00:49        488 15.1258013
g0pbvgnt34jfn                 18-JUL-09 11:00:50        489 16.1010179
g0pbvgnt34jfn                 18-JUL-09 12:00:51        490 17.0779127
g0pbvgnt34jfn                 18-JUL-09 13:00:52        491 17.6645101
g0pbvgnt34jfn                 22-JUL-09 16:00:18        590 .692287076
g0pbvgnt34jfn                 22-JUL-09 17:00:19        591 1.66930926
g0pbvgnt34jfn                 22-JUL-09 18:00:20        592 2.64625218
g0pbvgnt34jfn                 22-JUL-09 19:00:22        593 3.62319036
g0pbvgnt34jfn                 22-JUL-09 20:00:23        594 3.70975912
g0pbvgnt34jfn                 23-JUL-09 16:00:27        614 .843130081
g0pbvgnt34jfn                 23-JUL-09 17:00:28        615 1.82019513
g0pbvgnt34jfn                 23-JUL-09 18:00:29        616 2.79714654
g0pbvgnt34jfn                 23-JUL-09 19:00:31        617  3.7740758
g0pbvgnt34jfn                 23-JUL-09 20:00:32        618 4.75088779


This is a packaged application (Peoplesoft). What we did.. we played with hints, and it was success.. or should I say luck? :p

Well, plan stability is still a "gray area" for me.. I need some more time to do some test cases and study..

Thanks for the help guys :)

Received on Mon Jul 27 2009 - 06:14:32 CDT

Original text of this message