I’m headed to Las Vegas for Collaborate / IOUG on Sunday. Looking forward to learning a ton and getting the chance to listen to some of the super smart DBAs I follow online. And a little bit of Vegas fun of course.
Oh, happy day! Looks like you have to set up your profile and preferences again when you first login, but it is worth it.
As many of the comments to this handy article echo, we got stung by this one today. Definitely check out the article. After a large data shuffle with some new partioning, involving index rebuilds, we saw a huge load on the server. Didn’t take long to see that we had a query utilizing over 80 parallel threads. Dig down further, turns out the tables are set to DEGREE of 1 but the indexes are set to DEGREE 8. A quick alter index <name> noparallel; and the explain plan went back to normal, no parallelism. It is possible you might have to gather stats afterward to get the plan to change, but I didn’t have to.
Note that if you have an IOT (index organized table) then you will need to alter the table to noparallel, not the index, even though the index will show up in dba_indexes.
There is no parallel DML (since it was an UPDATE query) enabled on any session: SQL> select distinct PDML_STATUS from v$session; PDML_STA -------- DISABLED And the tables are not set to parallel degree > 1: 1* select degree from dba_tables where lower(table_name)='mytable' and owner='myschema' SQL> / DEGREE ------------------------------ 1 So no problems there ... BUT the indexes are set to parallel 8 ... 1* select degree from dba_indexes where index_name='PK_mytable' and OWNER='myschema' SQL> / DEGREE ---------------------------------------- 8 1* select count(*) from dba_indexes where degree <> 'DEFAULT' and to_number(degree,99) > 1 and owner='myschema' SQL> / COUNT(*) ---------- 703 Explain plan:
UPDATE STATEMENT ()
UPDATE () AGN_TOOL_VIEW_STATE
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10004
NESTED LOOPS ()
Query to generate noparallel statements for all the indexes that need fixing (with thanks to the article mentioned above for the original query showing which had a degree > 1): SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' noparallel;' FROM DBA_INDEXES WHERE degree <> 'DEFAULT' AND to_number(DEGREE) > 1 -- put any exclusions here --AND INDEX_NAME NOT IN ('BINARY_SEARCH_INDEX','TEXT_SEARCH_INDEX','SIMPLE_SEARCH_INDEX') ORDER BY INDEX_NAME /
Fantastic article by Jonathan Lewis on why Oracle couldn’t just use REDO to cover the purposes of both UNDO and REDO.
In 2008, after a few years in support, eight years as a programmer, and four years as an IT manager, I wanted a change and moved into an Oracle DBA role. I hoped to chronicle the experience and tracks bits of flotsam that helped me along the way, but the first year was such a whirlwind that I didn’t have much time for blogging. Now I am going to give it a shot with new things I learn as well some of my accumulated file of useful stuff.