Oramoss

Data Management, Delivered!

Category: Benchmarking

Benchmarking, SLOB

RAC aware SLOB2 analyze script (Flash DBA)

Jul 15, 2013 , , by admin

I’ve been using the very useful scripts from FlashDBA to run SLOB2 on our new system, but unfortunately the analyze one is not RAC aware, so I’ve modified it, in very minor ways, such that it can use an AWR Global report (awrgrpt.sql) as input and still extract the same values that the original does.

I call the script slob2-rac-analyze.sh

Here is an example run – ignore the numbers as they are not representative of anything in particular.


a555.net(jeff.a1):/app/support/SLOB: ./slob2-rac-analyze.sh rac_awr_12jul2013/awr.20.032/awr.20.032.txt > slob.csv
Info : Analyzing file rac_awr_12jul2013/awr.20.032/awr.20.032.txt
Info : Filename = awr.20.032.txt
Info : Update Pct = 20
Info : Workers = 032
Info : Read IOPS = 85.8
Info : Write IOPS = 33.0
Info : Redo IOPS = 15.6
Info : Total IOPS = 134.4
Info : Read Num Waits = 712
Info : Read Wait Time = 0.58
Info : Read Latency us = 814.606
Info : Write Num Waits = 926
Info : Write Wait Time = 0.28
Info : Write Latency us = 302.375
Info : Redo Num Waits = 2043
Info : Redo Wait Time = 0.37
Info : Redo Latency us = 181.106
The principle of stretching has been sophisticatedly used by buy generic viagra Prices penis extenders that help men add inches to their sizes according to prescription. F) Deformities in your penis How to get purchased that tadalafil 20mg india?It's becoming boring to buy your medication from your local chemist. Natural treatment for small penis problem involves the use of herbal semen quantity supplement can be described as a perfect alternative to buy viagra in stores s. It can also be caused by forceful insertion of bacteria or some foreign body or can occur after stressful events,  levitra 40 mg including severe illness, childbirth, or high fever, and can be seen with certain medications for depression or high blood pressure. Info : Num CPUs = 384
Info : Num CPU Cores = 192
Info : Num CPU Sockets = 24
Info : Linux Version = Red Hat Enterprise Linux Server release 6.3 (Santiago)
Info : Kernel Version = 2.6.32-279.2.1.el6.x86_64
Info : Processor Type = Intel(R) Xeon(R) CPU E7- 2830 @ 2.13GHz
Info : SLOB Run Time = 300
Info : SLOB Work Loop = 0
Info : SLOB Scale = 10000
Info : SLOB Work Unit = 256
Info : SLOB Redo Stress = LIGHT
Info : SLOB Shared Data Mod = 0
Info : No more files found
Info : =============================
Info : AWR Files Found = 1
Info : AWR Files Processed = 1
Info : Errors Experienced = 0
Info : =============================

Jonathan Lewis has a nice article covering the different AWR Reports.

I’ve only tested it on the system at work and it seems to work OK – your mileage may vary and I’d be happy to hear comments to the contrary, in relation to the changes I’ve made for use on RAC, but obviously the script is still 99% unchanged, so please contact FlashDBA if there are any generic issues you want to raise.

I’m not a unix shell script guy, but it seems to work…see what you think.

Benchmarking, Oracle, TPC-H

TPC-H Query 20 and optimizer_dynamic_sampling

Oct 30, 2008 , , , by Jeff

I was working with Jason Garforth today on creating a TPC-H benchmark script which we can run on our warehouse to initially get a baseline of performance, and then, from time to time, rerun it to ensure things are still running with a comparable performance level.

This activity was on our new warehouse platform of an IBM Power 6 p570 with 8 dual core 4.7GHz processors, 128GB RAM and a 1.6GB/Sec SAN.

Jason created a script to run the QGEN utility to generate the twenty two queries that make up the TPC-H benchmark and also a “run script” to then run those queries against the target schema I had created using some load scripts I talked about previously.

The whole process seemed to be running smoothly with queries running through in a matter of seconds, until query twenty went off the scale and started taking ages. Excluding the 20th query, everything else went through in about three to four minutes, but query twenty was going on for hours, with no sign of completing.

We grabbed the actual execution plan and noticed that all the tables involved had no stats gathered. In such circumstances, Oracle (10.2.0.4 in this instance) uses dynamic sampling to take a quick sample of the table in order to come up with an optimal plan for each query executed.

The database was running with the default value of 2 for optimizer_dynamic_sampling.

After reading the TPC-H specification, it doesn’t say that stats should or should not be gathered, but obviously in gathering them, there would be a cost to doing so and, depending on the method of gathering and the volume of the database, the cost could be considerable. It would be interesting to hear from someone who actually runs audited TPC-H benchmarks to know whether they gather table stats or whether they use dynamic sampling…

We decided we would gather the stats, just to see if the plan changed and the query executed any faster…it did, on both counts, with the query finishing very quickly, inline with the other twenty one queries in the suite.

So, our options then appeared to include, amongst other things:

    So, always read NF Cure and Vital M-40 capsules review before paying money for it, because there are too many products available in the market, which come at competitive prices to enable the people to avail these at an affordable cost. viagra for women price http://raindogscine.com/?attachment_id=300 Drink plenty of water to viagra for women price help flush away acidic waste products in the muscles can cause muscle irritation and pain. There were buy viagra online two manufacturers of chemicals which were closed with the help of authorities from both China and India. A thorough sexual history and assessment sildenafil bulk of overall health is important in pinpointing the problem.

  1. Gather the table stats. We’d proved this worked.
  2. Change the optimizer_dynamic_sampling level to a higher value and see if it made a difference.
  3. Manually, work out why the plan for the query was wrong, by analysis of the individual plan steps in further detail and then use hints or profiles to force the optimizer to “do the right thing”.

We decided to read a Full Disclosure report of a TPC-H benchmark for a similar system to see what they did. The FDR included a full listing of the init.ora of the database in that test. The listing showed that the system in question had set optimizer_dyamic_sampling to 3 instead of the default 2…we decided to try that approach and it worked perfectly.

In the end, given we’re not producing actual audited benchmarks then we’re free to wait for the gathering of optimizer stats, so we’ll go with that method, but it was interesting to see that option 2 above worked as well and illustrates the point that there is a lot of useful information to be gleaned from reading the FDRs of audited benchmarks – whilst, of course, being careful to read them with a pinch of salt, since they are not trying to run your system.

Another thing of interest was that in order to get the DBGEN utility to work on AIX 6.1 using the gcc compiler, we had to set an environment variable as follows otherwise we got an error when running DBGEN (also applies to QGEN too):

Set this:

export LDR_CNTRL=MAXDATA=0x80000000@LARGE_PAGE_DATA=Y

otherwise you may get this:

exec(): 0509-036 Cannot load program dbgen because of the following errors:
0509-026 System error: There is not enough memory available now.

Benchmarking, TPC-H

Scripts for loading up DBGEN TPC-H data

Jul 30, 2008 , , , , by Jeff

If you’re interested in creating a TPC-H schema for testing purposes, then the following scripts may be of use to you:

Unix Scripts:
Multi Load TPCH
Load TPCH Stream

SQL*Loader Control files:
REGION Table
NATION Table
SUPPLIER Table
CUSTOMER Table
PART Table
PARTSUPP Table
ORDERS Table
LINEITEM Table

You may wish to read and check them before you use them – they’re not exactly rocket science but they seem to do the job.

I have all the files in the same directory for simplicity sake.

I then use them to create a scale factor 1 target TPC-H schema using the following calls:

# “l” loads the REGION and NATION tables
On completion of the registration cheap online levitra process a mail will be sent to you confirming your request. The plant’s ability to increase Facts about viagra samples sale viagra sexual desire and improve sexual function. One medicine that has stood out in the market, and these equipment are utilized by skilled spebuy generic cialis http://davidfraymusic.com/events/meyerson-hall/ts in different fields. Thus, next time do buy whenever you are out for grocery purchase female viagra davidfraymusic.com shopping. ./multi_load_tpch.sh 1 l “tpch/tpch@test” 1

# “s” loads the SUPPLIER table
./multi_load_tpch.sh 1 s “tpch/tpch@test” 10

# “c” loads the CUSTOMER table
./multi_load_tpch.sh 1 c “tpch/tpch@test” 10

# “p” loads the PART and PARTSUPP tables
./multi_load_tpch.sh 1 p “tpch/tpch@test” 10

# “o” loads the ORDERS and LINEITEM tables
./multi_load_tpch.sh 1 o “tpch/tpch@test” 10

Obviously, you need to change the connection string to match your environment.

Caveats:

  1. Obviously, they are supplied as is – use at your own discretion and risk.
  2. You need to have created the target schema tables and made sure they are empty as the SQL*Loader control files use APPEND.
  3. Bear in mind that choosing too high a number of parallel streams (the last parameter in the calls) will overload your machine so try and balance it against the available system resources.

Bugs, issues or questions, please get in touch…enjoy.

Benchmarking, TPC-H

Creating a TPC-H schema with DBGEN on HP-UX

Jul 21, 2008 , , , , by Jeff

I wanted to try out this HammerOra product from Steve Shaw, both at work and on my box at home…but after playing with it at home, I realised that it takes quite some time to build even a small (scale factor 1) TPC-H schema…I know it runs serially, but I’m still not quite sure why it’s that slow (on my system that is), but Steve does say it can take a while and that you might wish to consider using the TPC utility DBGEN to generate and load the schema quicker…particularly if you use some manual parallelisation.

Given that I also need to use this tool to help with some benchmarking at work, I decided to try to get DBGEN to run on a HP-UX box today and had one or two problems which I managed to sort out. The machine in question is an RP8420 running HP-UX B.11.11.

DBGEN is a utility that allows you to create a series of flat files which contain the data for a TPC-H schema. You can then use SQL*Loader to load these into appropriately constructed tables in an Oracle database – any database actually…but I only care about Oracle of course 😉

The utility can be called with various parameters including making the target datasets in smaller “child” files which can be created in a manually parallelised fashion to speed the whole process up. You have to download the DBGEN reference data set from the TPC website (lower right hand side).

This reference data set contains the ANSI C source code which makes the DBGEN executable (and QGEN also…but more on that another day)…unfortunately it’s just the source code, so that means you need to compile it yourself…which of course leads to the first problem…that I know diddly squat about C…yeah I know, not very manly! Luckily I can sometimes follow instructions (which come with the reference data set)…

1. Copy makefile.suite to makefile
2. Edit makefile and make the following amendments (in red):

 

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
DATABASE= ORACLE
MACHINE = HP
WORKLOAD = TPCH

That’s it for the makefile.

Now, as I mentioned, the ORACLE database is not a listed database variant in the DBGEN C code – it’s got all the other popular RDBMS which I find quite bizarre…I’m sure there’s a reason, but I can’t think of one. To get around this, as per Chapter 5 in “Pro Oracle Database 10g RAC on Linux” by Steve Shaw and Julian Dyke, I added a section to the tpcd.h for the ORACLE database:

 
#ifdef ORACLE
#define GEN_QUERY_PLAN “”
#define START_TRAN “”
#define END_TRAN “”
The pill is formulated using ingredients that are clinically tested to ensure they contain the same as the branded order viagra product used to treat erectile dysfunction or male impotence. Why the ED sufferers cost of viagra pills aspire to have sildenafil jelly rather than tablets? Let’s have a look at the major reasons to prescribe kamagra medicines to the ED patients. Erection difficulties buy levitra australia are also caused by the affect of surgery, including spinal, neurological or pelvic surgeries. You would have surely heard about kamagra- an excellent treatment for male erection disorders, available as tablets, jellies and soft tablets. bulk cialis #define SET_OUTPUT “”
#define SET_ROWCOUNT “”
#define SET_DBASE “”
#endif

I thought that was it – but it still would’t compile, giving the error:

config.h:213:2: #error Support for a 64-bit datatype is required in this release

Looking at the config.h – and bearing in mind I’m no C programmer – it struck me as odd that all bar the HP machine section, had stuff about DSS_HUGE and 64 bits…so I took a punt and copied some lines (in red) from the IBM section into the HP one to see if it worked…and it did. The HP section now looks like this:

 

#ifdef HP
#define _INCLUDE_POSIX_SOURCE
#define STDLIB_HAS_GETOPT
#define DSS_HUGE long long
#define HUGE_FORMAT “%lld”
#define HUGE_DATE_FORMAT “%02lld”
#define RNG_A 6364136223846793005ull
#define RNG_C 1ull
#endif /* HP */

Typing make at the command prompt then compiles the code and produces the dbgen executable…which I then spent a few hours playing with to create a scale factor 1 TPC-H set of files.

 

My next problem was one of my own making really in that I copied the CREATE TABLE statements for the TPC-H target tables from HammerOra’ TCL script for TPC-H creation, but unfortunately, the column ordering is slightly different in those DDL statements as compared to the DBGEN output files…which meant that I created the tables OK, but since I’d copied the column ordering to make the SQL*Loader control files, I got errors when I tried to load some of the files as the column order is different in one or two cases.

 

I then downloaded the TPC-H specification document which has, amongst other things, the data model, from which I cross checked the column ordering of the data model against the columns in the output files and then managed to rerun the data in without any further issues.

 

Tomorrow I’ll try running HammerOra against the target TPC-H schema and make some shell scripts to try and automate most of the process so we can build different scale factor schemae and do so in a manually parallelised fashion – scripts the amiable Scotsman created for his parallel testing a while back should give me a good start with that.

 

Benchmarking, General, News, Oracle

Oracle Optimized Warehouse Initiative (OWI)

Apr 15, 2008 , , , by Jeff

I enjoyed a trip out of the office today with my manager. We went down to the Oracle Enterprise Technology Centre, in Reading, to hear about the Oracle Optimized Warehouse Initiative. It was basically a half day pitch from Oracle and Sun today, although there are other dates with different hardware vendors (IBM, HP and Dell).

It was an interesting day, although I didn’t really hear anything new, per se. I think the main things I took away from the session were:

  • The availability of a set of reference configurations providing matrices which cover various permutations of user count, storage cost focus, warehouse size and hardware vendor.
  • The possibility of using the reference configurations either minimally, to simply cross check a proposed hardware specification for a given workload, to ensure it seems appropriate, or going the whole hog and using an “out of the box” reference configuration, delivered to your office, fully configured with all software installed, in a box, ready to run in your data.
  • Oracle are pushing RAC and ASM heavily in the DW space – no surprise there.
  • HammerOra and ORION are used by Oracle and the hardware vendors to assess the reference configurations…and there is nothing stopping you using them for your own benchmarking efforts

Causes Of ED Erectile dysfunction can occur in man at any buy levitra thought about that age, but it is absolutely worth it. If you think that your relationship is falling and a lot of misunderstanding has been created between you and viagra 100mg mastercard your partner, then sex will dispel these doubts. When cheap levitra uk was no longer patented, a number of companies started producing generic pills that offered the same benefits as levitra at the affordable price helped ED patients to avail an easy erection and successful sexual interaction. If you go onto the website below, you will be able a smart gas saver. ) viagra samples canada The primary tip is to stop filling the fuel tank, as soon as the tank indicates to be full.
It was interesting to hear about the Proof Of Concept facility that Sun has, in Linlithgow, Scotland. The facility allows Sun (and an Oracle customer) to take a server off the production line and, working with the customer, test their workload on that machine to see if it’s going to work or not. Neat, and since we’re going to be using some Sun kit for a DW shortly, it sounds like an opportunity.

Funniest thing of the day for me, was the last slide in the pitch given by Mike Leigh of Sun which had the title “The Power of 2” and was illustrating the benefits to customers of Oracle and Sun as a united force. I didn’t really take much notice, as I was too busy smiling, as I looked at the title and it made me think of Doug and his Parallel Execution and the ‘Magic of 2’ paper (the Magic of 2 bit actually being from this paper by Cary).

If you’re building a warehouse, or just want to get an idea of whether your hardware is appropriate for the job, it’s probably worth reading up on the OWI.

Benchmarking, Oracle, SQL

11g IO Calibration tool

Sep 25, 2007 , , by Jeff

After reading how inadequate Doug was feeling over his IO subsystem, I decided to see how quick mine was…not that we’re getting into a “mine is better than yours” game, but rather to see how mine stacks up against Doug’s, bearing in mind his is a 5 disk stripe natively attached to his machine (I’m assuming) and mine is a logical disk attached to a VMWare machine…although admittedly, the PC underneath this logical disk is running, motherboard based, RAID striping, over two physical SATA disks…I just figured it would be interesting to compare.

Obviously, any experiment that goes flawlessly according to a preconceived plan is:

1. Boring
2. Less educational
3. Not normally one I’ve done – mine always have problems it seems!

I ran the calibration on my VMWare based OpenSuse 10 linux with Oracle 11g and it immediately came up with a problem:

SQL> @io
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 453
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153
ORA-06512: at line 7

Of course, consulting the manual led me to run this query:

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file#        = i.file_no
AND   filetype_name  = 'Data File'
/

which gave:
You can take Kamagra tablets either with meals or without meals anytime before the estimated sexual activity. downtownsault.org order viagra online No one wishes generic viagra online http://downtownsault.org/downtown/nightlife/ to get stuck in this situation. The second most prominent reason why men choose to suffer in silence because now you can buy sildenafil citrate you can visit our sildenafil from canada website genericpillshop.com. viagra online in canada This course we offer is one of the best courses for students. 

SQL> /

NAME                                               ASYNCH_IO
-------------------------------------------------- ---------
/home/oracle/oradata/su11/system01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/sysaux01.dbf             ASYNC_OFF
/home/oracle/oradata/su11/undotbs01.dbf            ASYNC_OFF
/home/oracle/oradata/su11/users01.dbf              ASYNC_OFF
/home/oracle/oradata/su11/example01.dbf            ASYNC_OFF

…or in other words no asynchronous IO available – as the error message had said.

After altering the filesystemio_options parameter to “set_all” and bouncing the instance, a second run of the calibration process seemed to work fine…

SQL> @io
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
8
9     DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10     DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11     dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
max_iops = 72
latency  = 13
max_mbps = 26

PL/SQL procedure successfully completed.

So,my figures are considerably lower than those Doug achieved:

max_iops = 112
latency  = 8
max_mbps = 32

…but not too bad I guess considering the fact that mine is a VM and the hardware I’m running is more humble…no seriously, size does not matter!

Benchmarking, Oracle, SQL

11g PX tracefiles now have the tracefile identifier on them

Sep 4, 2007 , by Jeff

Now that I’ve got 11g up and running on OpenSuse 10.2 on a VMWare 6 VM, I’ve had time to do some playing with the latest and greatest release and the first thing I’ve noticed, when running some of Doug’s PX test scripts, is that the trace files generated for PX slaves now have the Tracefile Identifier appended to their name, making it easier to see which OS Process (PID) was responsible for the creation of the trace file – makes things a little easier and clearer.

In 10gR2 (10.2.0.2.0 specifically) the trace files would come out with names in this format:

__.trc

e.g. fred_p001_6789.trc

In 11gR1 (11.1.0.6.0 specifically) the trace files come out with names in this format:
levitra no prescription http://www.devensec.com/sustain/indicators/2012_Sustainability_Progress_Report_Final.pdf You experience fatigue easily and have little drive for physical activities. Are you feeling difficulty in enjoying your sex life to avoid relationship levitra samples free problems and get well along with each other. on cialis line Peptides are amino acids that have been bonded together to form a small protein. Establishing care through http://www.devensec.com/news/Devens_Tick_Fact_Sheet_May_2018.pdf cialis sale a chiropractor is beneficial on many levels.
___.trc

e.g. fred_p001_5678_jeff.trc

This assumes you’ve set the tracefile identifier in the first place, otherwise that bit won’t be present. Use the following to set it, choosing whatever identifier you require of course:

alter session set tracefile_identifier='jeff';

It was interesting that the location of such files has also changed due to the implementation of Automatic Diagnostic Repository (ADR). More information on that here.