{"status":"ok","message-type":"work","message-version":"1.0.0","message":{"indexed":{"date-parts":[[2025,5,13]],"date-time":"2025-05-13T18:25:04Z","timestamp":1747160704292},"reference-count":18,"publisher":"Association for Computing Machinery (ACM)","issue":"2","content-domain":{"domain":[],"crossmark-restriction":false},"short-container-title":["Proc. VLDB Endow."],"published-print":{"date-parts":[[2008,8]]},"abstract":"<jats:p>Execution plans for SQL statements have a significant impact on the overall performance of database systems. New optimizer statistics, configuration parameter changes, software upgrades and hardware resource utilization are among a multitude of factors that may cause the query optimizer to generate new plans. While most of these plan changes are beneficial or benign, a few rogue plans can potentially wreak havoc on system performance or availability, affecting critical and time-sensitive business application needs. The normally desirable ability of a query optimizer to adapt to system changes may sometimes cause it to pick a sub-optimal plan compromising the stability of the system.<\/jats:p>\n          <jats:p>In this paper, we present the new SQL Plan Management feature in Oracle 11g. It provides a comprehensive solution for managing plan changes to provide stable and optimal performance for a set of SQL statements. Two of its most important goals are preventing sub-optimal plans from being executed while allowing new plans to be used if they are verifiably better than previous plans. This feature is tightly integrated with Oracle's query optimizer.<\/jats:p>\n          <jats:p>SQL Plan Management is available to users via both command-line and graphical interfaces. We describe the feature and then, using an industrial-strength application suite, present experimental results that show that SQL Plan Management provides stable and optimal performance for SQL statements with no performance regressions.<\/jats:p>","DOI":"10.14778\/1454159.1454175","type":"journal-article","created":{"date-parts":[[2014,6,24]],"date-time":"2014-06-24T12:17:57Z","timestamp":1403612277000},"page":"1346-1355","source":"Crossref","is-referenced-by-count":10,"title":["Optimizer plan change management"],"prefix":"10.14778","volume":"1","author":[{"given":"Mohamed","family":"Ziauddin","sequence":"first","affiliation":[{"name":"Oracle Corporation, Redwood Shores, CA"}]},{"given":"Dinesh","family":"Das","sequence":"additional","affiliation":[{"name":"Oracle Corporation, Redwood Shores, CA"}]},{"given":"Hong","family":"Su","sequence":"additional","affiliation":[{"name":"Oracle Corporation, Redwood Shores, CA"}]},{"given":"Yali","family":"Zhu","sequence":"additional","affiliation":[{"name":"Oracle Corporation, Redwood Shores, CA"}]},{"given":"Khaled","family":"Yagoub","sequence":"additional","affiliation":[{"name":"Oracle Corporation, Redwood Shores, CA"}]}],"member":"320","published-online":{"date-parts":[[2008,8]]},"reference":[{"key":"e_1_2_1_1_1","doi-asserted-by":"publisher","DOI":"10.5555\/645481.655611"},{"key":"e_1_2_1_2_1","doi-asserted-by":"publisher","DOI":"10.1007\/s007780050026"},{"key":"e_1_2_1_3_1","doi-asserted-by":"publisher","DOI":"10.1145\/1376616.1376721"},{"key":"e_1_2_1_4_1","volume-title":"Influence query optimization with optimization profiles and statistical views in DB2 9","author":"Chen K.","year":"2006","unstructured":"Chen , K. Influence query optimization with optimization profiles and statistical views in DB2 9 , 2006 , http:\/\/www.ibm.com\/developerworks\/db2\/library\/techarticle\/dm-0612chen\/index.html Chen, K. Influence query optimization with optimization profiles and statistical views in DB2 9, 2006, http:\/\/www.ibm.com\/developerworks\/db2\/library\/techarticle\/dm-0612chen\/index.html"},{"key":"e_1_2_1_5_1","doi-asserted-by":"publisher","DOI":"10.5555\/1316689.1316784"},{"key":"e_1_2_1_6_1","doi-asserted-by":"publisher","DOI":"10.1145\/191843.191872"},{"key":"e_1_2_1_7_1","first-page":"103","volume-title":"VLDB","author":"Ioannidis Y.","year":"1992","unstructured":"Ioannidis , Y. , Ng , R. T. , Shim , K. and Sellis , T . Parametric query optimization . VLDB , 1992 , pages 103 -- 114 . Ioannidis, Y., Ng, R. T., Shim, K. and Sellis, T. Parametric query optimization. VLDB, 1992, pages 103--114."},{"key":"e_1_2_1_9_1","doi-asserted-by":"publisher","DOI":"10.1145\/276305.276315"},{"key":"e_1_2_1_10_1","doi-asserted-by":"publisher","DOI":"10.1147\/sj.421.0098"},{"key":"e_1_2_1_11_1","doi-asserted-by":"publisher","DOI":"10.1145\/564691.564766"},{"key":"e_1_2_1_12_1","doi-asserted-by":"publisher","DOI":"10.1145\/1007568.1007642"},{"key":"e_1_2_1_13_1","volume-title":"SQL Server 2005 Books Online","author":"Microsoft","year":"2007","unstructured":"Microsoft , Hints (Transact-SQL) , SQL Server 2005 Books Online , 2007 , http:\/\/msdn2.microsoft.com\/en-us\/library\/ms187713.aspx Microsoft, Hints (Transact-SQL), SQL Server 2005 Books Online, 2007, http:\/\/msdn2.microsoft.com\/en-us\/library\/ms187713.aspx"},{"key":"e_1_2_1_14_1","first-page":"18","author":"Oracle","year":"2007","unstructured":"Oracle , Using Plan Stability, Oracle Performance Tuning Guide , Oracle Database, 11g Release 1 (11.1) Documentation , Chapter 18 , 2007 . Oracle, Using Plan Stability, Oracle Performance Tuning Guide, Oracle Database, 11g Release 1 (11.1) Documentation, Chapter 18, 2007.","journal-title":"Chapter"},{"key":"e_1_2_1_15_1","volume-title":"Microsoft TechNet, 2005","author":"Patel B. A.","year":"2005","unstructured":"Patel , B. A. , Forcing query plans , Microsoft TechNet, 2005 , http:\/\/www.microsoft.com\/technet\/prodtechnol\/sql\/ 2005 \/frcqupln.mspx Patel, B. A., Forcing query plans, Microsoft TechNet, 2005, http:\/\/www.microsoft.com\/technet\/prodtechnol\/sql\/2005\/frcqupln.mspx"},{"key":"e_1_2_1_16_1","doi-asserted-by":"publisher","DOI":"10.1145\/1247480.1247597"},{"key":"e_1_2_1_17_1","first-page":"19","volume-title":"VLDB","author":"Stillger M.","year":"2001","unstructured":"Stillger , M. , Lohman , G. , Markl , V. and Kandil , M . LEO -- DB2's learning optimizer , VLDB , 2001 , pages 19 -- 28 Stillger, M., Lohman, G., Markl, V. and Kandil, M. LEO -- DB2's learning optimizer, VLDB, 2001, pages 19--28"},{"key":"e_1_2_1_18_1","doi-asserted-by":"publisher","DOI":"10.1145\/276305.276317"},{"key":"e_1_2_1_19_1","volume-title":"Oracle's SQL performance analyzer","author":"Yagoub K.","year":"2008","unstructured":"Yagoub , K. , Belknap , P. , Dageville , B. , Dias , K. , Joshi , S. and Yu , H . Oracle's SQL performance analyzer . IEEE Data Engineering Bulletin , 2008 , volume 31 , number 1. Yagoub, K., Belknap, P., Dageville, B., Dias, K., Joshi, S. and Yu, H. Oracle's SQL performance analyzer. IEEE Data Engineering Bulletin, 2008, volume 31, number 1."}],"container-title":["Proceedings of the VLDB Endowment"],"original-title":[],"language":"en","link":[{"URL":"https:\/\/dl.acm.org\/doi\/pdf\/10.14778\/1454159.1454175","content-type":"unspecified","content-version":"vor","intended-application":"similarity-checking"}],"deposited":{"date-parts":[[2022,12,28]],"date-time":"2022-12-28T09:56:02Z","timestamp":1672221362000},"score":1,"resource":{"primary":{"URL":"https:\/\/dl.acm.org\/doi\/10.14778\/1454159.1454175"}},"subtitle":["improved stability and performance in Oracle 11g"],"short-title":[],"issued":{"date-parts":[[2008,8]]},"references-count":18,"journal-issue":{"issue":"2","published-print":{"date-parts":[[2008,8]]}},"alternative-id":["10.14778\/1454159.1454175"],"URL":"https:\/\/doi.org\/10.14778\/1454159.1454175","relation":{},"ISSN":["2150-8097"],"issn-type":[{"value":"2150-8097","type":"print"}],"subject":[],"published":{"date-parts":[[2008,8]]}}}