How can I learn to make realistic assumptions about database performance?

Question

I am primarily a Java developer who works with Hibernate, and in some of my use cases I have queries that run very slowly compared to what I expect. I spoke with local database administrators, and in many cases they claim that performance cannot be improved due to the nature of the query.

Nevertheless, I do not dare to take them according to their words. What resources can I use to learn, when I have to suck it up, and find another way to get the information I want, or learn how to live with speed, and when I can call nonsense for database administrators.

Answer 1

You are in an interesting place. Most Java developers use ORM tools because they don’t know anything about databases, they don’t want to learn anything about databases, and especially they don’t want to learn anything about the features of their own specific DBMS. ORM supposedly protect us from all this.

But if you really want to understand how the query should be executed, you will need to understand how the Oracle database works. This is good: you will certainly create more advanced hibernation applications if you work with database grain.

The Oracle documentation set includes a volume of performance tuning. This is the place to start. Find out more . As others have said, the entry-level tool is EXPLAIN PLAN . Another important requirement is the Oracle Database Concepts Guide . An important aspect of customization is an understanding of the logical and physical architecture of the database. I also agree with the recommendation of Tom Kit's DCooke book.

Keep in mind that there are contractors and consultants who do a great job of tuning Oracle performance. If it were easy, they would be much poorer. But you can certainly give yourself enough knowledge to get these annoying administrators to work properly with you.

Answer 2

DCookie, OMG, and APC get +1 for their responses. I was on the side of Hibernate DBA applications, and, as Tom Kit says, there is no “fast = true” parameter that can lead to inefficient SQL and speed up its work. From time to time, I managed to collect the problematic Hibernate query and use the Oracle SQL parser to create an SQL profile for this statement, which improves performance. This profile is a set of hints that “intercept” the creation of the optimizer of the execution plan and make it (without changes in the application) for the better, which for some reason is usually ignored by the optimizer. However, this data is the exception rather than the rule for poorly executed SQL.

One thing you can do as a developer who apparently understands the data better than the ORM layer is to write effective views to solve specific query problems and present those views in Hibernate.

Of particular note are the Oracle DATE (not TIMESTAMP) columns that fall into queries generated by Hibernate and are compared with bind variables in the WHERE clause - a type mismatch with the Java timestamp data type prevents the use of indexes on these columns.

Answer 3

What are your expectations for query performance? Do you feel If you are going to argue with database administrators, you need to know (at least) what your queries are, understand EXPLAIN PLAN and be able to indicate how to improve the situation. And, as @OMG Ponies points out, Hibernate may not work well on querying - then what are you doing?

Not easy? Perhaps the best approach is to take a slightly less competitive approach to DBA employees and politely ask what concerns queries that impede productivity and if there are any suggestions they might have about how you could reorganizing them for work is better.

Answer 4

Post comment in response:

This is a compromise using ORM - you spared it, how it builds a query that is sent to the database. LINQ is the only thing that interested me, because you can use it in tandem with stored procedures for such situations. I am surprised that the database administrators are not saying that you want to stop ORM if you need a higher speed ...

The EXPLAIN plan will give you an idea of ​​efficiency, but not really a perspective on speed. For Oracle, you will need to use tkprof (suppose you are available) to analyze what is going on.

Answer 5

it can also be a table structure problem (normalization). Basically, that’s why I don’t deal with hibernate - you should always be able to create your own optimal queries.