More Questions
Yogesh, March 29, 2002 - 10:04 pm UTC
Does it mean that if I set sort_area_size to 'N' as you mentioned, which is sufficient for sorting etc. It would have given me statistices in one shot ?
Another observation about V$SESSION_LONGOPS, If some query is getting executed may be a complicated join.... which gonna take 1 hr or so... sometimes I get entry of it in table sometime not ... What is the criteria on which it is decided whether the entry will be in V$SESSION_LONGOPS or not
March 30, 2002 - 10:18 am UTC
If the index build it done entirely in one step, it would have worked like the first one did.
v$session_longops -- as i said -- is not an "end to end" thing for a statement. It reports on the progress of the STEPS within the statement. If a long running query consists of thousands of discrete steps each taking less then 5 seconds or so -- you won't see any entries. It is a function that takes longer then 5 seconds that you will generally see in there. Don't forget the entries come and go as they steps complete as well. Just because you didn't observe anything in there doesn't mean nothing was in there -- just that you didn't look at the exact moment.
Predicating the end of a statement is a horribly inexact science. You can use v$session_longops to
a) verify the statement is going (makes you feel better sometimes)
b) how far along the current step is.
how long / how far
A reader, September 02, 2004 - 6:01 am UTC
Tom,
1.Can i use v$session_longops to see if my procedure is executing and how far more to execute?
2. if my procedure is executing how long its executing?
How to get this information. Please let me know.
Thanks a lot,
Pravesh Karthik
September 02, 2004 - 9:00 am UTC
1) sure, IF YOU instruemented your code using dbms_application_info.set_session_longops!!!
2) sure, IF YOU instruemented your code using dbms_application_info.set_session_longops!!!
A reader, September 02, 2004 - 9:20 am UTC
Tom,
we were not aware of this, we have moved to production. i mean time, we will fix it. Sure .. Change Managment team takes more time to get us the code changed. Nothing wrong in there side, they have there own process
But now, can there be a way where i can check for it? Please let me know. I am really thankful for your inputs
Thanks
September 02, 2004 - 9:56 am UTC
unless you instruemented it, the only thing you can see is what sql is currently is executing, what it is currently waiting one and such.
Thank You
A reader, September 02, 2004 - 1:42 pm UTC
Just what I was looking for, almost
Bill C., October 15, 2004 - 7:59 pm UTC
Thanks, Tom. You confirmed what I was seeing.
I found this thread when attempting to find out how many of these Table Scan/Sort/Merge/Sort Output steps I can expect for a new, composite (2 column pre-fixed), non-unique, locally partitioned index creation statement. (It's Friday night and it's been running for 2 hours now. Wish I knew if I were close or another 6 hours from being done.) I expected two cycles of those steps, one for each column, but it's now on its third cycle. Urm....
Any way to guesstimate? Any clues as to what causes the cycles of the same three steps?
Background:
Our product requires an Oracle database to run on. Some of our clients use 9i. Some 8i. Some have 500M to 1B rows in the table I'm adding an index to. I'm doing tests on 500M rows to get benchmarks and write a paper to help end users estimate needed Oracle settings and decide between ONLINE, PARALLEL, GLOBAL and other index creation options.
I set my SAS to 100MB as my real test. I'm thinking it should have been much higher.
Further Background:
I'm fairly aware of the pros and cons adding a composite index to such a big table. I'm fixing a horrid situation. Turns out the data modeler that preceded me created a 7 column UK on this 14 column table, where the leading column was the PK! So the business rule behind the remaining 6 columns was skipped (there is duplicate data), and the UK is never used for any index access thanks to the most selective column being in the 2nd slot (well the 9i customers might have a little luck with skip scanning). Our poor 8i customers have to wait for hours when deleting 1 row from the parent table to which this monster child table FK's. This is also due to the fact that there is no index to the support the FK on the child table. I'm hoping one composite index with the date (used to partition) and the FK column, will take care of both problems. Oh, I'm dropping the useless UK.
Finally:
Since this is a v$session_longops thread, I thought I might post the query I'm using to view the progress of this statement (thanks to Tom for instructing me in his book to instrument using dbms_application_info).
SELECT
start_time AS "Started"
,'Operation: '||opname||' on '||NVL(target,'--not given--')||' is '||
'Roughly ' || ROUND((((time_remaining+elapsed_seconds) - time_remaining) / (time_remaining+elapsed_seconds)) * 100) ||'% Complete' AS "Progress"
,ROUND((time_remaining/60),1)||' minutes' AS "Remaining"
FROM v$session_longops l
, v$session s
WHERE s.username = USER
AND s.status = 'ACTIVE'
AND s.module = 'cr31905.ddl'
AND s.sql_address = l.sql_address
AND l.time_remaining > 0
ORDER BY start_time DESC;
October 16, 2004 - 10:38 am UTC
the sort area size will control the number of sort/merge steps -- think of it like a "paging algorithm". The smaller the pages, the more work involved.
would you like to see how to build a locally partitioned index in steps bit by bit? rather than all at once -- we can do that (create an index immediately -- with no work -- and then rebuild the partitions one at a time or in parallel, all at the same time). could significantly reduce the time to create.
Confirmation
Bill C., October 17, 2004 - 6:13 am UTC
Yes, please. Is it already in one of your books? I'm at home, but can crack 'em open when I get back to work Monday morning.
Thanks. I read the 8i-10g documentation backwards and forwards looking for some trick to somehow build this monster with minimal impact. Any tricks up your sleeve would be great.
- bill c.
October 17, 2004 - 10:07 am UTC
pclxutil
Bill C., October 18, 2004 - 2:28 pm UTC
Ah. I had looked at that earlier, but rejected using it. From the first page of the Oracle docs on it, I understood it to be useful only in cases where the number of CPUs exceeded the number of partitions. I don't know the state of our clients' boxes, but the modelers that preceded me subpartitioned this puppy (94 partitions and 394 subpartitions) and didn't put any archiving in place (they went for parititioning solely for the performance benefit). I doubt they have 394+ CPUs, let alone 94+ CPUs.
To make matters worse, they have this large table in one tablespace, but since we have no control over their hardware, that tablespace may or may not be on separate physical devices. So I can't verify that partition-wise building will even do much good.
If it would still be of use, I'll take a good look at it again.
I had originally hoped to find some CREATE INDEX syntax that would allow me to procedurally build one partition at a time, so that 1) it would not consume all the resources on the box as it "ate the elephant" piece-by-piece, 2) the build could be re-entrant if, say, the tablespace ran out halfway through and 3) using anonymous tx logging, the users could have an idea as to the progress. Alas, I was disappointed. If pclxutil can let me do #1 or #2, or something else wonderful I'm not considering, I'm all over it.
Thanks for your time Tom!
October 19, 2004 - 7:37 am UTC
that is not true -- you just need a certain number of cpus (1) or more.
it is a way to parallelize the index rebuild using the job queues, you control the degree of parallelism totall.
you can do it yourself piece by piece as well. all pclxutil is doing is automating this process by scheduling each alter index rebuild partition P as a job!
ops$tkyte@ORA9IR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y int
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select to_date( '01-mar-2003') + rownum, rownum, rownum from all_users;
21 rows created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x) local UNUSABLE;
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX PART1 UNUSABLE
T_IDX PART2 UNUSABLE
T_IDX JUNK UNUSABLE
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition part1;
Index altered.
ops$tkyte@ORA9IR2> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX PART1 USABLE
T_IDX PART2 UNUSABLE
T_IDX JUNK UNUSABLE
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition part2;
Index altered.
ops$tkyte@ORA9IR2> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX PART1 USABLE
T_IDX PART2 USABLE
T_IDX JUNK UNUSABLE
ops$tkyte@ORA9IR2> alter index t_idx rebuild partition junk;
Index altered.
ops$tkyte@ORA9IR2> select index_name, partition_name, status from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T_IDX PART1 USABLE
T_IDX PART2 USABLE
T_IDX
Creating local non-prefixed partitioned index
Bill C., October 19, 2004 - 3:06 pm UTC
Tom,
Thanks. I shouldn't have been so chicken to check it out, just because the docs talked about more CPUs than partitions.
To get my syntax right and ensure my DB supported it, I first tested pclxutils on a replica of the target table, but with only 4 partitions and 4 subpartitions per partition, and 22K rows. Normally the local index creation takes 9 seconds on that table. It took 6.5 minutes using dbms_pclxutils. I think that's to be expected due to the overhead involved.
I then tested it on the dev copy of the target table (500K rows) before trying it out on the performance environment copy (475M rows) -- btw, the clients have 500M to 1B rows in production. The 500K row test was very, very slow (finally killed my session after 20 minutes, whereas it normally takes 28 seconds). All the dev, int, release and performance copies of this table have the 94 partitions and 376 subpartitions. Perhaps that has something to do with the overhead of this approach. However, I thought this thing took advantage of paralellism...
When I create the index with my usual syntax on 8.1.7.3 (the 500K row table), I use:
CREATE INDEX t_idx ON t(myid, mydt)
STORAGE (INITIAL 64K)
LOCAL
--COMPRESS -- not supported with composite partitioned tables. Aaaargggh!
--ONLINE -- Can't have ONLINE and COMPUTE STATISTICS at the same time in 8i. Aaaarrgh!
COMPUTE STATISTICS -- can use COMPUTE and PARALLEL on 9i
TABLESPACE STL_INDEX
PARALLEL -- no degree lets db auto-pick it = 30% faster in tests
NOLOGGING
/
I query v$pq_slave before creation; all five are all idle. During the creation, I can see that all five are BUSY.
But when I used dbms_pclxutils, I do the same and all the slaves show as IDLE. Anything I might be forgetting to look at (I also tried the other v$ views for PQ and PX, but they all turned up empty or with 0 values)?
If I had 4 jobs kicking off with 4 processes per, all trying to divvy up 376 subpartitions and funnelling through 1 CPU, I can certainly see why it would take so long. So since my test went so badly, I'm hesitant to try it on the 475M row copy.
- bill c.
October 19, 2004 - 4:16 pm UTC
it uses dbms_job -- not pq slaves -- what is job_queue_processes set to on your system?
job_queue_processes
Bill C., October 19, 2004 - 7:16 pm UTC
Set to 20 on this test box.
'nother follow-up question?
Just finished the SGA section in the Concepts guide, but must have missed this section: The SGA, is that as much physical memory as the OS will be allowed to give an Oracle instance? Or can a session, by setting the SAS high, be allowed to suck more than what the SGA is set to?
I've started another test run of this behemoth, this time with parallel_max_servers set to the number of CPUs (all of 4). It was 0 which is why my PARALLEL keyword did nothing in the last test.
Otherwise I left my script as is, including the line that temporarily sets the sort_area_size to 200MB (automatic memory mgmt not turned on in this 9i instance; not sure why). Now as I look at the memory usage, it seems that every slave is using 200MB. Here's what the DBAs have set for this instance:
SQL> show sga
Total System Global Area 214,402,584 bytes
Fixed Size 730,648 bytes
Variable Size 134,217,728 bytes
Database Buffers 78,643,200 bytes
Redo Buffers 811,008 bytes
Here's what I've been using to see how much my session is using (perhaps it is flawed?)
SELECT
s.sid
,SUBSTR(s.program,DECODE(INSTR(s.program,'('),0,NULL,INSTR(program,'('))) slave
,TO_CHAR(st.value,'FM999G999G999G999') Bytes
,TO_CHAR(st.value/1024/1024,'FM999G999G999G999') MBytes
FROM v$sesstat st
,v$session s
,v$statname n
WHERE n.NAME = 'session pga memory'
AND st.statistic# = n.statistic#
AND st.sid = s.sid
AND s.sid IN (<subq using module in v$session>);
SID SLAVE BYTES MBYTES
----- ------ ---------------- ------
8 1,512,152 1
9 (P000) 212,311,904 202
14 (P001) 212,306,688 202
20 (P002) 212,289,488 202
25 (P003) 212,289,488 202
So if each slave is using 200MB and the SGA, total, is 200MB, am I causing a massive amount of swap as it's reading/sorting/merging? I'm no Solaris guru; I've tried using vmstat, sar and top, but am coming up short trying to gauge paging/swapping on the host (the DBAs are gone too).
To tie this back to the original thread, I'm having much less luck using v$session_longops since increasing the SAS. Before (at 1MB) there were constantly operations showing up in there. Now, it's definitely doing stuff, but it's tough to tell unless I query the session's stats, which shows me that "table scan rows gotten" is slowing inching upward (about 30M per hour, which is horrid). The wait views generally tell me it's waiting on a "null event." So that's not helpful. Just wish I had the magic wand to show me where the bottleneck is at. I'm guessing physical memory or something to do with the fact that all 376 of these subpartitions are in one tablespace (many datafiles of course, and a few disks).
Cheers. And thanks for your precious time you've lent me so far.
October 19, 2004 - 8:59 pm UTC
the sort area does not come out of the SGA, it is dynamically allocated in the SERVER processes themselves as part of the PGA.
Thanks
Bill C., October 20, 2004 - 5:49 pm UTC
Thanks! Don't know why I've never known that and why I had a hard time finding it with so many Oracle docs at my fingertips. I must have skipped it somehow. Kudos.
There's just something really wrong with this little E450 I'm doing the testing on. With the SAS set high, it becomes like cold tar running uphill. I'm thinking the box just has no more resources left, so it ends up doing a lot of disk effort.
Having set the SAS down to 20M, and using the dbms_pclxutil package, it was looking good. Made it about halfway through in 3 hours. But now it's stuck again. I'm giving up on this box. It's just too strapped for resources.
October 20, 2004 - 8:55 pm UTC
if your e450 is as old as mine, your desktop PC might be able to outperform it in many respects :)
dbms_pclxutil
Bill C., October 22, 2004 - 1:53 pm UTC
Tom,
I was pretty excited after getting parallel_max_servers set, the db bounced, and then watched pclxutil do its "thang". I loved how I was able to stop it in the middle and restart, picking up where it left off. I loved how (I thought) using ALTER INDEX .. REBUILD could enable the table to remain online and usable whilst I built the index.
Just tested it in QA as they were trying to run tests. Come to find out that when the index, or even one tiny subpartition of it, is UNUSABLE (the first step to using pclxutil) the users get ORA-01502. This new, local index does not really exist yet, it has no statistics, and is UNUSABLE and yet the CBO hits it when existing sessions try to query the table. Why would the CBO touch it? Why wouldn't it skip an unusable index, not complain, and just full scan or use the remaining indexes? My new index does support a FK. But disabling and dropping the FK, and finally recreating the unusable index didn't help either.
I'm aware of SKIP_UNUSABLE_INDEXES, but it doesn't really work, even with the FK dropped. The only query I got to go around the unusable index with skip_unusable_indexes set to true, was "SELECT COUNT(*)...". If it worked, I would need skip_unusable_indexes to be instance-wide during the pclxutil rebuilds, so that I don't have to temporarily inject that session-level setting into the java classes that set up connections and the connection pool.
So far, the only thing I've found that helped was to use the NO_INDEX hint. But that's unacceptable because I'd have to temporarily modify production DML and recompile.
Unless you have some more magic up your sleeve, I'm going to have to go back to a normal CREATE INDEX.
Thanks if you have time to take a peek.
October 23, 2004 - 9:10 am UTC
the CBO always considers ALL indexes. sessions would have to set "skip unusable indexes" = true to skip it.
the reasoning: you were doing maintenance, you set some indexes to unusuable, the maintenance failed (but you didn't notice). you have indexes that are vital to your system (for performance, for whatever). They cannot be used. We notify you instead of blindly skipping them.
it is what it does.
dbms_pclxutil, unusable index
Bill C., October 22, 2004 - 2:38 pm UTC
Seems that bugs around ORA-01502 are prevalent and pop up in many versions of 8, 8i and 9i.
Just found my problem as possibly "fixed" in 10g:
"The SKIP_UNUSABLE_INDEXES session parameter is now a dynamic initialization parameter, and its default value is TRUE at all levels. By changing the default setting for the SKIP_UNUSABLE_INDEXES to TRUE, the optimizer ignores UNUSABLE indexes at parse time and avoids potential ORA-1502 errors. "
And since my clients are on 8i and 9i... :-(
As a recap I want to:
- drop the existing FK
- create a new local, non-prefixed index (which will eventually support the FK) in unusable state.
- run dbms_pclxutil.build_part_index while the users continue querying for the 6 to 12 hours it takes to build, without hitting ORA-01502
- run dbms_stats on new index
- recreate the FK, which should use new index since the FK is on the leading column
(In case I haven't mentioned it, the second and last column in the new index is the date key used to partition the composite parititioned table. The table has no other index including the partition key.)
Words of wisdom from the wizard of PL/SQL?
October 23, 2004 - 9:11 am UTC
not sure what you mean about prevelant -- but if an index is usuable and the cbo considers it and decided to use it, it will fail unless that session/system setting is in fact "set"
if the users are querying the data only -- you might be able to
a) transport the data off to another machine
b) build index
c) transport data and index back
Thanks anyway...
Bill C., October 25, 2004 - 11:54 am UTC
By prevalent, I meant that when searching and searching Metalink for help, I ran across so many notes, forum threads and bugs related to this issue, with one 1502 bug fixed in almost every release of Oracle from 8.0.4 to 9.2.0.1, that I became aware that unusable indexes are an accute issue for many data warehouses.
To be fair, most of them related to users not understanding that skip_unusable_indexes did not apply when the unusable index was on a unique index and they were attempting DML.
My case is much more simple: if an index is unusable I don't want the CBO to consider it and I don't want it to complain. If it has to, I want it to use a sub-optimal plan like it was doing before the new index creation.
Instead, the second I create it as unusable in prep to use dbms_pclxutil, existing queries start puking on ORA-01502. The CBO is including them in it considered permutations, and halts when it finds an unusable one. I find this unacceptable. But the habits of the CBO are not your problem, so I'm not expecting a response to that. As I indicated, I'm just bummed since the benefits of using this package are negated by the fact that its use basically forces my system offline while I "rebuild" the index.
The option of moving the data off and back on is not likely given some history I'm not totally familiar with. The primary aim is for the least downtime or uninterrupted time as possible. So barring some pre-10g magic, it's back to huge SAS and CREATE INDEX ONLINE COMPUTE STATISTICS PARALLEL NOLOGGING.
Thanks Tom.
October 25, 2004 - 12:03 pm UTC
prevalent
Bill C., October 25, 2004 - 12:02 pm UTC
Since we're so big on the empirical evidence here, I guess I should put some bug #'s behind my "prevalent" explanation (all but one of which do not apply to my problems with existing SELECT attempts and SELECT cursors against a table with a new unusable index):
743843
589199
772089
3553343
2206492
2166556
2146920
3087677
1904696
1395260
index status n/a but DBMS_STATS gives
jose t a, January 21, 2005 - 5:40 am UTC
One similar issue
My problem is when using DBMS_STATS to analyze the indexes of partitioned
table, I get the error ORA-1502 even the status is USABLE in dba_ind_partitions
January 21, 2005 - 8:27 am UTC
unless you give me a test case that shows this, you'll need to work with support if you believe something incorrect is happening.
LongOps and Nested Loops
A Reader, January 27, 2005 - 4:34 am UTC
Tom,
Assuming i have a nested loop join between 2 tables and that is part of a much bigger query. During the time query does the NL, nothing figures in the session_longops table. That could be (as you said) because each step in the Nl takes < 5secs for completion.
My question is :
Is there anyway to find out what the query is currently doing apart from v$session_longops. Reason is that my NLs take more than 20 mins and during which i have no idea what the query is doing. Iam assuming that it is doing the nested loops.
PS:- Iam also closely monitoring the waits.
Thanks,
January 27, 2005 - 9:00 am UTC
you can peek at v$sesstat to see how much work it is performing. The problem is -- you don't know (and neither do we) know how much work it HAS TO perform.
So, you can watch the LIO's go up and up and up -- but you really won't know when they will end.
Sounds like you need to use some juicy full scans. If I had a nested loops query taking more than seconds -- I'd be wondering "why"