See here a nice basic explaination with examples about this nice feature of Oracle 10g: SQL Model clause.

SELECT table_name
,index_name
FROM user_indexes
WHERE _name IN (SELECT table_name FROM user_part_tables)
AND      partitioned = ‘NO’

(when having ORA-14075 then this little querie might help you find those global indexes on partitioned tables)

To check all the parameters (also the hidden ones) and their values, or a specific one by changing ‘%’ into his name:
select nam.ksppinm NAME
,           val.KSPPSTVL VALUE
from x$ksppi nam
,          x$ksppsv val
where nam.indx = val.indx
and nam.ksppinm like ‘%’
order by 1

Do you need to increase the shared_pool_reserved_size. Default Oracle puts it at 5% of the shared_pool_size. General recomedations is to put it at 10% of the shared_pool_size.  A good article can be found at DBASPOT or Metalink 146599.1
select ‘You may need to increase the SHARED_POOL_RESERVED_SIZE’ Description
, ‘Request Failures = ‘||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES > 0
and 0 [...]

See here a good white paper about query optimizationin Oracle 10g: click here.

Thanks to dbmotive i’ve put a lookup for Oracle error messages. See top of right sidebar. or try on this post:
ORA-

Watch out for hardcoded ORA-01401 (inserted value too large for column) error handling because from 10g onwards this errorcode has been discarded. His replacer is the error code ORA-12899.
It’s always anoying that codes are changing but the good thing about the change is that the error description changes. It tell you which column causes the [...]

See here some books i bought recently. The first one (Oracle Tuning) is ok (nothing more, nothing less). But those 2 other ones is really a shame for printed press!!! First of all they insult the buying of being an idiot; yes buying a book of 230 or 272 pages which actually can be printed on 60 pages. [...]