first thing you should do is get AWR(10g on)/statspack(if your on an older version of the DB) running on your database so that it generates a snap every few minutes (few being say 15, 30 ..if you need more resolution, make the snaps more frequent). this will give you a system overview on IO, CPU etc and will show you the TOP SQL (in terms of IO, CPU, runtime, executions and the like). it will advise on SGA / buffer cache usage too.
From that point onwards you should start looking at the worst sql/areas and run tools like explain plans/tkprof/sql trace on sql statements and plsql profiler on any slow running pl/sql code.
Asked if I could take a look and see what could be done, got a yes as answer.
That's very encouraging: you're in an excellent position to advance your career and learn something!
Some of the things you mentioned are problems, some not. I'll answer them briefly and follow with some broader advice.
procedure returns almost 2000 rows with lot of duplicate indexes. If they're really duplicates -- type, order, etc. -- extras can be dropped without loss and without fear. The DBMS can use only one, and it doesn't care which. Insert
performance improves whenever an index goes bye-bye.
NC indexes including all the columns from a table. Normal. These are known as "covering indexes". They're similar to materialized views.
lots of heap tables. Bad. Every table needs a key. Sometimes you'll be told, "it has no key". If true, the whole row can be defined as the primary key, except that an extra column is added (not in the key) as a count. "Adding" a row becomes a matter of adding 1 to the count.
really wide indexes might or might not help, cf. covering indexes, above.
databases are over 100Gb. Normal, databases grow with data.
Statistics are updated everyday for every table. Good.
There are reports that take more than hour to finish. Every problem is an opportunity.
My advice is to do your homework by reading four books, and to start small with something dramatic, by fixing the longest-running procedure (or the one you boss thinks is the biggest bugaboo).
When you suggest a change, you want to be able to speak confidently and authoritatively in the domain you're addressing yourself to: relational theory and database design, and SQL Server.
For theory, I recommend CJ Date's An Introduction to Database Systems, SQL and Relational Theory, and Database Design and Relational Theory, in that order. That will give you the information you need, and intellectual reference points when you're asked about why something should be done a certain way. For SQL Server, I can recommend Inside Microsoft SQL Server 2008: T-SQL Querying by Itzik Ben-Gan.
Reading the books is unfortunately the easy part; the hard part is applying them to the database. You may find it difficult to get a list of all the queries used with a table. Not only will make it hard to know which indexes are needed, it means splitting one table into two will require arduous testing to make sure nothing breaks (and application changes, too, potentially). The guy who wants to normalize the database has few friends because any change, even one that's correct and necessary, may create work and disruption for others.
I'd look at your bad boy, the long-running report or the one that seems to be wrong a lot of the time. Examine the query plan and think about the tables in terms of BCNF. If you're lucky, the answer might be adding an index, or possibly removing some. If not, you've got a design problem on your hands, something that's bound to involve others.
Good luck. You have your work cut out for you. The good news is you've got a place to learn some things, and permission to work smarter instead of harder. If you keep at it, one of these days you'll reduce query time not by 30% or 50%, but 99% or more. When that happens, you'll be looking at the new DBA in the mirror, so be careful what you wish for. :-)
Best Answer
I'll begin by saying that an Oracle DBA should know what the problem was by looking at ASH and AWR reports. Without AWR reports it's hard to know what the real problem is here. However, I'll help you with the first query.
The top query in the
Top SQL with Top Row Sources
shows the the query with the sql_id of8t441yd5bwygd
is performing a table scan. You can see the text of the query in theComplete List Of SQL Text
section.The main problem here is that there is a
LIKE
clause in the query, which also uses thelower()
function to convert the column data into lowercase, which is then compared with the bind variable:1
, which is the value passed into the query from the application.Cut down:
select * from PERSON_CARD where lower(PERSON_ID) like :1;
To answer the question properly we really need more information regarding the data types of the columns involved & the possible values that can be passed into the WHERE clause by the application, but there are a few possibilities.
My worry is that this SQL (and probably the schema design) is generated by Hibernate, and that PERSON_ID is a long hex string UDID rather than an integer.
1) The data in the PERSON_ID column is a non-number string:
If (and only if) the input value :1 only contains a single wildcard at the end of the string (eg:
LIKE "foo%"
) you can create a functional index on the PERSON_ID column:This will cause an index range scan rather than a table scan (I hope!).
2) The data in the PERSON_ID column is actually an integer that exactly matches the value passed in as :1
2 possibilities here. If PERSON_ID isn't already indexed in the database, add an index. If it's already indexed, get the developers to change the application to use
PERSON_ID=:1
instead of LIKE with a LOWER() call.3) The data in the PERSON_ID column is a non-number string & the value in :1 being matched contains wildcards '%' at the beginning of the string
You'll have to use Oracle text indexes.