Statistics
Posted: Thu Nov 15, 2012 6:00 pm
I have a very complex database pumping about 10-20 mil records a day of different data types. The most resource intensive routine is a correlation routine that makes heavy use of bind variables. Over the years I've setup different SQL Profiles against these statements so that the cost based optimizer would be locked into certain explain plans.
I'm using a heavily partitioned database (daily partition divided by feature/data set). My object statistics are up to snuff but my index statistics won't stay recent being rebuilt often. The SQL Profiles have worked like a charm to get around this limitation. Some of the indexes are function based so they cannot be eliminated.
CATEGORY NAME
------------------------------ ------------------------------
DEFAULT SYS_SQLPROF_01326da4465e0000
DEFAULT SYS_SQLPROF_01357f33feff0006
DEFAULT SYS_SQLPROF_013292c5e7be0001
DEFAULT SYS_SQLPROF_013aafa58be20005
DEFAULT SYS_SQLPROF_01355e861f480000
DEFAULT SYS_SQLPROF_01336f406d770000
DEFAULT sqlt_s47637_p268518099
DEFAULT SYS_SQLPROF_01355e8620c80001
DEFAULT SYS_SQLPROF_01355e8622020003
DEFAULT SYS_SQLPROF_013826bfbd390003
DEFAULT SYS_SQLPROF_01396296bbb20004
DEFAULT SYS_SQLPROF_0136919e122c0000
DEFAULT SYS_SQLPROF_01355e86223b0004
DEFAULT SYS_SQLPROF_0135656df4c50005
DEFAULT SYS_SQLPROF_01371cae9ab30001
DEFAULT SYS_SQLPROF_01372a647fc00002
DEFAULT SYS_SQLPROF_01355e8621920002
DEFAULT SYS_SQLPROF_01365c6ff93e0000
DEFAULT SYS_SQLPROF_013b051e48090006
That's the list of SQL Profiles that I have locked into the system! Most of my servers will have 1 maybe 2 at the most. I hear DBAs say their databases are like children. Well mine is a red headed stepchild. Little bastard.
Right now I'm looking at taking the correlation routine outside of the database and performing some of the heavier lifting in a binary. The result could then be inserted. Problem is I'm not a programmer. /cry
I'm using a heavily partitioned database (daily partition divided by feature/data set). My object statistics are up to snuff but my index statistics won't stay recent being rebuilt often. The SQL Profiles have worked like a charm to get around this limitation. Some of the indexes are function based so they cannot be eliminated.
CATEGORY NAME
------------------------------ ------------------------------
DEFAULT SYS_SQLPROF_01326da4465e0000
DEFAULT SYS_SQLPROF_01357f33feff0006
DEFAULT SYS_SQLPROF_013292c5e7be0001
DEFAULT SYS_SQLPROF_013aafa58be20005
DEFAULT SYS_SQLPROF_01355e861f480000
DEFAULT SYS_SQLPROF_01336f406d770000
DEFAULT sqlt_s47637_p268518099
DEFAULT SYS_SQLPROF_01355e8620c80001
DEFAULT SYS_SQLPROF_01355e8622020003
DEFAULT SYS_SQLPROF_013826bfbd390003
DEFAULT SYS_SQLPROF_01396296bbb20004
DEFAULT SYS_SQLPROF_0136919e122c0000
DEFAULT SYS_SQLPROF_01355e86223b0004
DEFAULT SYS_SQLPROF_0135656df4c50005
DEFAULT SYS_SQLPROF_01371cae9ab30001
DEFAULT SYS_SQLPROF_01372a647fc00002
DEFAULT SYS_SQLPROF_01355e8621920002
DEFAULT SYS_SQLPROF_01365c6ff93e0000
DEFAULT SYS_SQLPROF_013b051e48090006
That's the list of SQL Profiles that I have locked into the system! Most of my servers will have 1 maybe 2 at the most. I hear DBAs say their databases are like children. Well mine is a red headed stepchild. Little bastard.
Right now I'm looking at taking the correlation routine outside of the database and performing some of the heavier lifting in a binary. The result could then be inserted. Problem is I'm not a programmer. /cry