Re: SQL Profile
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