Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Webcast for Latin American Oracle User Group

Thank you all those attended the websession today for LAOUG. It was a great honor to be the first speaker in the virtual conference series. Many thanks for inviting me, Francisco. I'm also looking forward to the next three I am supposed to deliver.

For those who attended, you may want to download the scripts at www.proligence.com/sec_scripts.zip

Little Things Doth Crabby Make – Part XII. Please, DD, Lose My Data! I Didn’t Need That Other 4K Anyway.

It’s been a while since the last installment in my Little Things Doth Crabby Make series. The full series can be found here. So, what’s made me crabby this time? Well, when a Linux utility returns a success code to me I expect that to mean it did what I told it to do. Well… What’s [...]

Where are the logs for the SCAN listeners?

Quick post and note to self. Where are the SCAN listener log files? A little bit of troubleshooting was required, but I guess I could have read the manuals too. In the end it turned out to be quite simple!

First of all, I needed to find out where on my four node cluster I had a SCAN listener. This is done quite easily by asking Clusterware:

[grid@rac11gr2node2 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node rac11gr2node2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node rac11gr2node4
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node rac11gr2node3

I was initially on the first node, so had to ssh to the second. From there on I thought that the proc file system might have the answer. I needed to get the PID of the SCAN listener first:

[grid@rac11gr2node2 ~]$ ps -ef | grep -i scan
grid      4738     1  0 Jun03 ?        00:00:13 /u01/app/grid/product/11.2.0/crs/bin/tnslsnr LISTENER_SCAN1 -inherit
grid     24694 24147  0 20:55 pts/0    00:00:00 grep -i scan

Now /proc/4738/fd lists all the open file descriptors used by the SCAN listener. Surely the log.xml file would be there somewhere:

[grid@rac11gr2node2 ~]$ ll /proc/4738/fd
total 0
lrwx------ 1 grid oinstall 64 Jun 16 20:46 0 -> /dev/null
lrwx------ 1 grid oinstall 64 Jun 16 20:46 1 -> /dev/null
lrwx------ 1 grid oinstall 64 Jun 16 20:46 10 -> socket:[20906]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 11 -> socket:[20908]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 12 -> socket:[20927]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 13 -> socket:[20957]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 14 -> socket:[20958]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 15 -> socket:[22991]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 16 -> socket:[10712179]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 17 -> socket:[10173760]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 18 -> socket:[10176036]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 19 -> socket:[9106216]
lrwx------ 1 grid oinstall 64 Jun 16 20:46 2 -> /dev/null
lr-x------ 1 grid oinstall 64 Jun 16 20:46 3 -> /u01/app/grid/product/11.2.0/crs/rdbms/mesg/diaus.msb
lr-x------ 1 grid oinstall 64 Jun 16 20:46 4 -> /proc/4738/fd
lr-x------ 1 grid oinstall 64 Jun 16 20:46 5 -> /u01/app/grid/product/11.2.0/crs/network/mesg/nlus.msb
lr-x------ 1 grid oinstall 64 Jun 16 20:46 6 -> pipe:[20893]
lr-x------ 1 grid oinstall 64 Jun 16 20:46 7 -> /u01/app/grid/product/11.2.0/crs/network/mesg/tnsus.msb
lrwx------ 1 grid oinstall 64 Jun 16 20:46 8 -> socket:[20904]
l-wx------ 1 grid oinstall 64 Jun 16 20:46 9 -> pipe:[20894]

Well maybe not. Next option is to query the listener itself via lsnrctl. Nothing easier that that:

LSNRCTL> set current_listener LISTENER_SCAN1
Current Listener is LISTENER_SCAN1
LSNRCTL> show log_file
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
LISTENER_SCAN1 parameter "log_file" set to /u01/app/grid/product/11.2.0/crs/log/diag/tnslsnr/rac11gr2node2/listener_scan1/alert/log.xml
The command completed successfully
LSNRCTL>

Aha, it uses the ADR as well. So back there, change the base and query the file:

[grid@rac11gr2node2 ~]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Jun 16 20:58:17 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> set base /u01/app/grid/product/11.2.0/crs/log
adrci> show home
ADR Homes:
diag/tnslsnr/rac11gr2node2/listener_scan1
diag/tnslsnr/rac11gr2node2/listener_scan3
diag/tnslsnr/rac11gr2node2/listener_scan2
adrci> set home diag/tnslsnr/rac11gr2node2/listener_scan1
adrci> show alert -tail
2010-06-16 20:58:25.021000 +01:00
16-JUN-2010 20:58:25 * service_update * polstdby_1 * 0
2010-06-16 20:58:27.441000 +01:00
16-JUN-2010 20:58:27 * service_update * poldb_2 * 0
2010-06-16 20:58:30.444000 +01:00
16-JUN-2010 20:58:30 * service_update * poldb_2 * 0
16-JUN-2010 20:58:30 * service_update * poldb_1 * 0
2010-06-16 20:58:33.442000 +01:00
16-JUN-2010 20:58:33 * service_update * poldb_2 * 0
2010-06-16 20:58:35.784000 +01:00
16-JUN-2010 20:58:35 * service_update * prod1 * 0
16-JUN-2010 20:58:36 * service_update * poldb_2 * 0
16-JUN-2010 20:58:36 * service_update * poldb_1 * 0
2010-06-16 20:58:39.546000 +01:00
16-JUN-2010 20:58:39 * service_update * poldb_2 * 0
16-JUN-2010 20:58:39 * service_update * poldb_1 * 0
2010-06-16 20:58:42.574000 +01:00
16-JUN-2010 20:58:42 * service_update * poldb_2 * 0
2010-06-16 20:58:45.574000 +01:00
16-JUN-2010 20:58:45 * service_update * poldb_2 * 0
2010-06-16 20:58:48.576000 +01:00
16-JUN-2010 20:58:48 * service_update * poldb_2 * 0
16-JUN-2010 20:58:48 * service_update * poldb_1 * 0
2010-06-16 20:58:51.575000 +01:00
16-JUN-2010 20:58:51 * service_update * poldb_2 * 0
16-JUN-2010 20:58:51 * service_update * poldb_1 * 0
2010-06-16 20:58:54.578000 +01:00
16-JUN-2010 20:58:54 * service_update * poldb_2 * 0

Job done.

Do It Yourself Exadata Performance! Really? Part III.

I just noticed that the vote count for my Oracle Mix Suggest-A-Session is up to 92! I’m flattered and thanks for the votes, folks. I promise this is the last post on this thread! The session I aim to present has some content that I delivered to our EMEA Sales Consultants during an event we [...]

Data Structures: Knuth Got It Wrong

Data structures and how data is grouped, stored and manipulated seems to be a lost art in computer programming yet it's the cornerstone to programming.
Poul-Henning Kamp's article is a refreshingly clear perspective on performance and datastructures, check it out.
Reminds me somewhat of the Oracle notion of how deep is your index
As you can see the traditional binary heap, on left, is deeper than the new structure, the "b-heap", on the right. The "b-heap" is slightly computationally more expensive but if less pages are visited avoiding visiting any paged out pages, the "b-heap" will perform much better.
The article goes into detail quite normal situations where the new structure performs an order of magnitude better than the traditional binary heap.
(thanks to Mark Bobak for sharing this link with me)

crsctl status resource – state details are really useful

A very short post about a cool new feature I noticed today. RAC 11.2 has moved a lot of commands previously having their own syntax into crsctl. One of the cool new things is the fact that crsctl status resource -t (“tabular”) reports state details. Here I could see that my lab environment had a stuck archiver. Other state details include information about the cluster time synchronisation daemon ctss, or ASM instances. Have a look at my 4 node cluster:

[oracle@rac11gr2node2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.LISTENER.lsnr
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.OCRVOTE.dg
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.asm
 ONLINE  ONLINE       rac11gr2node1            Started             
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  INTERMEDIATE rac11gr2node4                                
ora.eons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.gsd
 OFFLINE OFFLINE      rac11gr2node1                                
 OFFLINE OFFLINE      rac11gr2node2                                
 OFFLINE OFFLINE      rac11gr2node3                                
 OFFLINE OFFLINE      rac11gr2node4                                
ora.net1.network
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.ons
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
ora.registry.acfs
 ONLINE  ONLINE       rac11gr2node1                                
 ONLINE  ONLINE       rac11gr2node2                                
 ONLINE  ONLINE       rac11gr2node3                                
 ONLINE  ONLINE       rac11gr2node4                                
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.LISTENER_SCAN2.lsnr
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.LISTENER_SCAN3.lsnr
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.oc4j
 1        OFFLINE OFFLINE                                                   
ora.poldb.db
 1        ONLINE  INTERMEDIATE rac11gr2node3            Stuck Archiver      
 2        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
ora.poldb.drcp.svc
 1        ONLINE  ONLINE       rac11gr2node3                                
 2        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.poldb.nondrcp.svc
 1        ONLINE  INTERMEDIATE rac11gr2node4                                
ora.polstdby.db
 1        ONLINE  INTERMEDIATE rac11gr2node4            Stuck Archiver      
 2        OFFLINE OFFLINE                                                   
ora.prod.batchserv.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.prod.db
 1        ONLINE  ONLINE       rac11gr2node1            Open                
 2        ONLINE  ONLINE       rac11gr2node2                                
ora.prod.reporting.svc
 1        ONLINE  ONLINE       rac11gr2node2                                
 2        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node1.vip
 1        ONLINE  ONLINE       rac11gr2node1                                
ora.rac11gr2node2.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.rac11gr2node3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                
ora.rac11gr2node4.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan1.vip
 1        ONLINE  ONLINE       rac11gr2node2                                
ora.scan2.vip
 1        ONLINE  ONLINE       rac11gr2node4                                
ora.scan3.vip
 1        ONLINE  ONLINE       rac11gr2node3                                

Nice!

Exadata Offload - The Secret Sauce

The “Secret Sauce” for Exadata is it’s ability to offload processing to the storage tier. Offloading means that the storage servers can apply predicate filters at the storage layer, instead of shipping every possible block back to the database server(s). Another thing that happens with offloading is that the volume of data returned can be further reduced by column projection (i.e. if you only select 1 column from a 100 column table, there is no need to return the other 99 columns). Offloading is geared to long running queries that access a large amount of data. Offloading only works if the Oracle decides to use it’s direct path read mechanism. Direct path reads have traditionally been done by parallel query slaves but can also be done by serial queries. In fact, as of 11g, Oracle has changed the decision making process resulting in more aggressive use of serial direct path reads. I’ve seen this feature described as “serial direct path reads” and “adaptive direct path reads”.

I’ll digress here a bit to discuss this feature since direct path reads are critical to Exadata Offloading. Direct path reads do not load blocks into Oracle’s buffer cache. Instead, the data is returned directly to the PGA of the process requesting the data. This means that the data does not have to be in Oracle block format. That means no 8K block that is only partially filled, that may only have a record or two that you’re interested in, containing every column including ones you don’t want, and with additional header information - needs to be shipped back up from the storage layer. Instead, a much more compact result set containing only the columns requested and hopefully only the rows you need are returned. As I said, direct path reads are traditionally used by parallel query slaves. They are also used in a few other instances such as LOB access and sorts that spill over into TEMP. So the ability to use direct path reads is very important to the Exadata platform and thus the changes to the make them more attractive in 11g. Here are a few links to info on the subject of serial direct path reads:

  1. Doug Burns has a good post on 11g serial direct path reads.
  2. Alex Fatkulin has a very good post on some of the factors controlling adaptive direct path reads.
  3. There is a note on MOS (793845.1) on changes in 11g in the heuristics to choose between direct path reads or reads through the buffer cache.
  4. You may also find MOS note (50415.1) on misleading nature of “direct path read” wait events of interest.

Also be aware that direct path reads are only available for full scans (tables or indexes). So any statement that uses an index to get to a row in a table will not use this mechanism. Also keep in mind that direct path requires extra processing to ensure that all blocks on disk are current - (i.e. a check point), so frequently modified tables will suffer some overhead before direct path reads can be initiated.

I must say that I think the changes to the heuristics in 11g may be a little on the aggressive side for non-Exadata platforms (the changes may well be driven by Exadata). And by the way, serial direct path reads are not always faster than the normal reads that go through the buffer cache. Dion Cho has a good post on a performance problem due to serial direct path reads kicking in on one node of an 11g RAC environment (not Exadata). The node doing the direct path reads was running the query much slower than the node using the normal buffer cache reads. He also has a post on turning off serial direct path reads.

But enough about the direct path reads stuff, on to the Offloading. One of the first things I wanted to know when I got my first look at a system running on Exadata was whether a particular query was eligible for offloading and if so, how much of the expected i/o was saved by the feature. So of course I wrote a little script to show me that. Turns out there is plenty of info in V$SQL to see what’s going on. I called the script fsx.sql (short for Find_Sql_eXadata). Here’s a little demo:

SYS@LABRAT1> @test_offload
SYS@LABRAT1> -- ran but turned off output
SYS@LABRAT1> -- select /* test full */ * from kso.skew3; 
SYS@LABRAT1> 
SYS@LABRAT1> select /* test count */ count(col1) from kso.skew3;
 
COUNT(COL1)
-----------
  384000036
 
1 row selected.
 
Elapsed: 00:00:24.04
SYS@LABRAT1> select /* test count(rll) */ count(pk_col), count(col1), count(col2), count(col3), count(col4) from kso.skew3;
 
COUNT(PK_COL) COUNT(COL1) COUNT(COL2) COUNT(COL3) COUNT(COL4)
------------- ----------- ----------- ----------- -----------
    384000048   384000036   384000036   384000036   384000036
 
1 row selected.
 
Elapsed: 00:00:39.09
SYS@LABRAT1> select /* test max */ max(col1) from kso.skew3;
 
 MAX(COL1)
----------
   1000000
 
1 row selected.
 
Elapsed: 00:00:24.51
SYS@LABRAT1> select /* test max all */ max(pk_col), max(col1), max(col2), max(col3), max(col4) from kso.skew3;
 
MAX(PK_COL)  MAX(COL1) MAX(COL2)                      MAX(COL3) M
----------- ---------- ------------------------------ --------- -
 1.0000E+12    1000000 asddsadasd                     01-JAN-09 Y
 
1 row selected.
 
Elapsed: 00:00:57.32
SYS@LABRAT1> select /* test avg */ avg(col1) from kso.skew3;
 
 AVG(COL1)
----------
495000.194
 
1 row selected.
 
Elapsed: 00:00:30.00
SYS@LABRAT1> select /* test avg filter */ avg(col1) from kso.skew3 where col1 between 2 and 10000;
 
 AVG(COL1)
----------
 
 
1 row selected.
 
Elapsed: 00:00:13.97
SYS@LABRAT1> select /* test index */ avg(pk_col) from kso.skew where col1 = 136133;
 
AVG(PK_COL)
-----------
   15636133
 
1 row selected.
 
Elapsed: 00:00:00.00
SYS@LABRAT1> set echo off
SYS@LABRAT1> set timing off
SYS@LABRAT1> @fsx
Enter value for sql_text: % test %
Enter value for sql_id: 
Enter value for inst_id: 
 
 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO    AVG_PIO AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------------ ---------- ------ ----------- ---------- ----------------------------------------
    1 1767x3dw3m8pa      0 2684249835          1         57.31    1,956,226  1,956,219      0 Yes               9.35 select /* test max all */ max(pk_col), m
    1 1wp72w4c5d449      0 2684249835          1         29.98    1,956,226  1,956,219      0 Yes              71.84 select /* test avg */ avg(col1) from kso
    1 46vd0hducqms8      0 1667163222          1        422.77    2,032,691  1,956,219      0 No                 .00 select /* test full */ * from kso.skew3
    1 5ry8vd8u0n0bf      0 2684249835          1         24.03    1,956,226  1,956,219      0 Yes              71.85 select /* test count */ count(col1) from
    1 6jtf4rnxu2dq7      0 2684249835          1         24.50    1,956,226  1,956,219      0 Yes              71.85 select /* test max */ max(col1) from kso
    1 8pfxs6mdf62mq      0 3723858078          1           .00           35          0      0 No                 .00 select /* test index */ avg(pk_col) from
    1 abwyftyy395gk      0 2684249835          1         13.96    1,956,226  1,956,219      0 Yes              99.99 select /* test avg filter */ avg(col1) f
    1 fxpkhxt4jwqqm      0 2684249835          1         39.08    1,956,226  1,956,219      0 Yes               9.35 select /* test count(rll) */ count(pk_co
 
8 rows selected.

So I did several queries. Some were off-loadable and some were not. Then I ran the script to see what how the statements had been handled. There are a couple of things to note here:

  1. I used IO_CELL_OFFLOAD_ELIGIBLE_BYTES in V$SQL to indicate whether a statement can be offloaded or not.
    (i.e. decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,’No’,'Yes’) Offloadable) Note, if you run this script on a non-Exadata 11gR2 database, all the SQL statements should show up as Offloadable=NO.
  2. All the statements using the aggregate functions (max,count,avg…) are marked as off-loadable even though the functions themselves are not off-loadable. I believe that projection accounts for reduced IO (limited to columns requested) and the marking as off-loadable. Note that the statement commented “test max all” shows almost no reduction in IO (it applies the max function to each column). I’m not yet sure why there was a slight reduction in I/O even with all columns listed. And by the way, V$SQLFN_METADATA has a list of all valid functions and whether they are off-loadable or not (none of the aggregate functions are).
  3. Statements selecting all columns with no where clause are not off-loadable (no filter predicate or column projection).
  4. Statements using index range scans are not off-loadable (the optimizer has to pick a plan with a full scan). Check out the statement commented “test index” for an example.

It is possible to turn offloading off via a parameter, CELL_OFFLOAD_PROCESSING, or via a hint. Here’s a bit of the Oracle Exadata Storage Server Software User’s Guide:

The CELL_OFFLOAD_PROCESSING initialization parameter enables SQL processing offload to Exadata Cell. When the value of the parameter is set to TRUE, predicate evaluation can be offloaded to cells. The default value of the parameter is TRUE. If the parameter is set to FALSE at the session or system level, then the database performs all the predicate evaluation with cells serving blocks. You can set CELL_OFFLOAD_ PROCESSING dynamically with the SQL ALTER SYSTEM or ALTER SESSION command. For example:
SQL> ALTER SESSION SET CELL_OFFLOAD_PROCESSING = TRUE;
The CELL_OFFLOAD_PROCESSING parameter can also be set with the OPT_PARAM optimizer hint to enable or disable predicate filtering for a specific SQL command. For example:
– to disable cell_offload_processing for a SQL command SELECT /*+ OPT_PARAM(’cell_offload_processing’ ‘false’) */ COUNT(*) FROM EMPLOYEES;
– to enable cell_offload_processing for a SQL command SELECT /*+ OPT_PARAM(’cell_offload_processing’ ‘true’) */ COUNT(*) FROM EMPLOYEES;

And a quick example:

SYS@LABRAT1> select /* test avg filter hint */
  2  /*+ OPT_PARAM('cell_offload_processing' 'false') */
  3  avg(col1) from kso.skew3 where col1 between 2 and 10000;
 
 AVG(COL1)
----------
 
 
1 row selected.
 
SYS@LABRAT1> @fsx
Enter value for sql_text: % test avg filter %
Enter value for sql_id: 
Enter value for inst_id: 
 
 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO    AVG_PIO AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------------ ---------- ------ ----------- ---------- ----------------------------------------
    1 0nb4tjmjuq67t      0 2684249835          1        104.89    1,956,227  1,956,219      0 No                 .00 select /* test avg filter hint */ /*+ OP
    1 abwyftyy395gk      0 2684249835          1         13.96    1,956,226  1,956,219      0 Yes              99.99 select /* test avg filter */ avg(col1) f
 
2 rows selected.

So as you can see, the hint disabled offloading and resulted in the statement taking about 10X the elapsed time of the offloaded version.

Now for one more example to whet your appetite for things to come:

SYS@LABRAT1> select count(*) from kso.skew3 where col1 is null;
 
  COUNT(*)
----------
        12
 
1 row selected.
 
Elapsed: 00:00:00.08
SYS@LABRAT1> @fsx
Enter value for sql_text: %col1 is null%
Enter value for sql_id: 
Enter value for inst_id: 
 
 INST SQL_ID         CHILD  PLAN_HASH      EXECS     AVG_ETIME      AVG_LIO    AVG_PIO AVG_PX OFFLOADABLE IO_SAVED_% SQL_TEXT
----- ------------- ------ ---------- ---------- ------------- ------------ ---------- ------ ----------- ---------- ----------------------------------------
    1 0u1q4b7puqz6g      0 2684249835          1           .08    1,956,226  1,956,219      0 Yes             100.00 select count(*) from kso.skew3 where col
 
1 row selected.
 
Elapsed: 00:00:00.09
SYS@LABRAT1> @dplan
Enter value for sql_id: 0u1q4b7puqz6g                               
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0u1q4b7puqz6g, child number 0
-------------------------------------
select count(*) from kso.skew3 where col1 is null
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   533K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |    12 |    60 |   533K  (1)| 01:46:38 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1" IS NULL)
       filter("COL1" IS NULL)
 
 
20 rows selected.
 
Elapsed: 00:00:00.02

Yes that’s a full scan on a 384M row table that occupies about 2M blocks. And it took 8/100’s of a second. That’s 80 milliseconds - roughly the amount of time necessary to do 10-15 single block disk i/o’s.

Wow! Can you say storage index? More on that in the next installment!

Display scheduler class for a process in linux

The ps command in the ways I use it most (ps -ef and ps auxwww) doesn’t display the scheduling class for a process. Oracle have cunningly released a patchset to update Grid Infrastructure that changes the scheduling class from the VKTM and LMSn ASM processes to “Timeshare” instead of Realtime.

So far so good, but I had no idea how to display the scheduling class of a process so some man page reading and Internet research were in order. After some digging around I found out that using the BSD command line syntax combined with the “–format” option does the trick. The difficult bit was in figuring out which format identifiers to use. All the information ps can get about a process are recorded in /proc/pid/stat. Parsing this with a keen eye however proves difficult due to the sheer number of fields in the file. So back to using ps (1).

Here’s the example. Before applying the workaround to the patch, Oracle ASM’s VKTM (virtual keeper of time) and LMSn (global cache services process) run with TS priority:

[oracle@rac11gr2node2 ~]$ ps ax --format uname,pid,ppid,tty,cmd,cls,pri,rtprio \
>| egrep "(vktm|lms)" | grep asm
grid      4296     1 ?        asm_vktm_+ASM2               TS  24      -
grid      4318     1 ?        asm_lms0_+ASM2               TS  24      -

After applying the workaround the scheduling class changed:

[oracle@rac11gr2node1 ~]$ ps ax --format uname,pid,ppid,tty,cmd,cls,pri,rtprio | egrep "(vktm|lms)" | grep asm
grid      2352     1 ?        asm_vktm_+ASM1               RR  41      1
grid      2374     1 ?        asm_lms0_+ASM1               RR  41      1

Notice how the cls field changed, and also that the rtprio is now populated. I have learned something new today.

New Oracle Security presentation available

I was in Holland the week before last on June 2nd, to speak at the Logica Guro4Pro event at their offices close to Den Haag. This was a nice event with some really great questions and discussions during my talk....[Read More]

Posted by Pete On 15/06/10 At 11:07 AM

Jonathan Lewis / Kyle Hailey Webinar Replay

Thanks to everyone who joined for the webinar with Jonathan Lewis last Thursday. I've been getting a number of inquiries asking if the webinar was recorded. The webinar was recorded and you can view it at
http://www.embarcadero.com/master-sql-tuners-oracle-lewis-hailey
Here is a snippet from my part using DB Optimizer

It's a bit fuzzy on youtube. The real presentation is high quality.
Here is a clear image from DB Optimizer: