EM12c’s Configuration Topology
This is another little known feature, but one you have to work with to get a solid feel for and find value in. When I came upon it in EM12c, I was not that impressed, but quickly became more impressed with the “just the facts” and “all in one” interface that it is, especially if you are working in a RAC environment.
Its another one of those features the DBA can stumble upon as they are working through issues, but to purposefully utilize it, the quickest way is after logging into the Enterprise Manager, click on Targets –> Hosts and then click on the host you wish to inspect thru the topology interface.
Once on this host, click just below the host name on the Host drop down button, then click on Configuration –> Topology.
The first view is quite simple- just the host:
If you click on the drop down next to “View” where it has defaulted to “Uses” and click on “Used By”, the view will change, drastically if the chosen host happens to be in a RAC environment.
The default for the “Used by” view is a vertical layout, which I don’t prefer, having a widescreen on my laptop and this can be easily adjusted by clicking on Options –> layout –> left right.
This gives you a display that can be zoomed and easily readable on a wide screen as is common these days.
1. You can drop and drag the topology layout on the screen to narrow in on anything you wish to concentrate on.
2. The boxes have notification symbols on any parent or child that has an event, incident or information that can be drilled down when you “hover” over the object.
3. By clicking on the object, a pane will appear with high level info on the parent or child.
4. That small pane can be expanded to offer more info, such as version, status and incident type.
5. There is a “more” link that can be clicked to open a larger info box with tabs containing even more detailed info.
6. Each box contains links to drill down to EM pages to address or read more details on the specified subject.
7. You have the ability to adjust the layout as complex as you would like to make it, including labeling the links between the node/db /listener/etc. relationships
8. You can print the topology map for reference.
The topology map, in its graphical interface, can be used in the following example:
Let’s say our goal is to become familiar with a new RAC environment’s physical layout, hardware, nodes and supporting structures.
Using the configuration topology, this becomes an easily accomplished feat:
We can see the toplogy, (already changed the view left to right…):
We can then Look at the relationships between the objects by changing the Annotations in the menu above the graph, choosing to show the status, incidents and/or link labels, also choosing how much metric detail we want to show in the graph.
The following close up shows you the information showed in the metrics data for each object:
Hovering over an object will show you more detail, which will open other panes with extended detail information and tabs about incident summaries,
The picture becomes clearer as you continue to browse the topology of the enironment.
You can filter your view by what objects have critical incidents that need to be addressed, simply by clicking on the critical incidents, (the red circle with an ‘X’ in the middle of it..) above the topology graph:
You can also sort by the targets that are up by clicking on the green up arrow next to the critical incidents symbol.
Now after all of that, if you aren’t a graph person, you can even switch it to table mode and drill down through the same features, just through a table format instead of the graph:
From this interface, either the graph or table view, a large amount of the EM12c features are available to the user. I highly recommend utilizing the UI to get a great view of the Oracle environment that the Enterprise Manager is privy to, allowing the DBA to have one more way to interact with monitored targets.
Andy Colvin is Now an Oracle ACE!
Congratulations to my coworker, Andy Colvin who just received his Oracle ACE!! Just one more immensely technically gifted individual who is very deserving of this award at Enkitec and makes me proud to be part of such a great group of folks!
My Review of REI Monorail Sling Bag
The REI Monorail Sling bag has the volume of a daypack and the features to meet all the challenges of the daily urban grind. Outdoor-inspired details make hauling the load comfortable and convenient.
Light, Sturdy, Roomy and Comfortable!!
Pros: Lightweight, Easy To Access Items, High Quality, Comfortable, Good Capacity, Easy To Carry, Stylish
Cons: No Zipper pocket in main
Best Uses: Weekend Trips
Describe Yourself: Modern, Stylish, Practical, Comfort-Oriented, Career
Travel Frequency: 6-10 Annual Trips
Primary use: Business
Was this a gift?: No
Going on both regional and international trips this year and needed a bag to carry both my ultrabook, tablet and accessories. This bag sits close to the body and is extremely comfortable. I have arthritis in my neck and shoulders, so for anyone needing extra comfort for long trips through airports, cities and towns- this is a great bag!!
(legalese)
SQL Saturday Presentation!
Due to scheduling conflict though, it’s on a Thursday evening for once!
I will be presenting for the first time as an Enkitec employee in Colorado Springs, March 22nd. All you SQL Server folks out there that want to come on out, here are the details:
The Technical Bully Scenario
I’ve been again, unfortunately, reminded what makes a poor work environment for a DBA or Developer. Eddie Awad had sent this out and the second one really resonated with me:
I refer to this in the IT world as the “technical bully syndrome” and it’s quite common in the arena. It’s not because it’s a male dominated group- I’ve seen bullies come in all shapes and sizes, along with being of both genders. In fact, there are a couple studies out there that say women managers and leads are more likely to bully a subordinate than a male is.
Now there isn’t any law against having a bad boss/manager/lead. I lucked out in that I actually have quite a good manager where I currently work, but even if you are a good manager, you also have to note the cost to the business when you have people report to someone who does bully. This individual pinpointed as the bully, will most likely get along very well with 90% of the folks around them. They have a consistent need though, to pick out an “outlet” for their aggressions/frustrations and may have a small set of subordinates or peers that they switch from, bullying in intervals. Often the switch occurs as the bully’s target starts to or simply threatens to escalate to management or HR if the bullying does not cease, the bully will then move their bulls-eye onto another target.
The cost? Time, resources, revenue and productivity to the business.
It takes time to bully someone- time out of the bully’s day, but also time out of the target’s day when they could be productive. The target can also be highly impacted post the incidents, feeling frustrated, disrespected and devalued. The time lost then results in productivity loss to both parties, which in turn then costs the company revenue. If left to continue for very long, resources are lost and high-turnover becomes the norm.
The target, who may have been highly productive in the past will start to show less results and seem to have conflicting requirements. They may seem frustrated and angry, feeling unable to understand how to address the problem successfully or no matter what changes they make to their work style, without management intervention, they are unable to find a way to avoid the bully’s wrath.
The bully often chooses targets that are not weak, but strong. Peers or subordinates that make them feel threatened, no matter if the threat is real or imagined. They will continually find ways to undermine the target and make them seem insignificant. If the target does decide to choose a defensive pose, becoming angry or responding in kind, this can escalate the behavior from the bully. Often the bully does not see themselves as doing anything wrong! They may be completely unaware of their behavior, having grown up with it and/or may live with similar disrespect in their personal relationships currently.
When I have been a target in the past, I have often either threatened the bully because of my technical skills, which they seemed to be completely focused on trying to convince everyone that were not sound and/or give conflicting requests to make it impossible for me to meet requirements or I reminded them of someone, (often a wife or mother..) that they had long-time issues with and felt it was acceptable to use me as an outlet when they became frustrated.
There are those that were equal-opportunity bullies. It didn’t matter if you were male, female, what race you belonged to or your position- as long as you were below them, you were fair game and you simply did your best to avoid their bulls-eye.
I have stayed employed for a company where I was a target on and off for a couple years, until someone figured out what was going on and rid the company of the bully that was causing the high-turnover and tumultuous work environment. The DBA’s group at that time found quite peaceful, productive years post the bully’s departure.
I have worked for another company for a very short time, where I saw the bulls-eye aiming for me and left so fast that the bully’s head spun.
No one knows what a DBA does but a DBA. No one knows what a good DBA is except other DBA’s, (and then some would argue that there are a few that are heavily followed that would not be if most DBA’s only knew… J) As a DBA who has seen how well IT groups can work together when everyone respects each other and respects what skills each person offers and how poorly the group will perform, how high turnover will be when a bully exists in the group, I hope more managers taken notice.
Yes, it’s not against the law to have a bully in your midst, but it should be a crime to allow it to damage the productivity, growth, resources and revenue for the company.
Kellyn’s Presentation Schedule, Spring/Summer 2012
Kellyn’s Schedule of Presentations, 2012, 2nd and 3rd Quarter
March 24th, 2012, Colorado Springs, CO
SQL Saturday
Oracle for SQL Server DBA’s
April 19th, 2012, Billund, Denmark
MOW 2012
ASH Analytics- Top Activity, the Next Generation
April 20th, 2012, Billund, Denmark
MOW 2012
EM12c: Making Oracle Enterprise Manager Work for You
May 30th, 2012, Hämeenlinna, Finland
OUG Harmony 2012
EM12c: Making Oracle Enterprise Manager Work for You
June 1st, 2012, Riga, Latvia
JUV Harmony 2012
EM12c: Making Oracle Enterprise Manager Work for You
June 26th, 2012, San Antonio, TX.
KSCOPE 2012
EM12c: Making Oracle Enterprise Manager Work for You
11g, Sherlock Holming it with Statistics, Histograms and Bind Peeking
Yes, my dear Watson, Bind Peeking is still an issue in 11g….
We recently upgraded from 10.2.0.4 to 11.2.0.2.0 and come across a couple interesting performance challenges. I do like this type of problem and have been quite busy working on the fixes for each one that has shown itself. This was one of the latest, requiring a solid, positive relationship between the developer and the DBA to solve- The DBA may have the answer, but the developer must be comfortable with the solution and make the code/design change that is required to correct the issue. I do work with a number of such developers, so this was a quick fix in our fast-paced environment…
Oracle’s Cost Based Optimizer makes the best decisions when the design and the code are in accordance with “best practices” as well. I know the word “best practices” is a bit over-used, so we will discuss first what this means.
In the example I’ll be using in my case below, the following is the design practices that should have been applied when the objects were first introduced to production, but were not. I wanted to include the example so that folks could see just how many different ways Oracle could slice and dice the data when deciding what path to take through the database to a completion of a process.
Best practice in this case would be:
1. partitions with similar sizing.
2. No truncated partitions.
3. Single column partitioning keys.
4. Stage tables that are utilized for a process and then promptly emptied after the completion of the process.
Scenario:
Performance challenge on one step in feed process. This process utilizes the following steps:
1. Chooses a “partition ID”, which is the partitioning key on a stage table. There is no logic to the partitioning key, (p_id) simply chosen as part of the steps from a list marked as a truncated partition that is ready to be utilized.
2. Inserts into this partition. Some loads can be quite small, (less than 1M, others can be quite large, 100G+.
3. Merge statement is then performed on stage table to delete from partition any duplicates and other non-loading indicators.
4. This partition is then used to join to the final table to decide what is inserted, updated and deleted from another partition. The final partition has the same vulnerability in design of askew sizing in partition data.
5. The partition in the staging table is then truncated.
First Step by the DBA:
dbms_xplan.display_awr
Twelve different explain plans were found to have been used for the insert statement in question.
Second step by the DBA:
Checked table, partition and column stats.
Select count(column_name) Col_cnt, last_analyzed from dba_tab_columns where table_name='CUSTOMER_ACCOUNTS' group by Last_analyzed;
COL_CNT LAST_ANALYZED 27 1/24/2012 3:09:58 AM 3 2/23/2012 12:41:23 PM
The columns with the newest date in question are the indexed columns, which is good in this environment. The bad is the amount of histograms on columns, we have already researched and found our waits are on the data dictionary and the scheduler in 11g since moving to collecting histograms on tons of columns, on tons of partitions that we do not use in any way for where clauses, etc., but no way to notify the database of this in the largely CTAS type process code base.
So, we now know we do have new stats, histograms were bloated, set to auto on method_opt, but they are still accurate and up to date. The performance issue started before the date we changed over to the method option on the stats..
Question by DBA: How does the database know what and how to build histograms when different logic is used depending on the code, when a partition can be used by any one of the processe? This results in the CBO making poor decisions on column stats.
This is quickly verified when column stats are compared between one partition and the next for large feeds using the same logic last use of the partition.
Unique keys are multi-column, partitioning key is askew on final tables. This key is in where clause, so we do have accurate information outside of the CTAS code/stats histogram issue. With more data involving histograms and partition data askew across the partitions, does this leave us vulnerable to bind peeking?
DBA Test case for bind peeking:
After pinpointing the hash_value for the plan used by the poor performing, large processing version of the performance issue, detail what the values for both bind variables that would have been used.
Replaced the bind variables, one at a time, with the actual values, noted the change in execution plan.
Noted that when the partition was a literal vs. the bind variable, the execution plan changed drastically and contained valid values for the cost and row count for the process.
Although the :B1 bind variable appears to be just as susceptible to the bind peek, it is the partitioning key for the staging table, (CTS1) that is the true culprit on large processing.
DBA recommendation:
Change from inserting into a partition to performing a CTAS for the logic involved, bypassing any deletes or merges, simply creating the table with the data it requires, uniquely named.
Use this in the insert’s subselect to eliminate the bind peek issue.
Summary:
The simple concept behind the “CTS1″ table was that is was a “parking lot”, where a process could simply find an empty parking space, (partition) and proceed to utilize it. The problem is that Oracle can’t figure out if it’s just parked a smart car or a semi in the spot. To eliminate the issue, the parking lot was removed by going to a working table vs. the partitioned stage table and then the database simply had to work with whatever size of “vehicle” it had been issued in the process, (post stats collection, of course!)
DBMS_XPLAN.DISPLAY_AWR Output: PLAN_TABLE_OUTPUT SQL_ID 7rjk8mr85s2nq -------------------- INSERT INTO DW_USR.CT1 (COL1,COL2,COL3,COL4,COL5,COL6,COL7..... COL18,COL19,COL20) SELECT * FROM ( SELECT S.COL1, S.COL2, S.COL3, ROW_NUMBER () OVER (PARTITION BY S.COL5, S.COL6,S.COL7, S.COL8 ORDER BY S.COL12) COL6,S.COL2, S.COL12, S.COL15, S.COL19, S.COL5, S.COL15, S.COL22, S.COL27, S.LAST_FILE_ID, S.COL28, S.COL29, S.COL31, S.COL33, S.COL35, S.COL36, S.COL37, S.COL38, S.COL39, S.COL40, S.COL43, S.COL44, S.F1, S.F2, S.F3, S.F4, S.F5, S.F6, S.F7, S.F8, S.F9, S.F10, S.COL45, S.COL46, S.COL47, S.COL48, S.COL81 FROM ( SELECT /*+ use_hash(cts ca) */ CTS.COL5, CA.COL3, CTS.COL6, CTS.COl76 AS COL6, CTS.COL2, CTS.COL12, CTS.COL15, NULL AS COL19, CTS.COL5, CTS.COL15, CTS.COL22, CTS.COL27, CTS.COL7 AS LFID, CTS.COL28, CTS.COL17, CTS.COL31, CTS.COL33, CTS.COL35, CTS.COL36, CTS.COL37, CTS.COL38, CTS.COL39, CTS.COL40 AS COL40, :B5 AS COL43, :B5 AS COL44, CTS.F1, CTS.F2, CTS.F3, CTS.F4, CTS.F5, CTS.F6, CTS.F7, CTS.F8, CTS.F9, CTS.F10, CTS.COL45, :B4 AS COL46, :B3 AS COL47, CTS.COL48, CTS.COL49, CTS.COL50, CTS.COL51, CTS.COL52, CTS.COL53, CTS.COL54, CTS.COL55, CTS.COL48, CTS.COL28 FROM DW_USR.CTS1 CTS, (SELECT COL2, COL3, COL5 FROM (SELECT COL2, COL3, COL5, ROW_NUMBER () OVER(PARTITION BY COL2, COL5 ORDER BY COL43 DESC, IND_ID) C_RNK FROM DW_USR.CA1 WHERE COL2 = :B1 ) C WHERE C_RNK = 1) CA WHERE CTS.P_ID = :B2 AND CTS.COL2 = :B1 AND CTS.COL2 = CA.COL2 (+) AND CTS.COL5 = CA.COL5 (+) AND CTS.COL72 = 'N' ) S ) X WHERE X.COL3 IS NOT NULL AND NOT EXISTS ( SELECT /*+ use_hash(ct) */ 1 FROM DW_USR.CT1 CT WHERE X.COL9 = CT.COL9 AND X.COL4 = CT.COL4 AND X.COL2 = CT.COL2 AND X.COL2 = CT.COL3 AND CT.COL3 = :B1 )
Plan hash value: 653243445
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 1659 | | | | 1 | HASH JOIN RIGHT ANTI | | 23 | 110K| 1659 | | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID | CT1 | 1 | 52 | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | 1 | KEY | KEY | | 5 | VIEW | | 23 | 109K| 1657 | | | | 6 | WINDOW SORT | | 23 | 4232 | 1657 | | | | 7 | HASH JOIN | | 23 | 4232 | 1644 | | | | 8 | VIEW | | 1 | 56 | 14 | | | | 9 | WINDOW SORT PUSHED RANK | | 1 | 52 | 14 | | | | 10 | FILTER | | | | | | | | 11 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 12 | TABLE ACCESS BY LOCAL INDEX ROWID| CA1 | 1 | 52 | 1 | KEY | KEY | | 13 | INDEX RANGE SCAN | CA1 _PK | 1 | | 1 | KEY | KEY | | 14 | PARTITION RANGE SINGLE | | 11M| 1346M| 1385 | KEY | KEY | | 15 | TABLE ACCESS FULL | CTS1 | 11M| 1346M| 1385 | KEY | KEY | -------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1492830811
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 23810 | | | | 1 | HASH JOIN RIGHT ANTI | | 23 | 110K| | 23810 | | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CT1 | 1 | 52 | | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | | 1 | KEY | KEY | | 5 | VIEW | | 23 | 109K| | 23808 | | | | 6 | WINDOW SORT | | 23 | 4646 | | 23808 | | | | 7 | HASH JOIN | | 23 | 4646 | | 23795 | | | | 8 | PARTITION RANGE SINGLE | | 1 | 146 | | 20 | KEY | KEY | | 9 | TABLE ACCESS FULL | CTS1 | 1 | 146 | | 20 | KEY | KEY | | 10 | VIEW | | 14M| 753M| | 23623 | | | | 11 | WINDOW SORT PUSHED RANK | | 14M| 457M| 1293M| 23623 | | | | 12 | FILTER | | | | | | | | | 13 | PARTITION LIST SINGLE | | 14M| 457M| | 2267 | KEY | KEY | | 14 | TABLE ACCESS FULL | CA1 | 14M| 457M| | 2267 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1588626705
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 3699K| | | | 1 | HASH JOIN RIGHT ANTI | | 62M| 287G| 2977M| 3699K| | | | 2 | PARTITION LIST SINGLE | | 70M| 2165M| | 10018 | KEY | KEY | | 3 | INDEX FAST FULL SCAN | CTS1_PK | 70M| 2165M| | 10018 | KEY | KEY | | 4 | VIEW | | 62M| 285G| | 546K| | | | 5 | WINDOW SORT | | 62M| 9461M| 27G| 546K| | | | 6 | HASH JOIN | | 62M| 9461M| 2794M| 182K| | | | 7 | VIEW | | 43M| 2301M| | 75523 | | | | 8 | WINDOW SORT PUSHED RANK| | 43M| 1315M| 3620M| 75523 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 43M| 1315M| | 13076 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 43M| 1315M| | 13076 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 62M| 6107M| | 5407 | KEY | KEY | | 13 | TABLE ACCESS FULL | CTS1 | 62M| 6107M| | 5407 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 1672751078
------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | | | | 235K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | 2 | HASH JOIN ANTI | | 1355 | 6522K| 632M| 235K (7)| 00:03:24 | | | | 3 | VIEW | | 135K| 631M| | 134K (10)| 00:01:57 | | | | 4 | WINDOW SORT | | 135K| 24M| 26M| 134K (10)| 00:01:57 | | | | 5 | HASH JOIN | | 135K| 24M| 18M| 132K (10)| 00:01:55 | | | | 6 | PARTITION RANGE SINGLE | | 135K| 16M| | 287 (32)| 00:00:01 | KEY | KEY | | 7 | TABLE ACCESS FULL | CTS1 | 135K| 16M| | 287 (32)| 00:00:01 | KEY | KEY | | 8 | VIEW | | 11M| 610M| | 90184 (13)| 00:01:19 | | | | 9 | WINDOW SORT PUSHED RANK| | 11M| 338M| 480M| 90184 (13)| 00:01:19 | | | | 10 | FILTER | | | | | | | | | | 11 | PARTITION LIST SINGLE| | 11M| 338M| | 38574 (9)| 00:00:34 | KEY | KEY | | 12 | TABLE ACCESS FULL | CA1 | 11M| 338M| | 38574 (9)| 00:00:34 | KEY | KEY | | 13 | PARTITION LIST SINGLE | | 20M| 840M| | 6426 (5)| 00:00:06 | KEY | KEY | | 14 | INDEX RANGE SCAN | CTS1_PK | 20M| 840M| | 6426 (5)| 00:00:06 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1930179405
------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | | | | 1572K(100)| | | | | 1 | LOAD TABLE CONVENTIONAL | | | | | | | | | | 2 | HASH JOIN RIGHT ANTI | | 3033K| 13G| 691M| 1572K (2)| 00:22:44 | | | | 3 | PARTITION LIST SINGLE | | 17M| 489M| | 4192 (6)| 00:00:04 | KEY | KEY | | 4 | INDEX RANGE SCAN | CTS1_PK | 17M| 489M| | 4192 (6)| 00:00:04 | KEY | KEY | | 5 | VIEW | | 5152K| 23G| | 246K (9)| 00:03:34 | | | | 6 | WINDOW SORT | | 5152K| 835M| 909M| 246K (9)| 00:03:34 | | | | 7 | HASH JOIN | | 5152K| 835M| 619M| 155K (11)| 00:02:15 | | | | 8 | PARTITION RANGE SINGLE | | 5152K| 560M| | 11305 (31)| 00:00:10 | KEY | KEY | | 9 | TABLE ACCESS FULL | CTS1 | 5152K| 560M| | 11305 (31)| 00:00:10 | KEY | KEY | | 10 | VIEW | | 11M| 608M| | 69790 (16)| 00:01:01 | | | | 11 | WINDOW SORT PUSHED RANK| | 11M| 325M| 478M| 69790 (16)| 00:01:01 | | | | 12 | FILTER | | | | | | | | | | 13 | PARTITION LIST SINGLE| | 11M| 325M| | 20402 (16)| 00:00:18 | KEY | KEY | | 14 | TABLE ACCESS FULL | CA1 | 11M| 325M| | 20402 (16)| 00:00:18 | KEY | KEY | ------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2157924392
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 267K| | | | 1 | HASH JOIN RIGHT ANTI | | 4459K| 20G| 152M| 267K| | | | 2 | PARTITION LIST SINGLE | | 3204K| 116M| | 564 | KEY | KEY | | 3 | TABLE ACCESS FULL | CT1 | 3204K| 116M| | 564 | KEY | KEY | | 4 | VIEW | | 4459K| 20G| | 44503 | | | | 5 | WINDOW SORT | | 4459K| 723M| 1574M| 44503 | | | | 6 | HASH JOIN | | 4459K| 723M| 357M| 16818 | | | | 7 | VIEW | | 5516K| 294M| | 8663 | | | | 8 | WINDOW SORT PUSHED RANK| | 5516K| 173M| 505M| 8663 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 5516K| 173M| | 492 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 5516K| 173M| | 492 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 3052K| 331M| | 329 | KEY | KEY | | 13 | TABLE ACCESS FULL | C | 3052K| 331M| | 329 | KEY | KEY | --------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 2900900913
--------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 306K| | | | 1 | HASH JOIN RIGHT ANTI | | 35M| 164G| | 306K| | | | 2 | PARTITION LIST SINGLE | | 1 | 52 | | 1 | KEY | KEY | | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| CT1 | 1 | 52 | | 1 | KEY | KEY | | 4 | INDEX RANGE SCAN | CT1_IDX | 1 | | | 1 | KEY | KEY | | 5 | VIEW | | 35M| 163G| | 278K| | | | 6 | WINDOW SORT | | 35M| 6046M| 16G| 278K| | | | 7 | HASH JOIN | | 35M| 6046M| 687M| 47011 | | | | 8 | VIEW | | 10M| 566M| | 17018 | | | | 9 | WINDOW SORT PUSHED RANK | | 10M| 343M| 971M| 17018 | | | | 10 | FILTER | | | | | | | | | 11 | PARTITION LIST SINGLE | | 10M| 343M| | 988 | KEY | KEY | | 12 | TABLE ACCESS FULL | CA1 | 10M| 343M| | 988 | KEY | KEY | | 13 | PARTITION RANGE SINGLE | | 14M| 1624M| | 1595 | KEY | KEY | | 14 | TABLE ACCESS FULL | CTS1 | 14M| 1624M| | 1595 | KEY | KEY | ---------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 2973217172
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 208K| | | | 1 | HASH JOIN RIGHT ANTI | | 6346K| 29G| | 208K| | | | 2 | PARTITION LIST SINGLE | | 12908 | 630K| | 3 | KEY | KEY | | 3 | INDEX RANGE SCAN | CTS1_PK | 12908 | 630K| | 3 | KEY | KEY | | 4 | VIEW | | 6346K| 28G| | 85943 | | | | 5 | WINDOW SORT | | 6346K| 1373M| 2916M| 85943 | | | | 6 | HASH JOIN | | 6346K| 1373M| 533M| 33969 | | | | 7 | VIEW | | 8231K| 439M| | 15389 | | | | 8 | WINDOW SORT PUSHED RANK| | 8231K| 282M| 754M| 15389 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 8231K| 282M| | 2423 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 8231K| 282M| | 2423 | KEY | KEY | | 12 | PARTITION RANGE SINGLE | | 6346K| 1034M| | 1171 | KEY | KEY | | 13 | TABLE ACCESS FULL | CTS1 | 6346K| 1034M| | 1171 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
****This was the hash value used in our poorest performer!!!!*****
Plan hash value: 3005115193
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 89378 | | | | 1 | HASH JOIN RIGHT ANTI | | 287K| 1347M| 912M| 89378 | | | | 2 | PARTITION LIST SINGLE | | 22M| 658M| | 2193 | KEY | KEY | | 3 | INDEX FAST FULL SCAN | CTS1_PK | 22M| 658M| | 2193 | KEY | KEY | | 4 | VIEW | | 287K| 1338M| | 63268 | | | | 5 | WINDOW SORT | | 287K| 41M| 92M| 63268 | | | | 6 | HASH JOIN | | 287K| 41M| 29M| 61637 | | | | 7 | PARTITION RANGE SINGLE | | 287K| 26M| | 25 | KEY | KEY | | 8 | TABLE ACCESS FULL | CTS1 | 287K| 26M| | 25 | KEY | KEY | | 9 | VIEW | | 27M| 1446M| | 42660 | | | | 10 | WINDOW SORT PUSHED RANK| | 27M| 852M| 2482M| 42660 | | | | 11 | FILTER | | | | | | | | | 12 | PARTITION LIST SINGLE| | 27M| 852M| | 2548 | KEY | KEY | | 13 | TABLE ACCESS FULL | CA1 | 27M| 852M| | 2548 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3149367802
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 87386 | | | | 1 | HASH JOIN RIGHT ANTI | | 1260K| 5917M| 210M| 87386 | | | | 2 | PARTITION LIST SINGLE | | 4603K| 158M| | 327 | KEY | KEY | | 3 | TABLE ACCESS FULL | CT1 | 4603K| 158M| | 327 | KEY | KEY | | 4 | VIEW | | 1260K| 5874M| | 22365 | | | | 5 | WINDOW SORT | | 1260K| 251M| 539M| 22365 | | | | 6 | HASH JOIN | | 1260K| 251M| 198M| 12826 | | | | 7 | PARTITION RANGE SINGLE | | 1260K| 183M| | 142 | KEY | KEY | | 8 | TABLE ACCESS FULL | CTS1 | 1260K| 183M| | 142 | KEY | KEY | | 9 | VIEW | | 4701K| 251M| | 7346 | | | | 10 | WINDOW SORT PUSHED RANK| | 4701K| 152M| 430M| 7346 | | | | 11 | FILTER | | | | | | | | | 12 | PARTITION LIST SINGLE| | 4701K| 152M| | 238 | KEY | KEY | | 13 | TABLE ACCESS FULL | CA1 | 4701K| 152M| | 238 | KEY | KEY | --------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3517833265
------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 142K| | | | 1 | HASH JOIN ANTI | | 37130 | 174M| 173M| 142K| | | | 2 | VIEW | | 37130 | 173M| | 130K| | | | 3 | WINDOW SORT | | 37130 | 6563K| 13M| 130K| | | | 4 | HASH JOIN | | 37130 | 6563K| 4992K| 130K| | | | 5 | PARTITION RANGE SINGLE | | 37130 | 4532K| | 5 | KEY | KEY | | 6 | TABLE ACCESS FULL | CTS1 | 37130 | 4532K| | 5 | KEY | KEY | | 7 | VIEW | | 57M| 3089M| | 90806 | | | | 8 | WINDOW SORT PUSHED RANK| | 57M| 1655M| 4859M| 90806 | | | | 9 | FILTER | | | | | | | | | 10 | PARTITION LIST SINGLE| | 57M| 1655M| | 12448 | KEY | KEY | | 11 | TABLE ACCESS FULL | CA1 | 57M| 1655M| | 12448 | KEY | KEY | | 12 | PARTITION LIST SINGLE | | 14M| 548M| | 2629 | KEY | KEY | | 13 | INDEX FAST FULL SCAN | CTS1_PK | 14M| 548M| | 2629 | KEY | KEY | -------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Plan hash value: 3518559863
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | 49 | | | | 1 | HASH JOIN ANTI | | 1 | 4924 | 49 | | | | 2 | VIEW | | 1 | 4887 | 32 | | | | 3 | WINDOW SORT | | 1 | 154 | 32 | | | | 4 | HASH JOIN | | 1 | 154 | 19 | | | | 5 | VIEW | | 1 | 56 | 14 | | | | 6 | WINDOW SORT PUSHED RANK | | 1 | 52 | 14 | | | | 7 | FILTER | | | | | | | | 8 | PARTITION LIST SINGLE | | 1 | 52 | 1 | KEY | KEY | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| CA1 | 1 | 52 | 1 | KEY | KEY | | 10 | INDEX RANGE SCAN | CA1 _PK | 1 | | 1 | KEY | KEY | | 11 | PARTITION RANGE SINGLE | | 23150 | 2215K| 4 | KEY | KEY | | 12 | TABLE ACCESS FULL | CTS1 | 23150 | 2215K| 4 | KEY | KEY | | 13 | PARTITION LIST SINGLE | | 1963K| 69M| 1 | KEY | KEY | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID | CT1 | 1963K| 69M| 1 | KEY | KEY | | 15 | INDEX RANGE SCAN | CT1_IDX | 1333 | | 1 | KEY | KEY | -------------------------------------------------------------------------------------------------------------------
Note ----- - cpu costing is off (consider enabling it)
Restarting a Duplicate Process From a VERY Failed State
As part of an 11g Upgrade, it was found that a database environment could be built from one of the upgraded databases through a duplicate. As this process had never been performed before in this fashion, a test was in order. The test was an excellent chance to discover that the OSUser that performs the duplicate process was the proud owner of a .kshrc file with hard coded Oracle variables set which was an excellent choice if you want to really screw up a new duplicate database… 
Scenario:
1. Duplicate has started with correct variables set.
2. Subsequent shell scripts then “upset” the environment mid-process leaving a failed duplicate with ASM files for the controlfiles created, but the DBID and dbname still set to the target database, not the auxillary.
3. Post the failure, the auxillary, (duplicate) database can only be mounted, not opened.
| After setting environment to building auxillary database environment, after duplicate failure and you see in the spfile, name of db is no longer the auxilary database. |
| 1. Shutdown the auxilary database: |
| [oracledbs]$ sqlplus ‘/as sysdba’ |
| SQL> shutdown; |
| ORA-01109: database not open |
| Database dismounted. |
| ORACLE instance shut down.@ |
| In a second screen set to +ASM instance for auxillary, remove controlfiles for target db that have been set to that name through the asmcmd command console: |
| [oracle@dbs]$ asmcmd |
| ASMCMD> ls |
| DATA_H/ |
| DATA_RAM_H/ |
| ASMCMD> cd DATA_H |
| ASMCMD> ls |
| DB_H/ |
| ASMCMD> cd DB_H |
| ASMCMD> ls |
| CONTROLFILE/ |
| ASMCMD> cd CONTROLFILE |
| ASMCMD> ls |
| control1.ctl <–these are control files for auxillary, stuck with dbname of target, can’t be renamed, can’t mount db! |
| control2.ctl |
| control3.ctl |
| current.389.766743537 |
| current.445.766743537 |
| current.500.766743537 |
| ASMCMD> rm control* |
| You may delete multiple files and/or directories. |
| Are you sure? (y/n) y |
| ASMCMD> quit |
| Back on your original screen now, restart the auxillary with pfile set to corect dbname and start with this pfile- |
| SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initdb_h.ora’ |
| ORACLE instance started. |
| Total System Global Area 7.6964E+10 bytes |
| Fixed Size 2215704 bytes |
| Variable Size 3.0065E+10 bytes |
| Database Buffers 4.6708E+10 bytes |
| Redo Buffers 189513728 bytes |
| SQL> create spfile from pfile; |
| File created. |
| SQL> shutdown; |
| ORACLE instance shut down. |
| SQL> startup nomount; |
| ORACLE instance started. |
| Total System Global Area 7.6964E+10 bytes |
| Fixed Size 2215704 bytes |
| Variable Size 3.0065E+10 bytes |
| Database Buffers 4.6708E+10 bytes |
| Redo Buffers 189513728 bytes |
| Now you can restart the duplicate process and the database will again be recognized correctly. |
How to Break and EM12c and Other Hobbies
As many know I’ve been busy trying to bring our many-times patched 10g Enterprise Manager migrated to a new server with EM12c. I thought it would be interesting to see how fast I could break it, considering my skills, I knew it might not be a challenge.
Scenario
1. Bug with listener refusing to connect dynamically to EM12c repository database.
2. After releasing code to the database for advanced notification, the SYSMAN.MGMT_ADMIN_DATA package has gone invalid, which is used to manage much of the repository at the command line.
3. The repository owner password has become corrupted.
These were my high level notes as I went through the troubleshooting:
| Reconfigure Repository: |
| $AGENT_HOME/bin/emctl config oms -store_repos_details -repos_port 1521 -repos_sid emrep12c -repos_host host_nm -repos_user SYSMAN -repos_pwd password |
| Stopping Old Oracle OMS and configuring: |
| export ORACLE_HOME=/opt/oracle/app/OracleHomes/oms10g |
| cd $ORACLE_HOME/opmn/bin |
| ./opmnctl stopall |
| cd $ORACLE_HOME/bin |
| ./emctl config oms -change_repos_pwd |
| cd $ORACLE_HOME/opmn/bin |
| ./opmnctl startall |
| Invalid package after loop from listener bug, corrupt SYSMAN password and invalid MGMT_XXX pkgs! |
| Can’t reset password, pkg used for it is invalid! |
| ORA-04063: package body “SYSMAN.MGMT_ADMIN_DATA” has errors |
| ORA-06508: PL/SQL: could not find program unit being called: “SYSMAN.MGMT_ADMIN_DATA” |
| ORA-06512: at line 1 |
| Can’t start repository, password error in logs: |
| Error occurred. Check the log /local/u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/secure.log |
| [oracle@vwgrid01 bin]$ ./emctl start oms |
| Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0 |
| Soooo close…but did I get far enough?, (Nope, failure…have to remove everything!!) |
| Can’t drop repository: |
| <Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -SYS_PWD sys_password -SYSMAN_PWD password |
| Can’t reconfigure through the system, either! |
| <Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD sys_password -SYSMAN_PWD password |
| No chance either…I’m stuck in a terrible loop! |
| Solution- |
| backup and then edit out the 12c environment from the inventory.xml file in the ContentsXML dir of the Lsinventory |
| kill any process that are still running out the the EM12c home. |
| rm -rf the 12c home directory |
| Uninstall the emrep12 database in the 11g home |
| Create a new emrep12 database in the 11g home |
| recreate a new inventory file: |
| $ORACLE_HOME/oui/bin/runInstaller -silent -invPtrLoc “/u01/app/oraInventory/oraInst.loc” -attachHome ORACLE_HOME=”/u01/app/oracle/product/11.2.0″ ORACLE_HOME_NAME=”OraDb11g_home2″ |
| Install the EM12c once again, as now the installation appears to have never existed… |
| If a restart of the installation needs to be done, you need to look in the home it fails on, (*saying that it’s already installed there) and remove the *_temp file from the dir, then try again. |
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.
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!
