Share pool & lib cache really helps if ...

Shared pool library cache and DB Buffer cache doesn't make too much sense to me because :

1. If just one DB user issues a big FTS or long run SQL, Oracle in order to make enough room to cache all retrieving block in library cache must age out probably everything that already be there, so it means that even one wrong sql can reset the whole thing which was already cached for raiy days ?????????????

2. And for take benefit of Shared Pool Library cache : Two SQL statement must be EXACTLY the same till dumb Oracle can utilize existing parsed plan in library cache for the second coming similar sql.

could you explain what happen in real DB life ??

1. You don't understand correctly how oracle manages buffer cache, particulary during large FTS. It will not flush out the whole buffer cache, only a small number of buffers will be used to read data into them in cyclic fashion. Search the archives of this forum, there have been couple of threads regarding this isue.

2. I agree, to some extent Oracle is realy dumb regarding this isue. They should at least implement case-insensitivity before caclculating hash values of SQL statements. As a curiosity, Oracle follows some different rules when calculating hash values of SQL statements and PL/SQL blocks, so "EXATLY the same" has different meanings in SQL than in PL/SQL! But generaly I don't think this realy should a very big problem in a production database, at least if bind variables are used properly. After all, the vast majority of SQLs are coming from precreated aplications, so the repetition of "exatly the same SQLs" and thus reusability of parsed statements is usualy quite high. Of course, it helps if developers follow common coding rules...

Can you elaborate more your statement : " so "EXATLY the same" has different meanings in SQL than in PL/SQL! " What is the difference ?

I think you can reuse the statments as of 8i as setting the cursor sharing to force. Which I beleive mandates this operation of sharability to an extent.

Sam

Quote:

---

Originally posted by farrokhp
Can you elaborate more your statement : " so "EXATLY the same" has different meanings in SQL than in PL/SQL! " What is the difference ?

---

If you isue the following two statements, they will end up in the library cache as two *different* statements, simply because they differ in upper/lower case used:

Code:

---

SELECT COUNT(*) FROM dual;
select count(*) from DUAL;

---

But if you use the same two statements inside PL/SQL, like in the following:

Code:

---

declare
 x number;
begin
  SELECT COUNT(*) INTO X FROM dual;
  select count(*) into x from DUAL;
end;

---

they'll end up in the shared pool as *one statement*, where everything except litterals will be conveted in uppercase! Inside PL/SQL, SQLs are converted to uppercase and excessive whitespaces (blanks) are trimmed before the statements are parsed.