Showing posts with label 10g. Show all posts
Showing posts with label 10g. Show all posts

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

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

Monday, August 10, 2009

ASM datafile operation in NOMOUNT

Hi

Several times I have been asked to manipulate with data files placed on ASM. In 11g there is a very nice feature – Oracle added a cp command to asmcmd tool but in 10g we have to use database instance to do all work.
One of very common task is coping a data file from one ASM disk group to other one. It can be done via RMAN copy command but this command adds an entry to RMAN repository. There is another method to do that and I found it in Oracle documentation (Yes, yes, RTFM). Convert command can be used not only to copy data file from ASM to file system and vice versa but to copy between ASM groups too.

Example

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '+DATA/PIORO/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=+DATA/pioro/ copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished backup at 10-AUG-09


ASM to file system

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

Keep in mind that this command doesn’t add any information to control file so you want to rename a file you have to maintain it manually.

This command is working in NOMOUNT state too but a additional parameter is required

[oracle@piorovm ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 10 22:15:44 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: pioro10 (not mounted)

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' from platform 'Linux IA (32-bit)' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

If you need to copy archive log (ex. From flash recovery area) you can use a RMAN copy command - this is running only in MOUNT state.

RMAN> copy archivelog '+data/PIORO/ARCHIVELOG/2009_07_28/thread_1_seq_58.266.693419455' to '/home/oracle/arch_1_58';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=58 recid=1 stamp=694559795
output filename=/home/oracle/ arch_1_58 recid=3 stamp=694560324
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:07
Finished backup at 10-AUG-09

But it can be a dangerous command because additional entry to control file is created and if you delete this file from a file system without sending that information to RMAN archive log backup could failed. To avoid it always CROSSCHECK ARCHIVELOG ALL before archive log backup or use option “skip inaccessible”.
This is it for today but I have some additional ideas to check.


Marcin

Monday, May 11, 2009

Do we still need DBA ? A short story about Oracle CBO and data. – part 2

There is more deep investigation about potential cardinality errors in CBO.
At this place I want to thanks Jonathan Lewis for this brilliant book about Cost Based Optimization
in Oracle. That was and still this is a base of all my investigations.
I have got a query (described in part 1) which cause me a lot of problems with temporary tablespace.

I have used a 10053 event to generate details about CBO decisions for that particular query. Here I want to show only a part of that 5MB text file. According to that file join number 295 has been chosen as best join order.

...
Final - All Rows Plan: Best join order: 295
...

Here is a part of 10056 file

Join order[295]: TAB1_MV[P]#0 TAB2_MV[E]#1 TAB3_MV[S]#13 TAB4_MV[H]#11 TAB5_MV[D]#9 TAB6_MV[AC]#12 TAB7_MV[SR]#7 SP_MV[R]#19 ... rest of tables

***************
Now joining: SP_MV[R]#19
***************
....
Join Card: 0.00 = outer (11.29) * inner (30335.98) * sel (1.8863e-09)
Join Card - Rounded: 1 Computed: 0.00

There is no information about joining previous tables from TAB1 to TAB7. But join cardinality for those 7 tables has been calculated to 11.29. I want to know how it has been calculated. CBO can reuse calculations from other join orders so I have to find where it was.
Here is some information about tables. This is Single Table Cardinality which is used in join calculations.

TAB1_MV[P]#0 Card: Original: 1 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
TAB2_MV[E]#1 Card: Original: 3 Rounded: 3 Computed: 3.00 Non Adjusted: 3.00
TAB3_MV[S]#13 Card: Original: 21645 Rounded: 21645 Computed: 21645.00 Non Adjusted: 21645.00
TAB4_MV[H]#11 Card: Original: 550 Rounded: 550 Computed: 550.00 Non Adjusted: 550.00
TAB5_MV[D]#9 Card: Original: 117 Rounded: 117 Computed: 117.00 Non Adjusted: 117.00
TAB6_MV[AC]#12 Card: Original: 199128 Rounded: 2797 Computed: 2796.88 Non Adjusted: 2796.88
TAB7_MV[SR]#7 Card: Original: 23 Rounded: 23 Computed: 23.00 Non Adjusted: 23.00

As you can see it can be different between num_rows and computed cardinality. So CBO calculate a number of rows. There are detail table stats

Table Stats::
Table: TAB6_MV Alias: AC
#Rows: 199128 #Blks: 2688 AvgRowLen: 171.00
Column (#16): TAB6_CR_ID
AvgLen: 2.00 NDV: 6262 Nulls: 151566 Density: 8.4931e-05 Min: 1000 Max: 70516
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 140
Column (#17): TAB6_CR_NUMBER
AvgLen: 2.00 NDV: 5 Nulls: 150840 Density: 7.1446e-04 Min: 1 Max: 33
Histogram: Freq #Bkts: 5 UncompBkts: 48288 EndPtVals: 5
Column (#2): TAB6_ID
AvgLen: 5.00 NDV: 14584 Nulls: 0 Density: 6.1554e-05 Min: 607 Max: 70943
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 230
Column (#25): TAB_CR_SRCE_CD
AvgLen: 2.00 NDV: 2 Nulls: 150840 Density: 0.0016153
Histogram: Freq #Bkts: 2 UncompBkts: 48288 EndPtVals: 2

There are NULL values in 3 of 4 columns and because in join condition Oracle doesn’t use null values it is trying to adjust a number of rows using those values.
For only one column with null values calculated table cardinality is equal to

Cal cardinality = num_rows – number of nulls

# of rows # of nulls # of not null # not null/ # of rows
199128 151566 47562 0.238851
199128 150840 48288 0.242497
199128 0 199128 1
199128 150840 48288 0.242497


If there is more than one column with null values some more calculations are in place.

Cal cardinality col 1 and 2 = Cal cardinality col 1 * (number of not null / num_rows) for col 2

And so on

Cal cardinality col 1 and 2 and 3 = Cal cardinality col 1 and 2 * (number of not null / num_rows) for col 2

So in our example

Cal card = 47562 * 0.242497 * 1 *0.242497 = 2796.88

Did you read a part number 1 ? Above calculation are based on number of rows and number of nulls and there are no information about data values. Neither number of distinct values nor information from histograms was used.
So let’s CBO do it work

Join order[294]: TAB1_MV[P]#0 TAB2_MV[E]#1 TAB3_MV[S]#13 TAB4_MV[H]#11 TAB5_MV[D]#9 TAB6_MV[AC]#12 TAB7_MV[SR]#7 ...
***************
Now joining: TAB2_MV[E]#1
***************
...
Join Card: 1.00 = outer (1.00) * inner (3.00) * sel (0.33333)
Join Card - Rounded: 1 Computed: 1.00
...
***************
Now joining: TAB3_MV[S]#13
***************
...
Join Card: 373.19 = outer (1.00) * inner (21645.00) * sel (0.017241)
Join Card - Rounded: 373 Computed: 373.19
...
***************
Now joining: TAB4_MV[H]#11
***************
...
Join Card: 126.39 = outer (373.19) * inner (550.00) * sel (6.1576e-04)
Join Card - Rounded: 126 Computed: 126.39
...
***************
Now joining: TAB5_MV[D]#9
***************
...
Join Card: 126.39 = outer (126.39) * inner (117.00) * sel (0.008547)
Join Card - Rounded: 126 Computed: 126.39
...
***************
Now joining: TAB6_MV[AC]#12
***************
...
Join Card: 11.29 = outer (126.39) * inner (2796.88) * sel (3.1939e-05)
Join Card - Rounded: 11 Computed: 11.29
...
***************
Now joining: TAB7_MV[SR]#7
***************
...
Join Card: 11.29 = outer (11.29) * inner (23.00) * sel (0.043478)
Join Card - Rounded: 11 Computed: 11.29
...

As you can see calculated output cardinality is 11.29.
I have made some small experiment and I cut out from SQL query part related only to those 7 tables.

select count(*) FROM TAB4_MV H, TAB3_MV S,TAB1_MV P,TAB2_MV E,TAB5_MV D,TAB6_MV AC,TAB7_MV SR
WHERE S.TAB3_TC = H.TAB4_TC AND S.TAB3_CON_ID = H.TAB4_ID
AND S.TAB3_CON_NUMBER = H.TAB4_CON_NUMBER
AND H.TAB4_DETAIL_PAY_CODE = D.TAB5_DETAIL_CODE
AND S.TAB3_CON_ID = AC.TAB6_CR_ID
AND S.TAB3_CON_NUMBER = AC.TAB6_CR_NUMBER
AND AC.TAB6_CR_SRCE_CODE = SR.TAB7_CODE
AND S.TAB3_CON_ID = P.TAB1_ID
AND P.TAB1_ECAT_CODE = E.TAB2_CODE;

Here is a result of that query

COUNT(*)
---------
141 354 138

Comparing that to estimated 11.92 rows you can see that real join cardinality is almost 10 mln times more that calculated.
Ok so let’s execute a full SQL query. Best join order is set to 295 - see calculation at the begining of that post. As you can see cardinality is set to 1 now but result of query

select count(*) FROM SP_MV R, TAB4_MV H,TAB3_MV S,TAB1_MV P,TAB2_MV E,TAB5_MV D,
TAB6_MV AC,TAB7_MV SR
WHERE R.SP_ENTITY_IND = 'P'
AND R.SP_CHANGE_IND IS NULL
AND R.SID = S.TAB3_STU_ID
AND S.TAB3_TC = H.TAB4_TC
AND S.TAB3_CON_ID = H.TAB4_ID
AND S.TAB3_CON_NUMBER = H.TAB4_CON_NUMBER
AND H.TAB4_DETAIL_PAY_CODE = D.TAB5_DETAIL_CODE
AND S.TAB3_CON_ID = AC.TAB6_CR_ID
AND S.TAB3_CON_NUMBER = AC.TAB6_CR_NUMBER
AND R.SID = AC.TAB6_ID
AND AC.TAB6_CR_SRCE_CODE = SR.TAB7_CODE
AND S.TAB3_CON_ID = P.TAB1_ID
AND P.TAB1_ECAT_CODE = E.TAB2_CODE;

is

ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

This is not what a customer is expecting as results of report. I find out that when cardinality of table is set via dbms_stats.set_table_stats it is not changed in Single Table Cardinality Section. I have to investigate this more deeply why but for now this is my solution for that problem.

See you in part 3.

regards,
Marcin

Thursday, May 7, 2009

Do we still need DBA ? A short story about Oracle CBO and data. – part 1

What is a Cost Based Optimizer ?
It is a mathematic model which is trying to estimate using some calculation what will be a best execution plan for particular SQL. All the calculations are based on objects (tables and indexes) statistics, instance statistics and some build in rules. Step by step Oracle CBO is calculating a cost of query (join by join, filter by filter) but some very important part is missing from calculations. What is missing from that ?
A data itself. CBO (in normal mode) doesn’t care about data of course there can be some histograms or column selectivity but in general there is no overview of values of rows.

Why I’m writing about that – I have created a 14 database for 14 branches of that same intuition. There is one source system which is replicated to BI databases and in each database a materialized view based on that same query are generated.
For 12 database it was working perfectly well, but for 2 doesn’t at all. During creation or refresh phase a following error appear:

ORA-01652: unable to extend temp segment by 64 in tablespace TEMP



My first try was to increase a TEMP tablespace from 2 to 4 GB. But it didn’t help.
OK – lets resize once again – I have set up a autoextend for that file and maximum file size has been set to 20 GB. Should be enough – that tables has no more then 100 000 rows. But not,
After 15 min still

ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

This query has been joined about 12 tables. Nine from them was really small and have a good filter condition – a number of rows for that 9 join was really small between 20 and 500. But these tables have been joined to 2 big tables which have thousands of rows. A join condition between was really unique so overall results of query was limited to hundreds or thousands of rows.
I started with review of query to find a join which generate a lot of rows which has to be save in temporary space, after a few tries I had one !
Two tables have been joined on both columns – unfortunately it was not a unique join.

In table A it was four pair of values which meet join condition

Col1 Col2 Rows COUNT(*)
6001 3 8811
6001 4 292
6001 2 8246
6001 1 3292

In table B I got that same number of pairs

Col1 Col2 Rows COUNT(*)
6001 3 2700
6001 4 75
6001 2 10495
6001 1 6813

So if we multiple every join condition it is about 130 mln of rows generated from that query.
Of course design was a little bit different – there are more tables and joins which limit number of rows returned to user – but CBO decide to add that joins and filter after that.
So yes – at this stage I can say we still need DBA.

Next investigation in near future.

Thursday, February 26, 2009

RAC and Linux

Hi for many years I have been supported Linux and Oracle combination.

After few last experiences I’m thinking that newest RedHat 5 and Oracle 10g required more attention and work and you can run into some very strange issues.

1. Bonding

A new card (Intel NIC 4 port) has been added to server. All new interfaces have been configured on modprobe.conf. After that a new configuration for bonding has been created. As far looks good. Restart network interfaces and hurray we have bonding interfaces.

So simple RAC reconfiguration and we have ClusterWare using bonding interface.

But unfortunately until server reboot.

After reboot a order of “ethx” interfaces has been changed L

and Eth0 become Eth 4 and so on.

Ok lets change a configurations to new one.

Next restart and .... ?

Next order of interfaces !!!

Hopefully it was not my task, anyway my colleague found a
solution on RH website

After that is was OK.

Bonding was working ...

but RAC ....

see next point



2. RAC

So we have bounding up and running, according to Metalink we have to change interfaces using oifcfg and then change a VIP configuration.
I have stoped a cluster make all changes, restart cluster ...

Hurray is working ... yes ... until reboot of servers.

After reboot only on node was up and running, on second one ClusterWare didn’t start.

Fast check of logs and in ocssd.log I have found

[ CSSD]2009-02-20 19:43:44.758 [1115699552] >TRACE: clssnmReadDskHeartbeat: node(1) is down. rcfg(2) wrtcnt(14467) LATS(11888034) Disk lastSeqNo(14467)
[ CSSD] 2009-02-20 19:43:44.758 [1220598112] >TRACE: clssnmRcfgMgrThread: Local Join

WTF ? Node 1 is up and running and why local join ?

Another review and ...

[ CSSD] 2009-02-20 19:43:44.758 [1147169120] >TRACE: clsc_send_msg: (0x7271b0) NS err
(12571, 12560), transport (530, 113, 0)

My favourite error – no network. Quick check with ping – both interfaces private and public are up. So what is with that box ?
A problem is that bounding need some time after interfaces are up to start working.
If ClusterWare is starting BEFORE binding is 100 % operational, Oracle check only once if there is a other node using network connection and then decide to start ClusterWare in local configuration. But after that it realize that on vote disk other node is already registered so local join is impossible.
BTW in my opinion ClusterWare should be more flexible and check other node more then once.

A solution from Metalink is to add sleep a few second before ClusterWare will be started using /etc/init.d/init.crs

'start')
CMD=`$BASENAME $0`

# If we are being invoked by the user, perform manual startup.
# If we are being invoked as an RC script, check for autostart.
if [ "$CMD" = "init.crs" ]; then
$LOGMSG "Oracle Cluster Ready Services starting by user request."
$ID/init.cssd manualstart
else
$ID/init.cssd autostart
fi
;;


Add 'sleep ' before '$ID/init.cssd autostart', for example, sleep 20 seconds:

else
sleep 20
$ID/init.cssd autostart
fi


At the end of that my personal opinion - previous versions of Linux and Oracle was more stable.
I hope Linux and Oracle will not finish like MS products.

regards,
Marcin

Thursday, February 12, 2009

Strange error

Hi,

Short note to not forget about it.

I have to investigate it more deeply and replicate it, but today I hit into very strange issue.
I have started a standby DB in read only mode, but unfortunately audit_trail was set to DB.
I have had a error that I could not open database in read only with that parameter.
I have changed that and after restart I have got information that my db files need to be recovered to be consistent :-(

Hmmm right now I'm waiting for a airplane but I will come back to that issue.


Update - Friday 13th :)
RTFM ...
It is a mixed environment between PA-RISC and Itanium and standby can't be open in Read Only mode - see metalink note 413484.1

BTW
Another example of very useful error messages !!!

Wednesday, February 4, 2009

RMAN - random errors from years

Hi,


I have been work with RMAN from 8 years and I'm still wondering why some of RMAN errors are taking from /dev/random ;)

Last example:

Environment : Linux 32 bit - Oracle 10g 10.2.04 on ASM

Performed steps:
  1. Drop existing test DB from ASM - using drop database
  2. Copy backup from production server into test server
  3. restore controlfile from new location
  4. mount database
After that I wanted to restore a database. So I have catalog all necessary backup pieces
in controlfile and check it using list backupset command.
There was one backupset with all datafile with correct status. So it is simple let try to restore
DB.

RMAN>restore database;

creating datafile No=1 name=+DATA/oracle/orcl/datafile/o1_mf_system_3n5w1nky_.dbf
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/04/2008 10:54:29
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/dataprd/ORCL/datafile/o1_mf_system_3n5w1nky_.dbf'

Yeah, nice error.
There is some notes on metalink related to duplicated incarnation and corrupted controlfile
(BTW there is a solution to recreate a controlfile from command line before you restore datafiles - it is possible to recreate a controlfile without datafiles ???)

Anyway there was not my case.

A solution is very simple - I have found out that during catalog phase RMAN is scaning existing flash recovery area and I found archive logs in backupset from previous (droped) database and
bacuse there was differect incarnation of that archive log ... incarnation of my new database has been changed too. And now we have a strange behaviour of RMAN.

RMAN> list backupset;

still display a valid backups for that incarnation

RMAN> restore database;

raise error (see above)

Solution:

RMAN> reset database to incarnation xxx;

where xxx is a previous incarnation of database.

I can understand that Oracle could use a backup from previous incarnation in new (but why ?)
but why there is so stuip error about datafile number 1 ?

Is is impossible to display something more useful like there is no backup for that incarnation ?

ps.
All databases have this same DBID - there are clones
I know there is a bad idea to keep one DBID for many databases but I have thought that with RMAN catalog there is no issue.

regards,
Marcin