DUMMY!

A Thought(idea) like DUMMY on DUAL table (Oracle Database) ;)

A DUAL has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X.

SQL> desc dual

 

 Name                                                                    Null?    Type

 -----------------------------------------------------------------------------

 DUMMY                                                                            VARCHAR2(1)

We start a thought like "X"... 

SQL> select * from dual;

D

-

X

Anyway, We can make any thought(idea), and No limited;)

SQL> select 1 from dual;

         1

----------

         1

SQL> select sysdate from dual;

SYSDATE

---------

29-MAR-09

SQL> select 1 + 1 from dual;

       1+1

----------

         2

SQL> select 'New Idea' from dual;

'NEWIDEA

--------

New Idea

that show we have many thoughts, but start from dummy (on dual).

So, Anybody can make idea (thought), and do it.

Just ... Forcing a Specific Table Join Method

Oracle database, we can use hints to force a specific join method.

USE_NL = NESTED LOOPS join
USE_MERGE = SORT-MERGE join
USE_HASH = HASH join

Before use each of hints with table join, we should know about join method.

On 10g Performance Tuning Tips & Tech book told about a quick view of the primary join type.

Category	NESTED LOOPS join	SORT-MERGE join	HASH join
Hint	USE_NL	USE_MERGE	USE_HASH
Resource Concerns	CPU, disk I/O.	Memory, temporary segments.	Memory, temporary segments.
Features	Efficient with highly selective indexes and restrictive searches. Used to return the first row of a result quickly.	Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. Can work with limited memory.	Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.
Drawbacks	Very inefficient when indexes are missing or if the index criteria are not limiting.	Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all row are found.	Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk.

I think that's help us determine hints before we will force a specific table join.

Example to use hints to force a specific table join method:

>>> Create Tables

SQL> create table A as select * from dba_objects;

Table created.

SQL> create table B as select * from user_objects;

Table created.

>>> Create Index

SQL> create index A_I01 on A(object_id,object_name);

Index created.

Begin Testing: Use hints to force a specific table join method.

SQL> set autot trace explain

SQL> select /*+ ordered */ * from A , B where A.object_id=B.object_id ;

Execution Plan

----------------------------------------------------------

Plan hash value: 652036164

-----------------------------------------------------------------------------------

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time

-----------------------------------------------------------------------------------

0 SELECT STATEMENT 456 150K 475 (1) 00:00:06

* 1 HASH JOIN 456 150K 6448K 475 (1) 00:00:06

2 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

-----------------------------------------------------------------------------------

SQL> select /*+ use_nl (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

----------------------------------------------------------

Plan hash value: 4149619786

-------------------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

-------------------------------------------------------------------------------------

0 SELECT STATEMENT 456 150K 916 (1) 00:00:11

1 TABLE ACCESS BY INDEX ROWID A 1 177 2 (0) 00:00:01

2 NESTED LOOPS 456 150K 916 (1) 00:00:11

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

* 4 INDEX RANGE SCAN A_I01 1 1 (0) 00:00:01

-------------------------------------------------------------------------------------

SQL> select /*+ use_merge (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

----------------------------------------------------------

Plan hash value: 3028542103

------------------------------------------------------------------------------------

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time

------------------------------------------------------------------------------------

0 SELECT STATEMENT 456 150K 1515 (1) 00:00:19

1 MERGE JOIN 456 150K 1515 (1) 00:00:19

2 SORT JOIN 456 72960 4 (25) 00:00:01

3 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

* 4 SORT JOIN 34930 6037K 14M 1511 (1) 00:00:19

5 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

------------------------------------------------------------------------------------

SQL> select /*+ use_hash (A B) */ * from A , B where A.object_id=B.object_id ;

Execution Plan

----------------------------------------------------------

Plan hash value: 4090908061

---------------------------------------------------------------------------

Id Operation Name Rows Bytes Cost (%CPU) Time

---------------------------------------------------------------------------

0 SELECT STATEMENT 456 150K 158 (2) 00:00:02

* 1 HASH JOIN 456 150K 158 (2) 00:00:02

2 TABLE ACCESS FULL B 456 72960 3 (0) 00:00:01

3 TABLE ACCESS FULL A 34930 6037K 154 (1) 00:00:02

---------------------------------------------------------------------------

when use table join, we should determine some initialization parameters to join + performance ;)
pga_aggregate_target : improve performance of all sorts.
db_file_multiblock_read_count : good for full table scans.

Move LOBSEGMENT to different tablespace

When we created table by using "XMLTYPE" datatype...

create table tmp01 (id number, xml_data XMLTYPE) tablespace users;

that made us find LOBSEGMENT Segment Type.

select segment_name,segment_type,tablespace_name from user_extents where segment_type='LOBSEGMENT';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

------------------------------ ------------------ ------------------

SYS_LOB0000349415C00003$$ LOBSEGMENT USERS

Whenever we need to move LOBSEGMENT.

Before Move, Get informations:

select TABLE_NAME, COLUMN_NAME, STORAGE_TYPE from USER_XML_TAB_COLS where table_name='TMP01';

TABLE_NAME COLUMN_NAME STORAGE_TYPE

------------------------------ ------------------------------ -----------------

TMP01 XML_DATA CLOB

column table_name format a30

column column_name format a30

column segment_name format a30

select table_name,column_name,segment_name from user_lobs;

TABLE_NAME COLUMN_NAME SEGMENT_NAME

------------------------------ ------------------------------ ------------------------------

TMP01 SYS_NC00003$ SYS_LOB0000349415C00003$$

After that,use ALTER TABLE MOVE LOB command...

Example:

alter table tmp01 move lob (XML_DATA.XMLDATA) store as SYS_LOB0000349415C00003$$ (tablespace USERS);

Or

alter table tmp01 move lob (SYS_NC00003$) store as SYS_LOB0000349415C00003$$ (tablespace USERS);

If you need to move LOBSEGMENT of some tables for moving extents on tablespace(Assume: you need to resize datafile, but can't.... you want to do like "alter table ... move ")

It's a better way if you move it(lobsegment) to another tablespace else (different tablespace).

alter table tmp01 move lob (XML_DATA.XMLDATA) store as SYS_LOB0000349415C00003$$ (tablespace NEW_TABLESPACE);

alter table tmp01 move lob (SYS_NC00003$) store as SYS_LOB0000349415C00003$$ (tablespace NEW_TABLESPACE);

Masquerade sender domain (rewrite) on sendmail

I've worked like DBA 2.0 a long time.

So, I have been System Administrator in the same time. I think this a good way for me, Becuase I like it and happy ;)

I'd like to write idea to configure "masquerade sender domain" on sendmail MTA.  

Masquerade sender domain (rewrite) on sendmail??? 

why i had to do like that ... configure sendmail (Because some MTA servers, that check look up domain), 

My sender was "oracle@db01.domain.com"... 

DNS Servers on the world know "domain.com", But don't know "db01.domain.com"

Start...

- modified "sendmail.mc" file in /etc/mail PATH

FEATURE(masquerade_envelope)dnl

FEATURE(genericstable, 'hash -o /etc/mail/genericstable')dnl

FEATURE(generics_entire_domain)dnl

GENERICS_DOMAIN('domain.com')dnl

- use m4 to make new sendmail.cf (backup sendmail.cf file before)

$ m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

- created "genericstable" file in /etc/mail PATH

Example in genericstable file:

@db01.domain.com      %1@domain.com

that mean, every senders (%@dba01.domain.com) were masqueraded %@domain.com

- makemap genericstable file...

$ makemap -r hash /etc/mail/genericstable.db <  /etc/mail/genericstable

- restarted sendmail... (hope no error)

$ /etc/init.d/sendmail   restart

- Tested and checked..., recipients should see senders be "%@domain.com", when sender were db01.domain.com domain  on mail client.

when check header email:

Return-Path:

Delivered-To: recipient@domain.com

Received: (qmail 20395 invoked from network); 13 Mar 2009 12:32:37 +0700

Received: from ... (HELO mxlocal) 

          (envelope-sender )

          by mailrelay ) with SMTP

          for ; 13 Mar 2009 12:32:37 +0700

.

.

.

Received: from db01.domain.com

by MTA_server (8.13.1/8.13.1) with ESMTP id n2D5Wbal017581

for ; Fri, 13 Mar 2009 12:32:37 +0700

Received: (from oracle@localhost)

by db01.domain.com (8.13.1/8.13.1/Submit) id n2D5Wa6G016663

for recipient@domain.com; Fri, 13 Mar 2009 12:32:36 +0700

From: oracle@domain.com

Slack

That is a good idea! on that<blog> After I read... Hey! what should i do with my free time?

"Use my free time to improve my English".

Anyway I'd used my native language at the Office, Actually all day. So How?

Maybe I should think about it... and make something, that can help;) learn?

"Learn something. Become an expert" Actually I'd like to be...

I'm not sure about that, when i will be... .

I'd used more free time on Socials, Forums and Blog , and Learning from some Documents! Perhaps that help me become an expert... some days!

Finally, that's not importance anyway, Because I am very happy with what I do, and that's the best thing i do! ...

read more | digg story

SQL*Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time

Today, my colleague told me, Why I can't use "sqlplus" (Oracle client) on my application server connect your database, But I can use "tnsping"... And I'd ever connected!

I remoted on this server and found "sqlplus" hung ... and sqlplus used more CPU %

$ ps aux

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND

oracle   12722 96.4  0.2 19560 4600 pts/5    R    15:36   0:06 sqlplus

So, found out on metalink... (338461.1)

SQL*Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time (Linux x86)

Used "strace" :

$ strace sqlplus -V 2>&1 |less

execve("/oracle/10.2.0/client/bin/sqlplus", ["sqlplus", "-V"], [/* 31 vars */]) = 0

uname({sys="Linux", node="host01", ...})  = 0

brk(0)                                  = 0x804a000

access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)

.

.

.

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

It is looping on the times() function.

There have been cases where problem occurs when uptime reaches 60 days and others as long as 248 days.

In addition to sqlplus, it has been reported that the netca and dbca tools also hang.

Solution from metalink...

Select one of the following two solutions:

1) Apply one-off patch available for 10.2.0.1.

   a. Download one-off patch off Metalink:

       Patch 4612267

       Description OCI CLIENT IS IN AN INFINITE LOOP WHEN MACHINE UPTIME HITS 248 DAYS

       Product CORE

       Release Oracle 10.2.0.1

   b. To apply patch on Instant Client install, please follow instructions documented in the OCI manual.

        You can find this in:

        http://www.oracle.com/pls/db102/db102.show_toc?which=main&partno=b14250&maxlevel=2&section=&expand=3216

        under "Patching Instant Client Shared Libraries on Linux or UNIX".

2)  Apply Patchset 10.2.0.2  or higher. 

      According to Bug 4612267, this bug is fixed in version 11, and backported to 10.2.0.2 patchset.

srvctl start service; PRKP-1030 : Failed to start the service...ORA-44305: service ... is running

After I stoped  S01 service on node4 by srvctl command-line. It's OK .

$ srvctl stop service -d DB -s S01 -i DB4

After that, I need to start S01 service on this (node4) node (DB4)

$ srvctl start service -d DB -s S01 -i DB4

PRKP-1030 : Failed to start the service S01.

CRS-0215: Could not start resource 'ora.DB.S01.DB4.srv'

that's make me!... confused. So, I tried another idea... (try to force stop)

$ srvctl stop  service -d DB -s S01 -i DB4

PRKP-1065 : Service S01 is already stopped on instance DB4.

$ srvctl stop  service -d DB -s S01 -i DB4 -f 

PRKP-1065 : Service S01 is already stopped on instance DB4.

$ srvctl start service -d DB -s S01 -i DB4

PRKP-1030 : Failed to start the service S01.

CRS-0215: Could not start resource 'ora.DB.S01.DB4.srv'

that's not help me anything.

Check... Use "crs_stat"

ora.DB.S01.DB3.srv                         ONLINE     ONLINE on node03  

ora.DB.S01.DB4.srv                         ONLINE     OFFLINE on node04  

ora.DB.S01.cs                                ONLINE     ONLINE on node03  

So, I had some idea... I checked imon_DB.log file at $ORACLE_HOME/log/hostname/racg/* PATH on node, I used "srvctl start service -d DB -s S01 -i DB4"

2009-03-06 15:33:34.912: [    RACG][1273112928] [28413][1273112928][ora.DB.S01.DB4.srv]: clsrcsnstartsrv: service name S01 already exists

2009-03-06 15:33:34.913: [    RACG][1273112928] [28413][1273112928][ora.DB.S01.DB4.srv]: CLSR-0002: Oracle error encountered while executing clsrcsnstartsrv : execute2

2009-03-06 15:33:34.913: [    RACG][1273112928] [28413][1273112928][ora.DB.S01.DB4.srv]: ORA-44305: service S01 is running

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_SERVICE", line 444

ORA-06512: at "SYS.DBMS_SERVICE", line 365

ORA-44305: service S01 is running 

What's going on?

So, I connected database on DB4 instance (node4)

$ env | grep ORACLE_SID

ORACLE_SID=DB4

$ sqlplus / as sysdba

SQL>  select instance_name from v$instance; 

INSTANCE_NAME

----------------

DB4

SQL> show parameter service_name 

NAME                                 TYPE        VALUE

------------------------- ----------- --------

service_names                        string      S01, DB

Oh, This Instance  has S01 service online. 

My idea used DBMS_SERVICE.STOP_SERVICE to stop service (S01) on this instance.

SQL> exec DBMS_SERVICE.STOP_SERVICE('S01','DB4');

PL/SQL procedure successfully completed.

SQL> show parameter  service_name  

NAME                                 TYPE        VALUE

------------------------- ----------- --------

service_names                        string      DB

And then use "srvctl" command to start again...

$ srvctl start service -d DB -s S01 -i DB4

Wow, It worked... no error

And then check...

Use "crs_stat"

ora.DB.S01.DB3.srv                         ONLINE     ONLINE on node03  

ora.DB.S01.DB4.srv                         ONLINE     ONLINE on node04  

ora.DB.S01.cs                                ONLINE     ONLINE on node03  

And...

$ sqlplus / as sysdba

SQL>  select instance_name from v$instance; 

INSTANCE_NAME

----------------

DB4

SQL> show parameter service_name 

NAME                                 TYPE        VALUE

------------------------- ----------- --------

service_names                        string      S01, DB

... It's OK, thanks OCP & OCE help me this idea!

Enjoy!

Discussion, the way to improve knowledge

It's just a idea...

Discussion is a good way to improve knowledge. How? and Where? that it's a easy way. Just join some communities and use some time with discussion.

with Oracle...

OTN Forums, that's the way to ask, read and discuss something about Oracle Products. If you work in Oracle... I think you should to join this website. It can help more...

Oracle Community by Eddie Awad, you can discuss as well. Anyway I like to say 'hi' to peolple working on Oracle Products ;) (it's more than discussion;  join, communicate and discuss) 

If you work in oracle a long time, I think you know OraFaq,  Hey! join it...

If you can access meta link, don't find Bugs(ora- 0600)...  only, Join and discuss on Oracle Support Communities. I think it's a good one, But You need to be member on meta link.

Actually you can find many websites to join and discuss (some Oracle User Groups...) ... Or use some social networks to discuss (facebook, linkedin , blah blah... oracle mix, oracle wiki). Perhaps you could read some oracle's blogs (some expert's blogs) and then discuss ("comment")... I think they'll feel good, when people read and discuss(comment) on their blogs.

With Another ITs knowledge... Just find, join, commicate and discuss.

Just enjoy! It's gooooood for you anyway.

;)