How to read oracle awr report
and we said.
OLTP & DW. sort of! February 19, 2007 - 12pm UTC
Reviewer: Richard from Herftordshire, UK
You said: "surely in a transactional system, you would not want a full scan for something and an index range
for something else.
there are "we do queries per second" - they must, MUST bind.
there are "we do queries that take many many many seconds (dw)", they might not bind."
Scenario: BIG table (millions of rows). Data is skewed.
1. Shared Pool is flushed.
2. SQL issued, and Execution Plan, etc. generated and now in Shared Pool. Execution Plan says "Use index abc".
Same SQL issued, but (in reality) FTS would be the best option (owing to values used). However, owing to Execution Plan in the Shared Pool, index abc is used.
Question: Is this pretty much what happens? i.e no extra, smart parsing/checking is done by the CBO, leading to "incorrect" Execution Plan being used? If so, is the only real way around this to have 2 SQL statements; 1 for index and 1 for FTS? (maybe using uppercase & lowercase to distinguish them). If so, that's tantamount to second-guessing the CBO, isn't it?
Followup February 19, 2007 - 2pm UTC:
you would be hard parsing each time if your "smart" parsing was happening.Source: asktom.oracle.com