Category Archives: Oracle

Multithreaded Architecture in Oracle12c.

Until Oracle11g, Oracle databases  on Unix/Linux OS followed process based architecture.  Starting with Oracle12c,  databases on Unix/Linux systems can be configured to support multithreaded architecture. In other words one process can have multiple operating system threads in separate address spaces.   Threads are very useful whenever a process has multiple tasks to be performed independent of others. Also  multiple threads within the same processes allows sharing of resources such as memory and CPU. So far with out much testing , I feel the benefits of multithreaded architecture  is mainly for multiple instance servers because of reduced memory/CPU and other resource consumption.

This is not to be confused with shared servers or Mutlithreaded servers. This feature can be implemented by setting initialization parameter THREADED_EXECUTION to TRUE and bouncing the database.

On my test database , the number of process with thread based architecture was reduced by 87%.  Even though Oracle does not guarantee  any given process to have a process architecture  v.s threaded architecture, I was able to consistently able to get the same results over multiple shutdowns on a single  instance server.

Number of process with process based systems(48 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle    1880     1  0 Aug09 ?        00:00:00 ora_w006_orcl12c
oracle    2018     1  0 Aug09 ?        00:00:00 ora_w007_orcl12c
oracle    3765     1  0 06:00 ?        00:00:00 ora_vkrm_orcl12c
oracle    4812     1  0 Aug09 ?        00:00:00 ora_w008_orcl12c
oracle    4947     1  0 Aug09 ?        00:00:00 ora_w009_orcl12c
oracle   27527     1  0 Aug09 ?        00:00:01 ora_pmon_orcl12c
oracle   27529     1  0 Aug09 ?        00:00:01 ora_psp0_orcl12c
oracle   27531     1  0 Aug09 ?        00:00:00 ora_vktm_orcl12c
oracle   27535     1  0 Aug09 ?        00:00:00 ora_gen0_orcl12c
oracle   27537     1  0 Aug09 ?        00:00:00 ora_mman_orcl12c
oracle   27541     1  0 Aug09 ?        00:00:00 ora_diag_orcl12c
oracle   27543     1  0 Aug09 ?        00:00:00 ora_dbrm_orcl12c
oracle   27545     1  0 Aug09 ?        00:00:36 ora_dia0_orcl12c
oracle   27547     1  0 Aug09 ?        00:00:02 ora_dbw0_orcl12c
oracle   27549     1  0 Aug09 ?        00:00:02 ora_lgwr_orcl12c
oracle   27551     1  0 Aug09 ?        00:00:02 ora_ckpt_orcl12c
oracle   27553     1  0 Aug09 ?        00:00:00 ora_lg00_orcl12c
oracle   27555     1  0 Aug09 ?        00:00:00 ora_lg01_orcl12c
oracle   27557     1  0 Aug09 ?        00:00:00 ora_smon_orcl12c
oracle   27559     1  0 Aug09 ?        00:00:00 ora_reco_orcl12c
oracle   27561     1  0 Aug09 ?        00:00:00 ora_lreg_orcl12c
oracle   27563     1  0 Aug09 ?        00:00:46 ora_mmon_orcl12c
oracle   27565     1  0 Aug09 ?        00:00:24 ora_mmnl_orcl12c
oracle   27567     1  0 Aug09 ?        00:00:00 ora_d000_orcl12c
oracle   27569     1  0 Aug09 ?        00:00:00 ora_s000_orcl12c
oracle   27581     1  0 Aug09 ?        00:00:00 ora_tmon_orcl12c
oracle   27583     1  0 Aug09 ?        00:00:00 ora_tt00_orcl12c
oracle   27585     1  0 Aug09 ?        00:00:00 ora_smco_orcl12c
oracle   27587     1  0 Aug09 ?        00:00:00 ora_fbda_orcl12c
oracle   27589     1  0 Aug09 ?        00:00:00 ora_aqpc_orcl12c
oracle   27593     1  0 Aug09 ?        00:00:00 ora_p000_orcl12c
oracle   27595     1  0 Aug09 ?        00:00:00 ora_p001_orcl12c
oracle   27597     1  0 Aug09 ?        00:00:00 ora_p002_orcl12c
oracle   27599     1  0 Aug09 ?        00:00:00 ora_p003_orcl12c
oracle   27601     1  0 Aug09 ?        00:00:00 ora_p004_orcl12c
oracle   27603     1  0 Aug09 ?        00:00:00 ora_p005_orcl12c
oracle   27605     1  0 Aug09 ?        00:00:00 ora_p006_orcl12c
oracle   27607     1  0 Aug09 ?        00:00:00 ora_p007_orcl12c
oracle   27609     1  0 Aug09 ?        00:00:13 ora_cjq0_orcl12c
oracle   27644     1  0 Aug09 ?        00:00:00 ora_w000_orcl12c
oracle   27646     1  0 Aug09 ?        00:00:00 ora_qm02_orcl12c
oracle   27650     1  0 Aug09 ?        00:00:00 ora_q002_orcl12c
oracle   27652     1  0 Aug09 ?        00:00:00 ora_q003_orcl12c
oracle   28199     1  0 Aug09 ?        00:00:00 ora_w001_orcl12c
oracle   29096     1  0 Aug09 ?        00:00:00 ora_w002_orcl12c
oracle   31615     1  0 Aug09 ?        00:00:00 ora_w003_orcl12c
oracle   31726     1  0 Aug09 ?        00:00:00 ora_w004_orcl12c
oracle   31732     1  0 Aug09 ?        00:00:00 ora_w005_orcl12c

Number of process with thread based systems(6 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle   15016     1  0 21:17 ?        00:00:00 ora_pmon_orcl12c
oracle   15018     1  0 21:17 ?        00:00:00 ora_psp0_orcl12c
oracle   15020     1  0 21:17 ?        00:00:00 ora_vktm_orcl12c
oracle   15024     1  1 21:17 ?        00:00:01 ora_u004_orcl12c
oracle   15030     1  0 21:17 ?        00:00:00 ora_u005_orcl12c
oracle   15036     1  0 21:17 ?        00:00:00 ora_dbw0_orcl12c
oracle@myserver:orcl12c:/app/oracle$ oracle@myserver:orcl12c:/app/oracle$

So what are implications of threaded architecture?

 

1. OS Authentication

I was not able to connect as “sys as sysdba” without password after  enabling multithreaded architecture.  As of now OS authentication is not supported with threaded architecture.

oracle@myserver:orcl12c:/app/oracle$
oracle@myserver:orcl12c:/app/oracle$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 10 21:17:29 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: sys/mypasswd as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

2. Killing Sessions

There is no change in behavior to kill sessions from the database  as the sessions are killed based on serial#  using “ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’. However you loose the flexibility of killing specific sessions based on process id using OS  “kill” command if the process have multiple threads. All processes and threads can be viewed by querying V$PROCESS which has a new column STID that provides information about the threads. See sample output below.

SQL> SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY  1;

SPID                     STID                     PROGRAM
———————— ———————— ————————————————
15137                    15137                    oracle@ps-zsign-db1 (PMON)
15139                    15139                    oracle@ps-zsign-db1 (PSP0)
15141                    15141                    oracle@ps-zsign-db1 (VKTM)
15145                    15158                    oracle@ps-zsign-db1 (LGWR)
15145                    15147                    oracle@ps-zsign-db1 (GEN0)
15145                    15145                    oracle@ps-zsign-db1 (SCMN)
15145                    15162                    oracle@ps-zsign-db1 (SMON)
15145                    15164                    oracle@ps-zsign-db1 (LREG)
15145                    15154                    oracle@ps-zsign-db1 (DBRM)
15145                    15159                    oracle@ps-zsign-db1 (CKPT)
15145                    15160                    oracle@ps-zsign-db1 (LG00)
15145                    15161                    oracle@ps-zsign-db1 (LG01)
15145                    15148                    oracle@ps-zsign-db1 (MMAN)
15151                    15155                    oracle@ps-zsign-db1 (DIA0)
15151                    15151                    oracle@ps-zsign-db1 (SCMN)
15151                    15153                    oracle@ps-zsign-db1 (DIAG)
15151                    15213                    oracle@ps-zsign-db1 (QM02)
15151                    15167                    oracle@ps-zsign-db1 (D000)
15151                    15216                    oracle@ps-zsign-db1 (Q003)
15151                    15163                    oracle@ps-zsign-db1 (RECO)
15151                    15165                    oracle@ps-zsign-db1 (MMON)
15151                    15166                    oracle@ps-zsign-db1 (MMNL)
15151                    15792                    oracle@ps-zsign-db1
15151                    15168                    oracle@ps-zsign-db1 (S000)
15151                    15169                    oracle@ps-zsign-db1 (N000)
15151                    15176                    oracle@ps-zsign-db1 (TMON)
15151                    15177                    oracle@ps-zsign-db1 (TT00)
15151                    15178                    oracle@ps-zsign-db1 (SMCO)
15151                    15179                    oracle@ps-zsign-db1 (FBDA)
15151                    15180                    oracle@ps-zsign-db1 (AQPC)
15151                    15206                    oracle@ps-zsign-db1 (W000)
15151                    15182                    oracle@ps-zsign-db1 (CJQ0)
15151                    15183                    oracle@ps-zsign-db1 (P000)
15151                    15184                    oracle@ps-zsign-db1 (P001)
15151                    15185                    oracle@ps-zsign-db1 (P002)
15151                    15186                    oracle@ps-zsign-db1 (P003)
15151                    15187                    oracle@ps-zsign-db1 (P004)
15151                    15188                    oracle@ps-zsign-db1 (P005)
15151                    15189                    oracle@ps-zsign-db1 (P006)
15151                    15190                    oracle@ps-zsign-db1 (P007)
15151                    15207                    oracle@ps-zsign-db1 (VKRM)
15151                    15643                    oracle@ps-zsign-db1 (W001)
15151                    15215                    oracle@ps-zsign-db1 (Q002) 15157                    15157                    oracle@ps-zsign-db1 (DBW0)

3. Listener Changes

Finally update listener.ora file to set DEDICATED_THROUGH_BROKER_listener-name=ON to enables the server process to spawn threads when connections to the database are requested through the listener.

RDA HVCE Option

The purpose of this blog is not discuss RDA but instead focus on an important option of RDA.  Never shakeout an environment  after  OS upgrade or Oracle install without this option.

The option is hvce (health validation check engine). HCVE performs health check of Oracle environment to find incorrect OS settings,  Incorrect important  OS parameters, missing OS packages.

..

See below for output of rda.sh -hcve

ID NAME RESULT VALUE
A00110 ulimits OK? FAILED StackTooSmall
A00140 LD_LIBRARY_PATH Unse FAILED IsSet
A00160 LIBPATH Unset? FAILED IsSet
A00200 JDK Version FAILED JDK home is missing
A00210 Other O_Hs in PATH? FAILED OratabEntryInPath
A00310 DNS Lookup FAILED Host not correctly registered in DNS
A00320 /etc/hosts Format FAILED no entry found
A00335 aio_maxreqs OK? FAILED ioo and iostat error
A00010 OS Certified? PASSED Certified
A00015 HARDWARE_BITMODE 64? PASSED is 64-bit
A00016 System mode 64-bit? PASSED 64-bit kernel enabled
A00020 User in /etc/passwd? PASSED userOK
A00040 Group in /etc/group? PASSED GroupOK
A00060 ORACLE_HOME Valid? PASSED OHexists
A00070 O_H Permissions OK? PASSED CorrectPerms
A00080 oraInventory Permiss PASSED oraInventoryNotFound
A00090 Got Software Tools? PASSED ld_nm_ar_make_found
A00100 Umask Set to 022? PASSED UmaskOK
A00220 Other OUI Up? PASSED NoOtherOUI
A00230 Temp Adequate? PASSED TempSpaceOK
A00240 Disk Space OK? PASSED DiskSpaceOK
A00260 RAM (in MB) PASSED 40960
A00270 SwapToRAM OK? PASSED SwapToRAMOK
A00330 Kernel Parameters OK PASSED Parameters OK
A00350 LINK_CNTRL is Unset? PASSED LINK_CNTRLunset
A00410 Got OS Patches? PASSED PatchesFound
A00430 Got OS Packages? PASSED All required OS packages are installed
A00445 TCP/UDP Ephemeral OK PASSED Ephemeral OK
A00050 Enter ORACLE_HOME RECORD /app/home/oracle/product/11.2.0.2.0/db
A00190 Enter JDK Home RECORD
A00250 Swap (in MB) RECORD 20480
A00290 IP Address RECORD 10.XXX.XX.XX
A00300 Domain Name RECORD NotFound
A00340 AIXTHREAD_SCOPE=S? SKIPPED NA

I am speaking at IOUG 2013

http://collaborate13.ioug.org/p/cm/ld/fid=197

 

I am speaking at prestigious Hotsos 2013 symposium

https://portal.hotsos.com/company/news/hotsos-symposium-2013-speakers-announced

I am speaking at Sangam 2012

Got Selected to Speak at DOAG 2012

I got selected to speak at DOAG 2012. Unfortunately I will not be able to make two international trips in one single month.  I feel very sad in declining this opportunity. Hopefully I will get another opportunity next year or so

Tracing specific SQL’s (Tracing Series-2)

Until 11g ,tracing was synonymous  with sessions.  You could not trace anything other than sessions (Don’t take this literally)

With 11g  you have the option of  tracing individual  SQL’s by means a event/trigger.   This is very useful  if  you are executing a long running process  and interested in tracing only a  particular SQL.  You can trace the SQL either at session level or system level.

Syntax

  • alter system set events ‘sql_trace [sql:sql_id] level 12’
  • alter session set events ‘sql_trace [sql:sql_id] level 12’

Note:  The SQL trace command can be executed  for  more than 1 SQL_ID too by separating the  sql_id’s with pipe symbol

alter system set events ‘sql_trace [sql:sql_id|sql_id] level 12′;

 

=-

Steps to Trace the SQL

Step-1: Execute the following Query  to get the SQL_ID. 

.

SQL>select object_name from dba_objects where object_name=’DBA_TABLES’;

Step-2: Find the SQL_ID from v$sql

..
 SQL> column sql_text format a80
 SQL> set lines 120 pages 2000
 SQL> set long 3000
 SQL>
 SQL> select sql_id , sql_text from v$sql where sql_text like 
'%select object_name from dba_objects where object_name=%';
SQL_ID SQL_TEXT
 ------------- --------------------------------------------------------------------------------
 gynhgrz60jayw    select object_name from dba_objects where object_name='DBA_TABLES'
3n6v2xv2s5wm4     select sql_id , sql_text from v$sql where sql_text like '%select object_name fro
                  m dba_objects  where object_name=%'
--

.

Step-3: Enable the trace for the SQL_ID

.

SQL> alter system set events ‘sql_trace [sql:gynhgrz60jayw] level 12’;

System altered

.

Step-4: Execute the SQL statement to generate trace

.

SQL> select object_name from dba_objects where object_name=’DBA_TABLES’;

OBJECT_NAME
————————————————————————————————————————
DBA_TABLES
DBA_TABLES

Step-5: Analyze the trace file. This is the fun part. Identifying problem SQL is often easy but fixing it …

Trace file /app/oracle/diag/rdbms/mydb/mydn/trace/mydb_ora_36897294.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning option
ORACLE_HOME = /app/oracle/product/11.2.0.2/db
System name: AIX
Node name: mydb1
Release: 1
Version: 6
Machine: 00CF7$B54C00
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 36897294, image: oracle@mydb1 (TNS V1-V3)
*** 2012-08-07 16:55:40.894
*** SESSION ID:(1012.117) 2012-08-07 16:55:40.894
*** CLIENT ID:() 2012-08-07 16:55:40.894
*** SERVICE NAME:(SYS$USERS) 2012-08-07 16:55:40.894
*** MODULE NAME:(SQL*Plus) 2012-08-07 16:55:40.894
*** ACTION NAME:() 2012-08-07 16:55:40.894

=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870757326

hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=683,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=49002870757324
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=000004D6.0000.0001
EXEC #4575285296:c=0,e=1314,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1028580536,tim=49002870772280
FETCH #4575285296:c=0,e=89,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870772402
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=2 pr=0 pw=0 time=69 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=52,dep=1,type=0,tim=49002870772481
=====================
PARSING IN CURSOR #4575285296 len=37 dep=1 uid=0 oct=3 lid=0 tim=49002870773099 hv=1398610540 ad=’700000127d65258′ sqlid=’grwydz59pu6mc’
select text from view$ where rowid=:1
END OF STMT
PARSE #4575285296:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773098
BINDS #4575285296:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=110b54bf0 bln=16 avl=16 flg=05
value=0000C0C4.0000.0001
EXEC #4575285296:c=0,e=77,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1028580536,tim=49002870773248
FETCH #4575285296:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1028580536,tim=49002870773283
STAT #4575285296 id=1 cnt=1 pid=0 pos=1 obj=59 op=’TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=8 us cost=1 size=15 card=1)’
CLOSE #4575285296:c=0,e=29,dep=1,type=0,tim=49002870773333
=====================
PARSING IN CURSOR #4575286160 len=66 dep=0 uid=21 oct=3 lid=21 tim=49002870784095 hv=3423120348 ad=’7000001208cd1f8′ sqlid=’gynhgrz60jayw’
select object_name from dba_objects where object_name=’DBA_TABLES’
END OF STMT
EXEC #4575286160:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,plh=2998908629,tim=49002870784093
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784259
FETCH #4575286160:c=0,e=336,p=0,cr=17,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870784629
WAIT #4575286160: nam=’SQL*Net message from client’ ela= 280 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870784977
WAIT #4575286160: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=49002870785044
FETCH #4575286160:c=0,e=2993,p=0,cr=304,cu=0,mis=0,r=1,dep=0,og=2,plh=2998908629,tim=49002870787996
STAT #4575286160 id=1 cnt=2 pid=0 pos=1 obj=1195 op=’VIEW DBA_OBJECTS (cr=321 pr=0 pw=0 time=332 us cost=79 size=132 card=2)’
STAT #4575286160 id=2 cnt=2 pid=1 pos=1 obj=0 op=’UNION-ALL (cr=321 pr=0 pw=0 time=328 us)’
STAT #4575286160 id=3 cnt=2 pid=2 pos=1 obj=0 op=’FILTER (cr=317 pr=0 pw=0 time=321 us)’
STAT #4575286160 id=4 cnt=2 pid=3 pos=1 obj=0 op=’NESTED LOOPS (cr=317 pr=0 pw=0 time=315 us cost=78 size=65 card=1)’
STAT #4575286160 id=5 cnt=2 pid=4 pos=1 obj=0 op=’NESTED LOOPS (cr=314 pr=0 pw=0 time=323 us cost=77 size=61 card=1)’
STAT #4575286160 id=6 cnt=2 pid=5 pos=1 obj=507636 op=’TABLE ACCESS FULL OBJ$ (cr=311 pr=0 pw=0 time=263 us cost=76 size=39 card=1)’
STAT #4575286160 id=7 cnt=2 pid=5 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=57 us cost=1 size=22 card=1)’
STAT #4575286160 id=8 cnt=2 pid=4 pos=2 obj=507644 op=’INDEX RANGE SCAN I_USER2 (cr=3 pr=0 pw=0 time=10 us cost=1 size=4 card=1)’
STAT #4575286160 id=9 cnt=0 pid=3 pos=2 obj=19 op=’TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)’
STAT #4575286160 id=10 cnt=0 pid=9 pos=1 obj=36 op=’INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)’
STAT #4575286160 id=11 cnt=0 pid=3 pos=3 obj=0 op=’NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=30 card=1)’
STAT #4575286160 id=12 cnt=0 pid=11 pos=1 obj=507644 op=’INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)’
STAT #4575286160 id=13 cnt=0 pid=11 pos=2 obj=507640 op=’INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=10 card=1)’
STAT #4575286160 id=14 cnt=0 pid=2 pos=2 obj=0 op=’NESTED LOOPS (cr=4 pr=0 pw=0 time=199 us cost=1 size=29 card=1)’
STAT #4575286160 id=15 cnt=89 pid=14 pos=1 obj=507644 op=’INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=98 us cost=1 size=352 card=88)’
STAT #4575286160 id=16 cnt=0 pid=14 pos=2 obj=105 op=’INDEX RANGE SCAN I_LINK1 (cr=3 pr=0 pw=0 time=133 us cost=0 size=25 card=1)’

*** 2012-08-07 16:55:44.200
CLOSE #4575286160:c=0,e=38,dep=0,type=0,tim=49002874076143

Like Operator and Indexes

Trying to  predict Oracle’s behavior with respect to using indexes with LIKE operator is not simple.  However you may be able to get close enough with the following algorithm.  Let me  list  the number of ways you can use the single  LIKE operator along with the algorithm.

  1. Beginning of search expression (%ABC) :  Less likely to use an Index (Most likely to perform full table scan)
  2. End of search expression (ABC%): More likely to use an Index
  3. In  between search expression (A%BC) : More likely to used an Index

However one key thing I want to mention is that if possible avoid using LIKE operator even if index is used and look for ways to use replace LIKE with  SUBSTR/INSTR/ASCII and so on.  This is to reduce the number of consistent gets even if index is being used.

See example below  for all the 3 case scenarios. In the 1st few commands, we are creating tables , indexes and gathering stats.

 

SQL> CREATE TABLE MY_DBAOBJECTS AS SELECT * FROM DBA_OBJECTS UNION ALL 
 2 SELECT * FROM DBA_OBJECTS UNION ALL SELECT * FROM DBA_OBJECTS;
-
TABLE CREATED.
SQL> 
SQL> CREATE INDEX IDX_OBJ_NAME ON MY_DBAOBJECTS (OBJECT_NAME);
-
INDEX CREATED.

SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'DATA_MASTER', INDNAME=>'IDX_OBJ_NAME');
-
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
-
SQL> SET AUTOTRACE TRACEONLY EXPLAIN

-
Case:1
#####################################################################################
Beginning of search expression (%ABC): Less likely to use Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE '%DBA';
-
EXECUTION PLAN
------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 869 | 276 (1)|
|* 1 | TABLE ACCESS FULL| MY_DBAOBJECTS | 11 | 869 | 276 (1)|
------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
1 - FILTER("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%DBA')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

-
Case-2
#####################################################################################
End of search expression (ABC%): More likely to use an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6176 | 476K| 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 6176 | 476K| 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%')
 FILTER("OBJECT_NAME" LIKE 'DBA%')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

--
Case-3
#####################################################################################
In between search expression (A%BC) : More likely to used an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%T';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228 | 18012 | 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 228 | 18012 | 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | 228 | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%T')
 FILTER("OBJECT_NAME" LIKE 'DBA%T')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION
SQL>

I am Speaking at InSync12 (Melbourne)

 

I will be presenting once again at this year InSync conference at  Melbourne (Aug 21/22),  Australia.  My session is about Oracle GoldenGate Best Practices and performance optimization techniques.  Last years conference was in Sydney convention center and had impressive list of speakers including Tom Kyte, Richard Foote, Andrew Holdsworth & Graham Wood.  
..
..
About InSync12
..
The InSync12 Conference and Exhibitor Showcase is a joint event of the Quest ANZ user group – representing JDE and PeopleSoft customers, and the Australian Oracle User Group (AUSOUG) representing the Technology, Development and Applications users.  This is the fourth time the independent user groups in Australia will come together for one national conference.  See link for more details http://www.insync12.com.au/

Tracing SPM’s and Datapump (Tracing Series-I)

Some of  the complex issues that we face in our day-day operations can be resolved by tracing with or without Oracle support.  It depends on how much you want to grind yourself as understanding some of the trace files are not for faint hearts.  I always end up spending time googling for appropriate  syntax or trace number. Sometimes you  are lucky enough to get  to the intended document but sometimes you end up at wrong place.   So here I am compiling a one stop shop for all tracing needs as series.  In  Part-I , I will be discussing SPM tracing and Datapump tracing.  SPM and datapump have nothing in common , just  picked them randomly


SPM Tracing: There may be many  reasons why one would trace SPM.  Few of the reasons I can think of is to diagnose SPM’s not being created or to see more information of plan evolution.  SPM Tracing can be enabled either of following ways

  •     exec dbms_spm.configure(‘spm_tracing’,1);

I could not find documentation about spm_tracing option  in 11gR2. Two documented parameters supported by dbms_spm.configure procedure are  space_budget_percent and plan_retention_weeks.  Only references for parameter spm_tracing was found in metalink with values 0 (disable tracing) and 1(enable tracing). The actual command however takes any values you provide.  You can query sys.smb$config to verify tracing is enabled or disabled

SQL> exec dbms_spm.configure('spm_tracing',1); 

PL/SQL procedure successfully completed.

SQL> 
SQL>  select parameter_name, parameter_value from sys.smb$config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        25
PLAN_RETENTION_WEEKS                       523
SPM_TRACING                                  1
  •     alter session set events ‘trace [sql_planmanagement.*]’;

Noticed the 2nd command; If does not look familiar then you are not  alone. This is the new syntax introduced with 11g also referred to as universal tracing.  Unfortunately I was not able to get this working after trying different options. I will follow-up with Oracle on this command.
SQL>
SQL> alter session set events ‘trace [sql_planmanagement.*]’;
ERROR:
ORA-49100: Failed to process event statement [trace [sql_planmanagement.*]]
ORA-48904: String [SQL_PLANMANAGEMENT] is not a valid component name

SQL> alter session set events ‘trace {sql_plan_management.*}’;
ERROR:
ORA-49100: Failed to process event statement [trace {sql_plan_management.*}]
ORA-49109: Library Name [SQL_PLAN_MANAGEMENT] not found

SQL>

DataPump Tracing:  Datapump can be traced in multiple ways either at database level or at command level or with 10046 trace.  With datapump , tracing is as complex as analyzing the trace files.  Never understood the reasoning behind  exposing hexadecimals to users unless this was intended for Oracle support. Datapump tracing  is enabled with  TRACE parameter using 7 digit hexadecimal. The first three digits enable the trace  while last four digits are usually “0300”
At the database level, datapump tracing can be enabled with initialization parameter EVENT 39089.  Again you have to find the correct  hexadecimal value and  bounce the database.

Example:(Init.ora setting)

EVENT=”39089 trace name context forever,level 0x300″ .

Example (alter system ,10046 trace)
alter system set events ‘sql_trace {process : pname = dw | pname = dm} level=12’; Just replace 12 with off to disable tracing. Be aware of this command , it will take any argument for process name.


SQL> alter system set events ‘sql_trace {process : pname = dw } level=12’;

System altered.

SQL> alter system set events ‘sql_trace {process : pname =djsljl} level=12′;  ==> Incorrect value

System altered.

SQL>
—-

Example (Command level)
expdp system/manager DIRECTORY=mydata_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log full=y TRACE=480300

Here is information about  the hexadecimals supported trace levels supported.  Oracle also supports combination of these values

================================================================================
Hex     | Purpose
================================================================================
10300   |  SHDW: To trace the Shadow process (API) (expdp/impdp)
20300   |  KUPV: To trace Fixed table
40300   |  'div' To trace Process services
80300   |  KUPM: To trace Master Control Process (MCP,DM)
100300  |  KUPF: To trace File Manager
200300  |  KUPC: To trace Queue services
400300  |  KUPW: To trace Worker process(DW)
800300  |  KUPD: To trace Data Package
1000300 |  META: To trace Metadata Package
1FF0300 |  'all' To trace all components(full tracing)
================================================================================