jump to navigation

COMMAND_TYPE Values December 10, 2009

Posted by mwidlake in internals.
Tags: , ,

Follow-up post on finding most of the COMMAND_TYPES in the data dictionary is here and thanks to Christian Antognini who’s comment led me in that direction.

Spoiler – See end for getting a full list of COMMAND_TYPE values.

If you are in the habit of looking at V$SQLAREA and V$SQL to see what is going on in the database {for those of use who occasionally step out of the “GUI DBA Tool” environment} you will see a lot of stuff in there. I tend to look for the most demanding SQL so I am not interested in PL/SQL, I just want to see the SQL executed via it.

To do this, you can ignore those entires in V$SQL/V$SQLAREA with a “COMMAND_TYPE=47” filter.

This COMMAND_TYPE is useful. 3=SELECT, the most common thing you probablly see and look at, 6=UPDATE, 7=DELETE. 50=EXPLAIN.

If you google/bing/whatever COMMAND_TYPE you get many hits that list of some of the commands, all looking very similar. But very partial lists.

So I decided to amalgamate these partial lists, verrify what COMMAND_TYPE links to what commands in the databases I have access to and publish this fuller, verified list. I quickly found some commands do not stay in the SGA after issuing, so I could not confirm them {“drop index” being one}.

I got this far:

-- mdw 10/12/09
-- check for cmd types not seen before, as lists on web are not complete
-- * = I have verified
col sql_txt form a60
select command_type,sql_id,substr(sql_text,1,60) sql_txt
from gv$sqlarea
where command_type not in (
 1 --  create table  *
,2 --  INSERT        *
,3 --  SELECT        *
,6 --  UPDATE        *
,7 --  DELETE        *
,9 --  create index  *
,11 -- ALTER INDEX   *
,26 -- LOCK table    *
,42 -- ALTER_SESSION (NOT ddl)
--two postings suggest 42 is alter session
,44 -- COMMIT
,45 -- rollback
,46 -- savepoint
,47 -- PL/SQL BLOCK' or begin/declare *
,48 -- set transaction   *
,50 -- explain           *
,62 -- analyze table     *
,90 -- set constraints   *
,170 -- call             *
,189 -- merge            *
and rownum < 20

Please feel free to add to it.

Or save yourself the bother and check out the list provided in the Oracle documentation. Not under the descriptions for V$SQL or V$SQLAREA, which would have been the obvious place Mr Larry Ellison thank you, but under V$SESSION. . Further, the fact that in the V$SESSION table the column is called just COMMAND and not COMMAND_TYPE does not assist in locating this information (don’t google COMMAND and ORACLE, you get many millions of hits…). Just click the below.

This is the full table of values and meanings.

(This is the listing for 10.2 and 11.1 is very similar).

But it does not include 189 – MERGE, so that is one up for practical testing than just reading the manual 🙂

Back to the day job…



1. Doug Burns - December 10, 2009

for those of use who occasionally step out of the “GUI DBA Tool” environment

Do people still do that?!?!


mwidlake - December 10, 2009

We all know you have sold your soul to the Picture Pixies Mr Burns. It’s so sad to see the loss of such a stalwart of the Command Line….


2. Bernard Polarski - December 14, 2009

Average download of Smenu, my 110% +1 script tool has an average download of 5 per day and this trend is sustained since several months. This surprised me as I though to be the only user on earth. Seasoned DBA don’t care about smenu, they have all their own scripts and habits and new dba are supposed to be Gui oriented, so who could be interested in these Korn shell stuff? The assumption that newbies are GUI-doomed is not entirely true.

mwidlake - December 14, 2009

I’ve got a load of SQL scripts on my web site but I have no idea if anyone has ever downloaded any of them (the web site is in dire need of some love and attention, it even has one of those awful “under construction” messages on it!).

Maybe you have a couple of scripts Bernard that do things that most of us have just never got around to writing?

3. Bernard Polarski - December 15, 2009

Okey a challange : Let’s me try to surprise you with the most improbable. Some months ago while investigating an unsual load on a Linux box, the unix sysadmin told us he could not go back more than 2 weeks with ‘sar’ history. Since Oracle takes also system stats I quickly enriched smenu with and Oracle sar.
the example below gives the sar stats for the last 10 snaps, it is stored into shortcut sts and called with param ‘-sar’

( replace workd Superior_TO and Inferior_to with mathematic signs, system eat them)

/tmp: sts -sar 10

+ sqlplus -s '/ as sysdba'
set feed off verify off lines 190 pages 66
col inst new_value inst noprint;
col id1 head 'Idle time(%)' justify c
col usr1 head 'User time(%)' justify c
col sys1 head 'Sys time(%)' justify c
col io1 head 'Io Wait time(%)' justify c
col nice1 head 'Nice time (%)' justify c
col snap_len head 'Interval| (Secs)' justify c
col num_cpus new_value p_num_cpus  head 'Number of CPU';
col a1 new_value secs noprint;
col SNAP_BEGIN format a20 head 'Snap begin' justify c
col SNAP_END format a20 head 'Snap end' justify c

   select instance_number inst from v$instance;
   SELECT value num_cpus FROM v$osstat WHERE stat_name = 'NUM_CPUS';
 prompt Negatives values correspond to Shutdown:
select  snap_id, snap_len, round(id1 /snap_len *100,2) id1,
                       round(usr1 /snap_len *100,2) usr1,
                       round(sys1 /snap_len *100,2) sys1,
                       round(io1 /snap_len *100,2) io1,
                       round(nice1 /snap_len *100,2) nice1 , snap_begin, snap_end
from (
     select  snap_id,  id1, usr1,sys1, io1, nice1, snap_begin, snap_end ,
             round( extract( day from diffs) *24*60*60*60+
                    extract( hour from diffs) *60*60+
                    extract( minute from diffs )* 60 +
                    extract( second from diffs )) snap_len                  -- this is the exact length of the snapshot in seconds
     from ( select /*+ at this stage, each row show the cumulative value.
                       r1    7500  8600
                       r2    7300  8300
                       r3    7200  8110
                    we use [max(row) - lag(row)] to have the difference between [row and row-1], to obtain differentials values:
                       r1    200   300
                       r2    100   190
                       r3    0       0
        (max(id1)    - lag( max(id1))   over (order by a.snap_id))/100        id1 ,
        (max(usr1)   - lag( max(usr1))  over (order by a.snap_id))/100        usr1,
        ( max(sys1)  - lag( max(sys1))  over (order by a.snap_id))/100        sys1,
        ( max(io1)   - lag( max(io1))   over (order by a.snap_id))/100        io1,
        ( max(nice1) - lag( max(nice1)) over (order by a.snap_id))/100        nice1,
          max(to_char(BEGIN_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss'))          snap_begin,      -- for later display
          max(to_char(END_INTERVAL_TIME,' YYYY-MM-DD HH24:mi:ss'))            snap_end,        -- for later display
        ( max(END_INTERVAL_TIME)-max(BEGIN_INTERVAL_TIME))                    diffs            -- exact len of snap used for percentage calculation
        from ( /*+  perform a pivot table so that the 5 values selected appears on one line.
                    The case, distibute col a.value among 5 new columns, but creates a row for each.
                    We will use the group by (snap_id) to condense the 5 rows into one.
                    if you don't see the utility, just remove the group by and max function,
                    then re-add it and you will see the what  use is max() and here is what you will see:
        Raw data :   1000     2222
                     1000     3333
                     1000     4444
                     1000     5555
                     1000     6666
        The SELECT CASE creates populate our inline view with structure:
                       ID    IDLE    USER   SYS   IOWAIT   NICE
                     1000    2222
                     1000             3333
                     1000                   4444
                     1000                          5555
                     1000                                  6666
         the goup by(1000) condensate the rows in one:
                       ID    IDLE    USER   SYS   IOWAIT  NICE
                      1000   2222    3333   4444   5555   6666
               select a.snap_id,
                  case b.STAT_NAME
                       when 'IDLE_TIME' then a.value / &p_num_cpus
                   end  id1,
                  case b.STAT_NAME
                       when 'USER_TIME' then a.value / &p_num_cpus
                  end usr1 ,
                  case b.STAT_NAME
                       when 'SYS_TIME' then  a.value / &p_num_cpus
                  end sys1 ,
                  case b.STAT_NAME
                       when 'IOWAIT_TIME' then  a.value / &p_num_cpus
                  end io1,
                  case b.STAT_NAME
                       when 'NICE_TIME' then  a.value / &p_num_cpus
                  end nice1
                  from  WRH$_OSSTAT a,  WRH$_OSSTAT_NAME b
                            a.dbid      = b.dbid       and
                            a.STAT_ID   = b.stat_id    and
                            instance_number = &inst    and
                            b.stat_name in ('IDLE_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME','NICE_TIME') and
                            a.snap_id SUPERIOR_TO (( select max(snap_id) from  WRH$_OSSTAT) - 10  -1 )
                   order by 1 desc
              ) a,  wrm$_snapshot s
         where  a.snap_id = s.snap_id
         group by a.snap_id
         order by snap_id desc
   )where rownum  INFERIOR_TO (10+1);

Number of CPU

Negatives values correspond to Shutdown:

   SNAP_ID   (Secs)   Idle time(%) User time(%) Sys time(%) Io Wait time(%) Nice time (%)      Snap begin            Snap end
---------- ---------- ------------ ------------ ----------- --------------- ------------- -------------------- --------------------
      2212       3603        87.07         9.43        2.84           16.19             0  2009-12-15 14:00:19  2009-12-15 15:00:21
      2211       3613        90.15         7.31        2.14           18.97             0  2009-12-15 13:00:06  2009-12-15 14:00:19
      2210       3593        92.58         5.91         1.4            7.91             0  2009-12-15 12:00:13  2009-12-15 13:00:06
      2209       3574        91.24         6.78        1.73            9.37             0  2009-12-15 11:00:38  2009-12-15 12:00:13
      2208       3620        90.04          7.8        1.96           11.81             0  2009-12-15 10:00:19  2009-12-15 11:00:38
      2207       3594         89.8         6.89        2.37           12.99             0  2009-12-15 09:00:25  2009-12-15 10:00:19
      2206       3611        91.75         5.81        1.87            8.29             0  2009-12-15 08:00:14  2009-12-15 09:00:25
      2205       3599        92.38         5.63        1.68            8.57             0  2009-12-15 07:00:15  2009-12-15 08:00:14
      2204       3605        91.72         6.51        1.58           10.79             0  2009-12-15 06:00:11  2009-12-15 07:00:15
      2203       3601        86.36        10.35        2.94           10.62             0  2009-12-15 05:00:10  2009-12-15 06:00:11

4. Bernard Polarski - December 15, 2009

Lol the fixed format worked but the system truncated the output : I give up :p

mwidlake - December 15, 2009

Haha!! Don’t you just love technology.

I might pull it all into a proper posting for you later Bernard (but I still have some Partition postings to get around too!)

Thanks for your fine efforts.

5. Bernard Polarski - December 17, 2009

How is it that your blog is so narrow. Looking at J.Lewiss on this same site ‘wordpress’ and it has a much larger display. I checked on both Firefow and IE and it is the same problem.

mwidlake - December 17, 2009

It’s the layout template I chose when I started. It limits the width of the main pane. I did not realise how much of a problem it would be until I had done a few postings. I keep thinking of changing the template but then I would probably have to review what I have posted so far to make sure it still looked OK.

Another option is to swap from free to paid-for hosting and get access to more options.

6. Using AWR data for OS information « Martin Widlake’s Yet Another Oracle Blog - December 22, 2009

[…] posting was actually a comment by (and is the work of) Bernard Polarski on a recent post about command_types I did a week or two back. I’ve lifted it into a separate posting as (a) I thought it might be […]

7. Blogroll Report 04/12/2009-11/12/2009 « Coskan’s Approach to Oracle - December 23, 2009

[…] Martin Widlake-COMMAND_TYPE values […]

8. Raja - December 23, 2009


To which Oracle Version does this query run for ? Because i got the following error when i just the above script in 10gR1 :

a.snap_id SUPERIOR_TO (( select max(snap_id) from WRH$_OSSTAT) – 10 -1 )
ERROR at line 51: ORA-00920: invalid relational operator


mwidlake - December 23, 2009

Your timing is perfect Raja – I moved Bernard’s excellent code to it’s own blog posting (22nd Dec) and fixed the need to replace various signs (, if they come out!) with text.

9. Raja - December 23, 2009

Extremely SORRY, Got it.
Problem Solved.

It is a very useful query for analysis purpose ( finding bottlenecks ).
But didnt have access to WRH$_OSSTAT, so will get back to you, after i view the output sometime…


mwidlake - December 23, 2009

Good luck with it Raja, let us know what you think

10. Christian Antognini - January 7, 2010

Hi Martin

For an almost full list (few are missing, e.g. MERGE and CREATE DISKGROUP) you can execute the following query:

SELECT * FROM audit_actions


11. mwidlake - January 8, 2010

Hi Christian,

Thanks for that – in one of those accidents of fate, I’ve just been looking at AUDIT_ACTIONS. An “Updtae Post” should turn up later today.

12. Only Four Left… » Recently relevant (to me) links - June 21, 2010

[…] for the command_type column in V$SQL? Martin Widlake offers a few pointers to get that info, in not one, but two […]

13. Gerrit - March 8, 2016
14. Alejandro - January 12, 2017

17 grants
43 álter user

15. WheresTheMoneyLebowski - November 17, 2017

198 purge dba_recyclebin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: