One of your databases supports a mixed workload.
When monitoring SQL performance, you detect many direct paths reads full table scans.
What are the two possible causes?
A.
Histograms statistics not available
B.
Highly selective filter on indexed columns
C.
Too many sort operations performed by queries
D.
Indexes not built on filter columns
E.
Too many similar type of queries getting executed with cursor sharing disabled
Explanation:
Note:
* The direct path read Oracle metric occurs during Direct Path operations when the data is
asynchronously read from the database files into the PGA instead of into the SGA data buffer.
Direct reads occur under these conditions:
– When reading from the TEMP tablespace (a sort operation)
– When reading a parallel full-table scan (parallel query factotum (slave) processes)
– Reading a LOB segment
* The optimizer uses a full table scan in any of the following cases:
– Lack of Index
– Large Amount of Data
– Small Table
– High Degree of Parallelism
Answers: A,D
B is false, with highly selective filter on indexed column oracle will prefer index scan.
I would go for C & D
@PS, why do you think sort operations could favor FULL TABLE SCAN ?
@ Raka, Its not just Full table scan its Direct path read, so I read in Oracle doc that too many Sort operations is responsible for DirectPath read , with full table scan due to non availability of index.
Hence C&D
Direct reads occur under these conditions:
When reading from the TEMP tablespace (a sort operation)
When reading a parallel full-table scan (parallel query factotum (slave) processes)
Reading a LOB segment
having said that in 11g rel, if we only consider the reason for Direct path Full table scan, then Yes,
I do agree with Raka on A&D are correct answers.
I think tt’s C&D. If Histograms are missing Optimizer might choose to do FTS, but not direct path reads.
I Think C,D.
https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_direct_path_read_pct.html
Sites of interest we have a link to.
always a huge fan of linking to bloggers that I like but do not get a good deal of link like from
usually posts some really fascinating stuff like this. If you are new to this site
the time to study or stop by the material or sites we’ve linked to beneath the
that will be the end of this article. Right here you will come across some websites that we think youll enjoy, just click the hyperlinks over
please visit the web-sites we stick to, including this one particular, as it represents our picks from the web
the time to study or take a look at the material or websites we have linked to beneath the
we prefer to honor numerous other online web sites on the net, even if they arent linked to us, by linking to them. Under are some webpages really worth checking out
we came across a cool site which you may possibly love. Take a search for those who want
although internet sites we backlink to beneath are considerably not associated to ours, we really feel they may be truly really worth a go via, so have a look
Here is a great Weblog You may Come across Exciting that we Encourage You
we came across a cool web page that you may possibly delight in. Take a look when you want
always a massive fan of linking to bloggers that I adore but do not get a good deal of link enjoy from