Printable View
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
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?
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.
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?
Tell your DBA's that you need them to run "Statspack" while those tables are being queried. Make sure thay have timing set on.
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
None of these things need dba privileges.Quote:
Originally posted by marist89
What is "slow"?
What are the query plans?
What's your I/O setup?
What are you waiting on?
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.
Then you should better read PT Guide..Quote:
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.
Abhay.
Abhay,Quote:
Originally posted by abhaysk
Then you should better read PT Guide..
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.:D
I rather agree with Mr. Hanky for at least the latter part:
:DQuote:
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?
Rather indicative that we don't have an answer to this question!Quote:
Originally posted by Mr.Hanky
When was the last time you bought your DBA lunch?
=======
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.
====
Level 1 is equivalent to set sql_trace=true.
Level 4 is for getting the bind variables and their values in the trace file.
Level 8 is for getting wait events (db_file_sequential_read etc).
Level 12 is the combined setting of level 4 and level 8.
Tamil
Is PT Guide not a part of ** DBA books itself ** :confused:Quote:
Originally posted by reydp
Abhay,
And you might recomend to read another documents
like DBA books itself.:D
Abhay.