在创建outline前加上alter session set optimizer_features_enable=’10.2.0.3′ 就可以了
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.
Index maintained on update if no change
alter tablesapce test offline; — to test
current mode block gets
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).
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
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.
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.
Advanced Oracle Troubleshooting
Performance and Scalalability Improvements in Oracle 10g and 11g
Systematic application troubleshooting in Unix
Updated Session Wait script
cursor_space_for_time To Be Deprecated