PERFORMANCE TUNING

Gathering Fixed Objects Statistics

What are the fixed objects:Fixed objects are the x$ tables and their indexes. Why we must gather statistics on fixed objects:If the statistics are not gathered on fixed objects, the Optimizer will use predefined default values for the statistics. These defaults may lead to inaccurate execution plans. Does Oracle gather statistics on fixed objects:Statistics on […]

ORADEBUG How To

What is ORADEBUG: ORADEBUG is a debugger tool, can be used for (tracing any session, dump DB memory structure, suspend/resume a session and the most useful use is to analyze the hang when an instance is hanging by creating a report shows the blocked/blocker sessions). How to use ORADEBUG: If you are lazy to do […]

View Hardware & OS Statistics Using V$OSSTAT View

While I was checking the audit logs for a database I found a log tracking EM activities, I take a look inside and found it gathers some information on HW & OS using a view called v$osstat, I look at that view and found it will be helpful if  I include it in the database health […]

Fixing Migrated/Chained Rows

Definitions:Row Chaining: A row is too large to fit into a single database block. For example, if you use 8 KB block size for your database, and you need to insert a row of 16 KB into it, Oracle will use 2 blocks and store the row in pieces. Row Chaining is often unavoidable with tables have […]

Which is better for gathering statistics? DBMS_STATS or ANALYZE command

Use DBMS_STATS for gathering statistics that related to the optimizer,execution plans and so forth.Cost-Based Optimizer (CBO) will only use the statistics been gathered by DBMS_STATS. Use ANALYZE command to collect statistics NOT related to Cost-Based Optimizer (CBO)Such statistics can be used by VALIDATE or LIST CHAINED ROWS clauses, can help in gathering accurate data such […]