Oracle Text – 5 million rows part III
February 7, 2007 Leave a comment
One of the parallel indexing processes has finished, so the job’s nearly done now.
One of the interesting things about an Oracle Text index creation is that the whole thing is not transactional – thank goodness in this case.
For example, with a normal index, if you make an error in the index creation DDL, the whole thing rolls back.
With an Oracle Text index, if you make an error in the syntax (and maybe it depends in which particular bit of the syntax but I made an error in the MEMORY clause and the PARALLEL clause), you have to drop the index even though the creation errored – so the whole thing is not wrapped in a single transaction.
Anyway, I’m not really any closer to figuring out why my initial index creation is suffering from chronic slowdown as it gets nearer to finishing but now I’m running out of time to look because it’s nearly finished.
But, this being 10g, I thought I’d have a trawl around the extended information in the OWI views, do a statspack snap and also use the EM Gui and the ADDM pages.
I’m not usually one for GUIs like Enterprise Manager and Toad, partly because the more you use them, the more you forget how to do stuff old skool – you forget commands, views, etc. However, in more and more installations that I go to, these tools are the de facto standard, particularly with Toad and with me being more of a developer than a DBA. And against an accomplished GUI button presser, typing a bit of SQL and then correcting the basic typos starts to make you look a big cumbersome. Obviously, the ideal is to know all the buttons and functionality built in to the GUIs whilst retaining knowledge of the views and SQL to do the job behind the scenes.
Anyway, I’m in the process of looking at this stuff now.
I had a look at the EM console using:
emctl start dbconsole
From the home page, I clicked on the Active Sessions icon which took me into the Performance tab, then I clicked on the Run ADDM button which, after an interstitial confirmation, took me into the ADDM screen, clicked on View Snapshots and then selected the Reports tab.
Obviously, there’s a whole heap of interesting information here.
But, from a developer point of view, it’s interesting to note some of the SQL that the Oracle Text index creation seems to be using behind the scenes.
from table ( cast ( ctxsys.drvparx.ParallelPopuIndex ( cursor(select /*+ PARALLEL(base 4) FULL(base) */ rowid, NULL, NULL, NULL, 'N' from "MYSCHEMA"."MYTABLE" base), :idxid, :idxown, :idxname, :ixpname, :popstate) as sys.odcivarchar2list))
(Note that I’ve continued to substitute the real table name with ‘mytable’)
(ODCIVARCHAR2LIST is a VARRAY(32767) of VARCHAR2(4000) – didn’t know that.)
This snapshot report takes place over a 30 minute period during which the index creation is still in full swing.
So, given that this query is probably the main driver of the index creation, the elapsed time for this SQL was 99% of the total DB time.
No other SQL takes any significant time (well there’s only 1% left after the one above), but the next highest one is:
SELECT /*+ FULL ("A2") PARALLEL ("A2", 4) */ "A2".ROWID "ROWID", NULL "NULL", NULL "NULL", NULL "NULL", 'N' "'N'" FROM "MYSCHEMA"."MYTABLE" "A2"
select headline, summary, body from "MYSCHEMA"."MYTABLE" where rowid = :rid