Oracle 10

From email:

Oracle (since version 8 ? ) has been recommending to all that we move toward the Cost Based Optimizer (CBO). Prior to this Oracle used (and still can use) the RULE based Optimizer, which pretty much just uses predicate logic and knowledge of indexes to do its job. (Also, the order of tables in the FROM clause is important, and the order of the WHERE clauses)

The CBO attempts to come up with the cheapest row access plan based on the actual cost (CPU and IO) of the plan. To do this, Oracle needs accurate statistics on the tables and indexes involved. These statistics are gathered using the Oracle supplied PLSQL procedures within the DBMS_STATS package. These procedures gather a bunch of different information about the tables and indexes, and can also gather histogram information, which can be invaluable on (mainly indexed) columns where there is highly skewed data. Stats gathering is becoming more automagic as time goes on. Everytime Oracle release a new version, there are new and improved auto options for stats gathering. there is also the possibility (we are not doing this presently) of gathering "system stats" as well. These are separate to the data object stats, and must be gathered by monitoring a live system. These stats attempt to place real time values on IO based on system load. If these are available, the CBO correlates its IO estimates with these real time estimates.

The CBO is not perfect though, and occasionally it will choose a less than optimal access path. This happens mainly because stats usually contain imprecise estimates made during stats gathering. It is possible to collect complete and accurate stats, but this is extremely expensive. To give you an example, it takes over 30 hours to gather stats in HE90U5 (using the auto options, which will estimate some things).

Portal Navigation Structure

select t.portal_name, t.portal_reftype, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_objname as portal_objname, LPAD (' ', 10 * (LEVEL - 1)) || t.portal_label as portal_label, t.portal_ispublic, t.portal_seq_num, t.lastupdoprid, level lev, t.portal_urltext
from sysadm.psprsmdefn t
where t.portal_name = 'EMPLOYEE'
start with t.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR t.portal_objname = t.portal_prntobjname and PRIOR t.portal_name = t.portal_name and PRIOR t.portal_reftype = 'F';