How to check long running operations in Oracle

Long running sessions in oracle indicates the operations that run for longer than 6 seconds (in absolute time). They include many backup and recovery functions, statistics gathering, query execution etc. Based on newer oracle version many operations are added gradually.

To monitor long running operations in oracle the following two conditions must met.
1) Set the initialization parameter TIMED_STATISTICS or SQL_TRACE parameters to true.
2) Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.

After you have met above two conditions you can easily monitor your long running operations by querying V$SESSION_LONGOPS view.

SQL> desc V$SESSION_LONGOPS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 OPNAME                                             VARCHAR2(64)
 TARGET                                             VARCHAR2(64)
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 TIMESTAMP                                          DATE
 TIME_REMAINING                                     NUMBER
 ELAPSED_SECONDS                                    NUMBER
 CONTEXT                                            NUMBER
 MESSAGE                                            VARCHAR2(512)
 USERNAME                                           VARCHAR2(30)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_PLAN_HASH_VALUE                                NUMBER
 SQL_EXEC_START                                     DATE
 SQL_EXEC_ID                                        NUMBER
 SQL_PLAN_LINE_ID                                   NUMBER
 SQL_PLAN_OPERATION                                 VARCHAR2(30)
 SQL_PLAN_OPTIONS                                   VARCHAR2(30)
 QCSID                                              NUMBER

From above view,
- SOFAR is units of work done so far.
- TIME_REMAINING is estimate (in seconds) of time remaining for the operation to complete

In order to monitor how much percentage of an operation is completed query as,

SQL> SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops;

If you have long running operations in your database then above query will return rows.

You can query long running operations based on they have started like,

SQL> select opname, target, sofar, totalwork,
      units, elapsed_seconds, message
     from v$session_longops order by start_time desc;

Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html

Measure website performance using google webmaster tools

You might think about your website performance. Website performance indicates web page loading time for your site. In other words it is speed of your site, how fast is your web site. In fact the goal of any website is minimize the loading time for every page because lower loading time leads to increased user retention and activity, higher revenue and lower costs.

In this post I will discuss how we can measure the speed of individual webpage of our website and page speed tool using google webmaster tools.

Step 01: The first thing you need is to setup google webmaster tools for your site and verify your site. In the post Setup Google Webmaster Tools it is discussed about how we can setup webmaster tools for a blog or for a site.

Step 02:
- Log in to your webmaster tools.
- List of verified site you have will appeared. If you have many sites click on the sites that you want to measure page speed.

Step 03: You will enter into the Dashboard. Click on Labs from left menu and select Site Performance. Left menu will look like below.

Step 04: In the right side, you will see various suggestions like average load time, page speed suggestion along with performance overview.
Following is an example for my blog.

Performance overview

On average, pages in your site take 6.4 seconds to load (updated on Dec 31, 2009). This is slower than 82% of sites. The chart below shows how your site's average page load time has changed over the last few months. For your reference, it also shows the 20th percentile value across all sites, separating slow and fast load times.

Example pages

These are some example pages from your site and the time that they take to load in a browser (in seconds).

URL	Load time
/	19.4

Page Speed suggestions

These are some example pages from your site and some suggestions on how to optimize them, based on the Page Speed tool.

URL

/2009/01/ora-12557-tnsprotocol-adapter-not.htmlDetails: Save up to 79.4 KB, 5 requests, 6 DNS lookups Enable gzip compression Compressing the following resources with gzip could reduce their transfer size by 79.4 KB: http://www.blogger.com/static/v1/v-app/scripts/4095335807-common.js (16.1 KB) http://www.blogger.com/static/v1/widgets/995923812-widgets.js (41.6 KB) http://www.blogger.com/static/v1/widgets/3796602922-widget_css_bundle.css (7.65 KB) http://www.google-analytics.com/ga.js (13.7 KB) http://www.blogger.com/static/v1/v-css/3727950723-blog_controls.css (425 bytes) Combine external JavaScript There are 2 JavaScript files served from www.blogger.com. They should be combined into as few files as possible: http://www.blogger.com/static/v1/v-app/scripts/4095335807-common.js http://www.blogger.com/static/v1/widgets/995923812-widgets.js Minimize DNS lookups The domains of the following URLs only serve one resource each. If possible, avoid the extra DNS lookups by serving these resources from existing domains: http://1.bp.blogspot.com/_ZUvaE3CpMkI/SaphR7EJh3I/AAAAAAAAAVk/tvRutgicDl0/S45/C%3A%5CDocuments%20and%20Settings%5CAdministrator%5CMy%20Documents%5CMy%20Pictures%5Cpersonal%5CMyself.jpg&imgmax=640 http://c42.statcounter.com/t.php?sc_project=3579537&resolution=1024&h=1024&camefrom=&u=http%3A//arjudba.blogspot.com/2009/01/ora-12557-tnsprotocol-adapter-not.html&t=Oracle%20in%20World%3A%20ORA-12557%3A%20TNS%3Aprotocol%20adapter%20not%20loadable&java=1&security=7ddb1688&sc_random=0.278324234&sc_snum=1&invisible=1 http://img2.blogblog.com/img/b16-rounded.gif http://pagead2.googlesyndication.com/pagead/show_ads.js http://www.google-analytics.com/ga.js http://www.statcounter.com/counter/counter_xhtml.js Serve resources from a consistent URL The following resources have identical contents, but are served from different URLs. Serve these resources from a consistent URL to save 1 bytes and 1 requests: http://www.blogger.com/dyn-css/authorization.css?targetBlogID=8616954574848172664&zx=c45d0811-5453-4a50-9d33-e6a2fdce931b http://www.blogger.com/dyn-css/authorization.css?targetBlogID=8616954574848172664&zx=0aed3f77-2e10-4bb2-b007-4ab77989c2ee Combine external CSS There are 4 CSS files served from www.blogger.com. They should be combined into as few files as possible: http://www.blogger.com/dyn-css/authorization.css?targetBlogID=8616954574848172664&zx=c45d0811-5453-4a50-9d33-e6a2fdce931b http://www.blogger.com/static/v1/widgets/3796602922-widget_css_bundle.css http://www.blogger.com/static/v1/v-css/3727950723-blog_controls.css http://www.blogger.com/dyn-css/authorization.css?targetBlogID=8616954574848172664&zx=0aed3f77-2e10-4bb2-b007-4ab77989c2ee

Step 05: The next step is to install page speed tool. You can install page speed tool from the window Page Speed Tool. To make page speed tool work you also need to install firebug. You can install firebug from the link https://addons.mozilla.org/en-US/firefox/addons/versions/1843. After you install page speed tool and firebug open the site/link in browser for which you want to measure page speed.

Step 06: Click on the firefox Tools Menu, select Firebug from the dropdown menu and then select "Open Firebug in New Window". Following image is a snapshot from my windows.

Step 07: You will see firebug is loaded in a new window. Select "Page Speed" from the tabs and click "Analyze Performance".

You will see Overall performance summary for your webpage. Following is the performance for my site that is suggested by page speed tool.
1) [Score: 48%] Minimize DNS lookups

2) [Score: 53.8%] Leverage browser caching

3) [Score: 76.9%] Leverage proxy caching

4) [Score: 70.2%] Minify CSS

5) [Score: 79%] Optimize the order of styles and scripts

6) [Score: 67%] Avoid CSS expressions

7) [Score: 75.9%] Remove unused CSS

8) [Score: 78.4%] Serve static content from a cookieless domain

9) [Score: 71%] Use efficient CSS selectors

10) [Score: 99.8%] Enable gzip compression

11) [Score: 89%] Combine external JavaScript

12) [Score: 87.7%] Minify JavaScript

13) [Score: 95.5%] Optimize images

14) [Score: 100%] Combine external CSS

15) [Score: 90%] Specify image dimensions

16) [Score: 100%] Minimize cookie size

17) [Score: 100%] Serve resources from a consistent URL

18) [Score: 100%] Minimize redirects

19) [Score: 100%] Put CSS in the document head

20) [disabled] Defer loading of JavaScript

21) [n/a] Parallelize downloads across hostnames

Based on the score and the details recommendation you will take further decision to speed up your site. The target is to make the score 100%.
Related Documents
http://arjudba.blogspot.com/2009/06/different-types-of-web-hosting-services.html
http://arjudba.blogspot.com/2009/07/how-to-add-site-to-yahoo-directory.html
http://arjudba.blogspot.com/2009/07/how-to-add-site-to-google-directory.html
http://arjudba.blogspot.com/2009/12/how-to-know-when-googlebot-last-crawled.html
http://arjudba.blogspot.com/2009/12/how-to-add-different-meta-tags-to.html
http://arjudba.blogspot.com/2009/12/how-to-disable-or-remove-blogger.html
http://arjudba.blogspot.com/2009/12/scrolling-text-effect-html-code-with.html
http://arjudba.blogspot.com/2009/12/how-to-post-larger-images-in-blogger.html
http://arjudba.blogspot.com/2009/12/how-to-transfer-or-sell-blogger.html
http://arjudba.blogspot.com/2009/12/how-to-add-email-subscription-form-to.html
http://arjudba.blogspot.com/2009/12/how-to-add-favicon-to-blogger-blogspot.html
http://arjudba.blogspot.com/2009/12/how-to-add-tag-cloud-category-to.html
http://arjudba.blogspot.com/2009/12/how-to-add-auto-read-more-feature-with.html
http://arjudba.blogspot.com/2010/01/how-to-stop-comment-spam-on-your-blog.html
http://arjudba.blogspot.com/2010/01/url-not-allowed-this-url-is-not-allowed.html

Does oversize of datatype VARCHAR2 causes performance problem

From the beginning of learning Oracle SQL you have possibly heard that in case of VARCHAR2 datatype it allocates space exactly what it needs. So if you allocates 4000 bytes of VARCHAR2 data type and database needs 10 bytes only then exactly 10 bytes are allocated.

That is, in case of VARCHAR2(4000) and VARCHAR2(16) columns, if we store less then 16 bytes data in these two columns then same amount of space will be allocated, and performance should be the same. But, have you ever tested it? I got a funny example http://hrivera99.blogspot.com/2008/05/why-is-varchar2-oversizing-bad.html here. There it is said performance problem but in reality there is not. In the example it is shown problem in physical reads but I don't agree with the example. In fact in the first example it is cached data and hence physical reads is reduced.

In the following section I simulate same example and see no performance differences. However there may rise, http://arjudba.blogspot.com/2008/09/ora-01450-maximum-key-length-3215.html while creating index in case of bigger VARCHAR2 length.

The most misleading example can be created by omitting
"
ALTER TABLESPACE EXAMPLE OFFLINE;

ALTER TABLESPACE EXAMPLE ONLINE;"

If you omit this step you may get different result as data become cached. And you need to take tablespace offline in order to get most accurate result as offlining a tablespace uncache of corresponding tablespace data.

Step 1)Create varchar2_length_test table with VARCHAR2(4000) and insert data into it.

SQL> create table varchar2_length_test(
2  ID                  NUMBER,
3  COL2        VARCHAR2(4000),
4  COL3        VARCHAR2(4000),
5  COL4        VARCHAR2(4000),
6  COL5        VARCHAR2(4000),
7  COL6        VARCHAR2(4000),
8  COL7        VARCHAR2(4000),
9  COL8        VARCHAR2(4000),
10  COL9        VARCHAR2(4000),
11  COL10        VARCHAR2(4000),
12  COL11        VARCHAR2(4000),
13  COL12        VARCHAR2(4000),
14  COL13        VARCHAR2(4000)) TABLESPACE EXAMPLE;

Table created.

SQL>
SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 2)Create varchar2_length_test_short table with VARCHAR2(16) and insert data into it.

SQL> create table varchar2_length_test_short(
2  ID                  NUMBER,
3  COL2        VARCHAR2(16),
4  COL3        VARCHAR2(16),
5  COL4        VARCHAR2(16),
6  COL5        VARCHAR2(16),
7  COL6        VARCHAR2(16),
8  COL7        VARCHAR2(16),
9  COL8        VARCHAR2(16),
10  COL9        VARCHAR2(16),
11  COL10        VARCHAR2(16),
12  COL11        VARCHAR2(16),
13  COL12        VARCHAR2(16),
14  COL13        VARCHAR2(16)) TABLESPACE EXAMPLE;

Table created.

SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test_short VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 3)Clear caching in the tablespace.

SQL> ALTER TABLESPACE EXAMPLE OFFLINE;
Tablespace altered.

SQL> ALTER TABLESPACE EXAMPLE ONLINE;
Tablespace altered.

Step 4)Enable tracing and look at statistics

SQL> SET AUTOT TRACE
SQL> select count(*) from varchar2_length_test;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 1500664439                                                                                                                

-----------------------------------------------------------------------------------                                                        
| Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time     |                                                        
-----------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT   |                      |     1 |   418   (2)| 00:00:06 |                                                        
|   1 |  SORT AGGREGATE    |                      |     1 |            |          |                                                        
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST | 88364 |   418   (2)| 00:00:06 |                                                        
-----------------------------------------------------------------------------------                                                        

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


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1980  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed                                                                                                                

SQL> ALTER TABLESPACE EXAMPLE OFFLINE;

Tablespace altered.

SQL>
SQL> ALTER TABLESPACE EXAMPLE ONLINE;

Tablespace altered.

SQL> select count(*) from varchar2_length_test_short;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 161270611                                                                                                                 

-----------------------------------------------------------------------------------------                                                  
| Id  | Operation          | Name                       | Rows  | Cost (%CPU)| Time     |                                                  
-----------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT   |                            |     1 |   418   (2)| 00:00:06 |                                                  
|   1 |  SORT AGGREGATE    |                            |     1 |            |          |                                                  
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST_SHORT |   109K|   418   (2)| 00:00:06 |                                                  
-----------------------------------------------------------------------------------------                                                  

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


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1993  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed                                                                                                                

So we see in both VARCHAR2(4000) and VARCHAR2(16) almost same consistent gets and physical reads. So oversize of varchar2 does not cause performance problem issue but lead to other problems.

Related Documents

ORA-01450: maximum key length (3215) exceeded

Numeric Datatype in Oracle with Examples

Oracle datatype internal code representation

ROWID and UROWID Datatype in Oracle

Char, Varchar2, Long etc Datatype Limits in Oracle