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
Archive of posts tagged Oracle10g
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 [...]
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.
DBMS_XPLAN.DISPLAY_CURSOR does not display execution plan of all children associated with the SQL_ID
Per Oracle documentation, to display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format.
How to run multiple Oracle database instances on a single server
You can have multiple instances on the same machine, each with their own data files, either sharing the ORACLE_HOME or each with different ORACLE_HOME. ORACLE_HOME and ORACLE_SID are the key environment variables used by Oracle to identify an instance. In addition $ORACLE_HOME/bin must be in your PATH environment variable. To check the value of these [...]
How to read an Oracle SQL Execution Plan?
To execute any SQL statement Oracle has to derive an ‘execution plan’ . The execution plan of a query is a description of how Oracle will implement the retrieval of data to satisfy a given SQL statement. It is nothing but a tree which contains the order of steps and relationship between them.
Tuning SQL statements with AUTOTRACE in SQL*Plus
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements. To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you. DBA privileges are required to grant the PLUSTRACE role.