The NO_INVALIDATE Option in DBMS_STATS with 10g

I had worked hard on a report, 47 SQL statements in all to tune it down from 5 hrs to under 30 minutes.  The first runs had been quite successful, so when a third run sent an alert on temp usage, I knew something was wrong.

payday loans lenders online

     SID PROCESS      MACHINE SQL_TEXT    SQL_ID     TOTAL MB
-------- ------------ -------------------- ---------------------
     507 1137	      HOST SELECT *** 7t3muww36xhzn  45516
     600 1139	      HOST SELECT *** 7t3muww36xhzn  45516
     525 1132	      HOST SELECT *** 7t3muww36xhzn  45516
     509 1135	      HOST SELECT *** 7t3muww36xhzn  45516

I checked the stats first, as one of the fixes was to ensure the staging tables in this process were collecting stats after the initial feeds came in, but both tables involved showed valid statistics:

SQL> select num_rows, last_analyzed from dba_tab_partitions 2 where table_name='<I_STAGE>' 3 and partition_name='P170';

  NUM_ROWS LAST_ANAL
---------- ---------
 480900000 17-OCT-11
SQL> select last_analyzed from dba_tables 2 where table_name='<SML_TBL>';

LAST_ANAL
---------
17-OCT-11

I ran a quick AWR report for the specific SQL_ID to see what I was dealing with, execution plan wise..


              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     46198 18-Oct-11 09:00:09       282       7.9
  End Snap:     46200 18-Oct-11 10:00:13       245       8.4
   Elapsed:               60.07 (mins)
   DB Time:            1,874.62 (mins)

 

SQL ID: 7t3muww36xhzn           DB/Inst: PRODUCTION/PROD  Snaps: 46198-46200
-> 1st Capture and Last Capture Snap IDs
   refer to Snapshot IDs witin the snapshot range
-> SELECT ***

    Plan Hash           Total Elapsed                 1st Capture   Last Capture
#   Value                    Time(ms)    Executions       Snap ID        Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1   324636810               4,785,428             4         46199          46199
2   4097803110                  1,047             1         46200          46200
          -------------------------------------------------------------

Plan 1(PHV: 324636810)
----------------------

Plan Statistics                 DB/Inst: PRODBASE/prodbase  Snaps: 46198-46200
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                         4,785,428    1,196,357.0     4.3
CPU Time (ms)                             3,450,070      862,517.6     8.4
Executions                                        4            N/A     N/A
Buffer Gets                                 759,453      189,863.3     0.0
Disk Reads                                  683,619      170,904.8     2.8
Parse Calls                                      35            8.8     0.0
Rows                                              0            0.0     N/A
Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |     9 |       |       |        |      |
|   1 |  COUNT STOPKEY                |             |       |       |       |       |       |        |      |
|   2 |   PX COORDINATOR              |             |       |       |       |       |       |        |      |
|   3 |    PX SEND QC (ORDER)         | :TQ10002    |     1 |   240 |     9 |       |       |  Q1,02 | P->S | QC (ORDER)
|   4 |     VIEW                      |             |     1 |   240 |     9 |       |       |  Q1,02 | PCWP |
|   5 |      SORT ORDER BY STOPKEY    |             |     1 |   120 |     9 |       |       |  Q1,02 | PCWP |
|   6 |       PX RECEIVE              |             |     1 |   240 |       |       |       |  Q1,02 | PCWP |
|   7 |        PX SEND RANGE          | :TQ10001    |     1 |   240 |       |       |       |  Q1,01 | P->P | RANGE
|   8 |         SORT ORDER BY STOPKEY |             |     1 |   240 |       |       |       |  Q1,01 | PCWP |
|   9 |          HASH JOIN            |             |     1 |   120 |     5 |       |       |  Q1,01 | PCWP |
|  10 |           PX RECEIVE          |             |     1 |   109 |     3 |       |       |  Q1,01 | PCWP |
|  11 |            PX SEND BROADCAST  | :TQ10000    |     1 |   109 |     3 |       |       |  Q1,00 | P->P | BROADCAST
|  12 |             PX BLOCK ITERATOR |             |     1 |   109 |     3 |   KEY |   KEY |  Q1,00 | PCWC |
|  13 |              TABLE ACCESS FULL| I_STAGE     |     1 |   109 |     3 |   KEY |   KEY |  Q1,00 | PCWP |
|  14 |           PX BLOCK ITERATOR   |             |  5002 | 55022 |     2 |       |       |  Q1,01 | PCWC |
|  15 |            TABLE ACCESS FULL  | SML_TBL     |  5002 | 55022 |     2 |       |       |  Q1,01 | PCWP |
------------------------------------------------------------------------------------------------------------------------

This is the one that was eating up all the temp! Note that even though I checked stats, stats were correct as of the previous day, no changes to the partition stats, the execution plan only shows one row, which anyone who listens to Maria Colgan knows, that’s just Oracle giving you the benefit of the doubt and saying, “I don’t think there’s any rows in this object, (or sub-object in this case..) but I’ll give you 1 row for the fun of it!”

Second execution plan in the report is the one I desired:

Plan 2(PHV: 4097803110)
-----------------------

Plan Statistics                 DB/Inst: PRODUCTION/PROD  Snaps: 46198-46200
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100

Stat Name                                Statement   Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms)                             1,047        1,046.7     0.0
CPU Time (ms)                                   967          967.0     0.0
Executions                                        1            N/A     N/A
Buffer Gets                                   2,007        2,007.0     0.0
Disk Reads                                        3            3.0     0.0
Parse Calls                                       9            9.0     0.0
Rows                                             40           40.0     N/A
User I/O Wait Time (ms)                           1            N/A     N/A
Execution Plan
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |       |       |       |  2834 |       |       |        |      |
|   1 |  COUNT STOPKEY                |             |       |       |       |       |       |       |        |      |
|   2 |   PX COORDINATOR              |             |       |       |       |       |       |       |        |      |
|   3 |    PX SEND QC (ORDER)         | :TQ10002    |  1272K|   291M|       |  2834 |       |       |  Q1,02 | P->S | QC
|   4 |     VIEW                      |             |  1272K|   291M|       |  2834 |       |       |  Q1,02 | PCWP |
|   5 |      SORT ORDER BY STOPKEY    |             |  1272K|   152M|   389M|  2834 |       |       |  Q1,02 | PCWP |
|   6 |       PX RECEIVE              |             |    40 |  9600 |       |       |       |       |  Q1,02 | PCWP |
|   7 |        PX SEND RANGE          | :TQ10001    |    40 |  9600 |       |       |       |       |  Q1,01 | P->P | RA
|   8 |         SORT ORDER BY STOPKEY |             |    40 |  9600 |       |       |       |       |  Q1,01 | PCWP |
|   9 |          HASH JOIN            |             |  1272K|   152M|       |     7 |       |       |  Q1,01 | PCWP |
|  10 |           PX RECEIVE          |             |  5005 | 55055 |       |     2 |       |       |  Q1,01 | PCWP |
|  11 |            PX SEND BROADCAST  | :TQ10000    |  5005 | 55055 |       |     2 |       |       |  Q1,00 | P->P | BR
|  12 |             PX BLOCK ITERATOR |             |  5005 | 55055 |       |     2 |       |       |  Q1,00 | PCWC |
|  13 |              TABLE ACCESS FULL| SML_TBL     |  5005 | 55055 |       |     2 |       |       |  Q1,00 | PCWP |
|  14 |           PX BLOCK ITERATOR   |             |  1271K|   139M|       |     4 |   KEY |   KEY |  Q1,01 | PCWC |
|  15 |            TABLE ACCESS FULL  | I_STAGE     |  1271K|   139M|       |     4 |   KEY |   KEY |  Q1,01 | PCWP |
------------------------------------------------------------------------------------------------------------------------

So what changed? What impacted my statistics?

Upon investigation, I came to the conclusion that it is a combination of a “feature” with what I think is a bug in 10g dbms_stats.

A search of stats processing showed that during the one process that was executing against the P170 partition on the I_STAGE, there were a number of other partitions in this same table having stats gathered post loading.

declare v_stage_table_name varchar2(64); begin select min(stage_table_name) into v_stage_table_name from stage_tables 
where stage_table_type_cd = '<I_STAGE>'; dbms_stats.gather_table_stats (ownname => 'dw_user', tabname => v_stage_table_name, 
partname => 'P450' ,estimate_percent =>.01, granularity=>'PARTITION', method_opt=>'for all columns size 1', 
no_invalidate=> false, cascade=>false, degree=>4); end;

Now the key here in the statement above is:

no_invalidate=>false

If you read the description for this from Oracle:

no_invalidate Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE. to have Oracle decide when to invalidate dependent cursors. This is the default. The default can be changed using the SET_PARAM Procedure.

The surmised bug is one where even though the dbms_stats being performed by another process is partition level, the invalidation of the cursors is across all partitions in the object, causing them all to be invalidated, requiring them to re-parse the SQL.  (There are a number of similar bugs already documented in 10.2.0.4.0 for partition level statistics gathering…)

The feature to allow Oracle to re-parse and take advantage of the newest statistics information in the data dictionary resulted in a poor performance challenge in this instance, as the cursors were invalidated on a process that needed no changes to statistics.

I tested repeatedly against partitions, collecting stats with the no_invalidate set to false or true and even to AUTO to see what would occur and it consistently impacted my cursors against other partitions.  I can find not documented bug, but as many know, I’m about to move everything to 11g  in short order and expect it would be a waste of time to pursue it to far…

I notified the Java developer who owns this code to please update the no_invalidate=>true to correct the performance impact short term and look forward to 11g bugs to replace my exhaustion on 10g ones! :)

Oracle Open World 2011 Followup

Oracle Open World is over for me now, but what a great event it was.  I met so many people and actually was able to spend a little time getting to know a few of them.  I attended parties, dinners and meetups.  I networked myself, my company and RMOUG.  It was a phenomenal turn out, even with a few folks missing that I knew we’d miss terribly

Arriving-  California Zephyr

If you hadn’t heard, we decided to take a train from Denver, CO to San Francisco, CA. via Amtrak, called the California Zephyr.  This was a 33 hour train ride, slowly trekking at times, racing traffic at others, through beautiful scenery, all from a second floor sleeper car.  We had lovely meals with folks in the dinner lounge car and visited with others on the observation deck, (specialized car with a glass, domed ceiling.

Upon our arrival in San Fran, we quickly rented a car and headed up to a lovely party at Oracle’s one and only, Graham Woods.  I quickly found Gwen Shapira, (we’ve been trying to meet for awhile now, so was glad to FINALLY have the opportunity met…) caught up with Alex Gorbachev and teased by Cary Millsap about our challenges with understanding that we needed to PAY for our bottle of wine on the train and that it was not included in the price of the meal, (glad they couldn’t figure out how to pronounce my name over the intercom, but quickly figured out that it was me and my wonderful companion they were looking for-  his name is not so difficult to pronounce!)

Debra Lilley arrived soon into the party and many found fun as she brought me from room to room in search of those she wanted to introduce me to.  I’m to ensure she has a great birthday at February’s RMOUG, so it’s important she and I bond, ya know… :)

I ended up with a lovely embarassing situation upon being introduced to “Greg Brown” who I had to ask repeatedly where I knew him from, which he found quite hilarious, considering our emails until it hit me I was speaking to “Greg Rahn”.  He was a good sport about my lack of IQ after the long trip and I appreciate his patience.

Sunday, OOW11

The first day consisted of me attending a few of Tim Gorman’s sessions that I, as a VLDB DBA, had lived, but had never really sat through before.  I still picked up a few things from my first DBA God and yes, the Gods are good to me.  At his second session, Tim pointed out Andy Klock and I to each other, knowing we’d been tweeting back and forth about meeting up, so we sat together and it gave me an opportunity to physically meet one of the major clients I supported while at Pythian.  It was a pleasure to speak to her in person and glad to see someone not as indepth in the database world revel in the festivities and presentations of Oracle Open World.

Sunday evening was the ACE dinner and attendance was fantastic, (along with the food!)  It was easy to see why no one sat at any one table for very long and I did get to spend a good amount of time speaking with Mark Bobak, Kent Graziano, Doug Burns, James Morle, along with many others.

Monday, OOW11

I didn’t do to well on my schedule builder for Monday or Tuesday and if I go back over the actual schedule of sessions, I’m sure I’ll find a few that I should have gone to.  Monday night was the Oak Table dinner which was great fun.  We started out meeting up with Mike Swing and Craig Shalahammer for drinks before heading to the dinner.  Craig showed me some of his mathamatica graphics for buffers and latch visuals, (yes, his is one of those sessions I obviously missed adding to my schedule!)  We spoke about databases a bit, but the conversations regarding life were much more interesting.

Upon heading over to the Oak table dinner, there was a huge line of people waiting for taxis, but Mike Swing, Tim Gorman and I went up to the bellhop instead.  I’m not sure if it was Mike or Tim, but one of them asked if there was a better option and we had a personal SUV taking us to our dinner in just a few minutes for a few dollars more than a taxi would have cost, (note to future OOW attenders…screw the lines! :) )

As soon as we entered for the dinner, the gracious Carol Dacko ensured us our places and Mogens Norrsgard was busy entertaining everyone.  He and I quickly attained a quirky regard for each other and the jokes about Tim,  “I saw him first!” as the game of us challenging each other for Tim’s affections commenced.

I was seated next to Jeremy Schneider, who I thoroughly enjoyed conversing with.  He’s a brilliant young DBA, so his company, along with Gwen Shapira, Robyn Sands, Tim Gorman, Andy Klock, Rihaj Shamsudeen and Alex Gorbachev guaranteed a lively conversation.  This was also my first opportunity to meet up with Yury Velikanov from Pythian. I’ve only worked with him virtually, so this was a great chance considering he resides in Austrailia.  He is technically skilled, easy-going and quick to make friends-  a great representative for the Pythian name, like Gwen and Andy.

Tuesday, OOW11

Tuesday was the start for most of us feeling the heat from staying up to late and eliminating sleep from our diets.  My voice was starting to sound a bit horse at this point, so I’m sure folks were starting to wonder how well I had fought off my cold, (not very well in the colder, wetter weather of San Francisco…)  We met up with Ben Boise from Quest Software and spent a bit of time at the Enkitec booth.  The Enkitec booth was hands-down, the winner for me.  Kerry Osbourne had told me at Graham’s party, after I had finished teasing Frits Hoogland that he’d been given my copy of the Exadata book via Tanel, that if I came by, he’d have a copy for me.  Yeah, wasn’t turning that down… :)   So while there, spoke to Kerry about what a great DBA and all around good guy Karl Arao was.  He’s succeeding there and post the converation, asked Kerry and Randy Johnson to sign my copy of the book.  I went back a bit later to talk with Karl a bit more and for the fun of it, forced him to sign my copy, too.  Tanel had tweeted that he was going to have a secret Exadata hacking session that day, so I headed out with my book and was able to get Tanel’s signature, too…  No, none of you can have it… :)

On our way out of Moscone for the night, spent about 20 minutes speaking with Jonathan Lewis.  Dr. Steve Dorsey and a guest joined Tim Gorman, Jonathan and I to complete the conversation about the evenings plans.

We traveled down to The Stinking Rose for a wonderful dinner of wonderful dishes with way too much garlic in them.  I truly feared anyone who would come near us post the meal, but it was well worth it, (and apologies to anyone near us afterwards…)

Wednesday, OOW11

The day went quickly and the evening was the blogger meetup, there was a break between networking, dropping off postcards promoting RMOUG 2012, where we were able to head over to Mogen’s office, (i.e. back of Chevy’s restaurant this year…) where everyone had been hanging out throughout each day when needing a break from the chaos.  Throughout the day, there was some conversation via Twitter on who was going to get my wristband, as we were bowing out of the concert/chaos that night.  First it appeared that Greg Rahn would need it, but I had already pointed him towards Mike Swing who was offering him up one, so Doug Burns was the lucky winner and new owner of my band.  The group at Chevy’s, as always was fun to simply observe, let alone partake in conversation with and how can you say no to Mogens?

The meetup was a quick hop and a jump over to Jillian’s where there were a number of private parties going on, but the bloggers meetup by Pythian was the top deal.  We were all given a bandana and sharpies to get each other’s signature, which I was a happy blogger to just go around and meet as many as I could.  I enjoyed writing “Kellyn was here” and pointing arrows to the Pythian logo on the bandana or as everyone was wearing them on their heads, it had humor all in itself, (note to self, another reason I’m glad no one decided to wear the bandana as a bustier…)

Paul Vallee did a lovely tribute to Steve Jobs in the beginning of the meet up at the high time, Pythian first gave away an Apple TV to the blogger picked who had posts from the dates randomly chosen, (happened to be RMOUG Training Days week, so I didn’t even have to check, I KNEW I had posts out there.. LOL)  Yury won the TV and then they gave away an IPod Touch to the person who had received the most signatures.  I was sure I was no where near the top, but then got a look at the leader, Tim Hall’s bandana.  Upon counting mine up, I was two short of his number and he won, but Tim, the gracious guy he is, handed me the prize.  I, confused easily as I am, asked why I was getting it, I came in second and he replied, “I [worked] around to get my signatures and you just got them while meeting everyone, I’m disqualifying myself!”  Thank you, Tim Hall, from me and my children, who one in particular has been jonesing for one of these! :)

Post the meetup, Tim Gorman and I were going to head out for some dinner and Alex Gorbachev joined us at a wonderful Indian restaurant called Amber.  Wine and conversation flowed, while the fun and chaos of the Petty/Sting concert went on at Treasure Island.  When we did finally finish, it was just in time to meet everyone back over at the night’s bar of choice, “W”.  I sat and spoke most of the evening with Martin Paul Nash, Alex G. and Dan Norris, (Mogen’s slept in the corner, those Danes and their catnaps to catch up on jetlag really impresses me!)  Folks came in from the concert, Lisa Dobson, Connor McDonald, Andy Klock, Doug Burns and others, little by little.  We stayed and talked until my voice had become so hoarse that I was starting to sound a bit like Barry White.

Gotta say, another brilliant, easy-going and friendly DBA, Martin Paul Nash.  Between Martin, Andy, Jeremy, Connor and Dan, I’m feeling good about the future of our database administration world.

Thursday, OOW11

Surprise came the next morning when we found out many of the people we left the night before had never actually slept that night.  They continued to enjoy the opportunity to see folks that many may only see once a year and had simply stayed up!  A few of them were presenting on Thursday, so a lot of attendees may have wondered about that, too… :P

I attended only one session on Thursday, had slept in too late for the one I’d wanted to attend on optimal performance, (and had to answer to Gwen and others as to why I wasn’t there… :) )  Maria Colgan was great, (as usual) and she was one of the last folks I really wanted to meet, but had reserved the fact by the group that crowded her immediately after the presentation, that it just wasn’t going to happen.  Tim and I went over to Chevy’s to have a last OOW11 lunch with Mogens’ group before heading to the airport and who shows up to have lunch there, too?  Yes, Maria Colgan, so I did get to meet her…AND have lunch with her, (along with DBA Gods, Demi-Gods, you know the drill… :) )

During all of this, I did a lot of RMOUG networking to ensure that I added as much to the great plans for the 2012 conference that I could.  I was thrilled to have so many folks dedicated to coming out to Denver in February to talk, (because the conference is second to Debra Lilley’s birthday, I swear the marketing is there!)  Had a lovely conversation about bringing RAC Attack out for training days this year, which I think will be well received.  Jeremy Schneider is in Africa the week of the conference, but we are working on others who can really take on this great opportunity for DBA’s to take advantage of.

I wish I could say the plane ride back was relaxing and a wonderful time to reflect on a great Oracle Open World, but as usual, the airlines were busy trying to ruin travel for all of us.  I am thrilled with everyone I met while in San Francisco and although I should have attended more sessions, I wouldn’t have changed a thing.

Thank you Oracle, Pythian and all that I met this last week for such a wonderful experience!

OOW11 Dinners

Yes, typing on my tablet screen again, so patience with my short posts…:-)

Had the pleasure of attending both the ACE and Oak table dinners the last two nights.  Wonderful, impressive and technically gifted people at every table and a fantastic opportunity to meet so many that I’ve only known virtually.  I enjoyed another set of high energy conversations with Gwen Shapira, Debra Lilley, Robyn Sands and Lisa Dobsen- all women who make me proud of the representatives of my gender in the technical world. 

Spent sometime with Mark Bobak, Kent G., Alex G., Craig S., Jeremy Schneider, Mike Swing and Yuri Y.  I was also so thrilled to spend time with a virtual team member, Andy Klock- great guy to work with and happy to meet in person.  Carol Dacko did a phenomenal job planning the Oak Table event and I know Robyn Sands helped with some of the arrangements, too.  Mogens N. Is beyond entertaining and his legend is intact another year.  I threatened to stalk Tanel unless he signed my Exadata book, (thank you, thank you Kerry Osbourne for the copy…) and still am missing mentioning many others that should be named here.  All made an impact and were a pleasure to meet.

Food and spirits pale in comparison to the wonderful opportunity these dinners offer us all to sit and speak with the peers we admire so much…

First Day at Oracle Open World

After a long first day and a 33 hr train ride in, I’m ready for a fun and enjoyable Ace dinner that I’m a lucky guest to. 

First day, being Sunday, is commonly quiet, but Tim had two sessions that I wanted to attend and time flew by with meetups, expert panels, etc. I met a number of great new people today and saw some more old friends, (or as “new blood”, can I refer to them as old?) 

I was still recovering after a fantastic get-together at Graham Woods from lastnight.  I was impressed with the great group of people inattendance.  DBA Gods like Tim Gorman, Alex Gorbachev, Cary Millsap, Kerry Osbourne and even demi-Gods like Frits Hoogland and Greg Rahn were there.  I met,(finally) so many others that have been on my list.