Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts

Thursday, February 21, 2013

Oracle on AIX - where's my cpu time ?

This story begins couple of weeks ago when I was asked to look on performance problem for critical processes before go-live. There were differences between run times between different test systems and between runs with 10046 trace enabled or not.

First of all I started with tracing issue. I have checked server CPU and disk utilization using NMON (tool name is important here). There wasn’t any problem with CPU utilization – or at least this is what I was thinking at that time. Tracing was enabled with level 12 (bind and wait) and it generate 2 GB of trace file for 30 min work. First I decided to limit trace level to 8 and give it another try. In the meantime, I double-checked with Oracle community on Twitter (thanks @CaryMillsap and @MartinBerx) that there is not any know Oracle bugs related to trace and performance. Changing tracing level from 12 to 8 solve issue – I mean there no difference between process running with trace and without trace and also limit size of the file to 130MB from 2 GB (don’t ask ?)

In next step I focused on analyze differences between run times on two systems. It is quite easy to do with trace files and I was hoping for short solution.

My first surprise was that profile of SQL in both trace file were almost identical – there was not OS calls (waits) and query spend whole time on CPU. Execution plan were same and amount of data was same as well. Elapsed time for both executions was different and was equal to wall clock time recorded in process logs. To clarify my finding I will focus now on one UPDATE statement. 

When I compared elapsed time with CPU time for EXEC db call I have seen a huge gap between both values – on faster system ratio between CPU time / elapsed time was around 60 % and slower one around 37 % - remember there is no OS calls (wait events)
According to all my knowledge if there is no OS calls CPU time should be equal to elapsed time with probably some difference related to measurement or rounding. If there are differences, there is a huge chance that Oracle process was per-empted from CPU and was waiting in CPU queue. Well according to NMON stats system was utilized in 30 % only so why Oracle process was to wait in queue if there are lot of CPU’s free ? So maybe NMON is reporting system utilization it in wrong way and server itself is overloaded ? That question had to be answered.
I was lucky enough and I had a chance to run Oracle processes on idle server when no one else was connected. I got small LPAR with one core and 4 logical CPU’s as AIX SMT was enabled. I created a small test 

declare
 a number;
 begin
 for i in 1..100000 loop
 select count(*) into a from dba_users;
 end loop;
 end;
/
and I was surprised with results. I doesn’t trust nmon output anymore so I run topas  observing CPU utilization – it showed around 63 % of utilization for my session ! why not 95 – 100 % ? there was nothing else running there ?
I was even more surprised when I checked trace file – here is output formatted by tkprof for query I was looking at

SQL ID: 821zd9kr9k4yr Plan Hash: 3759210642

SELECT COUNT(*) 
FROM
 DBA_USERS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      1.66       2.73          0          0          0           0
Fetch   100000     53.75      86.03          0    4200000          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001     55.41      88.77          0    4200000          0      100000
55.51 / 88.71 * 100 % = 62.5 %

Hey ! Dude where is my CPU time ? Did my process spend 33 s in CPU run queue ? on idle box ?

I needed more data and at that stage I had three hypothesis – Oracle bug, configuration problem or SMT option. I had funny feeling that SMT option is related to these results somehow but I checked MOS looking for bugs and asked Twitter friends to run same test on other system. (@MartinDBA  confirmed my findings - Thanks for your help).

Next step was to disable SMT option and I learn that AIX is so nice that you can do it online.  I rerun my test – Bingo – topas is showing 95 – 98 % of CPU utilization for my Oracle process and trace file is confirming it

SQL ID: 821zd9kr9k4yr Plan Hash: 3759210642

SELECT COUNT(*) 
FROM
 DBA_USERS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      2.69       2.78          0          0          0           0
Fetch   100000     85.08      88.33          0    4200000          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001     87.78      91.12          0    4200000          0      100000
87.78 / 91.12 * 100 % = 96.3 %

This is what I was looking for. Based on both observations I was sure that there is no bug’s in tracing functionality for this version of Oracle (11.2.0.3) and I was sure that Oracle process can spin CPU to almost 100 %. There was still question – why there is a huge gap in cpu to elapsed time with SMT enabled. I start looking for IBM documentation and bugs and after some time I found that page - Understanding Processor Utilization on Power Systems - AIX and this picture (linked from IBM wiki page)



So for new PowerPC 7 if your process is running 1 sec on logical CPU (thread)  CPU utilization (user plus system) is 0.63 sec only. If you compare this one with no SMT enabled (or previous version of CPU) you can learn that if process was running for 1 sec on logical CPU it used to be reported as 1 sec of time used by CPU in user and system mode.

How tools are calculating CPU utilization for one process for  1 sec ? you need to divide CPU time used by this processes by 1 sec. So for pre-powerpc 7 or for power pc 7 without SMT

1 s on cpu / 1 s real time * 100 % = 100 %

For powerpc7 with 4 SMT

0.63 on cpu / 1 s * 100 % = 63 %

Have I seen this number before ? Yes this is CPU time taken from trace file divided by elapsed time ! My mystery has been solved – 63 % is now 100 % at least for that version of AIX and Oracle.


Where is a problem ? 

If you are using Cary Millsap Method-R methodology to diagnose performance problems you need to remember that unaccounted time for trace files will be impacted by this feature/bug/you name it.

ps.
I think this post will be updated when I will have more details.

regards,
Marcin

Sunday, July 1, 2012

Average Active Session in SQL*plus with refresh

Recently when I hit performance issues and figure out that OEM agent is mis-configured for that host I wish I have script to display live Average Active Session in SQL*Plus. Of course there is a plenty of other great tools like Tanel Poder’s Snapper or Tanel and Adrian Billington MOATS.
MOATS could be a answer for my needs but it required some objects to be created in database. From other side Snapper is using dynamic objects only but it is not displaying history so I can’t see at a glance if system performance has been improved or not.
I decided to answer my needs and I have created SQL*Plus script displaying AAS history using only dynamic objects like Snapper. I would like to thanks Tanel and Adrian for inspiration how to build SQL*Plus active output scripts.

Here is a sample screenshot.


Average Active Session is calculated based on v$session sampling and output is divided into three event category – CPU, DISK I/O and OTHER. Technically it is possible to add more classes but it become more tricky to read it from screen – so I think these three are a good balance between knowing what is going on and visibility. For deeper investigation you can use Snapper.

This tool is using two scripts (both have to be in one directory):
  • runtopaas.sql - is a main script to parse run attributes and specify a run environment for topaas.sql script. It is calling topaas.sql 100 times
  • topaas.sql - is sampling v$session every 1 s for time specified in refresh rate parameters and keep it in PL/SQL collection.  At the end AAS (divided into 3 sections: CPU, Disk I/O and other) is calculated and displayed on screen.  In addition to that AAS results are added to bind variables together with sample time. When topaas.sql is called next time it is reading data from bind variables and it allow it to have history of AAS from past and display it on screen. Default configuration allow to display 100 data point
How to use it:
  • Change SQL*Plus window / screen terminal to 45 characters height and 150 characters wide
  • Run in SQL*Plus window:
    • SQL> @runtopaas.sql aas:refresh rate  - it will specify refresh rate (ex. 15 s) and with 100 samples it allow to keep 25 min of AAS in SQL*Plus window.If script will be started again after 100 cycles or after user break in this same session it will still be able to display historical data
    • SQL> @runtopaas.sql aas:refresh rate:reset - like above but historical data are cleared
    • SQL> @runtopaas.sql aas:refresh rate:max aas - like above but maximum value of AAS (y axis) is set by user
    • SQL> @runtopaas.sql aas:refresh rate:max aas:reset - like above but historical data are cleared
Examples:

SQL> @runtopaas aas:15


Yes there was a problem - AAS around 600 is not a normal one. When issue has been fixed I want to reset historic data and run script again


SQL> @runtopaas aas:15:reset



after some time 



You can download both scripts here - Github repo.  


Let me know if this tool is useful for you or you if you found any problems with it.
regards,
Marcin

Thursday, April 19, 2012

Oracle and HugePages

I have got some very bad experience of running Oracle with quite huge SGA ( 60 GB ) on RedHat 5.6 without HugePages. Host has been completely blocked and I was wondering what was a root cause.
I have used following test configuration:
Host : 96 GB, 2 sockets 12 cores 24 threads

Oracle: 11.2.0.2
SGA_TARGET = 60 GB
I have also set "pre_page_sga" to be sure that all memory will be allocated during instance startup.

I have started instance without HugePages and this is a output from meminfo
testbox1$ cat /proc/meminfo
MemTotal:     98999832 kB
MemFree:      21527276 kB
Buffers:        933668 kB
Cached:       69720980 kB
SwapCached:          0 kB
Active:       64168548 kB
Inactive:      6802180 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     98999832 kB
LowFree:      21527276 kB
SwapTotal:     2096472 kB
SwapFree:      2096472 kB
Dirty:            1548 kB
Writeback:           0 kB
AnonPages:      316056 kB
Mapped:       62708540 kB
Slab:           599964 kB
PageTables:    3679796 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  50572388 kB
Committed_AS: 64086964 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    264440 kB
VmallocChunk: 34359473527 kB
HugePages_Total:  1000
HugePages_Free:   1000
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
As you can see Linux kernel used around 3.5 GB only to create all internal structures for default pages (parameter PageTables). Now let check same with HugePages
testbox1$ cat /proc/meminfo
MemTotal:     98999832 kB
MemFree:      14360908 kB
Buffers:        916216 kB
Cached:        9453272 kB
SwapCached:          0 kB
Active:        2191376 kB
Inactive:      8286108 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:     98999832 kB
LowFree:      14360908 kB
SwapTotal:     2096472 kB
SwapFree:      2096472 kB
Dirty:            3696 kB
Writeback:           0 kB
AnonPages:      230380 kB
Mapped:          76648 kB
Slab:           594156 kB
PageTables:      14368 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  14841956 kB
Committed_AS:  1102392 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    264404 kB
VmallocChunk: 34359473527 kB
HugePages_Total: 35893
HugePages_Free:   5245
HugePages_Rsvd:     73
Hugepagesize:     2048 kB
Now it is much better - PageTable has around 15 MB. If we compare this to 3.5 GB from previous output this a proof that non HugePage environment is wasting lot of memory. But wasting memory is not a biggest issue here. Let’s try to connect to database and run simple query
testbox1$ time sqlplus -s / as sysdba << EOF 
select * from dual;
> exit; 
> EOF

D
-
X


real    0m9.645s
user    0m0.006s
sys     0m0.007s
It took 9.645 sec to connect and run select – so where whole time has been spent?
When I used strace to find a solution it wasn’t 100 % successful attempt – there is a matching gap between chdir and mmap calls – so it looks like time is spend on CPU
15:35:57.494002 stat("/opt/app/oracle/product/11.2.0.2/db1/lib", {st_mode=S_IFDIR|0755, st_size=12288, ...}) = 0 <0.000008>
15:35:57.494044 chdir("/opt/app/oracle/product/11.2.0.2/db1/dbs") = 0 <0.000009>
15:36:07.105693 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b5320e95000 <0.000014>
15:36:07.105758 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b5320eb8000 <0.000007>
15:36:07.105831 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b5320edb000 <0.000008>
Maybe this is problem for local connection only. Let’s try wit listener:
testbox1$ time sqlplus -s user1/user1@log2perf << EOF 
select * from dual;
> exit;
> EOF

D
-
X

real    0m14.340s
user    0m2.300s
sys     0m0.023s
This time it was even worse.

Same tests with HugePages:
testbox1$ time sqlplus -s / as sysdba << EOF 
select * from dual;
> exit;
> EOF

D
-
X

real    0m0.437s
user    0m0.014s
sys     0m0.001s

testbox1$ time sqlplus -s user1/user1@log2perf << EOF 
select * from dual;
> exit;
> EOF

D
-
X

real    0m2.547s
user    0m2.247s
sys     0m0.016s
With HugePage's connection time is much (7 do 20 times) faster. So first pitfall with not HugePages configuration with big SGA is a connection time which is much longer than connection time for configuration with HugePages.

For more information about connection time see update below.

I decided to go further and check DB performance after session has been established. Do to so I have used Kevin Closson SLOB  script to test number of logical reads per sec but I have to do some modification to this great tool.
First modification was related to non huge page environment. SLOB is generating AWR snapshots between and after running workload and at the end it end up with AWR report. As connection time is an issue for non Huge Page environment I have to be sure that SLOB will run two AWR snapshots from one session. I have added one more script which is started like any other workers by semaphore and this script is taking two AWR snapshots with 10 sec gap between them. To increase workload time I have increased number of SQL executions in reader loop from 5000 to 25000. It increased average running time from 4 to 18 – 20 seconds and allow my session to grab two AWR snapshots.

New stats.sql script
set serveroutput off

HOST ./mywait
exec dbms_lock.sleep(2);
exec dbms_workload_repository.create_snapshot
exec dbms_lock.sleep(10);
exec dbms_workload_repository.create_snapshot
exit
runit.sh
#!/bin/bash

if [ -z "$2" ]
then

        echo "${0}: Usage : ${0}  "
        exit
else

        WU=$1
        RU=$2
fi

./create_sem > /dev/null 2>&1

cnt=1
until [ $cnt -gt $RU ]
do
        ( sqlplus -s user${cnt}/user${cnt} @reader > /dev/null 2>&1 ) &
        (( cnt = $cnt + 1 ))
done

until [ $cnt -gt $(( WU + RU )) ]
do
        ( sqlplus -s user${cnt}/user${cnt} @writer > /dev/null 2>&1 ) &
        (( cnt = $cnt + 1 ))
done

        ( sqlplus -L / as sysdba @stats.sql > /dev/null 2>&1 ) &
echo "start sleeping"
# sleep longer to allow all sessions to connect
sleep 120 
#sleep 20
# comment old awr_snap
#sqlplus -L '/as sysdba'  @awr/awr_snap > /dev/null
echo "running slob"

B=$SECONDS
./trigger > /dev/null 2>&1

wait

(( TM =  $SECONDS - $B ))

echo "Tm $TM"

# comment old awr_snap
#sqlplus -L '/as sysdba'  @awr/awr_snap > /dev/null
echo "running report"
sqlplus -L '/as sysdba'  @awr/create_awr_report > /dev/null
After tests with default SLOB default tables I decided to increase table size and effect force Oracle to use more memory for caching data blocks. I made 2 changes – one in setup.sh in cr_seed() function loop has been increased from 10000 to 200000 and similar change has been done in reader.sql I have extended random range from 10000 to 200000

setup.sh
function cr_seed () {

sqlplus -s user1/user1 <<EOF 
set echo on

CREATE TABLE seed
(
custid number(8),
c2 varchar2(128),
c3 varchar2(128),
c4 varchar2(128),
c5 varchar2(128),
c6 varchar2(128),
c7 varchar2(128),
c8 varchar2(128),
c9 varchar2(128),
c10 varchar2(128),
c11 varchar2(128),
c12 varchar2(128),
c13 varchar2(128),
c14 varchar2(128),
c15 varchar2(128),
c16 varchar2(128),
c17 varchar2(128),
c18 varchar2(128),
c19 varchar2(128),
c20 varchar2(128)
) PARALLEL PCTFREE 0 tablespace $TABLESPACE;

DECLARE
x      NUMBER :=1;
fluff  varchar2(128) := 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

BEGIN
FOR i IN 1..200000 LOOP
        insert into seed values (x,fluff, NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, fluff);
        x := x + 1;

END LOOP;
COMMIT;
END;
/
exit;

EOF
}
reader.sql
set serveroutput off

HOST ./mywait

DECLARE
x NUMBER := 0;
v_r PLS_INTEGER;

BEGIN
dbms_random.initialize(UID * 7777);

FOR i IN 1..25000 LOOP
        v_r := dbms_random.value(257, 10000) ;
        SELECT COUNT(c2) into x FROM cf1 where custid >  v_r - 256 AND  custid < v_r;
END LOOP;

END;
/
exit 

After all these work I have run my tests. To have better results I have every test 40 times. You can see results in table and graph below.

Configuration Avg Median Std var
NonHuge Page small table8,865,085.948,892,305.40185,926.15
NonHuge Page big table8,276,726.188,298,340.70175,300.08
Huge Page small table9,398,380.949,377,956.80263,674.90
Huge Page big table 8,575,646.658,597,997.20180,219.52
LIO / s for 40 tests runs - linear

LIO / s for 40 tests runs - radar view


For small tables Oracle is able to perform 6 % Logical IO more in HugePage configuration than in NonHuge configuration and for big tables this number is dropped to 3.6 % but in both cases HugePage configuration is better.

At the end I have to point out that I didn't measure session private memory utilization (heap and PGA) during this test but it has be taken into consideration if you are going to implement HugePages on your system.

regards,
Marcin

Update:

After morning Twitter conversation with Yury Velikanov @yvelikanov  I learned that he didn't have connection problems with non-HugePage configuration. I used pstack during connection time and here is a output:
#0  0x000000000498a2cd in ksmprepage_granule ()
#1  0x0000000004990aca in ksmgapply_validgranules ()
#2  0x000000000498a01c in ksmprepage ()
#3  0x0000000000aff997 in ksmlsge_phasetwo ()
#4  0x0000000000aff1cf in ksmlsge ()
#5  0x0000000000aff1ab in ksmlsg ()
#6  0x00000000017b547b in opiino ()
#7  0x0000000009006cba in opiodr ()
#8  0x00000000017ac9ec in opidrv ()
#9  0x0000000001e61c93 in sou2o ()
#10 0x0000000000a07a65 in opimai_real ()
#11 0x0000000001e6713c in ssthrdmain ()
#12 0x0000000000a079d1 in main ()
KSMPREPAGE gave me a idea that long connection time is related to pre_page_sga and how Oracle is dealing with it. Connection time is proportional to number of pages to check - of course with nonHugePage configuration number of pages too check in loop in bigger. Why in loop ? You can find this in one of the notes on MOS.

When I have removed pre_page_sga parameter connection time for nonHugePage and HugePage configuration is similar. Now it is time to run test and compare number of LIO/s with and without pre_page_sga. I will update that post soon.

Update 2 - 25 Apr 2012
I have tested number of LIO/s with pre_page_sga set to true and false for nonHuge and Huge Pages environment.

Configuration Avg Median Std var
NonHuge Page Pre_page = false8,072,479.218,096,358.30180,844.09
NonHuge Page Pre_page = true 8,190,746.988,212,969.55141,426.52
Huge Page Pre_page = false8,105,724.408,105,408.50165,420.39
Huge Page Pre_page = true8,735,329.938,744,138.20187,104.66

There is not big difference between pre_page_sga set to true or false in NonHugePage environment - around 1.5 %. For HugePages difference is bigger and it was around 7.7 % in my test. So after all checks I think that HugePage plus pre_page_sga is a winner but remember to double check if you haven't problem with connection time when SGA
is per-allocated.


LIO / s for 40 tests runs - linear

LIO / s for 40 tests runs -radar

Saturday, June 18, 2011

How to color (mark) SQL for AWR snapshots

Are all SQL statements you’re looking for in your AWR repository ? If not, there is a simple way to ‘ask’ Oracle to include list of SQL_ID in every snapshot. All you need to do is color it. No, not using marker on the screen with Grid Control Performance tab but using DBMS_WORKLOAD_REPOSITORY. ADD_COLORED_SQL  procedure. Since you mark sql_id as colored it will be included in every AWR snapshot even if it is not one of the top SQL's.
All colored sql_id together with creation date could be checked in the following view - DBA_HIST_COLORED_SQL. After all work there is a simple way to uncolor sql_id (make it white ? ). There is a procedure called DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL.

Simple example:
- script used to display SQL statistics from AWR repository
$ cat sql_stats.sql
col exetime format 99999999999.99
col begin_interval_time format a30
select begin_interval_time, PLAN_HASH_VALUE, ELAPSED_TIME_DELTA/EXECUTIONS_DELTA exetime, EXECUTIONS_DELTA, CPU_TIME_DELTA, ELAPSED_TIME_DELTA, DISK_READS_DELTA from dba_hist_sqlstat ss, dba_hist_snapshot s where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id;
Checking AWR repository for particular SQL_ID.
SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 

8 rows selected.
SQL has been executed in last hour a few times but was not included in AWR snapshot
SQL> select sample_time, session_id, sql_id, sql_plan_hash_value, sql_child_number  from v$active_session_history where sql_id = '6zfggtprazcvb' and sample_time > sysdate - 1/24 order by sample_time;

SAMPLE_TIME                   SESSION_ID SQL_ID        SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER
----------------------------- ---------- ------------- ------------------- ----------------
17-JUN-11 03.21.43.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.44.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.45.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.46.399 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.47.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.48.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.49.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.50.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.51.409 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.21.52.419 PM           1711 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.42.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.43.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.33.44.862 PM            474 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.37.819 PM           1724 6zfggtprazcvb          1587981875               10
17-JUN-11 03.49.38.819 PM           1724 6zfggtprazcvb          1587981875               10
SQL_ID has been colored (marked) using DBMS_WORKLOAD_REPOSITORY procedure
SQL> exec dbms_workload_repository.add_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

      DBID SQL_ID        CREATE_TIME
---------- ------------- -------------------
1878812188 6zfggtprazcvb 2011-06-17 15:55:45
Now this SQL_ID has been included in AWR snapshot even if it has been executed once - see last row
SQL> @sql_stats.sql
Enter value for sql_id: 6zfggtprazcvb
old   2: where s.snap_id = ss.snap_id and sql_id = '&SQL_ID' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id
new   2: where s.snap_id = ss.snap_id and sql_id = '6zfggtprazcvb' and s.snap_id > (select max(snap_id)-96 from dba_hist_snapshot) and EXECUTIONS_DELTA<>0 order by s.snap_id, sql_id

BEGIN_INTERVAL_TIME            PLAN_HASH_VALUE         EXETIME EXECUTIONS_DELTA CPU_TIME_DELTA ELAPSED_TIME_DELTA DISK_READS_DELTA 
------------------------------ --------------- --------------- ---------------- -------------- ------------------ ---------------- 
16-JUN-11 04.00.43.639 PM            927619989     90627544.40               20       12879044         1812550888           125728 
16-JUN-11 04.15.51.312 PM            927619989    282777061.76              151      279247551         4.2699E+10            72065 
16-JUN-11 04.30.57.081 PM           1587981875      1308903.00                7         203969            9162321             1894 
16-JUN-11 09.46.00.195 PM           1587981875       889464.06               16         760882           14231425             2463 
17-JUN-11 12.45.49.596 AM           1587981875      1390759.44               16         778881           22252151             2973 
17-JUN-11 04.45.38.168 AM           1587981875       621587.94               16         682897            9945407             2320 
17-JUN-11 06.45.18.396 AM           1587981875       415950.50               16         692895            6655208             1774 
17-JUN-11 07.45.35.975 AM           1587981875       865986.07               15         642901           12989791             1228 
17-JUN-11 03.45.11.552 PM           1587981875      1818946.00                1          12998            1818946              226 
At the end of this example SQL_ID has been uncolored (unmarked).
SQL> exec dbms_workload_repository.remove_colored_sql('6zfggtprazcvb');

PL/SQL procedure successfully completed.

SQL> select * from DBA_HIST_COLORED_SQL;

no rows selected

SQL>

Friday, June 10, 2011

Rolling partition and global index - do we need to rebuild ?

Some time ago (arrghhh - three month ago) I blogged about partition dropping when table has a global index - see. There was a example based on my tests. Now it is time to show some real figures. Both examples are sharing same scenario - about 2 years without touching indexes, 7 partitions are drop once per week. Partition drop is taking up to 6 - 7 hours. This time coalesce was not a option due to free up space requirement but it should help in future to avoid similar situations.
Example number 1
Table has around 562.000.000 rows and one partition has around 13.000.000 rows. Primary key index based on one number column growth to 140 GB and has been rebuilt to 13.9 GB. It is around 10 times less.
Example number 2 - bigger scale
Table has around 2.130.000.000 rows and one partition has around 45.000.000 rows.Primary key index based on one number column growth to 360 GB and has been rebuilt to 40 GB. It is 9 times less.
I put both examples here to show you that running coalesce for global indexes after partition drop can save you a lot of space and if done regularly prevent you from more resource consuming index rebuild.To be honest this is one of a few situations when I have seen any improvement (space wise this time) after index rebuild. But keep in mind that this is a special situation and for most of other cases you don't need to rebuild you indexes - if you think you need read Niall or Richard blog first.
regards,
Marcin

Saturday, May 7, 2011

Pivot tables and Active Session History in Oracle 11g

Oracle introduced a pivot tables in version 11g. If you want to use it there is still some limitation but as least it gave us a nice tool to display Active Session History information in various way. Please find one of possible example below - database CPU utilization for 5 minutes period with waits split into percent across wait classes
col "Administrative" format 999.99
col "Application" format 999.99
col "Cluster" format 999.99
col "Commit" format 999.99
col "Concurrency" format 999.99
col "Configuration" format 999.99
col "Network" format 999.99
col "Other" format 999.99
col "SYSTEM I/O" format 999.99
col "USER I/O" format 999.99
col "CPU" format 999.99
col Scheduler format 999.99
col Queueing format 999.99
col AAS format 999.99

select to_char(to_date(ts*300,'SSSSS'),'HH24:MI') sample_time, AAS,
nvl("'ON CPU'",0) "CPU",
nvl("'Scheduler'",0) Scheduler ,
nvl("'User I/O'",0) "USER I/O" ,
nvl("'System I/O'",0) "SYSTEM I/O" ,
nvl("'Concurrency'",0) Concurrency ,
nvl("'Application'",0) Application ,
nvl("'Commit'",0) Commit,
nvl("'Configuration'",0) Configuration,
nvl("'Administrative'",0) Administrative ,
nvl("'Network'",0) Network ,
nvl("'Queueing'",0) Queueing ,
nvl("'Cluster'",0) "CLUSTER",
nvl("'Other'",0) Other 
from (
 select trunc(to_char(sample_time, 'SSSSS')/300) ts,
 decode(session_state,'WAITING',wait_class,'ON CPU') wait_class, 
 count(*) cnt, 
 sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300)) sum, 
 sum(count(*)) over (partition by trunc(to_char(sample_time, 'SSSSS')/300))/300 AAS 
 from v$active_session_history 
 group by trunc(to_char(sample_time, 'SSSSS')/300),
          decode(session_state,'WAITING',wait_class,'ON CPU') 
 order by 1
) 
pivot (
   sum(round(cnt/sum*100,2)) 
   for (wait_class) in    
   ('Administrative','Application','Cluster','Commit','Concurrency',
    'Configuration','Network','Other','Queueing','Scheduler','System I/O',
    'User I/O','ON CPU'
   )
) order by 1;

SAMPLE_TIME          AAS     CPU SCHEDULER USER I/O SYSTEM I/O CONCURRENCY APPLICATION  COMMIT CONFIGURATION ADMINISTRATIVE NETWORK QUEUEING CLUSTER   OTHER
---------------- ------- ------- --------- -------- ---------- ----------- ----------- ------- ------------- -------------- ------- -------- ------- -------
2011-05-06 14:58    3.38    6.40       .00    38.42        .49         .00         .00     .00           .00          54.68     .00      .00     .00     .00
2011-05-06 14:59    3.67    4.55       .00    44.09        .00         .00         .00     .00           .00          51.36     .00      .00     .00     .00
2011-05-06 15:00    2.98    5.59       .00    29.05        .56         .00         .00     .00           .00          64.80     .00      .00     .00     .00
2011-05-06 15:01    2.53    7.89       .00    16.45        .00         .00         .00     .00           .00          75.66     .00      .00     .00     .00
2011-05-06 15:02    2.62    7.64       .00    21.66        .00         .00         .00     .00           .00          70.70     .00      .00     .00     .00
2011-05-06 15:03    2.32    9.35       .00    11.51        .00         .00         .00     .00           .00          79.14     .00      .00     .00     .00
2011-05-06 15:04    2.37    6.34       .00    30.99        .70         .00         .00     .00           .00          61.97     .00      .00     .00     .00
2011-05-06 15:05    2.93   10.80       .00     6.25        .00         .00         .00     .00           .00          82.95     .00      .00     .00     .00
2011-05-06 15:06    3.28   12.69       .00    14.72       1.02         .00         .00     .00           .00          71.07     .00      .00     .00     .51
2011-05-06 15:07    4.57   10.58       .00     8.39        .00         .00         .00     .00           .00          81.02     .00      .00     .00     .00
2011-05-06 15:08    4.72    5.30       .00    13.78        .00         .00         .00     .00           .00          80.92     .00      .00     .00     .00
2011-05-06 15:09    4.80    6.25       .00    15.28        .00         .00         .00     .00           .00          78.47     .00      .00     .00     .00
2011-05-06 15:10    4.42    8.30       .00     7.92        .00         .00         .00     .00           .00          83.77     .00      .00     .00     .00
2011-05-06 15:11    4.65    6.45       .00    12.19        .00         .00         .00     .00           .00          81.36     .00      .00     .00     .00
2011-05-06 15:12    4.48    7.06       .00     9.67        .00         .00         .00     .00           .00          83.27     .00      .00     .00     .00
2011-05-06 15:13    4.58    9.09       .00    13.09        .00         .00         .00     .00           .00          77.82     .00      .00     .00     .00
2011-05-06 15:14    4.40   10.61       .00     7.20        .00         .00         .00     .00           .00          82.20     .00      .00     .00     .00
2011-05-06 15:15    4.00    7.92       .00      .00        .00         .00         .00     .00           .00          92.08     .00      .00     .00     .00
2011-05-06 15:16    4.33    8.46       .00     6.15        .00         .00         .00     .00           .00          85.00     .38      .00     .00     .00
2011-05-06 15:17    4.60    5.07       .00     9.42        .00         .00         .00     .00           .00          85.51     .00      .00     .00     .00
2011-05-06 15:18    4.80    4.86       .00    15.97        .00         .00         .00     .00           .00          79.17     .00      .00     .00     .00
2011-05-06 15:19    4.27    5.08       .00     5.08        .00         .00         .00     .00           .00          89.84     .00      .00     .00     .00
2011-05-06 15:20    4.00    2.92       .00      .00        .00         .00         .00     .00           .00          97.08     .00      .00     .00     .00
2011-05-06 15:21    4.37    3.05       .00     8.02        .00         .00         .00     .00           .00          88.93     .00      .00     .00     .00
2011-05-06 15:22    7.23   17.05       .00    32.03        .00         .00         .00     .00           .00          50.92     .00      .00     .00     .00
2011-05-06 15:23     .55    6.06       .00     3.03        .00         .00         .00     .00           .00          90.91     .00      .00     .00     .00

That query can be easily change by adding filtering condition for sessions or sample time. Unfortunately list of values for pivot table can't be generated dynamically without XML output so it is required to hard coded number of wait class into this code. 



regards,
Marcin

Wednesday, April 27, 2011

MySQLPerformance Schema - is this introduction of Oracle event based monitoring in MySQL ?

Today I read presentation about MySQL performance MySQL Essentials part 8  recommended by @surachart and I learned that Performance Schema has been introduced - see documentation.
After basic review it looks like introduction of well know Oracle wait interface and statistics. I'm going to download MySQL version 5.5 shortly and check it out. I have a lot of concerns what happen to MySQL after Oracle acquisition but at least this is step in good direction.

regards,
Marcin

Friday, April 1, 2011

New version of S-ASH

Hello,

I'm pleased to inform you that new version of Simulate ASH has been published.
You can download it from ASH Masters website or from Source Forge project site.

If you have any questions or ideas how to improve it please feel free to contact me


regards,
Marcin

Monday, March 14, 2011

Rolling partition and global index coalesce

Resolving one of performance issues I figure out that primary key index is much bigger that it should be. Most of database activity is inserting data into partitioning table using some date field as a partitioning key. Rows can be inserted to every partition not only to most recent one. Daily job is dropping one oldest partition per day. Unfortunately primary key index is not partitioned and is maintained during partition drop as a global index. Correlation of these two things is a root cause of index size explosion.
Short test case below:
  • script test.sql has been used to check index size, number of rows in table and partition drop
  • in second session simple script inserting data into random partition was running continuously
Table definition:
  CREATE TABLE LOGS
   (    "LOG_ID" NUMBER NOT NULL ENABLE,
        "DELDATE" DATE NOT NULL ENABLE,
        "FILL" VARCHAR2(100),
         CONSTRAINT "LOGS_PK" PRIMARY KEY ("LOG_ID")
   )
  TABLESPACE "USERS"
  PARTITION BY RANGE ("DELDATE")
 (PARTITION "PARTITION1"  VALUES LESS THAN (TO_DATE(' 2011-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION2"  VALUES LESS THAN (TO_DATE(' 2011-03-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION3"  VALUES LESS THAN (TO_DATE(' 2011-04-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION4"  VALUES LESS THAN (TO_DATE(' 2011-05-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION5"  VALUES LESS THAN (TO_DATE(' 2011-06-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION6"  VALUES LESS THAN (TO_DATE(' 2011-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION7"  VALUES LESS THAN (TO_DATE(' 2011-08-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION8"  VALUES LESS THAN (TO_DATE(' 2011-09-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION9"  VALUES LESS THAN (TO_DATE(' 2011-10-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION10"  VALUES LESS THAN (TO_DATE(' 2011-11-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  PARTITION "PARTITION11"  VALUES LESS THAN (TO_DATE(' 2011-12-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 );
Source of test.sql script 
prompt number of rows in table
 select count(*) from pioro.logs;
 
 prompt LOGS_PK index size
 select sum(bytes)/1024/1024 from dba_extents where segment_name = 'LOGS_PK';

 prompt calculating stats
 exec dbms_stats.gather_table_stats('PIORO','LOGS',cascade=>true, estimate_percent=>null);
 
 prompt rows/block without update global indexes
 select blevel, leaf_blocks, distinct_keys, NUM_ROWS, NUM_ROWS/leaf_blocks "rows/block" from dba_indexes 
where owner='PIORO' and table_name='LOGS' and leaf_blocks <> 0;

 prompt number of rows in table partition
 select count(*) from pioro.logs partition(&partname);
 set timing on 

 alter table pioro.logs drop partition &partname update global indexes;
Inserts script running in loop looks like

insert into logs select seq_log_id.nextval, to_date('2011-'|| trunc(dbms_random.value(2,9)) ||'-15','yyyy-mm-dd'),
lpad('x',70,'x') from all_source a, all_source b where rownum <= 3000000;
commit;
Here is a output from test.sql for first partition drop
SQL> @test
number of rows in table COUNT(*)                                                                                                 
----------                                                                                                          
  12000000                                                                                                          

Elapsed: 00:00:13.74
LOGS_PK index size

SUM(BYTES)/1024/1024                                                                                                
--------------------                                                                
242           

Elapsed: 00:00:00.51
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.28
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block                                                          
---------- ----------- ------------- ---------- ----------                                                          
         2       30000      12000000   12000000        400         

Elapsed: 00:00:00.03
number of rows in table partition
Enter value for partname: partition1
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition1)

  COUNT(*)                                                                                                          
----------                                                                                                          
   3001361                                                                              
Elapsed: 00:00:00.35
Enter value for partname: partition1
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition1 update global indexes

Table altered.

Elapsed: 00:00:55.37
As you can see before first drop average number of rows in one index leaf for primary key was 400. ( 8 kB database block ) Output from next run looks like:
SQL> @test
number of rows in table

  COUNT(*)   
----------   
  11998639   

Elapsed: 00:00:09.59
LOGS_PK index size

SUM(BYTES)/1024/1024  
--------------------  
                 296  

Elapsed: 00:00:01.12
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:13.16
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block    
---------- ----------- ------------- ---------- ----------    
         2       37500      11998639   11998639 319.963707    

Elapsed: 00:00:00.05
number of rows in table partition
Enter value for partname: partition2
old   1:  select count(*) from pioro.logs partition(&partname)
new   1:  select count(*) from pioro.logs partition(partition2)

  COUNT(*)                  
----------                  
   3749280                  

Elapsed: 00:00:00.43
Enter value for partname: partition2
old   1:  alter table pioro.logs drop partition &partname update global indexes
new   1:  alter table pioro.logs drop partition partition2 update global indexes

Table altered.

Elapsed: 00:01:22.85
Numbers of rows in one leaf block drop from 400 to 320 rows per block. After a few next runs it looks like:
SQL> @test
number of rows in table

  COUNT(*)                                                                                                          
----------                                                                                                          
   9748583                                                                                                          

Elapsed: 00:00:17.67
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:00.85
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:01:11.78
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block        
---------- ----------- ------------- ---------- ----------        
         2       52500       9748583    9748583 185.687295        

Elapsed: 00:00:00.01
Now is really bad - only 186 rows in one leaf block and size of index segment is now 414 MB. It will have huge impact on database performance as much more index blocks have to be read during index range scans. Solution is quite simple and can be done online without huge overhead. It is a index coalesce - this command will consolidate a free space in existing index and provide more free blocks for particular index segment. See example:
SQL> alter index pioro.LOGS_PK coalesce;

Index altered.

Elapsed: 00:03:57.12
SQL> prompt number of rows in table partition
number of rows in table partition
SQL> select count(*) from pioro.logs partition(&partname);
Enter value for partname: partition4
old   1: select count(*) from pioro.logs partition(&partname)
new   1: select count(*) from pioro.logs partition(partition4)

  COUNT(*)                    
----------                    
   5248055                    

Elapsed: 00:00:10.47
SQL> alter table pioro.logs drop partition &partname update global indexes;
Enter value for partname: partition4
old   1: alter table pioro.logs drop partition &partname update global indexes
new   1: alter table pioro.logs drop partition partition4 update global indexes

Table altered.

Elapsed: 00:02:09.65
SQL> @test
number of rows in table

  COUNT(*)                          
----------                          
   7500528                          

Elapsed: 00:00:15.78
LOGS_PK index size

SUM(BYTES)/1024/1024      
--------------------      
                 414      

Elapsed: 00:00:01.47
calculating stats

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.30
rows/block without update global indexes

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS rows/block      
---------- ----------- ------------- ---------- ----------      
         2       26682       7500528    7500528 281.108163      

Elapsed: 00:00:00.08
After index coalesce we can see that Oracle is able to allocate space from free block and size of index remain stable and number of rows in one leaf block is now increasing.
This post has been created based on test data and test scenario but after sucesfull implementation in life systems I will blog about real numbers from production databases.

Wednesday, September 8, 2010

New version of Simulating ASH

Hello, 

I have mentioned some time ago about using Simulating ASH to solve performance issue in Oracle 9i database.
According to some restrictions I have to customize it a little bit and than I agreed with Kyle Hailey that I will publish it as a new version of S-ASH. A new project has been created on SourceForge to keep a repository and this new code and installation instruction can be find here.

Please feel free to post any comments or remarks. I have some idea about potential enhancement of that tool but I'm open for any new too.

regards,
Marcin

Thursday, July 29, 2010

Process hung on library cache lock

Customer reported a system slowdown/process hung during small and well defined period of time and it was related to one specific process only. Standard Oracle 9i performance analyze tool - Statspack didn't show anything unusual in that time but shapshots were taking very 30 min and this issue took about 5 minutes.
As we could not replicate issue I thought about SASH (a free simulator of Oracle Active Session History) and I have installed it and configured. From that time I was able to gather and keep information about active sessions.
When next time I got detailed information about similar issue I was able to use collected data and found a problem. After investigation I figured out that process used for that business functionality stuck on “library cache lock” event. When I compared my findings from SASH with Statspack I realize why I didn't spotted it before - "library cache lock" was about 1.8 % of all locks between statspack snapshots. From overall database review there was no problem but from process (and business) perspective that lock hung important process for almost 5 minutes.

Below I described all major steps of my investigation and what was a root cause.
Let's start with SASH output (I just left 3 lines but there was many more similar lines):
SQL> select sample_time,program, session_id, session_state, event, seq#, sql_id, '0' || trim(to_char(p1,'XXXXXXXXXXXXXXXXX')) "p1raw", p3
  2  from v$active_session_history
  3  where 
  4  sample_time >= to_date('18-7-2010 13:50:10','dd-mm-yyyy hh24:mi:ss')
  5  and sample_time <= to_date('18-7-2010 13:54:58','dd-mm-yyyy hh24:mi:ss')
  6  and event like 'library%'
  7  order by sample_time desc
  8  /

SAMPLE_TI PROGRAM        SESSION_ID SESSION_STATE EVENT              SEQ#  SQL_ID     p1raw             P3
--------- -------------- ---------- ------------- ------------------ ----- -------    -----------       ---
18-JUL-10 importantp.exe        472 WAITING       library cache lock  214  1548901218 07000002AC789A98  210
18-JUL-10 otherprogr.exe        436 WAITING       library cache lock    8  2855865705 07000002AC789A98  210
18-JUL-10                       375 WAITING       library cache lock   33   261624711 07000002AC789A98  310

First idea was about parsing problem but when I took a look on SQL it looked OK but it was just entry point to PL/SQL procedure. Next thing was to check event parameters and there was first surprise. According to Oracle documentation and Metalink note 34579.1 that event has following parameters:
  • p1 is an address of required object - in my case 07000002AC789A98
  • p3 is a lock type and namespace type (lock mode * 100 + namespace)  - in my case 310 or 210
In Oracle definitions (metalink or decode in v$librarycache) namespace number 10 is not defined. See whole list below
•    0 SQL Area
•    1 Table / Procedure / Function / Package Header
•    2 Package Body
•    3 Trigger
•    4 Index
•    5 Cluster
•    6 Object
•    7 Pipe
•    13 Java Source
•    14 Java Resource
•    32 Java Data
So what object it is ? P1 had a address and X$KGLOB has a list of all objects (I checked that table next day but there was no restart and it was a chance that there is still that same object in shared pool as session was running 24/7):
SQL> select kglnaown "Owner", kglnaobj "Object" from x$kglob where kglhdadr='07000002AC789A98'; 

Owner  Object
------ --------
SOFT   SNDQ
What type of objects is it ?
SQL> select object_type, object_name from dba_objects where owner='SOFT' and object_name = 'SNDQ';

OBJECT_TYPE OBJECT_NAME
----------- -----------
QUEUE       SNDQ
Bingo - object was a queue there was a dequeue in that PL/SQL code. My process called importantp.exe was waiting for lock on queue. Let's check lock type:
  • 2    - Share mode 
  • 3    - Exclusive mode 
For importantp.exe lock mode in p3 parameter was 210 which mean wait for shared lock. But queuing and dequeueing operations are running with shared locks so reason for waiting had to be related to none of them. I found it in last line of my listing there was another processes waiting for exclusive lock on that same queue. Using SQL_ID (261624711)  I discovered that there was another PL/SQL piece of code and add_subscriber function was called inside. 
That was a explanation of importantp.exe hungs - as dequeue function was called after add_subscriber function it had to wait until DDL command was finished. Add subscriber function had to wait until all previous operations in shared mode will be completed and there was typical chain of locks.

There are bugs in Metalink raised by other people describing similar situations and there is only one workaround do not try any DDL on queues during traffic peaks. This recommendation is well know and most DBAs preventing database from typical DDL operation ( like index rebuild or schema changes) during business hours. The problems starts with hidden  DDL commands used in application.


Sunday, July 18, 2010

OEM performance tab and Active Session History data.

Graphic representation of Active Session History data in Database Console or in Grid Control is causing some misunderstanding of how to read it. I have lot of questions about Y scale on OEM performance tab and now I decide to blog about it. I want to describe now OEM is using a ASH date and what we can do with this information. Anyway using SQL instead of OEM is more useful in performance tuning and can be easily focused on existing issue.

For other point of view knowledge of ASH give you opportunity to use free implementation of it developed by Kyle Hailey. (Thanks Kyle for your excellent scripts – this is great staff to learn from). By the way recently I have deployed SASH on big production database running on Oracle 9i and it help me to identify and address performance problems. As soon as I finish all these work I will blog about it too.

CPU utilization
CPU usage on performance tab is in green and in my case it has upper limit set to 2 – as I have two core CPU.


It is quite clear how to read CPU usage and what is a maximum CPU utilization. Scope of time which sessions can spend on CPU has an upper limit which is 100 % utilization of all CPU. It mean that maximum CPU time for session in sampling time can be calculated using following formula

Number of CPU x Sampling Time

So if we want to calculate a CPU usage we need to use following formula:

time consumed per all sessions / (Number of CPU x Sampling Time) * 100 %

ex.
time consumed per all sessions = 16 s
Number of CPU = 2
Sampling time = 15 s 

Util of all CPUs = 15 / (2 * 15) * 100 % = 53.3 %

How to calculate CPU utilisation using Active Session History data instead of OEM performance tab ?

Active Session History table is a results of sampling V$SESSION view every second. That mean there is no “real” waiting or executing time in V$ACTIVE_SESSION_HISTORY but just samples. We are assuming that every state in ASH view (WAITING or ON CPU) took exactly 1 s. This is important as we don’t sum waiting time from ASH view but we have to count how many 1 s events were in observed time.
Be aware that DBA_HIST_ACTIVE_SESS_HISTORY has only 1/10 data of  V$ACTIVE_SESSIN_HISTORY (only sample_id modulo 10 are copied into it) and each sample is now 10s long if we want to use it for our calculations.

Following query will calculate average CPU utilization for two minutes:
SQL> select trunc(count(*)/
  2  (min(p.value)*(max(sample_id) - min (sample_id)))*100,2) "CPU_UTIL",
  3  max(sample_id) - min (sample_id) "sampling time [s]",
  4  count(*) "time consumed per sessions [s]",
  5  min(p.value) * (max(sample_id) - min (sample_id)) "maximum possible time [s]"
  6  from v$active_session_history,
  7  (select value from v$parameter  where name = 'cpu_count') p
  8  where 1=1
  9  and sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
 10  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
 11  and session_state = 'ON CPU';

  CPU_UTIL sampling time [s] time consumed per sessions [s] maximum possible time [s]
---------- ----------------- ------------------------------ -------------------------
     29.23               118                             69                       236


But this doesn’t help us in understanding OEM graph. Refresh rate is set to 15 sec so we need to display data from that period split into 15 sec slices.
SQL> select trunc(to_char(sample_time,'SSSSS')/15) "15s_sample", trunc(count(*)/
  2  (min(p.value)*15)*100,2) "CPU_UTIL",
  3  count(*) "time consumed per sessions [s]"
  4  from v$active_session_history,
  5  (select value from v$parameter  where name = 'cpu_count') p
  6  where 1=1
  7  and sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
  8  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
  9  and session_state = 'ON CPU'
 10  group by trunc(to_char(sample_time,'SSSSS')/15)
 11  order by 1;

15s_sample   CPU_UTIL time consumed per sessions [s]
---------- ---------- ------------------------------
      2972       6.66                              2
      2973       6.66                              2
      2974      53.33                             16
      2975      23.33                              7
      2976      36.66                             11
      2977      26.66                              8
      2978      33.33                             10
      2979      43.33                             13

8 rows selected.

Now if you use any graphic tool to create a chart using 15s_sample column as X axis and "CPU util" column as Y axis you should get very similar picture to OEM chart.

Wait events

There is no easily calculated upper limit for waiting sessions. Why ? Number of waits per sample is depended on session state and number of waiting sessions. So what is a maximum for that ? Should we use only active sessions or all connected sessions ? The worst case scenario is all sessions are active and all are in waiting state. Both values multiplied each other give us a potential upper limit. 

Performance tab in OEM is calculating a average of waits in sample time divided by groups and all groups are displayed using stacked area graph.

Following SQL return data used to create following graphs
SQL> select trunc(to_char(sample_time,'SSSSS')/15) "15s_sample", wait_class, count(*)/15
  2  from v$active_session_history
  3  where sample_time >= to_date('18-07-2010 12:23:00','dd-mm-yyyy hh24:mi:ss')
  4  and sample_time <= to_date('18-07-2010 12:25:00','dd-mm-yyyy hh24:mi:ss')
  5  and session_state = 'WAITING'
  6  group by trunc(to_char(sample_time,'SSSSS')/15), wait_class order by 1,3
  7  ;

15s_sample WAIT_CLASS                                                       COUNT(*)/15
---------- ---------------------------------------------------------------- -----------
      2974 Other                                                                     .2
      2974 System I/O                                                        .533333333
      2974 Commit                                                                     2
      2974 User I/O                                                          2.06666667
      2974 Concurrency                                                       4.66666667
      2975 Application                                                       .066666667
      2975 System I/O                                                        .933333333
      2975 User I/O                                                                 3.8
      2975 Commit                                                            4.93333333
      2976 Concurrency                                                       .066666667
      2976 Application                                                       .066666667
      2976 Other                                                                     .4
      2976 System I/O                                                               1.2
      2976 Commit                                                                   5.4
      2976 User I/O                                                                 5.8
      2977 System I/O                                                        .866666667
      2977 User I/O                                                          2.93333333
      2977 Commit                                                                   5.6
      2978 Concurrency                                                       .066666667
      2978 System I/O                                                        .933333333
      2978 User I/O                                                          3.26666667
      2978 Commit                                                                     4
      2979 System I/O                                                        .866666667
      2979 User I/O                                                          1.46666667
      2979 Commit                                                            2.53333333

25 rows selected.
As results we have average time spend by all sessions divided per wait class for a 1 second.  What it mean that in sample 2976 wait class “Commit” average time was 5.4 ? It exactly mean that in that 15 seconds sampling time all active sessions were 5.4*15 = 81 times waiting for events in commit class. Is it bad or not ? This is a different question and will not be answered in that post.

Now if we take a look on sample nr 2976
2976 Application                                                       .066666667
2976 Other                                                                     .4
2976 System I/O                                                               1.2
2976 Commit                                                                   5.4
2976 User I/O                                                                 5.8

Sum of all waits give us 12.8 and this is more less what we can see as a top value on graph. A differences between graph and our calculation are related to observation period start time and how we split results into 15 sampling time. We are using a group by function based on sample_time truncated into 4 periods of seconds - using group by to_char function (based on Kyle idea).These periods are: 0 – 15, 15- 30 , 30-45, 45-60 but OEM can display data based on different 15 sec groups ex. 10 - 25, 25 - 40 so small differences can occure.

Now the question is – is this a bad thing to have high numbers of OEM graph ? And like always when we are talking about Oracle performance an answer is depends. 
What is bad for sure if our CPU usage is going over 85-90 % and stay there for a long time. In that case CPU response time will have good opportunity to increase and whole system performance can go down. What to do with waits ? In my opinion a best way is to create a baseline (even on paper) with average waiting time and react only when system is working over our baseline or what is more important when system users are claiming a performance problems.

Next time I will use drill-into functionality of OEM and will try to explain what other graphs means – unfortunately most of them are changing a scale between main performance graph and detailed ones so if you are not aware of that it can cause a lot of misunderstandings.


Regards,
Marcin

Sunday, August 30, 2009

SwingBench

First time I have seen that tool a few years ago and I was really surprise how useful it was. Now I have come back to it and a new version (release 2.3) in my opinion is very mature and can be used for many various performance tests.
Let me present show main features:
  • Few different benchmark test
    • OLTP Order Entry PL/SQL version
    • OLTP Order Entry Java version
    • OLTP Calling Circle
    • Stress test
    • DSS Sales History
    • Custom


  • Possible outputs
    • Transaction per minute
    • Transaction Response time
    • DML operation per minute


  • Many configuration options ex:
    • Number of concurrent users
    • Min and max delay time between transactions
    • Logon delays
    • Using of connection pooling


  • Distributed client coordinator
All features are described on Dominic Giles (author) homepage in documentation section. This document describe version 2.2 and a newest version is 2.3 and some additional configuration possibilities has been added from XML to GUI.

There are a lot of possible configurations and tests covered by SwingBench. Combining a customer defined transactions in tool with application knowledge SwingBench can be used as a small brother of Oracle Real Application Testing – of course it required much more work to define a workload but it is for free.

How to use it – it is very simple please take a look on my quick start guide.
  1. Download a package
  2. Unzip into target directory – ex. c:\SwingBench
  3. Edit a SwingBenchenv.bat file – only path below should be changed

    REM Set the following to reflect the root directory of your Java installation

    set JAVAHOME=C:\Program Files\Java\jre1.6.0_07

    REM Set the following to the directory where you installed swingbench

    set SWINGHOME=C:\swingbench

    REM Set the following to the location of your TimesTen install (optional)

    set TTHOME=C:\TimesTen\tt70

    REM If you don't have the ORACLE_HOME environment variable set uncomment the following and change it to reflect your environment

    set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

  4. Go to C:\swingbench\winbin and start oewizard.bat - configuration wizard



  5. Click next and choose a create schema



  6. Enter a server IP, database name and sys password



  7. Enter schema name and tablespace location - if you have a EE edition you can clik Use Partitioning option



  8. Choose a size of demo schema using sliders



  9. Confirm all settings and click Finish



  10. Wizard will display a additional window with Wizard log



  11. Click OK on confirmation screen



  12. Click Cancel in main Wizard Window



  13. You can start now a SwingBench - c:\swingbench\winbin\swingbench.bat



    And change a ConnectionString to proper one for your environment

  14. Click Start button and enjoy a performance test




This is all for a first time. Now I'm trying to find a fastest way to catch up
a workload on existing database and transform it into a SwingBench tests.

I hope it help some people to test a performance of existing and future installations.

regards,
Marcin

Thursday, August 13, 2009

Application Server - Forms session monitoring

Hi,

Oracle Application Forms server has some build in logging tools which allow a DBA to trace a performance or debug data. In Oracle documentation you can find a example how to use that tools.
If you check a URL you can notice that all examples are related to default forms environment

http://yourserver/forms/frmservlet?serverURL=/forms/lservlet/perf

But how to use that if you have a few different environment deployed on server (ex. a few applications) and you are using URL which include a environment name

http://yourserver/forms/frmservlet?config=myconfig.env

You have to realize (I spend some time several times on it so I decide to create that post)
that "serverURL" and config are a parameters in your URL and according to HTTP standard for GET method we can combine parameters using "&" character.

http://yourserver/forms/frmservlet?config=myconfig.env&serverURL=/forms/lservlet/perf

That's all folks and I will try to remember that using my blog memory :-)


regards,
Marcin

Oracle indexes

Hello,

Jonathan Lewis just started a series of posts about indexes. This is one of these things which has a flag "must to read". Please find some examples:
- Index fragmentation
- Index explosion part 1
- Index explosion part 2


regards,
Marcin

Saturday, May 16, 2009

A short story about Oracle CBO and data – part 3 - 11g

I have decided to check that same query on 11g.
And there is an improvement – I gathered a statistics and it is running without any problems.
So upgrade to 11g can be a step in good direction.
The main thing is that in 11g a new multicolumn join selectivity algorithm had been
used. I have to do some more research on that. Right now I want to show only
difference using my query as an example



Trace file from 10.2.0.4


Using multi-column join key sanity check for table TAB8
Revised join sel:6.1576e-04 = 0.0021556 * (1/1624.00) * (1/0.0021556)
Join Card: 126.39 = outer (373.19) * inner (550.00) * sel (6.1576e-04)
Join Card - Rounded: 126 Computed: 126.39


Trace file from 11.1.0.7


ColGroup cardinality sanity check: ndv for TAB8[H] = 550 TAB7[S] = 1
Join selectivity using 1 ColGroups: 0.002156 (sel1 = 0.000000, sel2 = 0.000000)
Join Card: 442.439495 = = outer (373.189655) * inner (550.000000) * sel (0.002156)
Join Card - Rounded: 442 Computed: 442.44


Please compare line 3 in both of them. Did you realize that computed join cardinality in 11g is almost 3.5 times bigger ?
In 10g a join selectivity was based on join columns selectivity, in 11g ?
I don't know yet.

update 18.05.2009
I shouldn't write anything during a weekend ;)
In 11g there is a new feature called a multicolumn histogram statistics
and now this is a source of cardinality in multicolumn joins.
See example on Pythian blog



ps.
Thanks to Alex GorbatchevI have a new syntax highlighting.

regards,
Marcin