Like any DBA I have a warchest of old tuning stories. I tend to use them to illustrate a point to a developer who can't see the reason for changing the way the program runs.
The easy low hanging fruit in tuning is finding the dodgy SQL which is not using a index. The performance increase with proper indexing can be truly amazing.
Given the large variety of index types and options available in Oracle, there will be an index for any tuning occasion. You have got to keep in mind though what the application is trying to achieve overall. Grabbing every row individually via an index, when you are going to read the whole table anyway should be dealt with differently then a complex or simple SQL join which randoms picks the rows it wants (adhoc user and search type queries).
I was talking to a developer about the log aggregation process which had failed and was taking a long time to catch up, about 8-10 hours for each days worth of weblogs. The application was in twlight zone, one of the applications and databases where the reason for it continued existence is questionable i.e. as long as it is stable and up, not much time is allocated to stressing over it.
A quick check of the logs produced nailed down the offending SQL.
select ... from t1, t2
where to_char(t1.username) = to_char(t2.username)
This was taking the 8-10 hours to run.
I checked the statistics, and added a function based index to t1.username
create functional index fx_t1_username
on t1(to_char(username))
tablespace ...
We rerun and it now takes.... 1 minute.
Now instead of the database being the slow portion of the whole process it is the perl scripts which aggregrate the weblogs. 1 hour per day. The catchup, instead of taking the rest of the month was finished in 12 hours.
Have Fun
Tuesday, June 28, 2005
Tuesday, June 21, 2005
AUS Credit Growth Year over Year
Here is the graph I was talking about earlier. It shows the credit growth by broad type (RBA defined).
It is current up to March 2005.
The most interesting info is the fact that housing credit has never been below 10% the start of 1984!!
When I upload the total credit, you will see what 20 years of compounding more than 10% does.
Housing affordability eh :)
Have Fun
It is current up to March 2005.
The most interesting info is the fact that housing credit has never been below 10% the start of 1984!!
When I upload the total credit, you will see what 20 years of compounding more than 10% does.
Housing affordability eh :)
Have Fun
Sunday, June 19, 2005
Media is becoming pull not push
Here is a article really a rant about podcasting which completely misses the point how media is changing. Podcasting is cast as a step back from broadcast radio. What about all those people from around the world who can now listen the writer's radio content via podcasting.
Media has been a push medium, where the content is broadcast (pushed) at set times. Normally only once. You miss it and its gone.
Podcasting, blogs enabled by RSS and Tivo in TV land are pull. A pull medium allows the consumer of the content to decide what to listen/read/watch and more importantly when to listen/read/watch.
RSS is the protocol which allows software to check a website from new content and download it.
The consumer is in control of the content they consume. They can also consume content which they could never have been able to in the past i.e. overseas content.
The real beauty of podcasting for me is the amazing content from US technology conferences available from the ITconversations website. You can listen and in most cases see the same slides as someone who went there. The other important part is you can rewind or relisten to the presentation. So even if you were at the same conference and had to choose which to presentation, you can still see and listen to the rest.
My living standard has improved because of podcasting!!! As now I can listen and see material I which would have cost a small fortune to attend!! or in the case of radio shows never heard.
Have Fun
Media has been a push medium, where the content is broadcast (pushed) at set times. Normally only once. You miss it and its gone.
Podcasting, blogs enabled by RSS and Tivo in TV land are pull. A pull medium allows the consumer of the content to decide what to listen/read/watch and more importantly when to listen/read/watch.
RSS is the protocol which allows software to check a website from new content and download it.
The consumer is in control of the content they consume. They can also consume content which they could never have been able to in the past i.e. overseas content.
The real beauty of podcasting for me is the amazing content from US technology conferences available from the ITconversations website. You can listen and in most cases see the same slides as someone who went there. The other important part is you can rewind or relisten to the presentation. So even if you were at the same conference and had to choose which to presentation, you can still see and listen to the rest.
My living standard has improved because of podcasting!!! As now I can listen and see material I which would have cost a small fortune to attend!! or in the case of radio shows never heard.
Have Fun
Wednesday, June 15, 2005
Oracle: Gotcha 1
I did an patch upgrade to 10G R1 (10.1.0.2) -> 10.1.0.4 on linux last night.
The patch went thru fine, cygwin x-window connected to linux with xauth.
A couple of clicks and then simple @?/rdbms/admin/catpatch.sql from sqlplus and its done.
However we have been having issues with Oracle being able to see more than 3 Gig of memory on linux. There is a solution, however I haven't seen the solution. I will shortly...
I restart the db with a older parameter file, save that as spfile and do the catpatch.
However the alert.log complained about the parameter PARALLEL_AUTOMATIC_TUNING being depreciated. I remove the parameter and restart.
Later on the production support blokes can't connect, they are getting TNS-12516, TNS-12560 errors. I log on from home can tnsping, can connect locally.
Eventually even sqlplus "/ as sysdba" as oracle from unix doesn't connect (without any errors as to why). Finally Oracle throws an max processes exceeded error when I try to connect as a different user.
It turns out setting the depreciated parameter PARALLEL_AUTOMATIC_TUNING = false (or not specifying it) means Oracle sets the processes to 30 automatically!!!!
I found that info in a forum entry on metalink (Oracle support website). Apparently that behaviour is undocumented and was raised to be documented. It is not there yet as I use the Oracle documentation libraries from tahiti.oracle.com (requires technet login)
I fixed set the PROCESSES parameter to a reasonable number and the connection isssues (and TNS errors) disappear.
Have Fun
The patch went thru fine, cygwin x-window connected to linux with xauth.
A couple of clicks and then simple @?/rdbms/admin/catpatch.sql from sqlplus and its done.
However we have been having issues with Oracle being able to see more than 3 Gig of memory on linux. There is a solution, however I haven't seen the solution. I will shortly...
I restart the db with a older parameter file, save that as spfile and do the catpatch.
However the alert.log complained about the parameter PARALLEL_AUTOMATIC_TUNING being depreciated. I remove the parameter and restart.
Later on the production support blokes can't connect, they are getting TNS-12516, TNS-12560 errors. I log on from home can tnsping, can connect locally.
Eventually even sqlplus "/ as sysdba" as oracle from unix doesn't connect (without any errors as to why). Finally Oracle throws an max processes exceeded error when I try to connect as a different user.
It turns out setting the depreciated parameter PARALLEL_AUTOMATIC_TUNING = false (or not specifying it) means Oracle sets the processes to 30 automatically!!!!
I found that info in a forum entry on metalink (Oracle support website). Apparently that behaviour is undocumented and was raised to be documented. It is not there yet as I use the Oracle documentation libraries from tahiti.oracle.com (requires technet login)
I fixed set the PROCESSES parameter to a reasonable number and the connection isssues (and TNS errors) disappear.
Have Fun
Tuesday, June 07, 2005
Interesting article about Trade Deficit
By now you might have guessed my low opinion of the MSM economic reporting and opinion. Here is an interesting article about trade deficit and housing on another Australian economics blog
I need to create a flckr account so I can upload some nice graphs here. The main one I want to show is credit growth.
Have Fun
I need to create a flckr account so I can upload some nice graphs here. The main one I want to show is credit growth.
Have Fun
Reason for it all
Why roobaron? Started as a joke and it stuck.
In Aus. we have beer barons, media barons and cattle kings. So why not one animal which is unique to Australia, the kangaroo or known locally as roo or due to their massive numbers roos. So was born the nick "roobaron".
I remember a while back there was a media storm about the culling of roos which I think even made it to the UK (I was there at the time). The population has seriously exploded due to the number of extra watering points such as bores and dams which have been built to sustain the sheep and cattle.
Kangaroo tastes pretty good too. Must be cooked medium to medium rare due the low fat marbling.
Now I am just rambling...
At some point I will get over it and use my real name rather than a nick. I have been using the internet for a long time using nicks is second nature as anonymity was/is a big part of the internet.
Back in them olden days sonnie almost everyone used a nick.
Have Fun
In Aus. we have beer barons, media barons and cattle kings. So why not one animal which is unique to Australia, the kangaroo or known locally as roo or due to their massive numbers roos. So was born the nick "roobaron".
I remember a while back there was a media storm about the culling of roos which I think even made it to the UK (I was there at the time). The population has seriously exploded due to the number of extra watering points such as bores and dams which have been built to sustain the sheep and cattle.
Kangaroo tastes pretty good too. Must be cooked medium to medium rare due the low fat marbling.
Now I am just rambling...
At some point I will get over it and use my real name rather than a nick. I have been using the internet for a long time using nicks is second nature as anonymity was/is a big part of the internet.
Back in them olden days sonnie almost everyone used a nick.
Have Fun
Thursday, June 02, 2005
Trade deficit and Foreign Debt - 3
Another month and another bunch of cherry picking economic reporting and analysis from the leading newspapers.
The ABS report releases a couple of different numbers; The trend estimate, seasonally adjusted and original. Most commentators seem to cherry pick the numbers to build a case for their report.
I will continue to use the original numbers where available rather than averages (Trend) of estimates, modified using a price deflator (Seasonally adjusted).
The Balance on the Current Account improved 19%! improving $3.215 Billion!
The Balance on Capital and Financial Account naturally improved as well by the same amount.
My bet is an adjustment to the figures will be required in the next couple of months.
I am still amazed at the amount of print and air time the woes of the rural sector have in comparison to their actual proportion of Australia's overseas trade.
Rural Goods exports = $6.390 Billion
Non-rural Goods exports = $22.636 Billion, almost 4 times as big or 80% of the total Goods exported. I looked up some sample reports that the ABS has and COAL is Australia's main export.
There are some nice historical spreadsheets on the Reserve Banks website. Basically the rural portion of Australian exports has been decreasing since the 1950s.
Even Treasurer Peter Costello got in on the act, saying the rural downturn was dragging on the national accounts. Big woop!
If we diverted some of the fresh monsoon rain which flows straight back out to sea towards inland Queensland, providing extra moisture, we could be the world's steak sandwich, or Teraraki Beef with rice, depending on which country the food is going to.
Unfortunately the fallacy of comparing foreign debt to income (GDP) continues. Even the ABS gets in on the game. Australia's net liability position is now 66% of GDP. It was 52.7% a decade ago.
Lending criteria is based on existing assets and incoming producing capacity.
In December 1994, the GDP stood at $139.530 Billion, in December 2004 it stood at $200.287 Billion. That is a 43.5% growth in GDP over ten years.
In December 1994, total foreign debt stood at $244.492 Billion, December 2004 it stood at $548.464 Billion. That was a 124% increase in ten years. Hence the increase as a percentage of GDP.
Before we throw the towel in, and start yelling "Banana Republic", we need to see how much the asset base grew. That is a task for stats from the ATO (a future post). We already know that the tax figures at saving we have an asset base around $4000 Billion or $4 Trillion. And that was just for reasonable sized businesses.
Clearly not all that extra debt when into investing in business. If you live in Sydney, Melbourne or Brisbane you already know the probable answer, Property. Sure enough, the RBA has a nice spreadsheet showing credit growth.
Property credit has rocketed, taking on the old exponential trajectory. Interesting enough it has never been below 10% year over year since 1982. It peaked at 20% twice in the last 5 years.
The other interesting tidbit is the increasing portion of the property credit which is securitised. Securitisation is the buzz financial tool in the US. Basically you, the lender bundle up a group of loans, low risk with a sprinkling of higher risk loans and offload/sell those loans as an asset! Hence the use of Mortgage Backed Assets (MBS) jargon in the US. This offloading clears the decks and allows you, the lender to turn about and lend again! A more detailed explanation can be found at the Prudent Bear. Doug Noland really nails this practice.
Business credit growth recovered rapidly from the nadir of the 1990-1992 recession "we had to have" but peaked in 1996. However recently it has started to pick again. My guess is commodity prices have allowed companies to start thinking about borrowing to explore, build and invest in new mine equipment. The longer the China build,US spend boom continues the more likely the trade deficit will go closer to zero, as increasing volume of commodities from Australia fuel the chinese manufacturers appetites.
I would love to show the graph of credit growth, however blogger doesn't allow inline images, they must come from somewhere else. I will sort out that somewhere else in time.
Have Fun.
Update:
The ABS released April trade figures. The increased price of goods and services kicked in, as volumes remained stable or declined slightly. More info to digest. The figures for May should be interesting, more so if the volumes pick up.
Previous articles:
Trade Deficit and Foreign Debt -1
Trade Deficit and Foreign Debt -2
Next articles:
Trade Deficit and Foreign Debt -4
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners
The ABS report releases a couple of different numbers; The trend estimate, seasonally adjusted and original. Most commentators seem to cherry pick the numbers to build a case for their report.
I will continue to use the original numbers where available rather than averages (Trend) of estimates, modified using a price deflator (Seasonally adjusted).
The Balance on the Current Account improved 19%! improving $3.215 Billion!
The Balance on Capital and Financial Account naturally improved as well by the same amount.
My bet is an adjustment to the figures will be required in the next couple of months.
I am still amazed at the amount of print and air time the woes of the rural sector have in comparison to their actual proportion of Australia's overseas trade.
Rural Goods exports = $6.390 Billion
Non-rural Goods exports = $22.636 Billion, almost 4 times as big or 80% of the total Goods exported. I looked up some sample reports that the ABS has and COAL is Australia's main export.
There are some nice historical spreadsheets on the Reserve Banks website. Basically the rural portion of Australian exports has been decreasing since the 1950s.
Even Treasurer Peter Costello got in on the act, saying the rural downturn was dragging on the national accounts. Big woop!
If we diverted some of the fresh monsoon rain which flows straight back out to sea towards inland Queensland, providing extra moisture, we could be the world's steak sandwich, or Teraraki Beef with rice, depending on which country the food is going to.
Unfortunately the fallacy of comparing foreign debt to income (GDP) continues. Even the ABS gets in on the game. Australia's net liability position is now 66% of GDP. It was 52.7% a decade ago.
Lending criteria is based on existing assets and incoming producing capacity.
In December 1994, the GDP stood at $139.530 Billion, in December 2004 it stood at $200.287 Billion. That is a 43.5% growth in GDP over ten years.
In December 1994, total foreign debt stood at $244.492 Billion, December 2004 it stood at $548.464 Billion. That was a 124% increase in ten years. Hence the increase as a percentage of GDP.
Before we throw the towel in, and start yelling "Banana Republic", we need to see how much the asset base grew. That is a task for stats from the ATO (a future post). We already know that the tax figures at saving we have an asset base around $4000 Billion or $4 Trillion. And that was just for reasonable sized businesses.
Clearly not all that extra debt when into investing in business. If you live in Sydney, Melbourne or Brisbane you already know the probable answer, Property. Sure enough, the RBA has a nice spreadsheet showing credit growth.
Property credit has rocketed, taking on the old exponential trajectory. Interesting enough it has never been below 10% year over year since 1982. It peaked at 20% twice in the last 5 years.
The other interesting tidbit is the increasing portion of the property credit which is securitised. Securitisation is the buzz financial tool in the US. Basically you, the lender bundle up a group of loans, low risk with a sprinkling of higher risk loans and offload/sell those loans as an asset! Hence the use of Mortgage Backed Assets (MBS) jargon in the US. This offloading clears the decks and allows you, the lender to turn about and lend again! A more detailed explanation can be found at the Prudent Bear. Doug Noland really nails this practice.
Business credit growth recovered rapidly from the nadir of the 1990-1992 recession "we had to have" but peaked in 1996. However recently it has started to pick again. My guess is commodity prices have allowed companies to start thinking about borrowing to explore, build and invest in new mine equipment. The longer the China build,US spend boom continues the more likely the trade deficit will go closer to zero, as increasing volume of commodities from Australia fuel the chinese manufacturers appetites.
I would love to show the graph of credit growth, however blogger doesn't allow inline images, they must come from somewhere else. I will sort out that somewhere else in time.
Have Fun.
Update:
The ABS released April trade figures. The increased price of goods and services kicked in, as volumes remained stable or declined slightly. More info to digest. The figures for May should be interesting, more so if the volumes pick up.
Previous articles:
Trade Deficit and Foreign Debt -1
Trade Deficit and Foreign Debt -2
Next articles:
Trade Deficit and Foreign Debt -4
Trade Deficit and Foreign Debt -5
Related Articles:
Australian Trade Partners
Subscribe to:
Posts (Atom)