I fully second Dan on this. It's very easy to get a lot of "perfect" small queries. Just write lots of functions:
get_ename(empno in emp.empno%type) get_job(empno in emp.empno%type) etc. Then use the functions everywhere ... Stéphane Faroult Daniel Fink wrote:
Let's start off with some assumptions (if these are wrong, they will show you tasks that you need to complete) 1) The queries are causing the high load (cpu time where load is defined as cpu utilization)2) The cpu time is caused by buffer gets3) The queries are properly formed (using bind variables where required and literals where performance dictates) 4) The queries are not recursive queries Oracle uses for parsing/managementYou have to ask "Why are these queries being executed?"1) If they are part of a custom function, development needs to go back to the drawing board so that they are not being used 2) If they are called as part of an application checking for data, perhaps the application can cache non-volatile data at the application server level 3) Determine the business purpose for the queries and see if the business process can be 'tuned'.You may need to throw more hardware at the issue, but you should first try to determine if the executions are really needed.Regards, Daniel Fink -- Daniel Fink Oracle Performance, Diagnosis and Training OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Zhu,Chao wrote:We have some databases with load profile like lots of light query (mostly pk based), each execution cost only 3-6 buffer_gets and no complex join etc. But due to excessive executions (10K+ query per second), load is also driving pretty high.Anyone has experience tune such kind of application/database? From single SQL point of view, they are already perfect.-- Regards Zhu Chao www.cnoug.org <http://www.cnoug.org> ------------------------------------------------------------------------ No virus found in this incoming message.Checked by AVG. Version: 7.5.524 / Virus Database: 269.23.6/1403 - Release Date: 4/29/2008 7:26 AM
-- //www.freelists.org/webpage/oracle-l