Re: ASM for single-instance 11g db server?

> Any wonder why I consider those certifications and tools as just noise when
> selecting anyone or anything for a modern dba job?

DBA shold be at least 30% sysadmin and/or netadmin too. Or at least in close collaboration with both: Same room or something like that.

An example (long): Slow, very slow application (Oracle in this case but it's not really relevant nor the application. Names and most details dropped to protect the guilty parties).

Application supplier claims response times (at application level) less than a second, we notice response times from several seconds up to timeout (30s) at QA level, with way more powerful servers than developers are using.

Supplier could demonstrate this in their own network and in laboratory environment we could do the same, but not in QA/production (hardware in other town and in leased DC).

Three reasons for this and I think you've seen all three before. ;)

First: In this case the application server, db-server and fileserver were in adjacent racks, but the routing between application server and db-server seemed very odd.

Leased DC and the support claims there that there's nothing wrong.

Of course simple tools like traceroute are blocked: no way to _know_ what happens. I had to insist that they deliver me the printout of traceroute and then I'll believe there's nothing wrong.

I didn't ever get the printout, but support eventually admitted that there really was something wrong:

All packets from application server were sightseeing in couple of nearby towns before arriving to db-server. And another tour while going back. ;)

It's a wonder application was running at all. There had been also a lot of misconfigured firewalls along the route as sqlnet port should have been shut tight from outside world in most of the places it visited.

Second: Application developers were making a lot of "select * from xyz", delivering all rows to application and doing the selection at the application level, which meant that in some cases data transferred from DB to client was 30MB per query(!). It's not OK even in a local gigabit network but unusable when your servers are 200 miles away and you have >200 simultaneous users. Vendor had testers and db-servers almost side by side and small datasets: No wonder it was fast. Real classic, this one. :)

Application by itself was very good, "just" technical stupidities, so vendor hired good SQL-guys to change the application logic (there are advantages in being a big company buying software from a small company) so that just few rows were returned and all the logic was done in SQL, not in the application. Currently application loads about 300K per query maximum and there's still some room for improvement. Much, much faster.

Third: Views upon views. Not just few but 6 or 16 stacked(!). Developers needed some fields changed or different where- section from an existing view so they used it and built another view on top of it, layer by layer. Nobody had bothered to create new views from the roots, base tables.

Some queries moved tens of megabytes of data (with explain plans several pages long) to gain an outcome of couple of rows, less than 1K and the query giving same result, totally rewritten and without 10 level of views between, moved 50K, 1000-fold drop.

Vendors SQL-guys have started to fix these also, another significant performance increase coming, I hope.

These things just happen. :)

-- 
Tuomas - VWs:'63 typ14, '65 typ34 & '61 typ2