| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: explain plans for PL/SQL and transactions
Patrice,
1 Billion buffer reads reminds me an interesting situation I encountered early this year.
One team developing a piece of code complained that it is too slow. So, I looked at the pl/sql code, it was okay. We decided to look at SQL, so we traced it and found one query taking about 80% of the time and over a billion buffer scans.
I was assured that this is exactly how the query should be written. So, I took the query outside and started playing with it. After 15 minutes I rewrote the query in the form that every column is prefixed with the table alias. And then it all made sense ... there were two tables in the FROM clause that were not being used as all. Hello Cartesian Joins ... Developer then confessed that he should have fixed the code after he finished testing some enhancements to the code. He fixed the SELECT and WHERE clause but forgot to fix the FROM clause. Removing the offending tables from the FROM clause, the process which used to run for about an hour, came down to < 10 minutes.
Remember dbms_profiler will tell you which line of code is being executed how many times and total how much time it took to execute, line by line. By using this once, I helped a group get rid of couple of procedures and merge the code in SQL itself. For every row they select, they had a function to convert a column from date to varchar2 and another to concat 3 columns from the select list in a specific format.
I analyzed first using profiler and then using 10046 ... once we fixed the code, we did the steps again and they liked what they saw.
This might not be your case, but that is my experience. YMMV Raj
-----Original Message-----
Sent: Friday, May 09, 2003 8:08 AM
To: Multiple recipients of list ORACLE-L
I am going to try dbms_profiler this morning, I read in RevealNet that it tends to create a very large amount of rows, to accommodate I moved the plsql_% tables and their associated indexes to a separate tablespace.
We just want an overview of what is happening, the code is looping often, to the point where a simple query produces over a billion (yes, billion) buffer reads.
The developers just asked me to add memory to the server, I hope to show that perhaps their code could be tuned a little.
If dbms_profiler doesn't do it I will trace the session.
Thanks for the help everyone, I appreciate the people on this list very much.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri May 09 2003 - 08:46:41 CDT
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |