trusted online casino malaysia

Archive for November 2010

How to Lock SQL Profiles Generated by SQL Tuning Advisor

November 30, 2010, 9:08 pm

I’ve mentioned (many times) that I think SQL Profiles that are generated by the SQL Tuning Advisor (STA) tend to sour over time.

After seeing it happen at a few sites I began to wonder why. So first a few facts about the SQL Profiles that STA generates:

  1. They are simply a set hints that get applied to statements behind the scenes during parsing
  2. They consist mainly of OPT_ESTIMATE hints which modify optimizer calculations
  3. They also may contain direct statistics modification hints (COLUMN_STATS, TABLE_STATS)
  4. They usually contain a OPTIMIZER_FEATURES_ENABLED hint
  5. They very occasionally contain other environment type hints (FIRST_ROWS, etc…)
  6. They do not contain directive hints (FULL, INDEX, NESTED_LOOP, etc..)
  7. The names of STA profiles start with SYS_SQLPROF
  8. STA’s goal is to do a more through job of analyzing a SQL statement to get a better plan

I wrote a little query (sql_profile_distinct_hint.sql) to pull a list of hints from a 10g database along with the number of their occurrences and ran it on several production systems where STA Profiles had been created. Here’s the output from a  system that had 14 STA Profiles.

SQL> @sql_profile_distinct_hints
Enter value for profile_name: SYS_SQLPROF%
 
HINT                                                 COUNT(*)
-------------------------------------------------- ----------
COLUMN_STATS                                               13
FIRST_ROWS                                                  1
IGNORE_OPTIM_EMBEDDED_HINTS                                 1
INDEX_STATS                                                 1
OPTIMIZER_FEATURES_ENABLE                                  14
OPT_ESTIMATE                                              178
TABLE_STATS                                                 2

Notice that the vast majority of hints are of the OPT_ESTIMATE variety. Now let’s have a look at the actual hints contained in a STA Profile.


SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_0126f1743c7d0005

HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default) 
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)
COLUMN_STATS("KSO"."SKEW", "PK_COL", scale, length=5)
COLUMN_STATS("KSO"."SKEW", "COL1", scale, length=4 distinct=828841 nulls=12.8723033 min=1 max=1000000)
TABLE_STATS("KSO"."SKEW", scale, blocks=162294 rows=35183107.66)

7 rows selected.

So on this particular STA Profile, the OPT_ESTIMATE hint has been used to tell the optimizer to change the estimate of rows for table A in query block SEL$6 by multiplying it by 2207 (roughly). In addition, there are hints which are basically hard coding table stats and column stats. So as you can see, these hints, while they may be accurate when the Profile is created, are unlikely to remain accurate over the long haul. In fairness, the OPT_ESTIMATE hint does make sense in situations where the optimizer will never get a calculation correct because of a short coming in it’s abilities (correlated columns is a good example of this type of situation). And in those conditions, implementing a STA generated Profile is a valid long term approach. But in my experience this is the exception rather than the rule.

So what are STA Profiles good for? Well two things:

First, they are very good at showing us where the optimizer is having a problem. If you look at the hints that are generated, it is easy to identify the OPT_ESTIMATE hints where the scaling factors are off the chart (hint: anything with an exponent is a place where the optimizer is struggling). This is easy to do with my sql_profile_hints.sql script by the way. Here’s a set of OPT_ESTIMATE hints. Can you spot the place where the optimizer is really having a problem?

OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_FILTER, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06) 
OPT_ESTIMATE(@"SEL$5DA710D3", INDEX_SKIP_SCAN, "F"@"SEL$1", IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=4.446153275) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("C"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=7.884506683) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("E"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=25.60960842) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1"), SCALE_ROWS=26.34181566) 
OPT_ESTIMATE(@"SEL$5DA710D3", JOIN, ("F"@"SEL$1", "B"@"SEL$1", "A"@"SEL$1"), SCALE_ROWS=839.9683673) 
OPT_ESTIMATE(@"SEL$5DA710D3", TABLE, "D"@"SEL$1", SCALE_ROWS=5.083144561)
OPT_ESTIMATE(@"SEL$5", INDEX_SCAN, "C"@"SEL$5", ORDER_FG_ITEM_IX3, SCALE_ROWS=0.2507281101)

It’s the first two lines and whatever alias F refers to is our problem area. The OPT_ESTIMATE hint tells the optimizer to decrease it’s estimate by a factor of 8.883203639e-06. So the optimizer has vastly overestimated the rows that will be returned by the index.

Second, STA Profiles are sometimes capable of producing better plans. This is primarily due to the fact that STA can take as long as you give it to analyze a statement, making sure that all the optimizer’s calculations are correct. It does this by running various pieces of the statement and checking that the number of rows the optimizer has estimated are actually correct. Obviously this can take a while on complex statements, much longer than the optimizer is allowed when parsing a statement. But as I’ve already shown, the SQL Profiles that get created to enable those better plans have a pretty good chance of going sour on us over time.

Which leads me to the point of this post. We can have our cake and eat it too! We can create the SQL Profile as recommended by STA and then “lock” the plan into place by converting the OPT_ESTIMATE hints to directive type hints. I put the word “lock” in quotes because there is really no such thing as “locking” a plan. It’s just that using directive hints as opposed to OPT_ESTIMATE hints, significantly lowers the probability of the plan changing in the future. So how do we make this conversion. Well I have a script for that called lock_STA_profile.sql. Here’s an example showing how it works.


SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: 

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
PROFILE_fgn6qzrvrjgnz          DEFAULT         DISABLED select /*+ index(a SKEW_COL1) */ avg(pk_col) from kso.skew a           NO
PROFILE_8hjn3vxrykmpf          DEFAULT         DISABLED select /*+ invalid_hint (doda) */ avg(pk_col) from kso.skew where col1 NO
PROFILE_69k5bhm12sz98          DEFAULT         DISABLED SELECT dbin.instance_number,        dbin.db_name, dbin.instance_name,  NO
PROFILE_8js5bhfc668rp          DEFAULT         DISABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
PROFILE_bxd77v75nynd8          DEFAULT         DISABLED select /*+ parallel (a 4) */ avg(pk_col) from kso.skew a where col1 >  NO
PROFILE_7ng34ruy5awxq          DEFAULT         DISABLED select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.prope NO
PROF_6kymwy3guu5uq_1388734953  DEFAULT         ENABLED  select 1                                                               YES
PROFILE_cnpx9s9na938m_MANUAL   DEFAULT         ENABLED  select /*+ opt_param('statistics_level','all') */ * from kso.skew wher NO
PROF_79m8gs9wz3ndj_3723858078  DEFAULT         ENABLED  /* SQL Analyze(252,1) */ select avg(pk_col) from kso.skew              NO
PROFILE_9ywuaagwscbj7_GPS      DEFAULT         ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_arcvrg5na75sw_3723858078  DEFAULT         ENABLED  select /*+ index(skew@sel$1 skew_col1) */ avg(pk_col) from kso.skew wh NO
SYS_SQLPROF_01274114fc2b0006   DEFAULT         ENABLED  select i.table_owner, i.table_name, i.index_name, FUNCIDX_STATUS, colu NO
SYS_SQLPROF_0127d10ffaa60000   DEFAULT         ENABLED  select table_owner||'.'||table_name tname , index_name, index_type, st NO
SYS_SQLPROF_01281e513ace0000   DEFAULT         ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO
coe_abwg9nwg8prsj_3723858078   DEFAULT         ENABLED                                                                         NO
PROF_84q0zxfzn5u6s_2650913906  TEST            ENABLED  select avg(pk_col) from kso.skew                                       NO
PROF_0pvj94afp6faw_FULL        DEFAULT         ENABLED  select /* test 1 hint */ avg(pk_col) from kso.skew a where col1 = 2222 NO
PROF_875qbqc2gw2qz_4201340344  DEFAULT         ENABLED  select /* NOT IN */ department_name                                    NO
PROF_09gdkwq1bs48h_167097056   DEFAULT         ENABLED  select /*+ index (skew skew_col3_col2_col1) */ count(*) from kso.skew  NO
PROFILE_4cp821ufcwvgc_moved    DEFAULT         ENABLED  select count(*) from kso.skew where col3 = '01-jan-10'                 NO
PROF_8wvgj0n4kh6dx_2650913906  DEFAULT         ENABLED  select avg(pk_col) from kso.skew a where col1 = 333333                 NO
PROFILE_g737q1pfmbvjj_moved    DEFAULT         ENABLED  select /*+ full (skew) */ avg(pk_col) from kso.skew where col1 = 13613 NO
PROFILE_cvdnr0b8dcxzz_MANUAL   DEFAULT         ENABLED  select /* aasdas */ avg(pk_col) from kso.skew where col1 = 136133      NO
PROF_719syuvrm29tq_931251584   DEFAULT         ENABLED  SELECT IOBJID, IDOBJID, INAME, IOWNER, IOWNERID, ISPACE, ITSNO, IFILEN NO
PROF_g4gp07gt2z920_105323984   DEFAULT         ENABLED  update sys.scheduler$_job set  last_start_date = :1, running_instance  NO

25 rows selected.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01281e513ace0000

HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default)
FIRST_ROWS(1)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)

5 rows selected.

SYS@LAB112> @find_sql
Enter value for sql_text: SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORD%
Enter value for sql_id: 

SQL_ID         CHILD  PLAN_HASH EXECS AVG_ETIME  AVG_LIO SQL_TEXT
------------- ------ ---------- ----- --------- -------- --------------------------------------------------
bqfx5q2jas08u      0 2496534803    86       .00       12 SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE
                                                         (T) ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_A
                                                         DVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR
                                                         _PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P
                                                         2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMP
                                                         LETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND
                                                         T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID
                                                         AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETE
                                                         R_VALUE = SYS_CONTEXT('USERENV','INSTANCE') AND T.
                                                         TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_I
                                                         D' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 ) ORDER BY
                                                          T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1


1 row selected.

SYS@LAB112> @dplan
Enter value for sql_id: bqfx5q2jas08u
Enter value for child_no: 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bqfx5q2jas08u, child number 0
-------------------------------------
SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */
T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC)
T, DBA_ADVISOR_PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE
T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START
>= (SYSDATE - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID
AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE =
SYS_CONTEXT('USERENV','INSTANCE') AND T.TASK_ID = P2.TASK_ID AND
P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 )
ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1

Plan hash value: 2496534803

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                        |       |       |     9 (100)|          |
|*  1 |  COUNT STOPKEY                     |                        |       |       |            |          |
|   2 |   VIEW                             |                        |     2 |    26 |     9   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                    |                        |       |       |            |          |
|   4 |     NESTED LOOPS                   |                        |     2 |   240 |     9   (0)| 00:00:01 |
|*  5 |      FILTER                        |                        |       |       |            |          |
|   6 |       NESTED LOOPS OUTER           |                        |     2 |   188 |     7   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                        |     2 |   126 |     5   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID| WRI$_ADV_TASKS         |     2 |    74 |     3   (0)| 00:00:01 |
|   9 |          INDEX FULL SCAN DESCENDING| WRI$_ADV_TASKS_PK      |   822 |       |     2   (0)| 00:00:01 |
|* 10 |         TABLE ACCESS BY INDEX ROWID| WRI$_ADV_PARAMETERS    |     1 |    26 |     1   (0)| 00:00:01 |
|* 11 |          INDEX UNIQUE SCAN         | WRI$_ADV_PARAMETERS_PK |     1 |       |     0   (0)|          |
|* 12 |        TABLE ACCESS BY INDEX ROWID | WRI$_ADV_EXECUTIONS    |     1 |    31 |     1   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | WRI$_ADV_EXECS_PK      |     1 |       |     0   (0)|          |
|* 14 |      INDEX UNIQUE SCAN             | WRI$_ADV_PARAMETERS_PK |     1 |       |     0   (0)|          |
|* 15 |     TABLE ACCESS BY INDEX ROWID    | WRI$_ADV_PARAMETERS    |     1 |    26 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   5 - filter((DECODE(NVL("E"."STATUS","A"."STATUS"),1,'INITIAL',2,'EXECUTING',3,'COMPLETED',4,'INTER
              RUPTED',5,'CANCELLED',6,'FATAL ERROR')='COMPLETED' AND
              NVL("E"."EXEC_START","A"."EXEC_START")>=SYSDATE@!-1))
   8 - filter(("A"."ADVISOR_NAME"='ADDM' AND "A"."HOW_CREATED"='AUTO' AND
              BITAND("A"."PROPERTY",6)=4))
  10 - filter("A"."VALUE"=TO_CHAR(:B1))
  11 - access("A"."ID"="A"."TASK_ID" AND "A"."NAME"='DB_ID')
  12 - filter("A"."ADVISOR_ID"="E"."ADVISOR_ID")
  13 - access("A"."ID"="E"."TASK_ID" AND "A"."LAST_EXEC_NAME"="E"."NAME")
  14 - access("A"."ID"="A"."TASK_ID" AND "A"."NAME"='INSTANCE')
  15 - filter("A"."VALUE"=SYS_CONTEXT('USERENV','INSTANCE'))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - SQL profile SYS_SQLPROF_01281e513ace0000 used for this statement


57 rows selected.

SYS@LAB112> @lock_STA_profile
Enter value for sql_id: bqfx5q2jas08u
Enter value for child_no (0): 0
Enter value for new_profile_name (PROF_sqlid_planhash): 
Enter value for force_matching (FALSE): 

PL/SQL procedure successfully completed.

SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: %bqfx5q2jas08u%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
PROF_bqfx5q2jas08u_2496534803  DEFAULT         ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: PROF_bqfx5q2jas08u_2496534803

HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
FIRST_ROWS(1)
NO_PARALLEL
OUTLINE_LEAF(@"SEL$86A1760A")
MERGE(@"SEL$5")
MERGE(@"SEL$532C0C35")
MERGE(@"SEL$6")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$532C0C35")
MERGE(@"SEL$4")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$58B2FD6B")
ELIMINATE_OBY(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "TASK_LIST"@"SEL$1")
INDEX_DESC(@"SEL$86A1760A" "A"@"SEL$4" ("WRI$_ADV_TASKS"."ID"))
INDEX_RS_ASC(@"SEL$86A1760A" "A"@"SEL$6" ("WRI$_ADV_PARAMETERS"."TASK_ID" "WRI$_ADV_PARAMETERS"."NAME"))
INDEX_RS_ASC(@"SEL$86A1760A" "E"@"SEL$4" ("WRI$_ADV_EXECUTIONS"."TASK_ID" "WRI$_ADV_EXECUTIONS"."NAME"))
INDEX(@"SEL$86A1760A" "A"@"SEL$5" ("WRI$_ADV_PARAMETERS"."TASK_ID" "WRI$_ADV_PARAMETERS"."NAME"))
LEADING(@"SEL$86A1760A" "A"@"SEL$4" "A"@"SEL$6" "E"@"SEL$4" "A"@"SEL$5")
USE_NL(@"SEL$86A1760A" "A"@"SEL$6")
USE_NL(@"SEL$86A1760A" "E"@"SEL$4")
USE_NL(@"SEL$86A1760A" "A"@"SEL$5")
NLJ_BATCHING(@"SEL$86A1760A" "A"@"SEL$5")

29 rows selected.

SYS@LAB112> @sql_profiles
Enter value for sql_text: 
Enter value for name: SYS%

NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FORCE
------------------------------ --------------- -------- ---------------------------------------------------------------------- -----
SYS_SQLPROF_01274114fc2b0006   DEFAULT         ENABLED  select i.table_owner, i.table_name, i.index_name, FUNCIDX_STATUS, colu NO
SYS_SQLPROF_0127d10ffaa60000   DEFAULT         ENABLED  select table_owner||'.'||table_name tname , index_name, index_type, st NO
SYS_SQLPROF_01281e513ace0000   SAVED           ENABLED  SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TAS NO

3 rows selected.

SYS@LAB112> @sql_profile_hints
Enter value for profile_name: SYS_SQLPROF_01281e513ace0000

HINT
------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(default)
FIRST_ROWS(1)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$6", SCALE_ROWS=2207.090256)
OPT_ESTIMATE(@"SEL$86A1760A", TABLE, "A"@"SEL$5", SCALE_ROWS=2261.586312)

5 rows selected.

SYS@LAB112>

So in this example I listed all the SQL Profiles in existence on the system (using sql_profiles.sql). Then I showed the hints associated with STA Profile, SYS_SQLPROF_01281e513ace0000 with sql_profile_hints.sql. Then I located the sql statement in v$sql using the find_sql.sql script. Then I used dbms_xplan (via the dplan.sql script) to show the plan for the statement (proving that it was using the STA Profile). Then I used the lock_STA_profile.sql script to create a directive hint based Profile in place of the OPT_ESTIMATE hint based Profile. Then I showed the hints for the new SQL Profile. Note that the original STA Profile is not dropped, but rather moved to the SAVED category, so you can still look at its hints as I have done at the end of this example.

So that’s it. This is a complex topic and I have blogged about it before on numerous occasions. You may want to look back at this post, Oracle Support Sanctions Manually Created SQL Profiles, to get a better feel for where the hints came from that are used to replace the OPT_ESTIMATE hints. By the way, Jonathan Lewis and Tom Kyte have also written about this feature. (I trust you can find them via Google)

Also, I have written a chapter on Plan Stability in the upcoming Apress book, Pro Oracle SQL. The chapter is 65 or so pages long and it covers SQL Profiles in depth, so if you are hungry for more info on this topic, I highly recommend it. 😉

You can pre-order the book here: Pro Oracle SQL (if you are so inclined)

It should be released in a few weeks.

Category: Oracle, Plan Stability, Tuning  |  20 Comments

Cellcli Command Syntax – Top 10 List

November 13, 2010, 1:19 pm

Exadata storage software uses the cellcli utility as its command line interface. Unfortunately, although the documentation set that comes with Exadata does have many examples of cellcli commands, and even a chapter dedicated to cellcli, it does not include any reference material on the syntax itself (particularly the LIST command). So I thought I would write up a few of the things I’ve learned while picking around at it. But first a little bit of a rant on why they wrote yet another command line interface.

<RANT>They already had SQL*Plus for crying out loud. Why not just use that. SQL*Plus has all kinds of functionality for using variables, formatting output, etc… And on top of that, they invented a new syntax. Why use LIST instead of SELECT? They used WHERE and LIKE, so why not SELECT? I find it more than a little annoying (in case you couldn’t tell). I’m told that storage admins don’t like SQL and that’s one of the reasons for not using straight SQL syntax. That seems pretty silly since the storage is designed specifically for use with Oracle databases. </RANT>

So anyway, here’s my quick top ten list of things you should know:

  1. cellcli does have a handful of SQL*Plus commands (START (@), SET ECHO ON, SPOOL, DESCRIBE, HELP)

  2. SELECT is replaced by LIST and it must be the first key word on the command line

  3. There is no FROM keyword (the LIST keyword must be immediately followed by the ObjectType which is equivalent to a table name)

  4. There is a DESCRIBE command which displays the attributes (columns) that make up an ObjectType (table)

  5. Column names are specified with the ATTRIBUTES keyword followed by the columns you wish to be displayed

  6. There is a default set of columns for each Object that will be returned if the ATTRIBUTES keyword is not specified

  7. There is a WHERE clause that can be applied to any attribute and multiple conditions can be ANDed together (no OR though)

  8. There is no ORDER BY equivalent

  9. The DETAIL key word can be appended to any LIST command to change the output from column oriented to row oriented

  10. The LIKE operator works but instead of the standard SQL wildcard, %, cellcli uses regex – so ‘%’ = ‘.*’

So here are a few examples:

CellCLI> help 

 HELP [topic]
   Available Topics:
        ALTER
        ALTER ALERTHISTORY
        ALTER CELL
        ALTER CELLDISK
        ALTER GRIDDISK
        ALTER IORMPLAN
        ALTER LUN
        ALTER THRESHOLD
        ASSIGN KEY
        CALIBRATE
        CREATE
        CREATE CELL
        CREATE CELLDISK
        CREATE FLASHCACHE
        CREATE GRIDDISK
        CREATE KEY
        CREATE THRESHOLD
        DESCRIBE
        DROP
        DROP ALERTHISTORY
        DROP CELL
        DROP CELLDISK
        DROP FLASHCACHE
        DROP GRIDDISK
        DROP THRESHOLD
        EXPORT CELLDISK
        IMPORT CELLDISK
        LIST
        LIST ACTIVEREQUEST
        LIST ALERTDEFINITION
        LIST ALERTHISTORY
        LIST CELL
        LIST CELLDISK
        LIST FLASHCACHE
        LIST FLASHCACHECONTENT
        LIST GRIDDISK
        LIST IORMPLAN
        LIST KEY
        LIST LUN
        LIST METRICCURRENT
        LIST METRICDEFINITION
        LIST METRICHISTORY
        LIST PHYSICALDISK
        LIST THRESHOLD
        SET
        SPOOL
        START

CellCLI> help set

  Usage: SET  

  Purpose: Sets a variable to alter the CELLCLI environment settings for your
           current session.

  Arguments:
    variable and value represent one of the following clauses:
    DATEFORMAT { STANDARD | LOCAL }
    ECHO { ON | OFF }

  Examples:
    set dateformat local
    set echo on 


CellCLI> help list

  Enter HELP LIST  for specific help syntax.
    :  {ACTIVEREQUEST | ALERTHISTORY | ALERTDEFINITION | CELL 
                     | CELLDISK | FLASHCACHE | FLASHCACHECONTENT | GRIDDISK
                     | IORMPLAN | KEY | LUN 
                     | METRICCURRENT | METRICDEFINITION | METRICHISTORY 
                     | PHYSICALDISK | THRESHOLD }

CellCLI> help list FLASHCACHECONTENT

  Usage: LIST FLASHCACHECONTENT [] [] [DETAIL] 

  Purpose: Displays specified attributes for flash cache entries.

  Arguments:
    :  An expression which determines the entries to be displayed.
    : The attributes that are to be displayed.
                      ATTRIBUTES {ALL | attr1 [, attr2]... }

  Options:
    [DETAIL]: Formats the display as an attribute on each line, with
              an attribute descriptor preceding each value.

  Examples:
    LIST FLASHCACHECONTENT DETAIL

So as you can see, the help system allows you to see a bit of the syntax for each command. You may also have noticed a couple of SQL*Plus carry-overs. SET, SPOOL, and START work pretty much as expected. Note the @ is equivalent to START and that the only things you can SET are ECHO and DATEFORMAT. Now for a couple of queries (er LIST commands):

CellCLI> desc flashcachecontent
         ^
CELL-01504: Invalid command syntax.

CellCLI> describe flashcachecontent
        cachedKeepSize
        cachedSize
        dbID
        dbUniqueName
        hitCount
        hoursToExpiration
        missCount
        objectNumber
        tableSpaceNumber

CellCLI> set echo on

CellCLI> @fc_content

> CellCLI> list flashcachecontent where dbUniqueName like 'EXDB' and hitcount > 100 attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize, hitCount, missCount
         EXDB    2       0       4194304         600     208
         EXDB    40      0       2424832         376     60
         EXDB    224     0       1802240         115     80
         EXDB    267     0       458752          128     9
         EXDB    383     0       2547712         157     27
         EXDB    423     0       1867776         180     41
         EXDB    471     0       4071424         552     85
         EXDB    472     0       1277952         114     22
         EXDB    474     0       13246464        286     326
         EXDB    475     0       5914624         519     124
         EXDB    503     0       5308416         669     455
         EXDB    5710    0       3735552         363     90
         EXDB    6207    0       393216          112     9
         EXDB    6213    0       3842048         359     147
         EXDB    6216    0       1245184         184     29
         EXDB    6373    0       3481600         222     61
         EXDB    56085   0       4194304         822     129
         EXDB    66849   0       438763520       1221    3322
         EXDB    71493   0       5636096         302     127
         EXDB    71497   0       1351680         320     22
         EXDB    71573   0       2760704         101     37
         EXDB    71775   0       1801412608      34994   46315

CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 -

> attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize 
         EXDB    2       0       4194304
         EXDB    18      0       1179648
         EXDB    37      0       622592
         EXDB    40      0       2424832
         EXDB    63      0       524288
         EXDB    104     0       688128
         EXDB    224     0       3407872
         EXDB    267     0       458752
         EXDB    383     0       2670592
         EXDB    420     0       1507328
         EXDB    423     0       1867776
         EXDB    424     0       720896
         EXDB    471     0       4071424
         EXDB    472     0       1277952
         EXDB    473     0       2351104
         EXDB    474     0       13574144
         EXDB    475     0       5521408
         EXDB    503     0       5308416
         EXDB    5702    0       262144
         EXDB    5709    0       2416640
         EXDB    5710    0       3735552
         EXDB    6207    0       393216
         EXDB    6210    0       131072
         EXDB    6213    0       4227072
         EXDB    6216    0       1245184
         EXDB    6373    0       3579904
         EXDB    56085   0       4194304
         EXDB    66849   0       438763520
         EXDB    71493   0       5636096
         EXDB    71497   0       1351680
         EXDB    71573   0       2801664
         EXDB    71775   0       1801412608

CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775

CellCLI> list flashcachecontent where dbUniqueName like '.*X.?.?' and objectNumber like '.*775' detail                                      
         cachedKeepSize:         0
         cachedSize:             1801412608
         dbID:                   2356637742
         dbUniqueName:           EXDB
         hitCount:               34994
         missCount:              46315
         objectNumber:           71775
         tableSpaceNumber:       6

CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775

CellCLI> list flashcachecontent attributes objectNumber, hitCount, missCount where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         71775   34994   46315

So DESC doesn’t work as an abbreviation of DESCRIBE. Notice that there are no headings for column oriented output. As you can see, you can run “scripts” and SET ECHO ON to display the commands in any scripts that you execute. One of the LIST commands was strung across two lines by using the continuation operator (-). The LIST commands look a lot like SQL except for LIST being used instead of SELECT and the regex expressions for matching when using the LIKE key word. Also notice that in the last command a number was matched with a regex expression implying a data type conversion, although all data may be treated at text. You can see that the ATTRIBUTES and WHERE key words can be anywhere on the command line after the “LIST objectName” keywords. In other words, these two key words are not positional, either one can be first. Finally, the DETAIL keyword turns the output sideways. Or as the help says, “Formats the display as an attribute on each line, with
an attribute descriptor preceding each value.”

So the cellcli interface is really not that bad, I just happen to like SQL*Plus better. 😉 I do think it would have been a simple matter to reuse SQL*Plus since they already have all the functionality built into it, but maybe there were other concerns that I’m not aware of. But cellcli works. And by the way, cellcli has the ability to scroll though previous commands and edit them via the arrow keys which is quite handy. The editing capability is definitely a step forward from SQL*Plus on unix like platforms (although you can use rlwrap to accomplish this – see this post for more details on that: Using rlwrap on Windows) And regex also provides a very powerful pattern matching capability although it’s still a little confusing to have SQL like syntax mixed with regex to my way of thinking. Maybe if they just added the ability to use the % wildcard in addition to the regex I would feel better about it.

Category: Exadata, Oracle  |  8 Comments