0

I seem to be seeing incredibly slow (5-10 seconds) to run a query against USER_TAB_PARTITIONS (Oracle) intermittently. When I do an explain plan on :

SELECT * FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='MY_TABLE'

One of the steps shows a Nested Loops/Merge Join (Option = CARTESIAN) operation against TABPART$. The TABPART$ system table shows it has been analyzed in the last day. Is there a known problem with 11.2 and USER_TAB_PARTITIONS?

By the way, I am running Oracle v11.2.0.1

  • If you've analyzed system tables manually, rather than using GATHER_DICTIONARY_STATS (which just analyzes a subset of tables that need it), you have messed up. – Philᵀᴹ Jul 27 '13 at 0:13
3

Found in support.oracle.com:

Bug 8334498 Query on DBA_TAB_PARTITIONS does full table scan on TABPART$

This bug fixed in version 11.2.0.2.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.