Well there's a similarity here but it's not the same. We need to make note of the differences between caching: 1) SQL queries; 2) query result sets; and 3) data in tables.
IF the query matches a previous query AND the data in underlying table(s) has not changed, THEN MySQL returns the cached result set but the instant the data changes then the SQL statement must be reparsed and the data retrieved from disk. If it's primarily a read only database then this may help but if the data changes then it's of little benefit. By contrast, Oracle caches the SQL and if bind variables are used then the statement (together with its execution plan) can be reused either via soft parse or no parse at all thus consuming less CPU. Unlike MySQL, Oracle can reuse SQL that doesn't have a "byte-for-byte match" with the use of bind variables for values that change. Also, if the query is against Oracle tables that are cached then you can get the result set from cache regardless of changes to the data. Finally, if the data was in a previous result set and remains in the buffer cache, then much of the data may be returned from cache even if some rows have changed and the table is not cached. (This is measured by the infamous buffer cache hit ratio.) AFAIK MySQL has no such mechanism. (But you can also cache MySQL tables.) ---------------------------------------------- Here's a primer on bind variables, SQL parsing and the shared pool: Parsing SQL statements requires significant CPU and all the commercial database engines have mechanisms to reduce the CPU overhead. In Oracle7 the library cache mechanism to reduce CPU requirements for SQL parsing was introduced. This takes place within the Shared Pool. The shared pool is a subset of the SGA containing: control structures, the dictionary cache, and the library cache and within the library cache is the Shared SQL Area. All SQL passes through the Shared SQL Area. (Take a look at figure 7.1 from the Concepts manual.) When a SQL execution is requested the Shared SQL is first examined to see if the statement is in memory. The first time SQL is processed it goes through a hard parse, the most expensive parsing operation. A hard parse performs the following: checking syntax; validating all database objects referenced, (tables and columns); naming translation, (synonyms); authenticating user privileges on all tables and columns; producing a SQL execution plan via the optimizer; hashing and storing the parsed statement in the Shared SQL Area. If the SQL statement is found in the Shared Pool then a soft parse may be performed in an attempt to use a shareable cursor. There are three types of soft parses: 1) The first time a SQL statement is found in the shared pool Oracle performs name translation, user authentication, and adds the user to the authentication list. 2) On the second soft parse name translation does not need to be performed but user authentication does just in case user privileges were changed since the last execution.; 3) An entry is created for the session's cursor cache and future cursor CLOSEs are ignored. Once in the session cursor cache the SQL statement does not need to be reparsed. This gives a significant performance boost! Giving credit where due: The above was inspired from pages 277-280 in "Scaling Oracle8i" by James Morle. ---------------------------------------------- Steve Orr Bozeman, MT -----Original Message----- Sent: Monday, February 25, 2002 12:59 PM To: Multiple recipients of list ORACLE-L har Hi Steve, I think Stephen might be referring to the stuff mentioned in the eweek comparison that was posted earlier today... http://www.eweek.com/article/0,3658,s=708&a=23115,00.asp "MySQL 4.0.1's new, extremely fast query cache is also quite notable, as no other database we tested had this feature. If the text of an incoming query has a byte-for-byte match with a cached query, MySQL can retrieve the results directly from the cache without compiling the query, getting locks or doing index accesses. This query caching will be effective only for tables with few updates because any table updates that clear the cache to guarantee correct results are always returned. " -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
