I want to know the use of the FLUSH command. How does it works, and where can we use this FLUSH? Like, "ALTER DATABASE FLUSH SHARED_POOL".
The FLUSH command here applies to a memory structure in an Oracle instance called the Shared Pool. This memory structure stores lots of information such as parsed SQL statements. If you issue the FLUSH SHARED_POOL command, you are forcing the contents of the Shared Pool to be wiped out (or washed down the drain). This can be useful if you suspect that the contents of the Shared Pool are fragmented or in need of a good house cleaning. But in the latest versions of Oracle, the database is doing a pretty good job of cleaning house on its own. So there really shouldn't be a need to issue this command except in the rarest of circumstances. If you do issue this command, your application(s) can suffer a performance hit until it reparses all SQL statements that used to be stored in the Shared Pool.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available here.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.