Low performance on Table

[akwete]

I have few heavily accessed tables with less than 1 million rows in each of them that are really taken a long time to return data. Could someone give me guidelines on how to improve performance on those tables?
I sincerely appreciate your assistance.

KC
Data Analyst

[marist89]

Use indexes?

Seriously, we need more info....

What is "slow"?
What are the query plans?
What's your I/O setup?
What are you waiting on?
How about a tkprof?

[akwete]

Jeff,

Thanks. I do not have DBA privilege and therefore cannot perform some of those tasks. This particular table has about 1 million rows and several indexes about 10 on heavily accessed fields and columns. The plan is RBO and the DBA's are moving to CBO soon. What else can I do from a developer/database analyst perspective to improve table performance?

Thank you.

[Mr.Hanky]

Uh oh, another developer doing a DBA's job.
Gotta go clean my gun collection.

When was the last time the tables was analyzed?
Can you view the indexes and try to code accordingly?
Do you think any performance type indexes would help?
When was the last time you bought your DBA lunch?

[slimdave]

Tell your DBA's that you need them to run "Statspack" while those tables are being queried. Make sure thay have timing set on.

[tamilselvan]

Tkprof is the greatest tool to figure the problem.
Also, you can try 10046 event with level 12/8 to generate trace file.
Use it wisely.

Tamil

[marist89]

Originally posted by marist89

What is "slow"?
What are the query plans?
What's your I/O setup?
What are you waiting on?

None of these things need dba privileges.

[akwete]

I thank everyone for your feedback and wonderful comments. We will run Statspack, Analyze the table as well as use Tkprof.
Tamil, what is 10046 event 6 with level 12/8. Please reduce the terminology for me.
By the way, I have done some light DBA tasks not the heavy stuff. I am more of an Oracle BI DBA primarily implementing Oracle and third party software that interact with Oracle Database.

[abhaysk]

Originally posted by akwete
By the way, I have done some light DBA tasks not the heavy stuff. I am more of an Oracle BI DBA primarily implementing Oracle and third party software that interact with Oracle Database.

Then you should better read PT Guide..

Abhay.

[reydp]

Originally posted by abhaysk
Then you should better read PT Guide..

Abhay.

Abhay,
I believe you're asking too much for a data analyst.
Even if she/he can somehow grasp the terminologies
used in in PT. She will still be wondering of what to
do next. So, she/he will come back here and ask question
again. And you might recomend to read another documents
like DBA books itself.

I rather agree with Mr. Hanky for at least the latter part:

Uh oh, another developer doing a DBA's job.
Gotta go clean my gun collection.

When was the last time the tables was analyzed?
Can you view the indexes and try to code accordingly?
Do you think any performance type indexes would help?
When was the last time you bought your DBA lunch?