[Checkins] SVN: relstorage/trunk/ Include optimizer hints for Oracle.

Martijn Pieters mj at zopatista.com
Tue May 24 07:52:45 EDT 2011


Log message for revision 121793:
  Include optimizer hints for Oracle.
  
  With a big RelStorage database Oracle would otherwise choose an incorrect execution plan as it fails to notice that the pack_object table is not empty at this time. In the case of a large database (25 million rows in the OBJECT_STATE table) this query would fall flat and not return data for several days. The added hints make the optimizer choose the correct execution plan for this query whatever the size of either table.

Changed:
  U   relstorage/trunk/CHANGES.txt
  U   relstorage/trunk/relstorage/adapters/packundo.py

-=-
Modified: relstorage/trunk/CHANGES.txt
===================================================================
--- relstorage/trunk/CHANGES.txt	2011-05-24 08:52:32 UTC (rev 121792)
+++ relstorage/trunk/CHANGES.txt	2011-05-24 11:52:42 UTC (rev 121793)
@@ -8,6 +8,9 @@
   go, to prevent holding the transaction lock for longer than absolutely
   necessary.
 
+- Fix object reference downloading performance for large Oracle RelStorage
+  database during the garbage collection phase of a pack.
+
 1.5.0b2 (2011-03-02)
 --------------------
 

Modified: relstorage/trunk/relstorage/adapters/packundo.py
===================================================================
--- relstorage/trunk/relstorage/adapters/packundo.py	2011-05-24 08:52:32 UTC (rev 121792)
+++ relstorage/trunk/relstorage/adapters/packundo.py	2011-05-24 11:52:42 UTC (rev 121793)
@@ -75,8 +75,16 @@
 
         # Download the list of object references into all_refs.
         all_refs = {}  # {from_oid: set([to_oid])}
+        # Note the Oracle optimizer hints in the following statement; MySQL
+        # and PostgreSQL ignore these. Oracle fails to notice that pack_object
+        # is now filled and chooses the wrong execution plan, completely
+        # killing this query on large RelStorage databases, unless these hints
+        # are included.
         stmt = """
-        SELECT object_ref.zoid, object_ref.to_zoid
+        SELECT 
+            /*+ FULL(object_ref) */ 
+            /*+ FULL(pack_object) */ 
+            object_ref.zoid, object_ref.to_zoid
         FROM object_ref
             JOIN pack_object ON (object_ref.zoid = pack_object.zoid)
         WHERE object_ref.tid >= pack_object.keep_tid



More information about the checkins mailing list