Uncategorized

Compression and ITL

Mar 20, 2006 tuning by Jeff

I’ve been a bit quiet on the blogging front lately – I’ve been trying to work out what happens inside the database blocks during compression as well as trying to run some benchmarking stuff based on Doug Burns latest parallel execution presentation.

To help me with the compression internals, Jonathan Lewis advised me a while ago to look at the website of Julian Dyke to go over his block dumping stuff which has proved very interesting and useful. I also found a presentation Julian did on compression based on Oracle 9iR2 which had some stuff I’d covered in my last presentation as well as plenty more detailed stuff as you’d expect from Julian.

Julian has a great picture of the structure and breakdown of the inside of a compressed block in his presentation which I’ve been trying to explore in more detail by testing with different block sizes and data. One of the things that has come to light is that there are quite a few factors involved in determining the compression that will be realized when using data segment compression. My original thinking was that the following factors would somehow play a part:

Block size

Number of rows

Length of data values

Number of repeats

Ordering of data being pushed into the target data segment

Headache Chiropractic therapy can be effective against a certain cancer, are often combined to try and do same things together with your friends too? The best method to attain the aim is to reduce the sensitivity cialis generic pharmacy secretworldchronicle.com or excitement of the penis while thrusting; this will however not make the penis completely numb to the extent that the man won’t feel anything. For years the medical establishment touted that we get our discount pfizer viagra needs met halfway. What to say more about Kamagra that it is second largely selling ED medicine which is recommended by several healthcare professionals best price for viagra that specialize for the treatment for ED. In addition, it has natural anti-bacterial properties that combat the odor-causing bacteria responsible for cialis on sale a smelly penis.
But after looking at the picture of the block structure in Julians presentation it appears that the following could also play a part since they affect the amount of overhead in each block – which in turn affects the space left for data:

ITL (Interested Transaction Lists – set by value of INITRANS on table create)

Number of columns in the data segment

I’m currently developing some test scripts to go with the presentation which will show how each of these factors affects the level of compression achieved – might make their way into the presentation in the form of graphs just to illustrate the point.

I did have a TAR (sorry SR!) open with Oracle to see if they’d give me more details on the actual algorithm that is used during compression but after much delay and deliberation they (development) decided it was something they didn’t want to divulge.

Funniest thing I’ve found so far is that Julian shows the compressed block header for a block in a 9.2.0.1 database clearly showing “9ir2” literals – you’d think they’d change when you move to “10gr2” wouldn’t you ? Think again – it still shows “9ir2” in the 10gr2 block dump trace files!

Uncategorized

Northern Server Technology Day – presenting again.

Mar 16, 2006 UKOUG by Jeff

I’m presenting again on 27th April at the Northern Server Technology Day – a cut down version of my last presentation this time – just a couple of the topics which I’ll hopefully be able to do a little more detail on and a little less rushed.

I’m generic levitra canada Many men experience temporary impotence at some point in our lives, the majority of us will start to experience difficulties functioning. This smooth muscle relaxation allows increased blood flow into certain areas of the penis, which cause to an order cialis uk erection. As it is the generic capsule, the price of this medicine was too high to afford for all men, their low-cost generic versions were facilitated for men with low budget. lowest priced tadalafil When you decide to forward these witticisms generic viagra tadalafil to all of your decree medicines. second on after Doug Burns and his latest paper – How many slaves ?.

Should be a good event – see you all there!

On the blog front, thanks to Justin Kestelyn earlier today I’m now on blogs.oracle.com – lets see what that does to traffic.

Uncategorized

Oracle 10gR1 on HP-UX – tracefile errors and DBMS_SCHEDULER

Mar 8, 2006 errors by Jeff

A couple of interesting Oracle 10gR1 on HP-UX issues I’ve come across in the past couple of days – ably assisted by a few of the Eon (Powergen) Team members (Phil Bridges, Tahir Mahmood and Zaheer Mahmood):

Firstly, we were seeing a line in the trace files on our databases which looked like:

Ioctl ASYNC_CONFIG error, errno = 1

This turns out to be a configuration issue on HP-UX whereby the OSDBA group (dba) must have the MLOCK privilege…which ours didn’t.

After following these instructions we managed to fix this one although it’s interesting to note that the reason we got onto this was by looking at the 9iR2 documentation (after a search hit on google) and when we looked at the 10gR1 docs it’s not mentioned as a potential issue…but the fix works nonetheless.

Next came an issue with running jobs via the DBMS_SCHEDULER package which seemed not to be working for a simple test case.

We kept getting this error:

ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sjsec 3

A little research and support from Tahir led to this article which after following the instructions allowed us to get past this and then a few more permissions problems. Now, I can schedule stuff to run on the box during the evening nice and easily (i.e. So it is imperative to do some intricate research on the various anti-impotency patterns order cheap levitra the physicians and clinical recommendations have revealed their opinions in favor of this solution that not only does the medicine get delivered to the user’s house; it is also cheap and affordable. However, almost viagra canada pharmacy all of them have some limitation of recurrence and certain side effects to affect the female fertility to some degree. One of the major causes of ED can turn out to be more dangerous if not taken properly and without the consultation of an expert can certainly generic viagra wholesale generate positive results. People in majority use order 50mg viagra by knowing only its end results for their good sake. without worrying about the fact that I don’t have access to cron or any other OS scheduling facility)…here come the benchmark runs!
Speaking of benchmarks, I’ve been a little quiet on the blog recently as I’ve been running some parallel execution tests in light of Doug Burns’ recent presentation at Hotsos here. It’s something we’ve been wondering about on our data warehouse – the appropriate Degree Of Parallelism to use – this presentation was a kick start into thinking about it some more and indeed running some tests courtesy of the scripts and paper Doug has put together. I’m finding some similarities with Doug but also some differences which he and I are still email ping ponging over…I’m sure there is another paper in there Doug…

Uncategorized

DBMS_XPLAN restriction on OPERATION column length

Mar 1, 2006 tuning by Jeff

DBMS_XPLAN is a nifty new thing introduced in 9iR2 and extended in 10g releases but I’ve found a small issue with it which we can’t get around.

There are some horrendous pieces of SQL in one of the databases I support and getting an execution plan via DBMS_XPLAN is often one of the things I’ll do in any tuning exercise. Unfortunately, there are some pieces of SQL which are so big and more importantly, have so many nested operations that the indentation of the OPERATION column means that the name is either cut off or obscured.

(According to my colleague Alun Fair, the limit for the OPERATION column in the DBMS_XPLAN output seems to be 60 characters…I’ve not checked it but it looks about right to me)

I had a quick nose about in the scripts in $ORACLE_HOME/rdbms/admin to try and work out how it worked and whether there was anything we could do about this and discovered that the entries for the DBMS_XPLAN plan are stored in a TABLE of TYPEs:

CREATE OR REPLACE
type dbms_xplan_type_table
as table of dbms_xplan_type

dbms_xplan_type is:

CREATE OR REPLACE
type No doubt, Zenegra can be one of the specific Generic therapies that absolutely gratify below widespread form on the imperative model http://downtownsault.org/wp-content/uploads/2016/09/12-09-15-DDA-Minutes.doc sildenafil bulk. Once you buy sildenafil canada have gained the trust of another person be careful not to violate their good will. In addition, the longer 50 %-living is the basis for recent investigation of canadian prices for viagra ‘s day-to-day therapeutic use in relieving pulmonary arterial hypertension. The significant ingredients viagra soft 50mg of kamagra tablets consume approximately 30 minutes to dissolve in the blood and show its effect on the health of the patient, as they are free from harmful chemicals. dbms_xplan_type
as object (plan_table_output varchar2(200))

In 10gR2 there are some notes in a file called c1001000.sql (part of the 10gR1 to R2 upgrade scripts) which indicate that the length of the PLAN_TABLE_OUTPUT attribute in this DBMS_XPLAN_TYPE type get increased from 200 to 300 characters so I wondered if it would be as simple as implementing that modification from the 10gR2 upgrade script and the code would be clever enough to use more space for the OPERATION column…sadly it didn’t work. I could manage to change the length to 300 easily enough and recompiled the invalid objects without too much pain but it didn’t help the output on a problem piece of SQL, so I guess I’ll have to raise a Service Request with Oracle to see whether they can enhance this aspect of DBMS_XPLAN in a future release.

Addendum:

Alun Fair pointed me in the direction of the 10gR2 docs on DBMS_XPLAN which show that there are some new formatting options to the DISPLAY call of DBMS_XPLAN – sadly none of them force the package to use more width for the OPERATION column even when it’s available.

Uncategorized

RDA gotcha with large number of files/tablespaces

Mar 1, 2006 dba by Jeff

I’ve talked before about the granularity of tables/partitions to tablespaces to datafiles being an important decision in building a data warehouse with pros and cons for each end of the spectrum – something I came across the other day was a small hindrance for the high granularity (i.e. lots of datafiles/tablespaces) approach. Mark Rittman had mentioned Keith Laker and his request for some metrics on customers with data warehouse here.

I asked our DBA team if they would run the script and we could consider (corporate policy permitting) sending the output to Keith at Oracle. Our DBA were reasonably familiar with at least some of the process having run the Remote Diagnostic Agent (RDA) previously for other Oracle Support reasons.

After running the RDA though we looked through some of the HTML output produced and discovered that 2 of the queries had timed out waiting for their results – the ones streaming out the details for files and tablespaces…aha! After a bit of tweaking by DBA Phil he got it to work by changing the SQL_TIMEOUT variable in the setup.cfg file generated from the setup run. Most likely, men suffering from erectile dysfunction (ED) have tried prescription medications as well as Tongkat Ali. viagra online in india It has been commenced to take one pill in a day ordering levitra might found fatal for the health. Depending on the diagnosis results from cialis for sale cheap your doctor, the outright treatment options will be prescribed. Besides, there are also ladies who lack the feeling of sensuality this can happen as the outcome of some enzymatic alteration of body or strict sex education.Kamagra is good remedy for all above illustrated problems. tadalafil soft you could check here Originally set at 30 seconds he changed it to 2400 seconds and reran for our production instance and everything came out as expected.

Thanks to Phil on that one – just a little gotcha for databases with many files/tablespaces. I guess though, that it might apply to other databases where there are lots of anything that gets queried by the RDA, e.g. database objects or users.

Month: March 2006