The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. Even columns with a lower number of repetitions and thus higher cardinality can be candidates if they tend to be involved in complex conditions in the WHERE clauses of queries.
Archive of posts tagged Oracle10g
DBMS_XPLAN.DISPLAY_CURSOR results in error cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Some of you received the below error while your tried to lookup the execution plan using DBMS_XPLAN.DISPLAY_CURSOR procedure.
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
Reading, Interpreting, and Components of Oracle STATSPACK Report
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables. In general, it is recommended to get the snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. Following is the compilation from metalink doc id 228913.1; and the statspack report shown here generated on a low end desktop system to see how the advisors work in 10gr2 (10.2.0.4)
Setting up Oracle Statistics Package (STATSPACK)
To install it, you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. To uninstall statspack, connect to database as SYSDBA and run spdrop.sql located under $ORACLE_HOME/rdbms/admin.
How to create an Oracle User to use both OS Authentication and Password Authentication (REMOTE_OS_AUTHENT and OS_AUTHENT_PREFIX)
The initialization parameter REMOTE_OS_AUTHENT offers the trusted authentication model to the network, which is users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication
Oracle Database Undocumented Parameters List
Table x$ksppi contains all documented and undocumented (aka hidden, which status with _ [underscore]); joining with x$ksppcv table on column indx will provide parameter name, value and description.
OERR: The command line Oracle error code lookup utility
OERR stands for Oracle Error, which is a utility ships with Oracle distribution for Linux and UNIX that helps retrieve messages from message (.msg) files based on the supplied error code. This utility is not available for Windows, but there are variants freely available on the Internet.
Oracle Metadata: PLAN_TABLE
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table.
STATEMENT_ID: VARCHAR2(30) – Value of the optional STATEMENT_ID [...]
Oracle Statistics Package (STATSPACK): A Free Performance Analysis Tool
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables.
How to identify PL/SQL performance bottlenecks using DBMS_PROFILER
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.