Oracle DBA – A lifelong learning experience

What is the overhead of using reserved words?

Posted by John Hallas on May 25, 2011

I wish I knew is the short answer.

We are using a 3rd party tool which was ported from elsewhere to run on Oracle (probably SQLServer). Consequently there are a lot of column names that are Oracle resererved words and have to be double quoted in all the queries and reports that are written for it. This is  a change/config/incident management product so as you can imagine there are a lot of reports /views in place and they are growing by the week.

I was trying to understand what overhead that must cause and I was seeing if I could produce a simple test case to prove the overhead.  I have failed miserably and I am now looking for some assistance.

Firstlya bit of background and a demonstration to prove that I have actually tried to think about this.

A list of columns using reserved words shows 199 distinct entries for one schema and some of them are shown below

 select distinct  column_name from dba_tab_columns t ,v$reserved_words r where t.COLUMN_NAME = r.keyword and    t.owner not in ('SYS','SYSTEM')  order by 1
ACCESS
ADD
ADMIN
ALIAS
ATTRIBUTE
ATTRIBUTES
AUTHENTICATION
BITMAP
BLOB
BLOCKSIZE
BUILD
CARDINALITY
CATEGORY
CHECKPOINT
CLASS
CLUSTER
COLUMN
COLUMNS
COMMENT
COMPLETE

I could not find anything on MoS or Google to help me understand the overhead so I thought that a simple testcase might help.

I firstly set up 2 tables, one named using a reserved word and one not and then ran a 10046 trace to see if I could see any overhead.

 create table "access" ( which number(6)); -- reserved table name
insert into "access" values (109153);
create table access1 (which number(6)); --good table name
insert into access1 values (109153);
exec dbms_stats.gather_table_stats(null, 'access1', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats(null, '"access"', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

 alter session set tracefile_identifier=JOHN5;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

select * from "access" ;
select * from access1 ;

Then run a tkprof and print out the results. I ran a sys=no in this example but could not see anything useful when I used sys=yes

 tkprof SID_ora_9033_JOHN5.trc JOHN1.tkp explain= sort=exec sys=no

Sort options: execnt
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: g8wmfdczz0yku
Plan Hash: 1085870286
select *
from
 access1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          8          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         15          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  (SYSOPR65)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL ACCESS1 (cr=7 pr=0 pw=0 time=0 us cost=3 size=5 card=1)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'ACCESS1' (TABLE)

********************************************************************************

SQL ID: 8t81qb68b2g1x
Plan Hash: 3097087663
select *
from
 "access"
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          8          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         15          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  (SYSOPR65)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL access (cr=7 pr=0 pw=0 time=0 us cost=3 size=5 card=1)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'access' (TABLE)

 The cost is the same as well as the excution plan for both queries.

Perhaps it is different if I use a valid table name and a reserved word column name. Using the code below I got the same results, no obvious overheads.

 create table test ( "access" number(6)); -- reserved column name
insert into test  values (109153);
create table test1 (access1 number(6)); --good column
insert into test1 values (109153);
commit;
exec dbms_stats.gather_table_stats(null, 'test1', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');
exec dbms_stats.gather_table_stats(null, 'test', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

alter session set tracefile_identifier=JOHN6;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

select "access" from test;
select access1 from test1;

I am now struggling to see how I can prove the overhead, if indeed one exists at all. Any thoughts or suggestions welcome.

Today I will experiment with the Tom Kyte scripts to capture delta values out of the v$mystat tables to see if that helps me understand this issue.

Advertisements

2 Responses to “What is the overhead of using reserved words?”

  1. coskan said

    Try to create a table with name system and see the effects for AQ tables:) You can end up clearing AQ tables on sys.obj table

    I still don’t understand why Oracle let users use reserved words , I mean if they are reserved then control it every DDL. I don’t see any developer bother to check if they can use the word or not when they can

  2. davidp said

    I wouldn’t expect any measurable overhead – quoted identifiers are a pretty trivial feature to implement, and after parsing them they will be replaced with object id’s and column id’s.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: