Partitioning is sometimes sold as the panacea to all performance problems. Anyone who has added local indexes on a column other than the one on which the table was partitioned quickly finds out this isn’t always the case.<\/p>\n
Keep in mind that partitioning is not only about improving performance, but also increasing availability and manageability.<\/p>\n
I came up with what is below to run across our databases and determine if we were actually using it, and where. It is an expensive option ($11.5K US per processor), so if you’re not using it, why pay for it?<\/p>\n
\r\nselect *\r\n from (select dhss.sql_id,\r\n object_name,\r\n dbms_lob.substr(sql_text,2000,1),\r\n sum(executions_delta) execs,\r\n operation,\r\n options\r\n from dba_hist_sql_plan dhsp,\r\n dba_hist_sqlstat dhss,\r\n dba_hist_sqltext dhst\r\n where dhsp.sql_id = dhss.sql_id\r\n and dhst.sql_id = dhss.sql_id\r\n and operation like '%PARTITION%'\r\n and options like '%SINGLE%'\r\n group by dhss.sql_id,\r\n dbms_lob.substr(sql_text,2000,1),\r\n operation,\r\n object_name,\r\n options\r\n order by 4 desc)\r\n where rownum <= 10000;\r\n<\/pre>\nIn general, you want to focus on the ones that have an OPTIONS value of SINGLE. This indicates partition pruning, which is where partitioning really shines from a performance perspective.<\/p>\n","protected":false},"excerpt":{"rendered":"
Partitioning is sometimes sold as the panacea to all performance problems. Anyone who has added local indexes on a column other than the one on which the table was partitioned quickly finds out this isn’t always the case. Keep in…<\/p>\n