Coskan’s Approach to Oracle

November 23, 2009

Reminder for Oracle Performance Firefighting by Craig Shallahamer

Filed under: Basics — coskan @ 2:07 am

Just a reminder for the ones who are waiting for PDF version of Oracle Performance Firefighting by Craig Shallahamer, it  is now available here so you can avoid shipping costs. Click here for my review of the book if you haven’t read before.

September 14, 2009

Simple blank matters

Filed under: Basics, Bugs — coskan @ 1:16 pm

I saw this interesting bug on Metalink headlines and I thought it will be nice to post it here.

This is the heading of the bug ‘ADDING COLUMN WITH DEFAULT NULL LEADS TO UNNECESSARY FULL TABLE UPDATE ‘ numbered 8840491.

Normally, if you add a column with a default null value, there won’t be an update on the table itself (I think after 10G because trace for 9i shows update on table), running at the background, but if you put some spaces to make your alter statement look better, it is where the problem begins.

What I mean is these 4 statements are different during runtime due to the bug.

alter table test add (id number default null);

alter table test add ( id number default null);

alter table test add (id number default null );

alter table test add ( id number default null );

Lets see how do they differ.  Actual bug is reported on 10.2.0.4  and I am able reproduce it on 11.1.0.6


SQL> set timing on
SQL> create table test (id number not null);

Table created.

Elapsed: 00:00:00.54
SQL> insert into test
  2  select rownum from dual connect by level<=1000000;

1000000 rows created.

Elapsed: 00:00:01.65

SQL> alter session set tracefile_identifier=null_bug_test;

Session altered.

Elapsed: 00:00:00.00
SQL> exec dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL> --no space before after ()
SQL> alter table test add (id2 number default null)  ;

Table altered.

Elapsed: 00:00:00.09
SQL> --space after (
SQL> alter table test add ( id3 number default null)  ;

Table altered.

Elapsed: 00:00:00.01
SQL> --space before )
SQL> alter table test add (id4 number default null )  ;

Table altered.

Elapsed: 00:01:12.90
SQL> --space before and after ()
SQL> alter table test add ( id5 number default null ) ;

Table altered.

Elapsed: 00:01:34.27
SQL> -- spaces everywhere but not after before ()
SQL> alter table test add (id6  number   default   null)  ;

Table altered.

Elapsed: 00:00:00.04
SQL>

As you see from the timings some of them are longer than expected. Lets find them in tracefile

These are the update statements from the trace file.

=====================
PARSING IN CURSOR #1 len=29 dep=1 uid=82 oct=6 lid=82 tim=21689660840 hv=720540867 ad='30679e28' sqlid='7nb3cf4pg5563'
update "TEST" set "ID4"=null 
END OF STMT
PARSE #1:c=15625,e=17207,p=0,cr=106,cu=1,mis=1,r=0,dep=1,og=1,tim=21689660835
=====================
PARSING IN CURSOR #5 len=29 dep=1 uid=82 oct=6 lid=82 tim=21762749484 hv=1088193227 ad='30679058' sqlid='f4g28hd0dt0qb'
update "TEST" set "ID5"=null 
END OF STMT
PARSE #5:c=46875,e=146504,p=294,cr=100,cu=0,mis=1,r=0,dep=1,og=1,tim=21762749480

As you see space before ) and space before and after () causing full table update and guess the damage of this tiny hidden change on over 10 million row huge table.

Next time, if you wait your not null new column getting added longer than it needs on 10G>, check the syntax you might be hitting this bug.

September 9, 2009

Blogroll Report 28/08/2009 – 04/09/2009

Filed under: Basics — coskan @ 2:16 am

<—- Blogroll Report 21/08/2009 – 28/08/2009

Finally I am able to finish the missing weeks. Oracle released 11G Release 2 this week , and as you might guess most of the posts were about 11GR2 new features. Virag Sharma, Amit Bansal ,Arup Nanda (as usual) and Amis Group were the ones who won the race of posting about new features. Some of them were excerpt from documentation but still nice to know so I put them in my list. I also tried to put the posts under  the correct topic of official guide so you can find your way in new features documentation.

1-How to use DBMS_UTILITY ?

Aman Sharma – DBMS_UTILITY, A Good Helping Hand For A DBA….

2-Select for update and Redo Generation

Aman Sharma -Select For Update A DML, Yes It Is….

3-How to perform easy math in Unix

John Hallas – Performing calculations in unix

4-Using database resident connection pooling with Perl

Rajeev Ramdas Thottathil – Perl and database resident connection pooling

5-Security issues with JAVA_ADMIN role

Paul M Wright – JAVA_ADMIN to OSDBA

6- Securing the Data Dictionary O7_dictionary_accessibility parameter

James Koopmann – Oracle 11g Security – Securing the Data Dictionary

7- How histogram gathering works when first N character are same?

Hemant K Chitale – Histograms on “larger” columns

8-Effects of OPTIMIZER_INDEX_CACHING parameter

Richard Foote-OPTIMIZER_INDEX_CACHING Parameter

9-How does response time increase while throughput is also increasing (Don’t miss comments)

Jonathan Lewis-Queue Time

10-How to use Linux /proc filesystem to get Oracle trace directory

Kevin Closson – Using Linux /proc To Identify ORACLE_HOME and Instance Trace Directories.

11-How to install BBED

Steve Callan – Installing Oracle Block Browser and Editor tool (bbed)

11GR2 New Features Posts

1-Moving the database audit trail tables out of the SYSTEM tablespace to a different tablespace. – New security feature of 11GR2

Virag Sharma-11G R2 New Feature : Purge audit trail records using DBMS_AUDIT_MGMT

2-Single Client Access Name (SCAN)- New clustering feature of 11GR2

Virag Sharma – Oracle 11g Release 2 (11.2 ) New Features : SCAN – Single Client Access Name

3-Edition Based Redefinition – New availability feature of 11GR2

Virag Sharma -Oracle Database 11g Release 2 New Features : Edition based redefination

4-Recursive With Clause – New Business Intelligence and Datawarehousing feature of 11GR2

Lucas Jellema-Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it

5-Flash Cache – New Database Management feature of 11GR2

Marco Gralike-Oracle RDBMS 11gR2 – Flash Cache

6-Preprocessing Data for ORACLE_LOADER Access Driver in External Tables- New Business intelligence and Datawarehousing feature of 11GR2

Marco Gralike-Oracle RDBMS 11gR2 – New PREPROCESSOR Syntax for External Table Use

7-LISTAGG-New Business intelligence and Datawarehousing feature of 11GR2

Lucas Jellema-Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings

8-Enhance CREATE or REPLACE TYPE to Allow FORCE-New availability feature of 11GR2

Lucas Jellema-Oracle RDBMS 11gR2 – alter or replace user defined types even when there are dependencies

9-Improved Deinstallation Support With Oracle Universal Installer- New clustering feature of 11GR2

Arup Nanda-Oracle 11g R2 Features

10-ASM Dynamic Volume Manager and ASM Cluster File System – New Server Manageability feature of 11GR2

Arup Nanda – ASM Dynamic Volume Manager and ASM Clustered File System

11-Oracle Restart and Grid Infrastructure for Single Instance – New clustering feature of 11GR2

Arup Nanda -Oracle 11g Release 2 is Finally Out

12-Installation Fixup script – New Installation GUI feature of 11GR2

Amit Bansal – 11gR2:What if Oracle gives you Kernel parameter fixup script

13-Step by step 11gR2 Database Installation with ASM on OEL5

Amit Bansal-11gR2 Database Installation with ASM on OEL5

14-How to install single 11GR2 RAC instance with ASM using Grid Infrastructure Software?

Charles Kim-Single Node RAC Grid Infrastructure Installation With Oracle Database 11g Release 2

15-ASM Dynamic Volume Manager and ASM Cluster File System – New Server Manageability feature of 11GR2

Surachart Opun – ASM (DVM) & ACFS by command-lines

16-Deferred_Segment_Creation- New database management feature of 11GR2

Christian Antognini-Deferred Segment Creation

17-How to compate execution plans with dbms_xplan.diff_plan_outline – New 11G feature

Optimizer Magic-What’s Changed between my New Query Plan and the Old One?

Blogroll Report 04/09/2009 – 11/09/2009–>

August 13, 2009

DBMS_XMLGEN and bug

Filed under: Basics, Bugs, PL/SQL — coskan @ 4:35 pm

Last 2 weeks I had to deal with a post-upgrade problem (9.2.0.5 to 10.2.0.4) on a system which is not well tested before upgrade.  The issue was error stack below

ORA-06502: PL/SQL: numeric or value error:invalid LOB locator specified: ORA-22275

or for some records

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

Developer was insisting that this is a character set issue because of the first line of the error stack and I was saying it is impossible because both servers were UTF8 and only difference is NLS_NCHAR_CHARACTERSET which is never used anyway.  Because developer already gave the last decision, it was my turn to prove that this wasnt a characterset issue.

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs:) ) .  Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB).   We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem.  DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i.  To be honest The error stack (was using  DBMS_UTILITY.FORMAT_ERROR_STACK and  DBMS_UTILITY.format_error_backtrace)   was not very clear  to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2  as it is working on 9i. The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

This is the demo to show the different behaviour (taken from metalink ).

in 10G


SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

in 9i

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-24347: Warning of a NULL column in an aggregate function

no rows selected

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
--------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

Lessons Learned.

1-Do not trust “we tested its ok”  and Try to understand the application and try to ask developers what packages they are using so you can search for possible bugs before you upgrade the system.

2-After 1 day of struggling, Search metalink first  for a possible bug.  Stop messing around google

August 1, 2009

Blogroll Report 24/07/2009 – 31/07/2009

Filed under: Basics — coskan @ 1:53 am

<—- Blogroll Report 17/07/2009 – 24/07/2009

I was very busy this week and I could only find time to write this weeks blogroll after a big migration. I felt I have to write as I promised, and by this way I read all the post in my to-read list in 1 day. These summary series keeps me up-to date with blogosphere I hope it works for you too

1- All about Critical Patch Update preparation process

Eric Maurice – Ensuring Critical Patch Update Quality

2- 11G and Using Hugepage

Kevin Closson -

3- ITL effect on index space

Jonathan Lewis- IQ2 – Answers

Jonathan Lewis – Index Explosion

4- How to refresh test database from production with incremental backups and standby database options?

Ilmar Kerm – Refreshing test database from production using incremental backups

5-How to sort in different languages with index access. ?

Ilmar Kerm – Using linguistic indexes for sorting in Oracle

6-How to diagnose ORA-04030 ?

Dion Cho- Playing with ORA-4030 error

7-Troubleshooting RAC connectivity issues with VIP

Alex Gorbachev – Pythian Video: Oracle RAC VIP’s — Troubleshooting Connectivity Issues

8- How does PLAN_HASH_VALUE generated and how to generate hash values.?

Randolf Geist – PLAN_HASH_VALUE – How equal (and stable?) are your execution plans – part 2

9- How to identify PID/SPID of killed session in 11G?

Miladin Modrakovic- Identifying PID/SPID for killed session in 11g

10- How to Attach a SQL Profile to a Different Statement?

Kerry Osborne -How to Attach a SQL Profile to a Different Statement

11-Why Isn’t Oracle Using My Outline / Profile / Baseline?

Kerry Osborne -Why Isn’t Oracle Using My Outline / Profile / Baseline?

12- Why A-Rows is 0 for DMLs ?

Christian Antognini -A-Rows and DML Statements

13- How to find choked table stats?

Martin Widlake-Automatic Statistics Gathering Fails #3

14- Simple number formatting with to_char

Jeffrey Kemp – Great number formatting trick

15-How does delete work with merge in 10G?

Alex Nuijten -DELETE in the MERGE statement

16- How to use AWR differences Report- Why toad leaves Parallel Slaves in use (Check comments) ?

Doug Burns – AWR Differences Report

17- Function based indexes and hidden stats

Dion Cho-Function based index and suspicious filter predicates

18- Function based index is not used with Or-Expansion and query rewrite workaround with restrictions

Dion Cho- Function-based Index and Or-Expansion

19-Using check all repair clause of alter diskgroup  to release space

John Hallas-http://jhdba.wordpress.com/2009/07/30/asm-space-marked-as-internal/

20- Review of Oracle Metasploit tool

Alexander Kornbrust -Oracle Metasploit Presentation

21- Why DBA_TAB_PRIVS is not enough and workaround ?

Paul Wright -DBA_OBJ_PRIVS

22- How to find table creation order with Foreign key relations (non circular)?

Frank Zhou-How to determine the table creation order in SQL

23-How to limit user connection times intervals?

Emre Baransel-Limit User Connection Based On Time Interval

24-How to set up Network ACL in 11G?

Don Seiler – Setting up Network ACLs in Oracle 11g… For Dummies

Blogroll Report 31/07/2009 – 07/08/2009 ->

July 27, 2009

When is my password file gets updated?

Filed under: Basics, Tips — coskan @ 3:51 pm

Till I read this oracle-l thread, I was thinking pasword file is only updated/touched when sys user password is changed however, it is not the only case. As you can see below it is updated/touched whenever a user password is changed, interestingly password file is not updated/touched for new user creations. My question is why oracle updates/touches the file for regular user passwords and why it doesn’t updates/touches for user creation ? If anybody knows can you please leave a comment.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

23/07/2009  00:01             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

SQL> create user passw identified by passw;

User created.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

23/07/2009  00:01             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

SQL> alter user passw identified by passw;

User altered.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

27/07/2009  16:47             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

Footnote:explanation below came via mail from a reader and looks logical.
—-
Anytime a password is changed Oracle opens the password file to check if the user is a dba user and if so it updates the file. If not, obviously oracle does not update it. When oracle opens the file to do the check, oracle does that in read/write mode rather than in read only mode. This is because if the user does happen to be a DBA user it will be more work by closing the file in read mode and reopening in read/write mode and then perform the update.

June 11, 2009

How to use SYSMAN schema without EM

Filed under: Basics, Tips — coskan @ 4:18 pm

As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.

on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema

on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema

As you can see it doubled up on 11G. New features brings new tables to this schema.

I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER

Lets start with this table and see what we can get from the table

SQL> @printtab "select * from sysman.MGMT_HC_HARDWARE_MASTER"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
VENDOR_NAME                   : Dell Inc.
SYSTEM_CONFIG                 : Latitude D620
MACHINE_ARCHITECTURE          : x86
CLOCK_FREQ_IN_MHZ             : 133
MEMORY_SIZE_IN_MB             : 2038
LOCAL_DISK_SPACE_IN_GB        : 74.53
CPU_COUNT                     : 2
CPU_BOARD_COUNT               :
IOCARD_COUNT                  : 1
FAN_COUNT                     :
POWER_SUPPLY_COUNT            :
-----------------

As you can see Tables under SYSMAN schema can give you very nice information, which you can’t find in any V$ view or DBA_ table especially for Host system.

Lets see what else we can get under MGMT_HC_%

SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';

TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS

We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)

SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : Microsoft Windows XP Workstation
VENDOR_NAME                   : Microsoft Corporation
BASE_VERSION                  : 5.1
UPDATE_LEVEL                  : Service Pack 2
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 3933
ADDRESS_LENGTH_IN_BITS        : 32-bit
PATCHES                       : 152
-----------------

PL/SQL procedure successfully completed.

on HP-UX same table shows

SQL>  @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : 66E304347A471561E044000000000000
NAME                          : HP-UX
VENDOR_NAME                   : Hewlett-Packard Co.
BASE_VERSION                  : B.11.11
UPDATE_LEVEL                  : U
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 15598.066
ADDRESS_LENGTH_IN_BITS        : 64
-----------------

Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you

SQL> @printtab "select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB867282
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB867282
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873333
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873333
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873339
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873339
INSTALLATION_DATE             :
-----------------

PL/SQL procedure successfully completed.

As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command


select table_name from dba_tables where owner='SYSMAN' and num_rows>0;

May 27, 2009

What if Vendor’s code was wrong ?

Filed under: Basics — coskan @ 12:32 pm

One of my biggest problem as a permanent worker is dealing with third party product databases. The main problem with them is, there is no one on site in case of a problem, calling someone from vendor  is a cost option which most of the companies try to avoid from.
Second problem is that, it is really hard to know, how that application works at database level. When you ask the guy who uses the program, they start to  talk business language with me and my brain, most of the time struggles to understand anything other  than 0 and 1.

Problems do not finish here, I dont know how it works at your company but most of the time when there is a major upgrade on the software they send a piece of DB upgrade script and you run it.

Please be honest, how many of you check , before you run this min 1000 lines of sql code ???

I hope I am wrong, but  probably most of you check it after it causes a problem on the DEV server especially when you are at a big enterprise which has lots of these third party products.  If it doesn’t cause a problem then you run it on prod.

Lets say you got a VM server or slow development server and most of the time users are already crying for slow performance on DEV system when they compare it with PRD so when it runs slow on DEV probably DEV system will be blamed instead of the batch upgrade code.  That is exactly how the incident occurred on one of our systems.

One of my colleague ran the batch job which took over 5  hours and they (him and the business guy) decided this slowness is because of VM DEV box.  They believed that PRD box will do this in  less than 2 hours which was acceptable for business.

Mistakes so far

Mistake 1- DBA did not check the code.

Mistake 2- DBA did not check the response time

Mistake 3- Common beliefs (slow DEV) directed both business and dba wrongly.

After they start upgrade on PRD, it was 5 hours so far when I decided to involve to the problem.  What I saw was was a fancy update with a more fancy execution plan.

it was something like below

SQL> create table test as select * from dba_tables;

Table created.

SQL> create table test2 as select * from test;

Table created.

SQL>
SQL> explain plan for
  2  update test t
  3  set OWNER=(select owner from test2 where owner=t.owner and table_name=t.table_name),
  4   TABLE_NAME=(select TABLE_NAME from test2 where owner=t.owner and table_name=t.table_name),
  5   TABLESPACE_NAME=(select TABLESPACE_NAME from test2 where owner=t.owner and table_name=t.table_name),
  6   CLUSTER_NAME=(select CLUSTER_NAME from test2 where owner=t.owner and table_name=t.table_name),
  7   IOT_NAME=(select IOT_NAME from test2 where owner=t.owner and table_name=t.table_name),
  8   STATUS=(select STATUS from test2 where owner=t.owner and table_name=t.table_name),
  9   PCT_FREE=(select PCT_FREE from test2 where owner=t.owner and table_name=t.table_name),
 10   INI_TRANS=(select INI_TRANS from test2 where owner=t.owner and table_name=t.table_name),
 11   MAX_TRANS=(select MAX_TRANS from test2 where owner=t.owner and table_name=t.table_name),
 12   NEXT_EXTENT=(select NEXT_EXTENT from test2 where owner=t.owner and table_name=t.table_name),
 13   PCT_INCREASE=(select PCT_INCREASE  from test2  where owner=t.owner and table_name=t.table_name),
 14   AVG_SPACE=(select AVG_SPACE from test2 where owner=t.owner and table_name=t.table_name),
 15   INSTANCES=(select INSTANCES from test2 where owner=t.owner and table_name=t.table_name),
 16   IOT_TYPE=(select IOT_TYPE from test2 where owner=t.owner and table_name=t.table_name),
 17   COMPRESS_FOR=(select COMPRESS_FOR from test2 where owner=t.owner and table_name=t.table_name),
 18   READ_ONLY=(select READ_ONLY from test2 where owner=t.owner and table_name=t.table_name),
 19   DROPPED=(select DROPPED from test2 where owner=t.owner and table_name=t.table_name);

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1747411015

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |  2350 |   461K|    23   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST  |  2350 |   461K|    23   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST2 |     1 |    34 |    23   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL| TEST2 |     1 |    34 |    23   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL| TEST2 |     1 |    51 |    23   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL| TEST2 |     1 |    51 |    23   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL| TEST2 |     1 |    51 |    23   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL| TEST2 |     1 |    40 |    23   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 10 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 11 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 12 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 13 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 14 |   TABLE ACCESS FULL| TEST2 |     1 |    47 |    23   (0)| 00:00:01 |
|* 15 |   TABLE ACCESS FULL| TEST2 |     1 |    41 |    23   (0)| 00:00:01 |
|* 16 |   TABLE ACCESS FULL| TEST2 |     1 |    42 |    23   (0)| 00:00:01 |
|* 17 |   TABLE ACCESS FULL| TEST2 |     1 |    45 |    23   (0)| 00:00:01 |
|* 18 |   TABLE ACCESS FULL| TEST2 |     1 |    37 |    23   (0)| 00:00:01 |
|* 19 |   TABLE ACCESS FULL| TEST2 |     1 |    37 |    23   (0)| 00:00:01 |
----------------------------------------------------------------------------

There wasn’t any index no stats nothing. Tables were created and updated and the developer was unaware from the syntax below.  (I know that Oracle is intelligent enough to cope with the dodgy one but as least developer should write more readable code)

SQL> explain plan for
  2  update test t
  3  set (OWNER,
  4  TABLE_NAME,
  5  TABLESPACE_NAME,
  6  CLUSTER_NAME,
  7  IOT_NAME,
  8  STATUS,
  9  PCT_FREE,
 10  INI_TRANS,
 11  MAX_TRANS,
 12  NEXT_EXTENT,
 13  PCT_INCREASE,
 14  AVG_SPACE,
 15  INSTANCES,
 16  IOT_TYPE,
 17  COMPRESS_FOR,
 18  READ_ONLY,
 19  DROPPED)=(select OWNER,
 20  TABLE_NAME,
 21  TABLESPACE_NAME,
 22  CLUSTER_NAME,
 23  IOT_NAME,
 24  STATUS,
 25  PCT_FREE,
 26  INI_TRANS,
 27  MAX_TRANS,
 28  NEXT_EXTENT,
 29  PCT_INCREASE,
 30  AVG_SPACE,
 31  INSTANCES,
 32  IOT_TYPE,
 33  COMPRESS_FOR,
 34  READ_ONLY,
 35  DROPPED from test2 where table_name=t.table_name)  ;

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
Plan hash value: 1507865077

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |       |  2350 |   461K|    23   (0)| 00:00:01 |
|   1 |  UPDATE            | TEST  |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TEST  |  2350 |   461K|    23   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TEST2 |    24 |  4824 |    23   (0)| 00:00:01 |
----------------------------------------------------------------------------

I asked to interrupt the running update and create index and gather stats before this update. Guess what, everything run under 1 minute.

But my colleague was against this idea that this script needs to be re-sent to vendor and must come back to us. I did not have time to deal with this discussion and left him to solve however he wants to as long as he doesn’t waste my resource and time.  Finally vendor approved what we did and the problem solved.

At our team meeting, I said this is unacceptable and anything running over 1 hour on OLTP DEV (no matter how the DEV configuration is ) systems deserves to be checked for performance initially by DBA, and thank god at least  this is accepted.

IMHO, as long as DBA doesn’t change any logical thing on the code, he should have the freedom of modifying the code, because ,sending code to vendor for review, is just easy way to get rid of dirt.  Vendor doesn’t have our data,  doesn’t have my stats, doesn’t have our configuration etc etc, and probably they are trying to write an application which will work on every RDBMS (deadliest mistake).

There is also a security related issue with these kind of third party codes but it is another issue …

Moral of the story for me is check everything before your run, during running don’t leave it monitor it, if you haven’t run it on DEV / TST ask for timings and  don’t trust word of anybody without any proof.

April 30, 2009

What I learned from AOT by Tanel Poder

Filed under: Basics, Blogroll, Diary, Performance — coskan @ 8:28 pm

Guys who personally knows me, knows also that I am a very big fan of Tanel Poder and his work. I have been following him, since he started blogging in 2007 and after reading all his stuff,  at UKOUG 2008 Conference I found chance to have his masterclass about Advanced Oracle Troubleshooting. When I saw how he uses the things he wrote on his blog, all my approach to troubleshooting oracle had changed, but something was still missing. Blog and masterclass session wasn’t enough for me, I wanted more and more everytime I read a post from him. God must have heard my wishes and sent him to Edinburgh for his Advance Oracle Troubleshooting seminar and gave me the chance to fill the missing part.

After nearly 8 Oracle trainings ( 3 of them were celebrity seminars), I can personally say, If you love Oracle, if you love performance tuning and troubleshooting, if you look for a method to troubleshoot, if you want to learn something systematic and also practical   this is the best one you can attend to find what you are looking for. What makes this training unique is not just his methodology and knowledge, it is also his instructing skills. I don’t really enjoy sessions of  the instructors who are strict about their agenda and who don’t allow questions before they finish the topic but Tanel was always open to questions and I think he is aware that off-topic questions can lead to cover an upcoming topic too, so he doesn’t say, “I will see it next chapters” (which might not be covered because of the time limit. ) he answers them all. I asked as many questions as I can and none of them were remained without an answer not just by talk also by proof. Maybe he couldn’t finish all the chapters totally, but he nearly covered all in the middle of other chapters, by allowing questions. To get more benefit from this training, I strongly recommend you to read all the posts on his blog and try them before you come. Believe me you will focus more and benefit as much as I did. One more confession is that this one was the only training I never lost concentration :)

After all this voluntarily marketing, let me tell you tiny bits about what I learned from this young (it was a big shock to hear his IT experience is nearly 18 years at my age I think thats why Jonathan Lewis said that he saw his youth on him :) ), %100 geek and genius evangelist.

*If the session state is WAITING it means waiting, otherwise no matter what is written on state column of v$session it is on the CPU as simple as this. (sw script)

*On ideal world, if the session is running, session stats (snapper script) will show you some increasing stats

* For short intervals of snapping with snapper scripts DBTime and DB CPU might show non realistic values ideal is 5 and over I think, not less

*Oracle does not immediately put a line to an event trace, it does it when the call finishes.

* on solaris kill -STOP SPID suspends the process without killing it and you can resume it again by PRUN. check manual page for you operation system.

*using wireshark , snoop (solaris) tcpdump (linux) to check network related issues

*Oracle might lie about a process but OS wont. What OS says on process stack is always the most reliable one.

*sed.sql is the simple but life and time saver script to check the P1 P2 P3 value definitions of a wait event.

*procmon is a life saver tool for dodgy errors with “unknown error” message on Microsoft OS.

* on a dump trace file you can ignore function calls at the top of stack they are related with dumping process

* If you see sqlid sqltext but no plan for the running sql. dont get shocked there can be an ongoing hard parse.

* vmstat on unix/solaris sr which is scan rate column needs to be 0 for healthy monitoring

*on linux swappiness is an important memory setting which isnt referenced in online documents or metalink officially.

* good practice if you can take the dump of the process if you need to kill it. for finding the problem

*there is an hang analyze analyzer on EM. Never took my attention.

* v$wait_chain is similar with hang analyze dump but available after 11G

* Finding latch related problems are not a big issue any more after latchprof and latchprofx. Wont tell anything here everything is on his blog.

* root block is the next block after the block header. useful info for latch buffer cache chain issues. (might be leaf block on very tiny tables)

* quest spotlight has session _spin_count autotuner in it . (see Tanels comment below. )

*To drink more by Working less , you should be practical and script the things you use most in addition to his scripts

*there is a tool called trcsess which can be used to bring tracefiles together.

* he also say it is pointless to check statspack for something still going on.
I personally used statspack/awr only 3 times and one was at a job interview. Some stupid guy before doing rman online backup to 7/24 system, tried to test me what is wrong with that statspack report. Was totally crap.
To be honest Purely or initially statspack oriented problem solving is something like gues from your .ss type problem solving. Thanks Tanel for this term :) And his perfsheet tool makes statspack more useful to check what has happened last week type problems on a graphical interface. Having your own ASH if you don’t have Diagnostic Pack is far more better than getting lost on system wide reports.

* If you cant sleep on the plane, sleep enough before the flight and fly during office hours and work during the flight. systematic approach from a man who flys nearly 2 times a week.

* if you are googling an about oracle internal functions put .ch or .ru  domains for some better results (yes you need google translate to understand the page)

*BEST ONE I asked him how do you learn all these interesting stuff. He gave me a perfect clue. Sign up for metalink headlines mailing list. Bugs docs they are all there after they are created. Perfect resource.

There are two things, I don’t agree with him, about his approach. First is , I want to see the sql first about a session I am troubleshooting. If I know the system this will give me a basic idea, it can also lead wrong way but still SQL is something worth to see before starting other steps.
Write one script run on all versions idea is another thing I am not very keen to follow. I prefer to have 3 different script home to benefit from new columns added to tables. The only thing I need to do is call the right home in a bat file before I connect.

These were just my tiny highlights, from a big training. If you need more just attend his class, you wont regret.

I heard that he will come to London for seminar possibly after UKOUG 2009. If I were you, I won’t miss it.

Thank you very much Tanel for sharing your knowledge with us. You are the rising sun over Oracle Performance World.

One special thanks is coming for Thomas C. Presslie from PISEC who was the organizator of this event. Venue, environment and all training package and his hospitality  was wonderfull. I wish I could come for the upcoming ones :(

March 18, 2009

What I learned during Oracle SQL Expert Exam Study Part-2

Filed under: Basics, Certification, PL/SQL — coskan @ 9:31 pm

I wrote Part-1 of this series for covering what I learned from SQL Fundamentals I while studying for SQL Expert exam. Part-2 is for covering what I learned from SQL Fundamentals II.

Here are the things I should have learned before; (There are questions on 6 and 9 and answers will be prized with guinness if you can catch me (try my pony tail when you see me at a conference))

1- session_privs / session_roles views; never need, so never heard, these views can be very useful for normal database users to see what privileges they have what roles are enabled for them.

SQL> connect hr/hr;
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
...
...
...

14 rows selected.

-----ON SYS SESSION GRANT
-----SELECT ANY TABLE TO HR

SQL> grant select any table to hr;

Grant succeeded.

---ON HR SESSION
---SELECT ANY TABLE is shown 

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
....
....

15 rows selected.

—SESSION ROLES

SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE

---ON SYS SESSION GIVE DBA ROLE to HR

SQL> grant dba to hr;

Grant succeeded.
---on HR Session set Role DBA

SQL> set role dba;

Role set.

SQL> select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
...
...

18 rows selected.

2- PASSWORD command of SQLPLUS I was always using alter user command to change my password or somebody else’s password but this command is available and working as it works on Unix. If you want to change your password when you are a normal user it asks old password but if you are DBA and want to change some other users password, it lets you to set the password.


SQL> connect hr/hr
Connected.
---for SCOTT by HR
SQL> password scott
Changing password for scott
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged
SQL>---FOR HR itself
SQL> password
Changing password for HR
Old password:
New password:
Retype new password:
Password changed

—with sys user

SQL> password SCOTT
Changing password for SCOTT
New password:
Retype new password:
Password changed

3- References Object Privilege; Document says it lets the given user to create references on the table or view.

SQL> create user coskan identified by coskan ;

User created.

SQL> grant resource,create session to coskan;

Grant succeeded.

SQL> grant create table to coskan;

Grant succeeded.

SQL> connect coskan/coskan;
Connected.

SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));
create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id))
                                                                *
ERROR at line 1:
ORA-00942: table or view does not exist

----GRANT REFERENCES priv
----on HR.EMPLOYEES by HR user

SQL> grant references on employees to coskan;

Grant succeeded.

---on COSKAN user
---it lets to user the table for ref.
SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));

Table created.

---PRIV is just for reference
---it is not for selecting
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

—on HR session, you cant revoke privilege without cascade option

SQL> revoke references on employees from coskan;
revoke references on employees from coskan
*
ERROR at line 1:
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

SQL> revoke references on employees from coskan cascade constraints;

Revoke succeeded.

----on COSKAN session constraint is droppped

SQL> @ddl t1

PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-------------------------------------------------------------------------
-------------------------------------------------------------------------

  CREATE TABLE "COSKAN"."T1"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

4- system and object privileges cannot be given in one sql statement.

SQL> grant create table,select on hr.employees to coskan;
grant create table,select on hr.employees to coskan
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant create table to coskan;

Grant succeeded.

SQL> grant select on hr.employees to coskan;

Grant succeeded.

5-on delete set null Another shame for me. I think this option is better than on delete cascade for constraints

SQL> create table emp_2 (id number,
  2  emp_name varchar2(32),
  3  emp_id number
  4  CONSTRAINT t1_empid_fk
  5  REFERENCES hr.employees(employee_id)
  6  on delete set null);

Table created.

SQL> insert into emp_2 select employee_id,last_name
  2  ,employee_id from hr.employees where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell                                198
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL> delete from hr.employees where employee_id=198;

1 row deleted.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL>

6- Inserting Using a Subquery as a Target This is the first time I came across an insert like this. When you do an insert using a subquery I think it is better to use it with check otherwise I cant think of a situation to use it

Here is the working example. When I use WITH CHECK I got the expected error



SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000)
  6  VALUES (9999, 'Entertainment', 2500);

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000 WITH CHECK OPTION)
  6  VALUES (9999, 'Entertainment', 2500);
FROM departments d,locations l
     *
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation



This is is the failed one, when I use WITH CHECK OPTION, I got ORA-01733: instead of ORA-01402: and I really dont know, why it fails. I asked it on Oracle forums too and if you need points at the forum ,you can answer it at this link


SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia'  )
 10  VALUES (5500, 'Wansdworth Common', 'UK');

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia' WITH CHECK OPTION )
 10  VALUES (5500, 'Wansdworth Common', 'UK');
INSERT INTO
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


7- Multiple inserts I heard about multiple inserts probably when I got DBA Workshop-1 , 4 years ago, but I never needed them after that date. Here is how you can use them

—unconditional INSERT ALL. As you might guess there is no condition and all inserts will run

create table sal_history as
select employee_id,hire_date,salary
from employees
where 1=2;

SQL> create table MGR_HISTORY as
  2  select EMPLOYEE_ID,MANAGER_ID,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL> create table SAL_HISTORY as
  2  select EMPLOYEE_ID,HIRE_DATE,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL>
SQL> INSERT ALL
  2  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  3  INTO mgr_history VALUES(EMPID,MGR,SAL)
  4  SELECT employee_id EMPID, hire_date HIREDATE,
  5  salary SAL, manager_id MGR
  6  FROM employees
  7  WHERE department_id > 20;

206 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
       103

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
       103

—conditional INSERT ALL. Only insert statements those fit to the condition will be inserted


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where department_id<20;

  COUNT(*)
----------
         1

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL> INSERT ALL
  2  WHEN DEPARTMENT_ID <20 THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN MGR is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  SELECT employee_id EMPID, hire_date HIREDATE,
  7  salary SAL, manager_id MGR,DEPARTMENT_ID
  8  FROM employees;

3 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         1

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         2

—conditional INSERT FIRST Only first insert whose condition is TRUE will run if it is not true the second and continue.


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL> truncate table emp_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL>
SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         1

SQL>
SQL>
SQL> select count(*) from employees
  2  where department_id is null and manager_id is null;

  COUNT(*)
----------
         1

SQL> select count(*) from (
  2  SELECT employee_id EMPID, hire_date HIREDATE,
  3  salary SAL, manager_id MGR,DEPARTMENT_ID
  4  FROM employees) t;

  COUNT(*)
----------
       106

SQL>
SQL>
SQL> ---When it finds first row with manager_id is null
SQL> ---it will skip mgr_history insert and will do
SQL> ---only sal_history insert and
SQL> ---there is no other department_id is null
SQL> ---so second insert will never run
SQL> ---at the end of this transaction
SQL> ---sal_history 2 row
SQL> ---mgr_history 0 row
SQL> ---emp_history 104 row
SQL>
SQL> INSERT FIRST
  2  WHEN MGR is null THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN DEPARTMENT_ID is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  ELSE INTO emp_history VALUES(EMPID,SAL,MGR,DEPARTMENT_ID)
  7  SELECT employee_id EMPID, hire_date HIREDATE,
  8  salary SAL, manager_id MGR,DEPARTMENT_ID
  9  FROM employees;

106 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         2

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         0

SQL> select count(*) from emp_history;

  COUNT(*)
----------
       104

—pivoting INSERT- The one I really like. It does pivoting :)

suppose that you have a table like below


SQL> create table HALF_YEAR_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  JAN number,
  5  FEB number,
  6  MAR number,
  7  APR number,
  8  MAY number,
  9  JUN number);

Table created.

SQL> insert into HALF_YEAR_BUDGET
  2  values ('IT','2009',5000,6000,2000,4000,7000,9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from HALF_YEAR_BUDGET;

DE YEAR        JAN        FEB        MAR        APR        MAY        JUN
-- ---- ---------- ---------- ---------- ---------- ---------- ----------
IT 2009       5000       6000       2000       4000       7000       9000

and you want to store it in 3 column 6 row table

all you need to do is pivoting the insert

usage is same with INSERT ALL

SQL> create table DEP_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  BUDGET number);

Table created.

SQL> INSERT ALL
  2     INTO DEP_BUDGET VALUES (department,year,jan)
  3     INTO DEP_BUDGET VALUES (department,year,feb)
  4     INTO DEP_BUDGET VALUES (department,year,mar)
  5     INTO DEP_BUDGET VALUES (department,year,apr)
  6     INTO DEP_BUDGET VALUES (department,year,may)
  7     INTO DEP_BUDGET VALUES (department,year,jun)
  8     SELECT department, year, jan,feb,mar,apr,
  9     may,jun
 10     FROM HALF_YEAR_BUDGET;

6 rows created.

SQL> select * from DEP_BUDGET;

DE YEAR     BUDGET
-- ---- ----------
IT 2009       5000
IT 2009       6000
IT 2009       2000
IT 2009       4000
IT 2009       7000
IT 2009       9000

6 rows selected.

8- Explicit Defaults This is nice way to set a column to a default value. Just use the word DEFAULT

SQL> create table t1 (id number,last_name varchar2(32) default 'coskan');

Table created.

SQL> insert into t1 values (1,'oracle');

1 row created.

SQL> insert into t1 values (2,'larry');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 oracle
         2 larry

SQL> update t1 set last_name=DEFAULT where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 coskan
         2 larry

9- Subquery in order by You need to show all the employees ordered by their department names.

FIRST WAY I thought was joining the tables


SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	..
	..
	..

105 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3011238288

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY                |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |             |   105 |  3255 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

19 rows selected.

SECOND WAY I LEARNED- using corrolated subquery in order by

SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        205 Higgins
        206 Gietz
        200 Whalen
	..
	..
	..
106 Rows selected

SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("D"."DEPARTMENT_ID"=:B1)

If you are carefull enough you would have seen that plans are different and rownumbers are different ?

What is the difference between 2?

Second one doesn’t care if there isnt any matching row on employees table. So it is something like outer join
So if you want the same result with the first method you need outer join

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	...
	...
106 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

As you see they have different plan. Cost of corrolated one is better then the outer join version
but consistent gets are higher for corrolated one. 

<strong>I think this needs more  investigation if I find something I will write as a new blog post</strong>  (to be honest I just realised. One more benefit of blogging; paying more attention to what you learn/write)
1
SQL> set autotrace traceonly;
SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("D"."DEPARTMENT_ID"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL>

This is the end of this series as I promised.Please check the SQL Reference for remaining interesting things I bet you will find one no matter how much you think you know.

« Newer PostsOlder Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 202 other followers