标签归档:awr

Reading this week

Outline不能正确使用的问题
数据库版本为10g,optimizer_features_enable为9.2.0。这样的话INDEX_RS_ASC这个hint不能被识别,因为其为10g所引入的。
在创建outline前加上alter session set optimizer_features_enable=’10.2.0.3′ 就可以了
http://www.dbafan.com/blog/?p=173

Hints – again
But if you read this entry and related articles you will notice that I don’t think it is a good idea to use hints in a production system unless there is no alternative. I would prefer to see, first, if a bad plan is a result of misleading statistics or an unlucky form of statement before slotting in some hints.
http://jonathanlewis.wordpress.com/2007/06/17/hints-again/

Index maintained on update if no change
alter tablesapce test offline; — to test
current mode block gets
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2613511000346462650

Analysing Statspack (11)
There are some clues in the report that might point towards the nasty object(s)

* there is some LOB activity in the database, note the statistics about direct path lob reads and writes. Maybe there are a lot of LOB inserts, and updates on LOBs which are small enough to stay in row, but long enough to create havoc (There are lots of table fetch continued row, which could be chained rows due to LOBs that get close to the 3960 byte limit for in-line storage).
* There have been 2,845 leaf node splits in 42 minutes – this isn’t inherently wrong, but it is worth looking at: are there some indexes with a lot of columns in their definitions that grow even more rapidly than the table they index ? (Be careful about building these, the impact of dropping the old index could be dire).
* HW and ST enqueues. The highwater mark jumps every five blocks: given the ratio of ST to HW enqueues (roughly 1 to 2), we might have a very large, and growing, object with an extent size of about 32KB.
* Rollback segment 24 suffered 2MB of writes, but 171 wraps – this means the average extent size (of the extents used in the 42 minutes) is 12KB. (There were no shrinks and extends in the period, so this rollback segment is not contributing to the seg$/uet$ activity, but the anomaly shows at least one sizing error in the database).

http://jonathanlewis.wordpress.com/2009/01/18/analysing-statspack-11/

Native Full Outer Join Officially Available in 10.2.0.5

To enable a new native full outer join implementation in the database, a user has to set the following underscore parameter:

_optimizer_native_full_outer_join =forc
http://antognini.ch/2010/05/native-full-outer-join-officially-available-in-10-2-0-5/

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases
Setting Semaphore Parameters

To determine the values of the four described semaphore parameters, run:
# cat /proc/sys/kernel/sem
250 32000 32 128
These values represent SEMMSL, SEMMNS, SEMOPM, and SEMMNI.
Alternatively, you can run:
# ipcs -l

shttp://www.puschitz.com/TuningLinuxForOracle.shtml#SettingSHMMNIParameter

Rowid
Technically they will need the object_id (which allows them to find the tablespace number), relative file number, block number within file, and row within row directory.
http://jonathanlewis.wordpress.com/2010/05/09/rowid/

Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)
If you check the predicate section, you will see that the predicate is “column <= subquery” is an ACCESS predicate. This means the subquery (tablescan) has to run first to generate a value that is used to drive the index range scan. You can confirm this by cloning the EMP table and using the clone in the subquery, then flushing the buffer cache and using sql_trace to watch the order of physical I/O.
http://blog.tanelpoder.com/2010/04/27/execution-plan-quiz-shouldnt-these-row-sources-be-the-other-way-around/

Advanced Oracle Troubleshooting
http://www.slideshare.net/gelek/advanced-oracle-troubleshooting

Performance and Scalalability Improvements in Oracle 10g and 11g
http://blog.tanelpoder.com/2008/05/25/performance-and-scalalability-improvements-in-oracle-10g-and-11g/

Systematic application troubleshooting in Unix
http://blog.tanelpoder.com/2008/01/05/systematic-application-troubleshooting-in-unix/

Updated Session Wait script
http://blog.tanelpoder.com/2008/01/08/updated-session-wait-script/

cursor_space_for_time To Be Deprecated
http://blog.tanelpoder.com/2008/06/17/cursor_space_for_time-to-be-deprecated/

构建高可用数据库监控系统
http://www.slideshare.net/NinGoo/ss-3630088#

reading this week outside greader

ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql
http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/comment-page-1/#comment-4456

Oracle内存全面分析(9)
http://www.hellodba.com/Doc/oracle_memory%289%29.htm

CPU used
http://jonathanlewis.wordpress.com/2009/05/26/cpu-used/

如何分析AWR (5)
http://www.os2ora.com/how-to-analyze-awr-report-5/

cardinality
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40230704959128

Using Cursors
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14354/chapter5.htm#BABECAEF

Ref cursors
http://www.adp-gmbh.ch/ora/plsql/cursors/ref_cursors.html

Cursor Sharing 3
http://jonathanlewis.wordpress.com/2010/05/03/cursor-sharing-3/

KGH: NO ACCESS allocations in V$SGASTAT – buffer cache within shared pool!
http://blog.tanelpoder.com/2009/09/09/kgh-no-access-allocations-in-vsgastat-buffer-cache-within-shared-pool/

KGH: NO ACCESS – Buffer cache inside streams pool too!
http://blog.tanelpoder.com/2010/04/21/kgh-no-access-buffer-cache-inside-streams-pool-too/