Optimizing for Performance by Joze Senegacnik 2 Day Seminar Notes Istanbul, Turkcell Academy 23-23/08/2007 - during version 7 Oracle wanted to have tpc benchmarks, during tests need for seeing where responce time passing for performance tuning caused wait interfaces birth. Of course after the availablity of WI the problems were solved within hours :) After a while WI killed tuning with ratios. Also see Danişment Gazi Ünal's Microstate Response-time Performance Profiling(MRPP) article - http://orafaq.com/papers/mrpp-1.0.2.pdf and YAPP Method - v$ information and awr information like Top 5 wait evets are based on instance averages, you need to do 10046 analysis for specific application performance problems - responce time = service time + wait time + un-accounted time * service time : v$sesstat and v$sysstat * wait time : v$session_event and v$system_event * un-accounted time : missing time spent in OS run queue or because of swapping, paging or even measurement error - speed is about how system performs now but scalability is related to how system will perform under load * Scaling Oracle8i by James Morle - http://www.scaleabilities.co.uk/component/option,com_remository/Itemid,53/func,select/id,3/ * less latching for scalability is important, latches protect internal oracle resources similar to lock behaviour, so serialization occurs, popular bind variable example - instead of update t set x=3 where y=:a for less i/o and redo-undo update t set x=3 where y=:a and x<>3 - v$sql_shared_cursor shows if there is another child of a cached sql because of NLS settings or a tracing in place - large pool is different from other oracle pools, can not be managed by a lru algor. so it is not called a "cache" - be careful with auto sga management; not like pga auto management, it favors buffer over sql cache maybe for development and test databases - private undo buffers(in memory undo) can be disabled with "_in_memory_undo=FALSE" query x$ktifp for imu commits or flushes - 10gR2 library cache mutexes "_kks_use_mutex_pin" = TRUE by default, use v$sqlstats to benefit with less cpu instr. for getting mutex, sleep statistics are maintained with v$mutex_sleep and v$mutex_sleep_history - RBO with 10g involved only with the hint RULE, where as Oracle uses internally on its dictionary this hint still with 10g for recursive statements especially - bind peeking is governed by "_option_peek_user_binds" parameter - work area management x$qesmmiwt - pga aggr target is a terget not a limit, for some conditions Oracle may use some more, see Joze's pga paper for details, x$qesmmsga and v$process_memory - db bock gets == current mode - different than TIMED_STATISTICS = TRUE at system level setting STATISTIC_LEVEL = ALL has a real burden over system overall performance, but you might be setting this during a specific performance problem period on production system, so that lots of extra statistics are published on some v$ views and awr - optimizer trace, alternative for event 10053 : alter session set "_optimizer_trace" = here value can be; hint, environment, physical, logical, medium, none, high, all or low - in the segment header oracle has the high water mark information, the size of the table. when statistics are not gathered for at least one referenced object with cbo than; * default statistics about the table and columns are used, * avr row len defaults to 100 - clustering factor defaults to 0,7821/number of blocks * may cause ineffective results, dynamic sampling introduced with 10g for this need - after 11g it is possible to generate histograms for two columns together - publishing cost maybe the most important mistakes of Oracle, it is an internal measure, during a comparison do not use cost value but use i/o units and latching amounts - for select fnc(..) from .. ; cost is based on 3000 cpu instr. and it is hard-coded - use dbms_stats.set_system_stats to fine tune the system statistics collected by gather_system_stats - all parameters are stored in sys.optstat_hist_controls - generate_stats works on b-tree and bitmap indexes used for manually controlling CF - with dbms_stats alter database/schema tab monitoring information is stored in sys.mon_mods$ - after 10gR2 novalidate => dbms_stats.auto_invalidate used not to invalidate cursors and "_optimizer_invalidation_period" defaults to 5 hours - tkprof is good for basic analysis but lacks of; * sql statemet hierarchy * exclusive timings for recursive sql statements * report for SQLs which were not parsed * report for cursor #0 (but what about 11g? and trcsess with 10g?) - dbms_session.set_identifier('TONGUC') -> v$session's client_identifier column updated similar to dbms_application_info - if the cursor is not closed there are no STAT lines in trace files - sql*et message from client is assumed to be an idle event but you might focus on this is some cases since this is a part of your responce time, also it is one of the most common waits - buffer busy waits => after 10g read by another session event - v$waitstats / v$segstat / v$segment_statistics - dbms_stats sys_op_lbid (undocumented) function can be used to determine clustering factor - index coalesce not be done online at 10gR2 - sys.col_usage$ have column usage for predicates - hints are directives not "hints" really, reduce hard-coding they must be seen as a last resort, during upgrades cbo or new access path changes you may not be benefiting - with 10gR2 qb_name hint allows you not to hard code the index name with index hint, you just give the column name with the query block name - for 9iR2 event 10143 to switch of all hints and after 10g "_optimizer_ignore_hints" = TRUE - to examine the contents of the recommended SQL Profile - select attr1 from wri$_adv_rationale where task_id = .. ad if you accept advice they are stored at sys.sqlprof$ and sqlprof$attr - SQL Profiles can also be trasfered between databases like statsitics with dbms_stats package; * dbms_sqltune.create_stgtab_sqlprof.. * dbms_sqltune.pack__stgtab_sqlprof.. * export table from source * import table to dest * dbms_sqltune.nomap_stgtab_sqlprof.. * dbms_sqltune.unpack_stgtab_sqlprof..