Understandingthe Shared Pool for Tuning, a recent webinar, I talked aboutparsing the library cache of the shared pool, how to cut down on hard parses,and AWR (Automatic Workload Repository) findings to help tune the library cache. The main purpose of the shared pool is to share your SQLstatements -- your execution plans, especially for online transactionprocessing. There are other areas in the shared pool that can be used, butthere are some important areas of the shared pool that are helpful to understand. The major components of the shared pool are: • Thelibrary cache, which stores sharedSQL and PL/SQL code and object metadata in areas distinguished bynamespaces. You're aiming to have most of your code, in an OLTP operation, inthe library cache. But, if it isn't in the library cache, you then have to do ahard parse. • Thedata dictionary cache, which holdsrow images from the data dictionary tables -- also called the row cache. • Theresults cache holds query resultsets and query fragments, so subsequent queries can retrieve the resultdirectly from the cache. If you have large SQL statements that are going to giveyou a smaller set of results, that will go into the results cache.
v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}w\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}
Normal 0 false false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:8.0pt;mso-para-margin-left:0in;line-height:107%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;}
Ifwe do the hard parse, we tend to slow down the overall performance. Instead, we’llwant to tune the library cache.SQL Statement Processing Phases SQLstatements are in four phases: a parse, a bind, an execute, and a fetch.
Theaspects that deal particularly with the shared pool are parse and bind: Parsephase is one of the stages in the processing of a SQL statement.When an application issues a SQL statement, the application makes a parse callto the Oracle Database. During the parse call, the Oracle Database checks thestatement for syntactic and semantic validity, determines whether the processissuing the statement has privileges to run it, searches for an sharable matchof the statement in the shared pool, and allocates a private SQL area for thestatement. Bind phase checks the statements for the bind variable and assigns/reassigns a valueto the bind variable. When bind variables are used properly, more soft parsesare possible, thereby reducing hard parses and keeping parsed statements in thelibrary cache for a longer period. Nowit’s good to look at the Automatic Workload Repository, or the Statspackindicators that you might be having problems with in your shared pool. Please watch my webinar, becausein it I show you these AWR reports in detail and how to use them for hints ofproblems. Specifically, we look at a load profile, instance efficiencies, topwait events, and the time model. I also provide several examples for how shared poolswork. Ihope you can join me for the full session. Check out other webinarsavailable through Oracle Learning Streams.
Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:"";mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:8.0pt;mso-para-margin-left:0in;line-height:107%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;}
ليست هناك تعليقات:
إرسال تعليق