Archive | CBO RSS feed for this section

Global Hints

9 Apr

Occasionally I encounter a situation when I need to affect a part of the plan that corresponds to a view, e.g.:

select *
from
(
   select v.x, x.y
   from v
) q
where q.x = 1

Such situations are resolved using global hints. Oracle offers two ways to specify a global hint: via a query block identifier (system generated or user defined) or via view aliases. System-generated query block identifiers can be obtained via dbms_xplan.display with ALL or ALIAS option (they have the form SEL$n, where n appears to be same as the depth, e.g. in our case 1 corresponds to the main query, 2 to the inline view, 3 to the view V inside that inline view) or defined by the user via qb_name hint.

Continue reading →

Tags: , , ,

SQL tuning: real-life example

22 Mar

An example of tuning — nothing special, but it does illustrate several aspects of tuning work, so I thought I’d make a blog post out of it.

Yesterday I as contacted by a development team working with a 10.2.0.4 database. Their complaint was the traditional “the system is slow”. Despite very unspecific symptoms, they were very specific about the time when it all started. I didn’t have access to AWR on that database, so instead I looked at top wait events in DBA_HIST_SYSTEM_EVENT for recent snapshots, nothing interesting. The top wait event was db file sequential read (with db file scattered read as a very distant second). Since that particular group was working with just one user in the database, I looked at ASH data for this user — same thing, just a bunch of db file seqential reads. Continue reading →

Tags: , , ,

Dynamic sampling pitfalls

21 Dec

Some more wisdom from Randolf Geist, this time about dynamic sampling…

As it turns out, the default behavior for dynamic sampling is to only be used for selectivity estimation, if cardinality is available from statistics (if not, then dynamic sampling will be used for both selectivity and cardinality). This can occasionally lead to very confusing behavior. In such cases one can use a “dynamic_sampling_est_cdn” hint.

 A few interesting links kindly provided by Randolf:

Tags: , , ,

Follow

Get every new post delivered to your Inbox.

Join 72 other followers