Oracle hidden costs revealed, Part2 – Using DTrace to find why writes in SYSTEM tablespace are slower than in others | Tanel Poder Consulting

[utterances-bot]

Oracle hidden costs revealed, Part2 – Using DTrace to find why writes in SYSTEM tablespace are slower than in others | Tanel Poder Consulting

_I have written two posts in one, about a performance issue with writes in system tablespace and introduction of a little DTrace stack sampling script.
_
Have you noticed that DML on tables residing in SYSTEM tablespace is slower than tables in other tablespaces?
Here’s an example, I’ll create two similar tables, one in USERS tablespace, other in SYSTEM, and inset into the first one (Oracle 10.2.0.3 on Solaris x64):
SQL> create table t1(a int) tablespace USERS;
Table created.
SQL> create table t2(a int) tablespace SYSTEM;
Table created.
SQL> exec for i in 1..100000 loop insert into t1 values(i); end loop;
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.09
Insert into table in USE

https://tanelpoder.com/2008/09/02/oracle-hidden-costs-revealed-part2-using-dtrace-to-find-why-writes-in-system-tablespace-are-slower-than-in-others/

[somroy31883]

Hello Sir,

getting error while using dstackprof.sh ..... kindly help

[root@*-mgt dtrace]# ./dstackprof.sh 32545

DStackProf v1.02 by Tanel Poder ( http://www.tanelpoder.com )
Sampling pid 32545 for 5.000 seconds with stack depth of 100 frames...

dtrace: invalid probe specifier
profile-1001
/pid == $target/ {
@U[ustack(100)] = count();
@k[stack(100)] = count();
}
tick-1sec
/i++ >= 5.000/ {
exit(0);
}
END {
printa(@U);
printa(@k);
}
: floating-point constants are not permitted
0 samples with stack below

[tanelpoder]

Interesting... I guess the /i++ >= 5.000 { part is causing trouble (should be just 5 not 5.000). I guess the 5.000 comes from the shell somehow - the default seconds is 5, but the .000 gets added somewhere if using default values.

If you specify the number of seconds to sample on command line, what happens:

./dstackprof.sh 32545 5

Btw what OS and exact version is this? I wrote and tested this script over 16 years ago on Solaris :-)

[somroy31883]

Thanks in tons !! Sir, Today , I feel blessed to see you post. I am a big fan of yours .. you are "GOD OF ORACLE " Back to issue. I had tried with ./dstackprof.sh 32545 5 but no luck. Linux version ***@***.*** ~]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.9 (Maipo) ***@***.*** ~]$ ***@***.*** dtrace]# uname -a Linux sv2lxex8db04-mgt.corp.equinix.com 4.14.35-2047.518.4.3.el7uek.x86_64 #2 SMP Mon Apr 17 14:08:26 PDT 2023 x86_64 x86_64 x86_64 GNU/Linux ***@***.*** dtrace]# sharing output of ./dstackprof.sh 32545 5 .. still not able to see output of query [image: image] 2. ./qer_trace but not able to see any out of query (executed on sqlplus ) [image: image] Thanks Roy

…

On Tue, Feb 6, 2024 at 9:01 PM Tanel Poder ***@***.***> wrote: Interesting... I guess the /i++ >= 5.000 { part is causing trouble (should be just 5 not 5.000). I guess the 5.000 comes from the shell somehow - the default seconds is 5, but the .000 gets added somewhere if using default values. If you specify the number of seconds to sample on command line, what happens: ./dstackprof.sh 32545 5 Btw what OS and exact version is this? — Reply to this email directly, view it on GitHub <#35 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQW3ZHC5APWBX2HGSQ4JKY3YSJEE7AVCNFSM6AAAAABC3WWJ5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZQGA2TOMJYGA> . You are receiving this because you commented.Message ID: ***@***.***>

[tanelpoder]

Thanks for the kind words. I wrote this script for Solaris and haven't even tested on Linux. Oracle's DTrace on Linux wasn't as fully functional back when I tested it (years ago). By now, latest Oracle's DTrace 2.0 on Linux uses eBPF as a tracing backend anyway - so I suggest you learn what BPF tools can offer you in Linux world instead. A good starting point is https://brendangregg.com/ebpf.html . He has an entire book on the subject too.

[somroy31883]

Definitely Sir, Really appreciate your response and time (spare some of your precious time) 😍 Once again ... seriously for me it is my dream to communicate with "God of Oracle". Whatever, I am because of you . I want to dedicate all my success to your feet ( who unleashes Oracle hidden treasure and makes it free for all ) . Always remain grateful to you !! Thanks & Regards

…

On Wed, Feb 7, 2024 at 9:07 AM Tanel Poder ***@***.***> wrote: Thanks for the kind words. I wrote this script for Solaris and haven't even tested on Linux. Oracle's DTrace on Linux wasn't as fully functional back when I tested it (years ago). By now, latest Oracle's DTrace 2.0 on Linux uses eBPF as a tracing backend anyway - so I suggest you learn what BPF tools can offer you in Linux world instead. A good starting point is https://brendangregg.com/ebpf.html . He has an entire book on the subject too. — Reply to this email directly, view it on GitHub <#35 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQW3ZHBYMWBB7Q6D72N7SRTYSLZHNAVCNFSM6AAAAABC3WWJ5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZRGIYTQNBSGE> . You are receiving this because you commented.Message ID: ***@***.***>