Script to check for Oracle table locks:
Display details on which table is being locked by which process/user
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
Oracle: Script to display current slow job details
Oracle Script to display slow jobs details
column e_dttm format a15
column usr format a10
column hp format 9999999999
select
TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm, -- Interval End Time
m.intsize_csec/100 ints, -- Interval size in sec
s.username usr,
m.session_id sid,
m.session_serial_num ssn,
ROUND( m. cpu) cpu100, -- CPU usage 100th sec
m.physical_reads prds, -- Number of physical reads
m.logical_reads lrds, -- Number of logical reads
m.pga_memory pga, -- PGA size at end of interval
m.hard_parses hp,
m.soft_parses sp,
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m, v$session s
where (m. physical_reads > 100
or m. cpu > 100
or m. logical_reads > 100)
and m. session_id = s. sid
and m. session_serial_num = s. serial#
order by m. physical_reads DESC, m. cpu DESC, m. logical_reads DESC;
column e_dttm format a15
column usr format a10
column hp format 9999999999
select
TO_CHAR(m. end_time, ' DD-MON-YYYY HH24:MI: SS' ) e_dttm, -- Interval End Time
m.intsize_csec/100 ints, -- Interval size in sec
s.username usr,
m.session_id sid,
m.session_serial_num ssn,
ROUND( m. cpu) cpu100, -- CPU usage 100th sec
m.physical_reads prds, -- Number of physical reads
m.logical_reads lrds, -- Number of logical reads
m.pga_memory pga, -- PGA size at end of interval
m.hard_parses hp,
m.soft_parses sp,
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m, v$session s
where (m. physical_reads > 100
or m. cpu > 100
or m. logical_reads > 100)
and m. session_id = s. sid
and m. session_serial_num = s. serial#
order by m. physical_reads DESC, m. cpu DESC, m. logical_reads DESC;
Oracle: Script to display session details
Script to display Oracle session details:
column value format a40
column description format a100
SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'The terminal where the user is logged' AS Description FROM Dual;
column value format a40
column description format a100
SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'The terminal where the user is logged' AS Description FROM Dual;
Oracle Hints (10G)
Hints for Optimization Approaches and Goals | |
| ALL_ROWS | The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). |
| FIRST_ROWS | The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS (n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows. |
| CHOOSE | The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement |
| RULE | The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g. |
Hints for Access Paths | |
| FULL | The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL (table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed. |
| ROWID | The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID (table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g) |
| CLUSTER | The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER (table) where table specifies the name or alias of the table to be accessed by a cluster scan. |
| HASH | The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH (table) where table specifies the name or alias of the table to be accessed by a hash scan. |
| HASH_AJ | The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ (table) where table specifies the name or alias of the table to be accessed. (Depricated in Oracle 10g ) |
| INDEX | The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX (table index) where: table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes: |
| NO_INDEX | The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX (table index) |
| INDEX_ASC | The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. |
| INDEX_COMBINE | If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE (table index). |
| INDEX_JOIN | Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query. |
| INDEX_DESC | The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. |
| INDEX_FFS | This hint causes a fast full index scan to be performed rather than a full table. |
| NO_INDEX_FFS | Do not use fast full index scan (from Oracle 10g) |
| INDEX_SS | Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_ASC | Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_DESC | Exclude range scan from query plan (from Oracle 10g) |
| NO_INDEX_SS | The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (From Oracle 10g) |
Hints for Query Transformations | |
| NO_QUERY_TRANSFORMATION | Prevents the optimizer performing query transformations. (From Oracle 10g) |
| USE_CONCAT | The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them . |
| NO_EXPAND | The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it. |
| REWRITE | The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view , then Oracle uses that view regardless of its cost. |
| NOREWRITE / NO_REWRITE | In Oracle 10g renamed to NO_REWRITE. The NOREWRITE / NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. |
| MERGE | The MERGE hint lets you merge views in a query. |
| NO_MERGE | The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster. |
| FACT | The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation. |
| NO_FACT | The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table. |
| STAR_TRANSFORMATION | The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. |
| NO_STAR_TRANSFORMATION | Do not use star transformation (from Oracle 10g) |
| UNNEST | The UNNEST hint specifies subquery unnesting. |
| NO_UNNEST | Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks. |
Hints for Join Orders | |
| LEADING | Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING (table) |
| ORDERED | The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join, the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could. |
Hints for Join Operations | |
| USE_NL | The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL (table table) where table is the name or alias of a table to be used as the inner table of a nested loops join. |
| NO_USE_NL | Do not use nested loop (from Oracle 10g) |
| USE_NL_WITH_INDEX | Specifies a nested loops join. (From Oracle 10g) |
| USE_MERGE | The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE (table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join. |
| NO_USE_MERGE | Do not use merge (from Oracle 10g) |
| USE_HASH | The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH (table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join. |
| NO_USE_HASH | Do not use hash (from Oracle 10g) |
| Hints for Parallel Execution | |
| PARALLEL | The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL (table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism. |
| NOPARALLEL / NO_PARALLEL | The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL. |
| PQ_DISTRIBUTE | The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make. |
| NO_PARALLEL_INDEX | The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation. |
| Additional Hints | |
| APPEND | When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo. |
| NOAPPEND | Overrides the append mode. |
| CACHE | The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification. |
| NOCACHE | The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. |
| PUSH_PRED | The PUSH_PRED hint forces pushing of a join predicate into the view. |
| NO_PUSH_PRED | The NO_PUSH_PRED hint prevents pushing of a join predicate into the view. |
| PUSH_SUBQ | The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. |
| NO_PUSH_SUBQ | The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. |
| QB_NAME | Specifies a name for a query block. (From Oracle 10g) |
| CURSOR_SHARING_EXACT | Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables. |
| DRIVING_SITE | The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle |
| DYNAMIC_SAMPLING | The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table. |
| SPREAD_MIN_ANALYSIS | This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (From Oracle 10g) |
| Hints with unknown status | |
| MERGE_AJ | The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ (table) where table specifies the name or alias of the table to be accessed. (Depricated in Oracle 10g ) |
| AND_EQUAL | The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes. The syntax of the AND_EQUAL hint is AND_EQUAL (table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g) |
| STAR | The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (Depricated in Oracle 10g) |
| BITMAP | Usage: BITMAP (table_name index_name) Uses a bitmap index to access the table. (Depricated?) |
| HASH_SJ | Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work. (depricated in Oracle 10g) |
| NL_SJ | Use a Nested Loop in a sub-query. (Depricated in Oracle 10g) |
| NL_AJ | Use an anti-join in a sub-query. (Depricated in Oracle 10g) |
| ORDERED_PREDICATES | (depricated in Oracle 10g) |
| EXPAND_GSET_TO_UNION | (depricated in Oracle 10g) |
Oracle: SqlPlus Useful Set Command
Useful SET commands for output formatting and SPOOL:
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
SET TERM OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT ON
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING OFF
-- HEADING = OFF will hide column headings
SET FEEDBACK OFF
-- FEEDBACK = ON will count rows returned
SET PAUSE OFF
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE 0
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE 80
-- LINESIZE = width of page (80 is typical)
SET VERIFY OFF
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF
Oracle: Monitoring progress of import jobs
Code to monitor speed of import jobs:
select
substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-
to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/
((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from
sys.v_$sqlarea
where
sql_text like 'INSERT %INTO "%'
and
command_type = 2
and
open_versions > 0;
select
substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-
to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/
((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from
sys.v_$sqlarea
where
sql_text like 'INSERT %INTO "%'
and
command_type = 2
and
open_versions > 0;
Oracle: Initialization Parameters (10g)
Initialization parameters for Oracle 10g can be obtained from v$parameter;
sample script to get values:
set pagesize 1000
set linesize 150
column name format a40
column value format a40
column description format a40
select name, value, description from v$parameter order by name;
useful links:
http://www.dba-oracle.com/t_oracle10g_initialization_parms.htm
sample script to get values:
set pagesize 1000
set linesize 150
column name format a40
column value format a40
column description format a40
select name, value, description from v$parameter order by name;
useful links:
http://www.dba-oracle.com/t_oracle10g_initialization_parms.htm
Oracle: Commonly Used System Tables
Below is an alphabetical listing of commonly used Oracle system tables.
| System Table | Description |
|---|---|
| ALL_ARGUMENTS | Arguments in object accessible to the user |
| ALL_CATALOG | All tables, views, synonyms, sequences accessible to the user |
| ALL_COL_COMMENTS | Comments on columns of accessible tables and views |
| ALL_CONSTRAINTS | Constraint definitions on accessible tables |
| ALL_CONS_COLUMNS | Information about accessible columns in constraint definitions |
| ALL_DB_LINKS | Database links accessible to the user |
| ALL_ERRORS | Current errors on stored objects that user is allowed to create |
| ALL_INDEXES | Descriptions of indexes on tables accessible to the user |
| ALL_IND_COLUMNS | COLUMNs comprising INDEXes on accessible TABLES |
| ALL_LOBS | Description of LOBs contained in tables accessible to the user |
| ALL_OBJECTS | Objects accessible to the user |
| ALL_OBJECT_TABLES | Description of all object tables accessible to the user |
| ALL_SEQUENCES | Description of SEQUENCEs accessible to the user |
| ALL_SNAPSHOTS | Snapshots the user can access |
| ALL_SOURCE | Current source on stored objects that user is allowed to create |
| ALL_SYNONYMS | All synonyms accessible to the user |
| ALL_TABLES | Description of relational tables accessible to the user |
| ALL_TAB_COLUMNS | Columns of user's tables, views and clusters |
| ALL_TAB_COL_STATISTICS | Columns of user's tables, views and clusters |
| ALL_TAB_COMMENTS | Comments on tables and views accessible to the user |
| ALL_TRIGGERS | Triggers accessible to the current user |
| ALL_TRIGGER_COLS | Column usage in user's triggers or in triggers on user's tables |
| ALL_TYPES | Description of types accessible to the user |
| ALL_UPDATABLE_COLUMNS | Description of all updatable columns |
| ALL_USERS | Information about all users of the database |
| ALL_VIEWS | Description of views accessible to the user |
| DATABASE_COMPATIBLE_LEVEL | Database compatible parameter set via init.ora |
| DBA_DB_LINKS | All database links in the database |
| DBA_ERRORS | Current errors on all stored objects in the database |
| DBA_OBJECTS | All objects in the database |
| DBA_ROLES | All Roles which exist in the database |
| DBA_ROLE_PRIVS | Roles granted to users and roles |
| DBA_SOURCE | Source of all stored objects in the database |
| DBA_TABLESPACES | Description of all tablespaces |
| DBA_TAB_PRIVS | All grants on objects in the database |
| DBA_TRIGGERS | All triggers in the database |
| DBA_TS_QUOTAS | Tablespace quotas for all users |
| DBA_USERS | Information about all users of the database |
| DBA_VIEWS | Description of all views in the database |
| DICTIONARY | Description of data dictionary tables and views |
| DICT_COLUMNS | Description of columns in data dictionary tables and views |
| GLOBAL_NAME | global database name |
| NLS_DATABASE_PARAMETERS | Permanent NLS parameters of the database |
| NLS_INSTANCE_PARAMETERS | NLS parameters of the instance |
| NLS_SESSION_PARAMETERS | NLS parameters of the user session |
| PRODUCT_COMPONENT_VERSION | version and status information for component products |
| ROLE_TAB_PRIVS | Table privileges granted to roles |
| SESSION_PRIVS | Privileges which the user currently has set |
| SESSION_ROLES | Roles which the user currently has enabled. |
| SYSTEM_PRIVILEGE_MAP | Description table for privilege type codes. Maps privilege type numbers to type names |
| TABLE_PRIVILEGES | Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee |
| TABLE_PRIVILEGE_MAP | Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names |
Subscribe to:
Posts (Atom)