how to make report generation faster in Oracle?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Piloute,

The application moved to production a month ago.At that time it was little bit slow but now it is getting worse day by day.

There is a daily script which runs in the night and loads the daily data in the oracle tables and deletes the data which is older more than 4 yrs.So by doing this we have 4yrs rolling data always in the application.

Application moved to production because the client is also doing this work first time and their DBA does not have so good knowledge.

By reading your detailed reply, i think below are the points which i can do:

1: I can create index on the columns of the table which are present on the user interface.As of now no column is indexed.Can you suggest i should make one column indexed or all the columns which are used on User interface??Will it make the data insertion process slower??

2.i could not understand by your statment
"Another imortant thing for your db is statistics. Have you updated them recently ? They are used by the oracle optimizer in order to make sql execution the more efficient possible. If those statistics are not up to date, you must update your statistics. This is specially important if you keep adding data to your table."
Database is updated daily by daily scheduled script.So what should i do for the statistics??Or should i ask DBA to take some action for this???Please explain.

3.Please explain what is partioning???and is it something which i can do in coding or DBA can do this at their end???Please let me know.

Thanks a lot for your detailed reply.

Regards,
OR

>>As of now no column is indexed

That's likely the problem. You should NOT index ALL columns. That will slow you down even more. You index columns typically used in the WHERE clause and in table joins.

When you designed the database id you not follow the rules of normalization?
http://en.wikipedia.org/wiki/Database_normalization

This should have created primary and foreign keys in your tables. These are basically indexes.

People make entire careers out of tuning databases. Now that the application is running, ongoing tuning efforts are a must. There are several tools that can help you with this. Depending on what you have licensed, you might be able to use ADDM and AWR. Also the Adivsors in Grid Control are becoming pretty useful.

>>"Another imortant thing for your db is statistics.

Always start with the docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94712

The tuning guide is a must read.

>>Please explain what is partioning

Again, the docs:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/schemaob.htm#CNCPT112

Partitioning is not free. You cannot use it if you don't have the license for it.

gatorvip

You probably don't need partitioning for "only" 30M records, especially given the extra licensing costs (unless you already have the Enterprise version, which in turn would probably imply that you have qualified DBAs on staff).

However, your question is very broad, because you provide no measurables, queries, explain plans, etc. What does your query do? What kind of reports do you generate? What qualifies as "faster"? If you have filters in your query, then indexes will probably help, but you definitely need to provide more details. Can you run your query in SQL outside of APEX?

>>you already have the Enterprise version,

Partitioning is an add-on even with Enterprise Edition.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

>>you definitely need to update your statistics quite often

Maybe. Maybe not. If I delete 1 million rows a day, then add 1 million rows a day and these new rows pretty much have the same cardinality, the plans probably won't change so no need to refresh stats.

As a general rule, you want accurate statistics. This may or may not mean 'fresh'.

@slightwv

How many chances a column will have exactly the same min & max value after the updates ? What happens if you are looking for a value that is not into the range your stats indicate ?

Although I've seen 11g is more flexible on the subject, a 10g I have worked with was completely explosing exec times when a requested value was far from the 'known' limit.

I wouldn't take the risk of running into such a problem just because I would hope values are accurate. As a possibility, imagine the person who will query the db exactly on the values he just inserted 'to see if they are there'... Now a bad exec plan, stored by that person, will just blow every other person's reports... :(

But you are right ! If the statistics are accurate, you don't care about freshness...

You and me, we would be probably be able to read an exec plan, compare expected cardinality and real one, check if stats are accurate, eventually generate new ones, etc. But do you think our 'rookie' will be able to do it ?

Personally, I would suggest him a statistics update after each insert on the table(s) that are affected by that insert.
Harmless and efficient. Only consts some time, but for a single table with 30 M rows should be fine.

:)
P

note :
after each insert = after each batch session

P

ASKER

Hi Piloute/slightwv,

Thanks for such a nice discussion and guidance.

After reading all above, i am thinking of below two points:

1.I think for creating index on year,month and company.Because these three are mostly used in where condition.Please let me know if i run the create index command on the table which has already data (30M) will it take too much time to run??Also should i go ahead and create index for other columns also which are used in where condition??I have read above that creating many indexes will also create problem.so I guess i need to restrict the indexes to as minimum as possible.

2.Also please let me know how can i do partition(syntax) in the already esisting table with data??I am thinking of doing partition in my table on Year column.i have searched but found the syntaxes on net for creating partition in the new created table.After doing this report will search within 1/4 th of the total records.Please let me know your thoughts on both points.

Thanks a lot to you guys.Today i realized the benefit of premium account at EE.

Regards,
OR

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Piloute/Slightwv,

I have checked with the client DBA and he told me that they don't have license for partitioning.

Now first i will create an index for one column.Will you people suggest on which column i should create index?I have following filters on the user interface which are used in where condition in the reports:
Company
Reporting Month
Reporting Year
Office
Reporting Day

All of the above are multiselect and optional except year.User can leave all the multiselect and may select only a year then report will create the whole reports for the selected year.

So i guess the index on year column will work for me.But for being sure,I thought of your views.

Regards,
OR

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I should have added:
With all the batch inserts/deletes you are doing, you might also think about a complete index rebuild after every load to keep things 'fresh'.

ASKER

Hi slightwv,

Will you please tell me how to do complete index rebuild after every load??

Do you want in my daily script/sql package i drop the index and again create the index???

If i create index only once will it not work for me??

Also after reading the statistics doc,i have a question that

Do i need to add something in my code for the db statistics??or DBA need to do something for this??

Please help.

Regards,
OR

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.