Oracle

Jonathan Lewis's picture

Debugging

The OTN database forum supplied a little puzzle a few days ago – starting with the old, old, question: “Why is the plan with the higher cost taking less time to run?”

The standard (usually correct) answer to this question is that the optimizer doesn’t know all it needs to know to predict what’s going to happen, and even if it had perfect information about your data the model used isn’t perfect anyway. This was the correct answer in this case, but with a little twist in the tail that made it a little more entertaining. Here’s the query, with the two execution plans and the execution statistics from autotrace:

iggy_fernandez's picture

When SQLT is not enough

A SQLT report has all kinds of pertinent information including—to name just a few—optimizer settings, indexes, statistics, plan history, and view definitions. However, sometimes a SQLT report is not enough to solve a SQL performance problem.(read more)

JonathanGennick's picture

SQL Joinery

Fourth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL supports three types of join operation. Most developers learn the inner join first. But there are two other join operations you should know about. These are the outer join, and the full outer join. These additional join types allow you to write in essence could be termed as optional joins

Inner Joins

The so-called inner-join is the default. It's the happy path from a theory perspective, and it's the join type most SQL developers learn first. Use it to combine related rows from two or more tables. 

For example, perhaps you want to report on all the customers in the AdventureWorks database. You might begin working that business problem by writing the following query:

JonathanGennick's picture

SQL Joinery

Joins are fundamental in SQL, and are used in most every production query.
There are three types in particular that every developer should fully
understand.



Read the full post at www.gennick.com/database.

iggy_fernandez's picture

How is data modeled in NoSQL?

The important thing to understand is the data does not change just because it is managed differently. If the data does not change, then the entities and the relationships contained in the data cannot change either. The entities and the relationships between them have not changed since the dawn of time. They were the same in the days of network database management systems which came before relational database management systems, they stayed the same when object-oriented database management systems came along, and they are the same now that we have NoSQL databases.(read more)

iggy_fernandez's picture

Why should Oracle Database professionals care about NoSQL and where to start?

Oracle Corporation openly admits that NoSQL database management systems have the performance advantage over relational database management systems “when data access is ‘simple’ in nature and application demands exceed the volume or latency capability of traditional data management solutions.” Database professionals should therefore look seriously at NoSQL technology.(read more)

iggy_fernandez's picture

Why Brian Hitchcock and I (probably) know more about Oracle Database than you‏

Brian Hitchcock’s secret is simple. For twelve years, he has been reading books on Oracle Database. And taking extensive notes. And publishing them in the NoCOUG Journal. My secret is simple. For twelve years, I have been reading the extensive book notes that Brian has been publishing in the NoCOUG Journal. I’m the editor of the NoCOUG Journal but that’s not the point. It’s that simple.
(read more)

Jonathan Lewis's picture

Any Questions

I’m going to be at the OUG Scotland conference on 22nd June, and one of my sessions is a panel session on Optimisation where I’ll be joined by Joze Senegacnik and Card Dudley.

The panel is NOT restricted to questions about how the cost based optimizer works (or not), we’re prepared to tackle any questions about making Oracle work faster (or more efficiently – which is not always the same thing). This might be configuration, indexing, other infrastructure etc.; and if we haven’t got a clue we can always ask the audience.

dbakevlar's picture

Enterprise Manager Support Files 101- The EMOMS files

Someone pinged me earlier today and said, “Do I even really need to know about logs in Enterprise Manager?  I mean, it’s a GUI, (graphical user interface) so the logs should be unnecessary to the administrator.”

Jonathan Lewis's picture

Stats History

From time to time we see a complaint on OTN about the stats history tables being the largest objects in the SYSAUX tablespace and growing very quickly, with requests about how to work around the (perceived) threat. The quick answer is – if you need to save space then stop holding on to the history for so long, and then clean up the mess left by the history that you have captured; on top of that you could stop gathering so many histograms because you probably don’t need them, they often introduce instability to your execution plans, and they are often the largest single component of the history (unless you are using incremental stats on partitioned objects***)

Syndicate content