ࡱ> egd#` 0.bjbj\.\. vF>D>D& B B B $f 3333 3Lf wI2424242424777HHHHHHH$mJhLIB 77777I 24241I<<<7, R24B 24H<7H<<E; "B .H24&4 h3:G8RHGI0wIG8M<Mp.HMB .H$77<77777II< 777wI7777f f f df f f f f f  Hidden Benefits of 10g (Oct 2004) There are various features of Oracle 10g that get all the publicity and press, and for many people the decision to migrate is driven by whether or not they happen to see anything in the high-profile features that might be useful. However, there are numerous other changes that are likely to be of benefit to almost everyone and in this article I have described just a few of them. This article is based on material first published in the presentation: "The evolution of Optimisation: 8i to 10g", and the seminar "Optimising Oracle Performance by Design". Optimizer Improvements (1). When an Oracle database is used well, most of the work it does is in the SQL so anything that improves the ability of the optimiser to find a better path is likely to be a good thing. You may have heard already about the ability of the 10g optimiser to perform an outer join "the wrong way round" if it's a hash join. Historically the "preserved" table had to appear in the join order before the "outer" table (the one with the additional null row indicated in Oracle syntax by the (+) in the join clause). Consider the query: select count(st.padding), count(lt.padding) from small_table st, large_table lt where st.id(+) = lt.n1 ; In Oracle 8 and 9, the join order has to be large table -> small table because of the outer join. This means Oracle has to hash the large table into memory, and then probe with the small table but hash joins usually work best if the "small table" is the one that is hashed into memory. In 10g, this can happen. Spot the difference in the execution plans from 9i and 10g. Execution Plan (9i) --------------------------------------------- SORT (AGGREGATE) HASH JOIN (OUTER) (Cost=51 Card=10000) TABLE ACCESS (FULL) OF 'LARGE_TABLE' (Cost=29 Card=10000) TABLE ACCESS (FULL) OF 'SMALL_TABLE' (Cost=3 Card=29) Execution Plan (10g) --------------------------------------------- SORT (AGGREGATE) HASH JOIN (RIGHT OUTER) (Cost=36 Card=10000) TABLE ACCESS (FULL) OF 'SMALL_TABLE' (TABLE) (Cost=3 Card=29)) TABLE ACCESS (FULL) OF 'LARGE_TABLE' (TABLE) (Cost=33 Card=10000) Note especially how the 10g execution plan has the (RIGHT OUTER) option on the hash join; and how the cost of the10g hash join is simply the sum of the two required tablescans. The 9i plan is not so lucky the large table would not hash into memory, and the cost reflects the expected disk access due to the one-pass hash. (A prediction reflected at run-time by the statistic: "workarea executions onepass"). Optimizer Improvements (2). But there are other more subtle optimisations that you might never spot (apart from the improved performance) unless you look very closely. Consider the following query: select /*+ index(t1 i1) */ * from t1 where n1 = 1 and n2 = 1 and n4 = 1 and (n3+1 = 1 or n3+1 = 2 or n3+1 = 3) ; To appreciate this example you have to know that there is an index on table t1, with the columns (n1, n2, n3, n4). In a simple test case against Oracle 9.2.0.4 and Oracle 10.1.0.2, the older version performed a full tablescan, the newer version used an index range scan. There were various reasons for this, but the most significant one showed up when I forced Oracle 9 to use the index (hints are orders, not suggestions) and then did a full explain plan, rather than just a quick autotrace. The columns in the plan to watch out for are the access_predicates and filter_predicates. It is quite hard to pack a full explain plan onto a short line but the following extract gives you a good indication of what's going on: Execution Plan (9i) --------------------------------------------- TABLE ACCESS T1 (by index rowid) - Filter ("T1"."N3"+1=1 OR "T1"."N3"+1=2 OR "T1"."N3"+1=3) INDEX NON-UNIQUE I1 (range scan) - Access ("T1"."N1"=1 AND "T1"."N2"=1 AND "T1"."N4"=1) - Filter ("T1"."N4"=1) Execution Plan (10g) --------------------------------------------- TABLE ACCESS T1 (by index rowid) INDEX I1 (range scan) - Access ("N1"=1 AND "N2"=1 AND "N4"=1) - Filter ("N4"=1 AND ("N3"+1=1 OR "N3"+1=2 OR "N3"+1=3)) Note the critical difference: in Oracle 9i, the optimiser considers only columns n1, n2, and n4 when examining the entries in the index leaf blocks. Any examination of column n3 is postponed until after the table has actually been visited. In Oracle 10g, the optimiser has managed to infer that column n3 could be examined as the index leaf blocks are being acquired, and before going to the table so the only table blocks visited are exactly the right ones. As most DBAs know, unless all your table accesses by index range scan are high precision (which means just two or three rows targeted by the index) then most of the cost of range scans is in the number of table blocks visited. (This is one of the main reasons why index rebuilds are generally a waste of effort, of course). So here, in the upgrade from 9i to 10g, we see a tiny enhancement in the optimiser that hits a critical performance area very precisely the more redundant table visits you eliminate, the faster your queries go. And with this optimisation Oracle reduces the redundant visits without introducing a penalty elsewhere. Developer fixes. But it's not just the optimiser. You've probably heard about the improvements in pl/sql it now has a proper optimising compiler built in, so it can do things like eliminate redundant assignments, promote constant assignments outside the loop, and so on. But there are some tricks in pl/sql that aren't relevant to 'normal' compilers, because 'normal' languages don't understand Oracle databases. Here's the cutest (and possibly most dangerous) specialist enhancement one that isn't in the press releases. Consider the following pl/sql fragment: declare m_n number; begin m_n := 0; for i in (select v1 from t1) loop i.v1 := upper(i.v1); m_n := m_n + 1; exit when m_n = 200; end loop; end; / Single row processing often results in a major scalability problem there are lots of little overheads and contention points all over the place if you have to run this type of code with any significant degree of concurrency. So 10g fixes the code up behind the scenes. If you look for the driving statement in v$sql, and check the number of executions, fetches and rows_processed, you will see the following differences: select executions, rows_processed, fetches, sql_text from v$sql where lower(sql_text) = 'select v1 from t1' ; 9.210.1Executions11Rows processed200200Fetches2002 Note the change in the number of fetches. Oracle 10g had processed the same number of rows as the 9.2 equivalent, but it has converted the single row processing into array processing using an array size of 100. In general, this improves performance and scalability. (In my test case, the number of buffer_gets also dropped from 202 to 10 which is another indication of the typical benefit relating particularly to cache buffers chains latching - you get from array processing). If youre wondering why my predicate was based on lower(sql_text), there's yet another little enhancement. Oracle 10g tries to 'standardise' the SQL as much as possible before it hits the shared SQL area: text is capitalised, and white-space eliminated before the parser has to check to see if the incoming SQL has been previously parsed. You probably noticed my comment about "in general, this improves performance". There are cases when it does exactly the opposite. The array size seems to be fixed at 100, so if I change my code to exit after just two rows what's going to happen if there is some complicated and expensive expression that has to be evaluated for each row I return: declare m_n number; begin m_n := 0; for i in (select wait_row(v1,0.02) v1 from t1) loop i.v1 := upper(i.v1); m_n := m_n + 1; exit when m_n = 2; -- quit after two rows end loop; end; / As a simple demonstration, the wait_row() function in this examples returns its first input after waiting the number of seconds given by its second input. Under Oracle 9.2, the loop completes in about 0.04 seconds. Under Oracle 10g it takes 2.00 seconds, because the array fetch triggers the wait 100 times. There is a workaround to this problem, brought to my attention by Paul Drake and described in a presentation (http://www.minmaxplsql/downloads/Oracle10g.ppt) by Steven Feuerstein. There are three different levels for pl/sql optimisation, and this particular optimisation is one of the effects that appears only at level 2 (the default). This particular feature is disabled if you set the optimisation level back to 1 or zero, for example with the command: alter session set plsql_optimize_level = 1; Moreover, as Steve Feuerstein's presentation shows, you can ensure that a package is compiled at a particular level and stays at that level when it is subsequently recompiled. So if you do have some code where this feature is a threat, you can handle it tidily. Conclusion: There are many ways in which Oracle has been enhanced to reduce the resources, improve performance, and increase scalability. Even if none of the headline features don't hold any appeal, it would be sensible to do some testing of 10g to see if some of your performance problems disappear "for no apparent reason". Jonathan Lewis is a freelance consultant with more than 16 years experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine. He is author of 'Practical Oracle8i - Designing Efficient Databases' published by Addison-Wesley, and has contributed to two other books on Oracle. Further details of his published papers, presentations, tutorials and seminars can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups. October 2004 !"V .7l}c7{{ a!l!""##'&'..... h-H6 hCH5hCHCJOJQJ hCH56 hCH6h-HhCH)"# U V W s - . lm o#..'abw(no  )  GIJ:; !5c!78M{  01no o#o1<ACD #!(!0!7!^!`!a! o#a!b!f!k!l!w!y!{!Qkd$$IflF L1  t%0    4 lap$If{!|!!!!|vvv$Ifkd$$IflF L1 t%0    4 la!!!!!|vvv$Ifkd$$IflF L1 t%0    4 la!!!##$$7&8&@&M&S&^&&|zzzzzzzxxxxxkdU$$IflF L1 t%0    4 la &&&&&&&&1(2())'*(*/+0+<+v,w,...... ^` & F; 0&P P@PBP. A!"#5$/%55 7 0P@PBP. A!"#8$8%88 $$If!vh5L551#vL#v#v1:V l  t%05L5514p$$If!vh5L551#vL#v#v1:V l t%05L5514$$If!vh5L551#vL#v#v1:V l t%05L5514$$If!vh5L551#vL#v#v1:V l t%05L5514"P@P Normal'CJOJQJ^J_HaJhmH sH tH J@J Heading 1$./@&a$ 5>*\<@< Heading 2@& 5>*\8@8 Heading 3@&5\@ Heading 47$&$d%d&d'd/+D@&5>*CJOJQJ\^JaJ~@~ Heading 57$&$d%d&d'd/+D@&5CJOJQJ\^JaJDA@D Default Paragraph FontVi@V  Table Normal :V 44 la (k(No List F@F Normal Indent0^`0<+<  Endnote TextCJaJ4@4 Header  o#D"D  Footnote Text ^5\>O2> Double Indent ^2OB2 Action 5>*\8"8 Caption xx5\>Ob> programCJOJQJ^JaJp-@rp  Macro Text"  ` @ OJQJ^J_HhmH sH tH rB@r Body Text1&$d%d&d'd/+DCJOJQJ^JaJ0U@0 Hyperlink>*B*BC@B Body Text Indent6]O bullet pointx & F hh>T^h`@V@@ FollowedHyperlink>*B* <O< aw list body  & F&O& n  & FHR@H Body Text Indent 2 ^<O< aw heading 3 & F.X@. Emphasis6]&W&F=F"#  UVWs-.lm'abw(no )   G I J : ; !5c!78M{  01no1<ACD#(07^`abfklwy{|78@MS^1 2 !!'"("/#0#<#v$w$&&&&&00000000h00000000000000000000000000000000h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h000000000000000000000000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000000000000000000000000000h0h0\"#  UVWs-.lm'abw(no )   G I J : ; !5c!78M{  01no1<ACD#(07^`abfklwy{|78@MS^1 2 !!'"("/#0#<#v$w$&&j0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h000000000000000000000000000000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000000000000000000000000000.oa!{!!!&. !".wx/X"$g:,㠆䟬E"$(]^jp.pEE3# L0e0e     A5% 8c8c     ?1 d0u0@Ty2 NP'p<'pA)BCD|E||@w 0(  B S  ?&    $ - . 7 l } }  &){!).>FADTWcdpx$!! "!";"G"&&Wr (    #    25<?&Q#')+058>8?ADMRVY_b!%!"&&3333333333333333333333333333333333333333333333333333333&|j}~\aLf}BH~(+{n"jʸ#~  8 )|ޑ>v@ ^ )ކSN30GJ~>4`cy>#$BC _~eCs) :?w0F-g} ^`.^`.^`.^`. ^`OJQJo( ^`OJQJo( ^`OJQJo( ^`OJQJo(hh^h`. hh^h`OJQJo(*hh^h`. hh^h`OJQJo( hh^h`OJQJo( hh^h`OJQJo(hh^h`. hh^h`OJQJo( hh^h`OJQJo( hh^h`OJQJo(@hh^h`B*OJQJo( hh^h`OJQJo(@hh^h`B*OJQJo( hh^h`OJQJo( hh^h`OJQJo(hh^h`. >~}|cy>_~eCsSN30@F-g}~:?w^ )|~>4> 8 #$B,>@h h^h`OJQJo(`y-HCH`abfklwy{|&&3@t\&P@UnknownGz Times New Roman5Symbol3& z Arial?5 z Courier New"CPhVʊ&3 F F!P2d&&2PHP ?-H210g Benefits (JL Comp).Oracle 10g performance tuning trouble-shootingJonathan LewisJonathan Lewisp                    Oh+'0DX p|    10g Benefits (JL Comp)Jonathan Lewis0Oracle 10g performance tuning trouble-shooting Hidden benefits in Oracle 10g Normal.dotJonathan Lewis198Microsoft Office Word@U@|Q@k@^&h ՜.+,0 hp  JL Computer ConsultancyF& 10g Benefits (JL Comp) Title  !"#%&'()*+-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSUVWXYZ[]^_`abcfRoot Entry FPA2hhData $1Table,/NWordDocumentvFSummaryInformation(TDocumentSummaryInformation8\CompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q