{"id":1705,"date":"2013-09-02T12:00:00","date_gmt":"2013-09-02T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1705"},"modified":"2013-09-02T12:00:00","modified_gmt":"2013-09-02T12:00:00","slug":"10053-trace-files-global-stats-on-partitioned-tables","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2013\/09\/02\/10053-trace-files-global-stats-on-partitioned-tables\/","title":{"rendered":"10053 Trace Files – Global Stats on Partitioned Tables"},"content":{"rendered":"

One of the reasons why it’s taken a while to get around to the next 10053 trace file post (apart from the more human reasons I talked about here<\/a>) is that I’d planned to show how 10053 trace files can show whether the CBO has used Global Statistics on Partitioned Tables or not, which is the example that I used the first few times I gave the 10053 presentation. <\/p>\n

The last time was at the OUG Scotland conference and I had an interesting conversation with Tony Hasler<\/a> afterwards that made me both question one of my assumptions about how the CBO works and also realise I could use a 10053 trace file to prove his argument one way or the other. We were discussing which statistics the Cost Based Optimizer uses on a Partitioned Table when there are no Partition-level stats but there are Global stats. For more on Global and Partition-level stats, see here<\/a>.<\/p>\n

I’ve done so much work with stats on partitioned objects in recent years that I was surprised when Tony came up with a strategy that I thought I must have tried and therefore understood, but it turned out that I hadn’t or I had forgotten (just as likely!). The question was around which stats are used when a query is guaranteed to access only one partition. In that situation, the CBO will usually use the Partition-level stats, but what if they don’t exist and there are only Global stats?<\/p>\n

Tony was using this as a strategy that I’d never really thought of and still think is a little unusual because in most cases I’d be able to gather or set suitable Partition Stats fairly easily compared to the work required to maintain Global Stats but I was interested in how it would work.<\/p>\n

To summarise a few different possibilities here :-<\/p>\n

1) Global Stats \/ Partition Stats \/ Query accessing more than one Partition – CBO uses Global Stats
2) Global Stats \/ Partition Stats \/ Query accessing a single Partition – CBO uses the Partition Stats
3) Global Stats \/ No Partition Stats \/ Query accessing more than one Partition – CBO uses Global Stats
4) Global Stats \/ No Partition Stats \/ Query accessing a single Partition – ????<\/p>\n

I thought<\/em> the answer to the last case was that Oracle would perform Dynamic Sampling against the single partition being accessed and ignore the Global Stats. Tonys claim was that the CBO actually switches to using the Global Stats that are available and later on sent me an example to show that he was right by setting Global stats to specific values and then running a query which accessed one partition. The estimated cardinalities showed that the CBO was definitely using the Global Stats which are the only ones available.\u00a0 <\/p>\n

SQL> set echo on\nSQL> CREATE TABLE t1\n  2  (\n  3     n1   NUMBER\n  4    ,d1      DATE\n  5  )\n  6  PARTITION BY RANGE\n  7     (d1)\n  8     (\n  9        PARTITION p1 VALUES LESS THAN (DATE '2000-01-01')\n 10       ,PARTITION pdefault VALUES LESS THAN (maxvalue));\n\nTable created.\n\nSQL> BEGIN\n  2     DBMS_STATS.set_table_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')\n  3                                ,'T1'\n  4                                ,numrows   => 1000000\n  5                                ,numblks   => 1000000);\n  6     DBMS_STATS.set_column_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')\n  7                                 ,'T1'\n  8                                 ,colname   => 'D1'\n  9                                 ,distcnt   => 5\n 10                                 ,density   => 1 \/ 5);\n 11  END;\n 12  \/\n\nPL\/SQL procedure successfully completed.\n\nSQL> EXPLAIN PLAN\n  2     FOR\n  3        SELECT *\n  4          FROM t1\n  5         WHERE d1 = SYSDATE;\n\nExplained.\n\nSQL> SET LINES 200 PAGES 0\n\nSQL> SELECT * FROM TABLE (DBMS_XPLAN.display);\nPlan hash value: 1258445941\n\n-----------------------------------------------------------------------------------------------\n| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |\n-----------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT       |      |   200K|  4296K|   135K  (1)| 00:00:08 |       |       |\n|   1 |  PARTITION RANGE SINGLE|      |   200K|  4296K|   135K  (1)| 00:00:08 |   KEY |   KEY |\n|*  2 |   TABLE ACCESS FULL    | T1   |   200K|  4296K|   135K  (1)| 00:00:08 |   KEY |   KEY |\n-----------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n   2 - filter(\"D1\"=SYSDATE@!)\n\n14 rows selected.\n\nSQL> ALTER SESSION SET tracefile_identifier='TONY';\n\nSession altered.\n\nSQL> ALTER SESSION SET events 'trace [SQL_Compiler.*]';\n\nSession altered.\n\nSQL>       SELECT *\n  2          FROM t1\n  3         WHERE d1 = SYSDATE;\n\nno rows selected\n\nSQL> ALTER SESSION SET events 'trace [SQL_Compiler.*] off';\n\nSession altered.\n<\/pre>\n
Although Tony was able to use clear stats and our shared knowledge of how the CBO works to more or less prove his case (because 1,000,000 rows \/ 5 distinct values = 200,000 estimated cardinality, the beauty of a 10053 trace file is that we can use it to prove what the CBO actually does. By running his same example and generating a 10053 trace, I was able to look at the following section.<\/p>\n
***************************************\nBASE STATISTICAL INFORMATION\n***********************\nTable Stats::\n\u00a0 Table: T1\u00a0 Alias: T1\u00a0 (Using composite stats) <\/pre>\n
The ‘(Using composite stats)’ is the notification that the CBO is looking at Global Stats. For contrast, here are two examples from the presentation. The first shows case 1) above because it accesses more than one subpartition.<\/p>\n
***************************************\nBASE STATISTICAL INFORMATION\n***********************\nTable Stats::\n\u00a0 Table: TEST_TAB1\u00a0 Alias: TEST_TAB1\u00a0 (Using composite stats)\n\u00a0 (making adjustments for partition skews)\n\u00a0 ORIGINAL VALUES::\u00a0\u00a0\u00a0 #Rows: 11\u00a0 #Blks:\u00a0 660\u00a0 AvgRowLen:\u00a0 18.00\u00a0 ChainCnt:\u00a0 0.00\n\u00a0 SUBPARTITIONS::\n\u00a0 PRUNED: 2\n\u00a0 ANALYZED: 2\u00a0 UNANALYZED: 0\n\u00a0\u00a0\u00a0 #Rows: 11\u00a0 #Blks:\u00a0 61\u00a0 AvgRowLen:\u00a0 18.00\u00a0 ChainCnt:\u00a0 0.00<\/pre>\n
… and the second is for case 2), where the query is able to prune to a single partition and therefore uses the Partition Stats. i.e. There is no mention of using Composite Stats.<\/p>\n
***************************************\nBASE STATISTICAL INFORMATION\n***********************\nTable Stats::\n\u00a0 Table: TEST_TAB1\u00a0 Alias: TEST_TAB1\u00a0 (making adjustments for partition skews)\n\u00a0 ORIGINAL VALUES::\u00a0\u00a0\u00a0 #Rows: 0\u00a0 #Blks:\u00a0 1\u00a0 AvgRowLen:\u00a0 0.00\u00a0 ChainCnt:\u00a0 0.00\n\u00a0 SUBPARTITIONS::\n\u00a0 PRUNED: 1\n\u00a0 ANALYZED: 1\u00a0 UNANALYZED: 0\n\u00a0 Partition [23]\n\u00a0\u00a0\u00a0 #Rows: 0\u00a0 #Blks:\u00a0 1\u00a0 AvgRowLen:\u00a0 0.00\u00a0 ChainCnt:\u00a0 0.00\n\u00a0\u00a0\u00a0 #Rows: 0\u00a0 #Blks:\u00a0 1\u00a0 AvgRowLen:\u00a0 0.00\u00a0 ChainCnt:\u00a0 0.00\n<\/pre>\n
Note that all of these examples were executed on 11.2.0.3<\/p>\n
Thanks to Tony both for correcting my misunderstanding and giving me another example 😉<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the reasons why it’s taken a while to get around to the next 10053 trace file post (apart from the more human reasons I talked about here) is that I’d planned to show how 10053 trace files can show whether the CBO has used Global Statistics on Partitioned Tables or not, which is… Continue reading 10053 Trace Files – Global Stats on Partitioned Tables<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1705","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1701,"url":"http:\/\/orcldoug.com\/blog\/2013\/03\/18\/10053-trace-files-getting-started\/","url_meta":{"origin":1705,"position":0},"title":"10053 Trace Files – Getting Started","date":"March 18, 2013","format":false,"excerpt":"Before getting into the contents of a 10053 trace file and looking at any useful stuff, you need to know what the files are for and how and where they are created. Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1699,"url":"http:\/\/orcldoug.com\/blog\/2013\/03\/17\/10053-trace-files\/","url_meta":{"origin":1705,"position":1},"title":"10053 Trace Files","date":"March 17, 2013","format":false,"excerpt":"Sometimes I'm really not sure whether a blog post is a good idea or not. This is one of those times.I remember a while ago that Neil Chandler wrote a blog post about why you probably don't need 10046 trace files that I didn't completely agree with and I kept\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1590,"url":"http:\/\/orcldoug.com\/blog\/2010\/03\/28\/statistics-on-partitioned-tables-contents\/","url_meta":{"origin":1705,"position":2},"title":"Statistics on Partitioned Tables – Contents","date":"March 28, 2010","format":false,"excerpt":"When Jonathan Lewis decided it was time to post a list of the Partition Stats posts on his blog and Noons suggested I made them easier to track down, I listened. So this post will link to the others and, at least in the short term, I've also included links\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":1705,"position":3},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1705,"position":4},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1637,"url":"http:\/\/orcldoug.com\/blog\/2013\/05\/15\/statistics-on-partitioned-tables-part-6e-copy_table_stats-bug\/","url_meta":{"origin":1705,"position":5},"title":"Statistics on Partitioned Tables – Part 6e – COPY_TABLE_STATS – Bug","date":"May 15, 2013","format":false,"excerpt":"I'd bet regular readers might have guessed I'd never get back to the stats series, particularly given my extremely limited output this year Well, here goes ... The theme of this post is already covered in the paper and the presentation, so if you've read either of those, then you\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1705","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1705"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1705\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1705"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}