This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

SQL_OPCODE values from an 11gR2 Database

If you have a need to find the actual numeric operation code for different SQL operations, then this is possible if you can access the ASH table as highlighted below.

The snapshot was taken from an 11gR2 database:

SQL> select distinct sql_opname,
                     sql_opcode
       from dba_hist_active_sess_history;

SQL_OPNAME                SQL_OPCODE
------------------------- -----------------
ALTER TABLESPACE          40
DELETE                    7
ALTER DATABASE            35
ANALYZE TABLE             62
ALTER TABLE               15
INSERT                    2
UPSERT                    189
ALTER SUMMARY             172
CREATE TABLE              1
SELECT                    3
LOCK TABLE                26
CREATE INDEX              9
PL/SQL EXECUTE            47
TRUNCATE TABLE            85
CALL METHOD               170
                          0
UPDATE                    6

I guess the newer the “feature” the higher the number, since Oracle couldn’t just go and change the number between releases.

Use of Oracle AWR / ASH leading to bad coding?

I had a brief email exchange with another Oracle guru the other day.
He suggested that the quality of Oracle coding in PL*SQL and Plain Jane (www.medicaltextbooksrevealed.com/blog/2010/02/plain-jane/) SQL had gone down hill.

This could be attributed to two factors:
1, The level of coding experience has dropped.  Older more experienced coders have filled into the new architect roles and the void is being filled quickly by newer in-experienced coders.
2, The rigour with which debugging, testing and tuning is performed has become somewhat lax (www.thefreedictionary.com/lax ) because there’s just no emphasis on the developers to tune their code when the DBA has such great tools to do it for them.

Is it possible that the use of the additionally licensed tools such as AWR (Automatic Workload Repository) and ASH (Active Session History) introduced in Oracle 10g, have provided an easy mechanism for DBAs to seek out better performance.
I don’t think these tools are just for DBAs, but the way they are marketed makes me feel they are pushed that way.