Show currently executing SQL

Whenever your database is experiencing a performance problem, it is very useful to have a script that displays all of the currently executing SQL statements. The following script joins the v$sql and v$session tables to show you all of the SQL that is executing at that moment in your database.

set pagesize  24

set newpage    1

set linesize 125

column sql_text  format a100

column user_name format a12

select

u.sid,

substr(u.username,1,12) user_name,

s.sql_text

from

v$sql s,

v$session u

where

s.hash_value = u.sql_hash_value

and

sql_text not like ‘%from v$sql s, v$session u%’

order by

u.sid;

Here is the output from this script.  As we see, this is extremely useful because a runaway SQL statement could cause degradation of your whole system.  If you identify a runaway query, you can issue the ALTER SYSTEM KILL SESSION ‘SID, SER#’; command to kill the query.

http://www.oracle-training.cc/oracle_tips_v$sql.htm

cursor_sharing

Oracle Documentation => Reference

Initialization Parameters

CURSOR_SHARING = { SIMILAR | EXACT | FORCE }

• FORCEForces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
• SIMILARCauses statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
• EXACTOnly allows statements with identical text to share the same cursor.

http://www.eygle.com/digest/2007/09/cursor_sharing_similar.html

当表的字段被分析过存在histograms的时候，similar 的表现和exact一样，当表的字段没被分析不存在histograms的时候,similar的表现和force一样。

What is STATSPACK?

STATSPACK is a set of tools that captures and reports on database statistics. It is included with the Oracle 9i server software and is implemented as a set of SQL scripts in the $ORACLE_HOME/rdbms/admin directory. (Note that STATSPACK has been replaced by the Automatic Workload Repository in Oracle 10g).

You set up STATSPACK by running the SPCREATE.SQL script, which creates the STATSPACK schema owner and objects.

You then take a “snapshot” of current statistics by executing the procedure STATSPACK.SNAP. After a period of time of your choosing, you take another snapshot, then run the script SPREPORT.SQL, which produces a report showing the statistics attributable to the time between the snapshots, by computing the delta of the two sets of statistics.

Some DBAs take STATSPACK snapshots periodically, and detect trends in statistics by viewing reports for each interval. Another application of STATSPACK might be to see the statistics produced by a certain operation or load profile. Statspack is not a substitute for analysis of individual session trace files, but it can be useful in revealing an Oracle server’s overall resource-use profile.

Want to use STATSPACK to calculate the efficiency of your Oracle database?

The problem when looking at STATSPACK or similar tools is that the tool will tell you where a problem is. The big question is whether this is a problem that is noticable to the end users. Unfortunately, no tool can directly tell you this. You’ll have to talk with your end users to find out where they feel the most pain in the performance of the application.

This does not mean that STATSPACK reports or the like are not useful even if you do not have feedback from the users. Typically when I look at a STATSPACK report, I am investigating a specific problem. However, there are times when I look at STATSPACK to get an overall feel of how things are going. The first thing I look at is the top five wait events. What events are in the top five? Are these normal events? You cannot answer that last question unless you look at your STATSPACK report during normal operations. In one of my databases, it is normal for me to see ‘db file sequential read’ and ‘CPU time’ as the top two events. I know this because I have looked at the reports for these systems many, many times. If I look at a STATSPACK report and another event is in the top two, then I know something is out of place and further investigation is warranted.

The next place I look is at the SQL statements. I typically know the most resource-intensive SQL statements in my database. If I see a new SQL statement as one of the most resource-intensive, then I may need to tune that new statement.

The last place I look in my STATSPACK report is at my disk usage summaries. Again, I look for things that are abnormal. All of this means you must know what is normal. What is normal for me may not be normal for you. So you’ll have to look at your STATSPACK report on a regular basis to get a general baseline of normal behavior. Oracle 10g takes this a step further by letting you collect a baseline of normal behavior and letting you compare future metrics against this baseline with the ADDM.

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1264070,00.html

Tuning a ‘LIKE-clause’ by using Oracle Text or Reverse Key Indexes

The LIKE-clause can ignore indexes, causing queries to run forever while doing full table scans. This document describes how to tune such SQL statements by making use of Oracle Text or reverse key indexes.

Tuning the ‘LIKE’ Clause:

Generally, search arguments in the WHERE clause such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500′” prevents Oracle from using an index to perform the search (however, not always).

If you use LIKE in your WHERE clause, try to specify one or more leading characters if at all possible. For example, use LIKE ‘m%’ and not LIKE ‘%m’. If you specify a leading character, Oracle has a better chance of being able to use an index to perform the query – this will increase performance and reduce the load on the database server.

To avoid such full table scans, consider the following scenarios:

Case 1: Tuning the LIKE-clause by using Oracle Text indexes

Requirements:

A. Install and configure Oracle’s TEXT (done as part of the installation process).

B. Check whether Oracle TEXT is installed by looking for the ‘CTXSYS’ schema.

The problem

I. Create a test table:

create table t as select * from tab;
CREATE INDEX normal_index ON t
(TNAME) NOPARALLEL;

SQL> select TNAME from t where TNAME LIKE '%SEG%';
TNAME
------------------------------
DBA_AUTO_SEGADV_CTL
DBA_AUTO_SEGADV_SUMMARY
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ
DBA_ROLLBACK_SEGS
DBA_SEGMENTS
DBA_SEGMENTS_OLD
GV_$SEGMENT_STATISTICS
GV_$SEGSTAT
GV_$SEGSTAT_NAME
GV_$SORT_SEGMENT
SEG$
SYSSEGOBJ
SYS_DBA_SEGS
SYS_USER_SEGS
USER_SEGMENTS
V_$SEGMENT_STATISTICS
V_$SEGSTAT
V_$SEGSTAT_NAME
V_$SORT_SEGMENT
WRH$_SEG_STAT
WRH$_SEG_STAT_BL
WRH$_SEG_STAT_OBJ
WRI$_SEGADV_CNTRLTAB
WRI$_SEGADV_OBJLIST

II. Run a query to demonstrate that it will do a full table scan:

SQL> select TNAME from t where TNAME LIKE '%SEG%';

Execution Plan
----------------------------------------------------------
Plan hash value: 2051864513

--------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    25 |   425 |     5   (0)| 00:00
|*  1 |  INDEX FAST FULL SCAN| NORMAL_INDEX |    25 |   425 |     5   (0)| 00:00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TNAME" LIKE '%SEG%')

Note
-----
   - dynamic sampling used for this statement

III. Drop the index:

SQL> drop index normal_index;

Index dropped.

Solution:

I. Create an Oracle Text index on the columns that you would like to search:

SQL> create index xyz_oracle_txt_idx on t(TNAME) INDEXTYPE IS CTXSYS.CONTEXT;
Index created.

II. Change the LIKE-clause to an CONTAINS-clause – WHERE CONTAINS(TNAME, ‘%SEG%’) > 0;

SQL> set autotrace off
 SQL> select TNAME from t WHERE CONTAINS(TNAME, '%SEG%')>0;

TNAME
------------------------------
SEG$
V_$SORT_SEGMENT
V_$SEGMENT_STATISTICS
V_$SEGSTAT_NAME
V_$SEGSTAT
GV_$SORT_SEGMENT
GV_$SEGMENT_STATISTICS
GV_$SEGSTAT_NAME
GV_$SEGSTAT
DBA_ROLLBACK_SEGS
SYSSEGOBJ

TNAME
------------------------------
SYS_USER_SEGS
SYS_DBA_SEGS
USER_SEGMENTS
DBA_SEGMENTS
DBA_SEGMENTS_OLD
WRI$_SEGADV_OBJLIST
WRI$_SEGADV_CNTRLTAB
DBA_AUTO_SEGADV_CTL
DBA_AUTO_SEGADV_SUMMARY
WRH$_SEG_STAT
WRH$_SEG_STAT_BL

TNAME
------------------------------
WRH$_SEG_STAT_OBJ
DBA_HIST_SEG_STAT
DBA_HIST_SEG_STAT_OBJ

III. Look at the SQL plan:

SQL> set autotrace traceonly explain

SQL> select TNAME from t WHERE CONTAINS(TNAME, '%SEG%')>0;

Execution Plan
----------------------------------------------------------
Plan hash value: 3734532763

--------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     2 |    58 |     4
|   1 |  TABLE ACCESS BY INDEX ROWID| T                  |     2 |    58 |     4
|*  2 |   DOMAIN INDEX              | XYZ_ORACLE_TXT_IDX |       |       |     4
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("TNAME",'%SEG%')>0)

Note
-----
   - dynamic sampling used for this statement

Better!

Case 2: Tuning the LIKE-clause by using reverse key indexes

Another trick for indexing queries with a leading wildcard character (like ‘%SON’) is to create a REVERSE index – and then programmatically reverse the LIKE-clause to read “LIKE ‘NOS%’”, effectively indexing on the other side of the text, clumsy, yet effective.

Steps:

1. Create reverse key index on columns that will be searched. For example, create a reverse key index on Cust_Name of the customer table:

CREATE INDEX Cust_Name_reverese_idx
ON customer(Cust_Name)
REVERSE;

2. Programmatically reverse the SQL LIKE-clause to read ’saliV%’:

Existing Query:

SELECT *
FROM customer
WHERE Cust_Name LIKE '%Vilas'

New Query:

SELECT *
FROM customer
WHERE Cust_Name LIKE 'saliV%';

I modified a bit after reading http://www.orafaq.com/node/1918

What is the difference between a soft parse and a hard parse ?

When you submit an SQL statement for processing, it passes through a number of stages before your server process finally gets to execute it and give you back the results.

First your command is checked for syntax errors – basically, is the command correctly formed ?

If the syntax check is passed, it is checked for semantic errors – things like do all the objects required exist, does the user have appropriate privileges etc.

The next stage is to pass the command thorough a hashing function to obtain a hash value for the statement. This hash value is used as a lookup in the library cache to see if this command has been used before. If the hash value is found in the cache, then the SQL for that command is compared with yours to see if they are identical (down to letter case etc). If so, the next step is to ensure that all objects referenced in the cached command are the same objects referenced in your new one. If so, then the parse tree and execution plan for the existing command can be used for your as well and the optimiser stage is missed out This is a soft parse.

Assuming that there is not an identical SQL command in the library cache, then your command will be passed on to the Optimiser to work out the best plan of attack to get back the data you want. The optimiser builds a parse tree which will involve processing some recursive SQL. Once a parse tree has been build, an execution plan is created from it. This constitutes a hard parse.

Once an execution plan has been created, or an existing one re-used, the command can be executed.

In summary,

1. Perform syntax check
2. Perform semantic check
3. Perform hash function
4. Perform library cache lookup
5. If hash value found then
6. …..If command is identical to existing one in cache then
7. ……….If the objects referenced in the cached command are the same as the ones in the new command then
8. ……………This is a soft parse, go to step 11
9. This is a hard parse, build parse tree
10. Build execution plan
11. Execute plan.

The building of the parse tree and execution plan are the two most expensive parts of the parsing, and if these have to be done, then we have a hard parse.

From http://www.jlcomp.demon.co.uk/faq/soft_hard_parse.html

Which one is faster, IN or EXISTS? When should I use IN and EXISTS?

As a general rule of thumb, Oracle processes the IN operator faster than the EXISTS operator. But as with all rules of thumb, you should verify the results for your specific query in your specific environment.

EXISTS vs. IN

The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences. For example:

PRODUCT – 1000 rows
ITEMS – 1000 rows

(A)
SELECT p.product_id
FROM   products p
WHERE  p.item_no IN (SELECT i.item_no
                     FROM   items i);

(B)
SELECT p.product_id
FROM   products p
WHERE  EXISTS (SELECT '1'
               FROM   items i
               WHERE  i.item_no = p.item_no)

For query A, all rows in ITEMS will be read for every row in PRODUCTS. The effect will be 1,000,000 rows read from items. In the case of query B, a maximum of 1 row from ITEMS will be read for each row of PRODUCTS, thus reducing the processing overhead of the statement.

Rule of thumb:

• If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.
• If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.