Relax Time! and Work... again

I was on vacation at Phu Kradung (wiki). That's a great location...and great time. No Oracle, No Internet... that made me Enjoy...

It's time to work and online again...

I thought... if i want to find what oracle process make more CPU... and trace ...it!

Maybe I should begin with OS process (unix/linux command), check any OS process make more percentage CPU.

$ ps -e -o pcpu,user,pid,args | sort -k 1 | grep oracle | tail

.

.

.

3.2 oracle 32657 oracle[ORACLE_SID](LOCAL=NO)

3.9 oracle 3834 ora_j000_[ORACLE_SID]

15.3 oracle 16463 oracle[ORACLE_SID](LOCAL=NO)

Or

$ top

.

.

.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

16463 oracle 16 0 7824m 1.6g 1.6g S 34 10.2 1:06.41 oracle

28150 oracle -2 0 7834m 6.6g 6.6g S 9 42.4 3914:51 oracle

I knew os process... and I want to trace it, So sqlplus command with "oradebug" trace 10046 event on OS process can help! (Example: oradebug trace oracle process)

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug TRACEFILE_NAME

$ORACLE_BASE/admin/[ORACLE_SID]/udump/[ORACLE_SID]_ora_16463.trcc

SQL> oradebug unlimit

SQL> oradebug event 10046 trace name context forever, level 12

I should find trace file.

$ cd $ORACLE_BASE

$ cd admin/[ORACLE_SID]/udump

$ ls *16463*

[ORACLE_SID]_ora_16463.trc

and then use tkprof command-line .

$ tkprof [ORACLE_SID]_ora_16463.trc /tmp/file.out sys=no

After that I can investigate a problem on /tmp/file.out file, Check SQL statement and resolve...

Example:

$ less /tmp/file.out

.

.

.

SELECT COUNT(*)

FROM

"DTABLE" "A1" WHERE "A1"."A"=:1 AND "A1"."DSTART"<=TO_DATE(:2,

'dd/mm/yyyy hh24:mi:ss') AND "A1"."DSTOP">=TO_DATE(:3,'dd/mm/yyyy

hh24:mi:ss')

call count cpu elapsed disk query current rows

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

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 4 1.85 3.41 1 93080 0 2

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

total 8 1.86 3.41 1 93080 0 2

.

.

.

Oh! I forgo: disable 10046 event

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug event 10046 trace name context off

That's easy to check and trace...

Oracle Insert Faster, How?

When I would like to Insert Faster on Oracle database. 

Using High I/O, High CPU and High Memory, they're a great to do.

Anyway What I should to do on Database,  (SQL) ... on Table... on Index ???

About my issue...

I have many clients and many sessions, they connected and inserted data on (TABLE) Oracle in the same time.

*** Not Use CTAS***

In My Idea I use bigger block size ,nolgging, no archivelog, no indexing (Actually I use 3 indexes) and Increase Buffer Cache.

I can't not use "APPEND", Becasue In the same session i insert only one row.

INSERT INTO TABLE_NAME VALUES(...);

Example:

begin

for x in 1 ..100

loop

insert into t01 values(x, 100);

commit;

end loop;

end;

/

Elapsed: 00:00:00.16

begin

for x in 1 ..100

loop

insert /*+ append */ into t01 values(x, 100);

commit;

end loop;

end;

/

Elapsed: 00:00:01.15

Enough? I thought No! about idea... So, I'd like to know another idea else, So I posted to ask on Oracle Community.

Thank You many people to post their suggestion.

I would like to share their idea to other.  Perhaps that can help... other

Gerald Venzl suggests:

1) Bulking (if you want to make it fast, do bulking!) which reduces commits and commit time

-  In the same session i insert only one row, that can not...help

2) Enough ITL on the block (INITRANS)

3) Depending on the size of a row: Blocksize, PCTFREE

4) No Indexing

5) No integrity checks (Foreign keys, not null constraints)

6) Buffer cache (if it's to small you'll early end up with I/O)

7) And Partitioning

Gerald's Suggestion great.

Filipe Martins 's Idea helpful with his real experience, he suggests good idea from his experience to create table with partitioning table.

He told i should create table with range and list ... 

Assume, I have clients (C1, C2, C3, ...,Cn) to connect and insert data, So create table with range partition on (Client)

So: 

On C1 clinet .. insert into table_name partition (Partition_C1) values() ;

On C2 clinet .. insert into table_name partition (Partition_C2) values() ;

.

.

On Cn clinet .. insert into table_name partition (Partition_Cn) values() ;

good!

TongucY help me what i should to do before... anyway , that's make SQL TRACE event 10046 level 8. and then use tkprof to check.

Arie told i should to know... It's inserted from other table or one row...

Finally, Joe Goodman suggests:

1. If Inserts are comming from many processes then contention might occur due to

1.1. Buffer Busy wait to to contention for update access to same table block. This

may be checked in v$segment_statistics. If so then smaller block sizes or 

higher PCTFREE may help reduce number of rows per block but using ASSM

will help as well.

- About smaller block size. I'n not sure about this idea, Becasue data is inserted ..., no update

1.2 If same process is inserting many rows using CTAS, or insert into... select 

from then parallel execution should speed the process along. 

-  In the same session i insert only one row, that can not...help

1.3 If indexes exist on the table then the problem may be cause by Buffer Busy 

Wait on the right hand block of the index also visible in v$segment_statistics.

If so then one may either use Reverse Key Indexes (if the index is not needed for

range scan access paths) or use Hash partitioned Global index (if you have a 

license for partitioning) or possibly both as these reduce contention on the right

had index block.

Many Ideas, I'm sure to improve performance insert faster... Thank You!

About my issue I'll start with new table:

- Partitioning Table, range ... perhaps list

- increase INITRANS...

- reduce number of Indexes

- try to no constraint

- increase buffer cache and check about variable on sql statement help reduce library cache size

- bigger block size ... actually not sure, I have to test before. 

- Perhaps use nologging (or no archivelog)

- finally, use SQL TRACE 10046 to trace about waited and ... tune!

...