Just a quick note to say congratulations to Paul Till, a mate of mine, at my current client, who has recently passed his OCM certification. I knew Paul was good, from having worked with him, on a DR implementation / upgrade for a large DW, but I hadn’t realised how good. As certifications go, it’s the daddy and the Oracle one to have.
This one turned out to be a an interesting bug the other day…
I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I checked. I knew the DBA team had been doing some reorganisations the previous weekend, to recover some space, so I wondered if that was connected….it was, and after opening an SR, the DBA, Phil, found an explanation (from Oracle Note: 604648.1) and a resolution.
Reproducing the issue and the way to fix it, is simple, using this script…
DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES; CREATE TABLESPACE new_tbs DATAFILE 'C:APPORACLEORADATAT111NEW_TBS.DBF' SIZE 100M ONLINE; CREATE TABLESPACE old_tbs DATAFILE 'C:APPORACLEORADATAT111OLD_TBS.DBF' SIZE 100M ONLINE; SELECT ts#,name FROM sys.ts$ WHERE name LIKE '%TBS'; CREATE TABLE jeff_test(col1 DATE NOT NULL ,col2 NUMBER NOT NULL ,col3 VARCHAR2(200) NOT NULL ) TABLESPACE old_tbs PARTITION BY RANGE(col1) SUBPARTITION BY LIST(col2) SUBPARTITION TEMPLATE( SUBPARTITION "S1" VALUES(1) ,SUBPARTITION "S2" VALUES(2) ) (PARTITION p1 VALUES LESS THAN(TO_DATE('31-DEC-2009','DD-MON-YYYY')) ,PARTITION p2 VALUES LESS THAN(TO_DATE('31-DEC-2010','DD-MON-YYYY')) ) / SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST'; SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST'; ALTER TABLE jeff_test MOVE SUBPARTITION p1_s1 TABLESPACE NEW_TBS; ALTER TABLE jeff_test MOVE SUBPARTITION p1_s2 TABLESPACE NEW_TBS; ALTER TABLE jeff_test MOVE SUBPARTITION p2_s1 TABLESPACE NEW_TBS; ALTER TABLE jeff_test MOVE SUBPARTITION p2_s2 TABLESPACE NEW_TBS; DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES; ALTER TABLESPACE new_tbs RENAME TO old_tbs; SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST'; SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST'; ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE old_tbs; ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p2 TABLESPACE old_tbs; SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST'; SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';
Which, when run in 126.96.36.199 on Windows 2003 Server 64 bit, gives:
DROP TABLESPACE old_tbs succeeded. CREATE TABLESPACE succeeded. CREATE TABLESPACE succeeded. TS# NAME ---------------------- ------------------------------ 9 NEW_TBS 10 OLD_TBS 2 rows selected CREATE TABLE succeeded. PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 OLD_TBS P2 OLD_TBS 2 rows selected SUBPARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1_S2 OLD_TBS P1_S1 OLD_TBS P2_S2 OLD_TBS P2_S1 OLD_TBS 4 rows selected ALTER TABLE jeff_test succeeded. ALTER TABLE jeff_test succeeded. ALTER TABLE jeff_test succeeded. ALTER TABLE jeff_test succeeded. DROP TABLESPACE old_tbs succeeded. ALTER TABLESPACE new_tbs succeeded. PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 _$deleted$10$0 P2 _$deleted$10$0 2 rows selected SUBPARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1_S2 OLD_TBS P1_S1 OLD_TBS P2_S2 OLD_TBS P2_S1 OLD_TBS 4 rows selected ALTER TABLE jeff_test succeeded. ALTER TABLE jeff_test succeeded. PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1 OLD_TBS P2 OLD_TBS 2 rows selected SUBPARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ P1_S2 OLD_TBS P1_S1 OLD_TBS P2_S2 OLD_TBS P2_S1 OLD_TBS 4 rows selected
Notice that the $n in “_$deleted$n$m” is 10, which is the ts# of the OLD_TBS before the rename. The problem revolves around entries in TS$, when you rename tablespaces to names that have previously been used and then dropped, basically because the old entries are not removed from TS$.
Bug Numbers:8291493, itself a duplicate of 5769963
According to the SR and bug, it was noticed in 10.2.0.4 and is fixed in 10.2.0.5. We’ve reproduced it in 188.8.131.52 on various ports, (results above) and updated our SR, so I guess the fix might also find it’s way into 184.108.40.206, perhaps.
Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all the partitions behind the current date, i.e. January through mid June were populated. There were no indexes on the table.
NOTE – I tried this on 10.2.04 (AIX) and 11.1.0 (Fedora 11) – the example below is from 11.1.0.
I’ll recreate the scenario here:
CREATE TABLESPACE tsp1 datafile '/u01/app/oracle/oradata/T111/tsp1.dbf' size 100M autoextend off extent management local uniform size 1m segment space management auto online / CREATE TABLESPACE tsp2 datafile '/u01/app/oracle/oradata/T111/tsp2.dbf' size 100M autoextend off extent management local uniform size 1m segment space management auto online /
DROP TABLE test PURGE
CREATE TABLE test(col_date_part_key DATE NOT NULL
,col2 VARCHAR2(2000) NOT NULL
PARTITION BY RANGE(col_date_part_key)
(PARTITION month_01 VALUES LESS THAN (TO_DATE(’01-FEB-2009′,’DD-MON-YYYY’)) TABLESPACE tsp1
,PARTITION month_02 VALUES LESS THAN (TO_DATE(’01-MAR-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_03 VALUES LESS THAN (TO_DATE(’01-APR-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_04 VALUES LESS THAN (TO_DATE(’01-MAY-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_05 VALUES LESS THAN (TO_DATE(’01-JUN-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_06 VALUES LESS THAN (TO_DATE(’01-JUL-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_07 VALUES LESS THAN (TO_DATE(’01-AUG-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_08 VALUES LESS THAN (TO_DATE(’01-SEP-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_09 VALUES LESS THAN (TO_DATE(’01-OCT-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_10 VALUES LESS THAN (TO_DATE(’01-NOV-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_11 VALUES LESS THAN (TO_DATE(’01-DEC-2009′,’DD-MON-YYYY’)) TABLESPACE tsp2
,PARTITION month_12 VALUES LESS THAN (TO_DATE(’01-JAN-2010′,’DD-MON-YYYY’)) TABLESPACE tsp2
REM Insert rows, but only up to 14-JUN-2009
INSERT INTO test(col_date_part_key,col2)
SELECT TO_DATE(’31-DEC-2008′,’DD-MON-YYYY’) + l
FROM (SELECT level l FROM dual CONNECT BY level < 166)
SELECT MIN(col_date_part_key) min_date
, MAX(col_date_part_key) max_date
This runs and gives the following output:
DROP TABLE test PURGE * ERROR at line 1: ORA-00942: table or view does not exist
DROP TABLESPACE tsp1 INCLUDING CONTENTS
ERROR at line 1:
ORA-00959: tablespace ‘TSP1’ does not exist
DROP TABLESPACE tsp2 INCLUDING CONTENTS
ERROR at line 1:
ORA-00959: tablespace ‘TSP2’ does not exist
165 rows created.
Now, lets see what the plan looks like from AUTOTRACE when we run the following query to get the maximum value of COL_DATE_PART_KEY:
SQL> SET AUTOTRACE ON SQL> SELECT MAX(col_date_part_key) min_date 2 FROM test 3 /
Plan hash value: 784602781
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 9 | 99 (0)| 00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ALL| | 132 | 1188 | 99 (0)| 00:00:02 | 1 | 12 |
| 3 | TABLE ACCESS FULL | TEST | 132 | 1188 | 99 (0)| 00:00:02 | 1 | 12 |
– dynamic sampling used for this statement
0 recursive calls
0 db block gets
320 consistent gets
51 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE OFF
It shows a full scan of all twelve partitions. I figured that the the plan for such a query would show a full table scan, of all partitions for that table – because, in theory, if all but the first partition were empty, then the whole table would have to be scanned to answer the query – and Oracle wouldn’t know at plan creation time, whether the data met this case, so it would have to do the full table scan to ensure the correct result.
What I thought might happen though, is that in executing the query, it would be able to short circuit things, by working through the partitions in order, from latest to earliest, and finding the first, non null, value. Once it found the first, non null, value, it would know not to continue looking in the earlier partitions, since the value of COL_DATE_PART_KEY couldn’t possibly be greater than the non null value already identified.
It doesn’t appear to have this capability, which we can check by taking one of the partitions offline and then rerunning the query, whereupon it complains that not all the data is present…
SQL> ALTER TABLESPACE tsp1 OFFLINE;
SQL> SET AUTOTRACE ON
SQL> SELECT MAX(col_date_part_key) min_date
2 FROM test
SELECT MAX(col_date_part_key) min_date
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/T111/tsp1.dbf’
SQL> SET AUTOTRACE OFF
So, even though we know we could actually answer this question accurately, Oracle can’t do it as it wants to scan, unnecessarily, the whole table.
I did find a thread which somebody had asked about this on OTN, but all the responses were about workarounds, rather than explaining why this happens (bug/feature) or how it can be made to work in the way I, or the poster of that thread, think it, perhaps, should.
Can anyone else shed any light on this? If it’s a feature, then it seems like something that could be easily coded more efficiently by Oracle. The same issue would affect both MIN and MAX since both could be
approached in the same manner.
Just a note, to myself more than anything, about what extra packages are required by a 64 bit installation of Fedora Core 10, when trying to install Oracle 11gR1.
The installation I undertook was on a FC10 64 bit VM running under VMWare Server 2.0 running on top of FC10 64 bit OS.
Tim, as usual, has a lovely guide which told me almost everything I needed to know, however the guide says “If you are performing the 64-bit installation, make sure both the 32-bit and 64-bit libraries are installed.” rather than explicitly stating the packages for a 64 bit install. Until I tried to install Oracle 11gR1, I didn’t know what these were. The Oracle installer for 11g soon told me in the pre install checks it does, so I went about installing the following packages, in order:
That got me past the pre install checks of the Oracle installer and on to a successful install.
I’ve added the list to the comments on the guide Tim produced as well.
If you’re interested in creating a TPC-H schema for testing purposes, then the following scripts may be of use to you:
You may wish to read and check them before you use them – they’re not exactly rocket science but they seem to do the job.
I have all the files in the same directory for simplicity sake.
I then use them to create a scale factor 1 target TPC-H schema using the following calls:
# “l” loads the REGION and NATION tables
./multi_load_tpch.sh 1 l “tpch/tpch@test” 1
# “s” loads the SUPPLIER table
./multi_load_tpch.sh 1 s “tpch/tpch@test” 10
# “c” loads the CUSTOMER table
./multi_load_tpch.sh 1 c “tpch/tpch@test” 10
# “p” loads the PART and PARTSUPP tables
./multi_load_tpch.sh 1 p “tpch/tpch@test” 10
# “o” loads the ORDERS and LINEITEM tables
./multi_load_tpch.sh 1 o “tpch/tpch@test” 10
Obviously, you need to change the connection string to match your environment.
- Obviously, they are supplied as is – use at your own discretion and risk.
- You need to have created the target schema tables and made sure they are empty as the SQL*Loader control files use APPEND.
- Bear in mind that choosing too high a number of parallel streams (the last parameter in the calls) will overload your machine so try and balance it against the available system resources.
Bugs, issues or questions, please get in touch…enjoy.
I wanted to try out this HammerOra product from Steve Shaw, both at work and on my box at home…but after playing with it at home, I realised that it takes quite some time to build even a small (scale factor 1) TPC-H schema…I know it runs serially, but I’m still not quite sure why it’s that slow (on my system that is), but Steve does say it can take a while and that you might wish to consider using the TPC utility DBGEN to generate and load the schema quicker…particularly if you use some manual parallelisation.
Given that I also need to use this tool to help with some benchmarking at work, I decided to try to get DBGEN to run on a HP-UX box today and had one or two problems which I managed to sort out. The machine in question is an RP8420 running HP-UX B.11.11.
DBGEN is a utility that allows you to create a series of flat files which contain the data for a TPC-H schema. You can then use SQL*Loader to load these into appropriately constructed tables in an Oracle database – any database actually…but I only care about Oracle of course 😉
The utility can be called with various parameters including making the target datasets in smaller “child” files which can be created in a manually parallelised fashion to speed the whole process up. You have to download the DBGEN reference data set from the TPC website (lower right hand side).
This reference data set contains the ANSI C source code which makes the DBGEN executable (and QGEN also…but more on that another day)…unfortunately it’s just the source code, so that means you need to compile it yourself…which of course leads to the first problem…that I know diddly squat about C…yeah I know, not very manly! Luckily I can sometimes follow instructions (which come with the reference data set)…
1. Copy makefile.suite to makefile
2. Edit makefile and make the following amendments (in red):
## CHANGE NAME OF ANSI COMPILER HERE
CC = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
MACHINE = HP
WORKLOAD = TPCH
That’s it for the makefile.
Now, as I mentioned, the ORACLE database is not a listed database variant in the DBGEN C code – it’s got all the other popular RDBMS which I find quite bizarre…I’m sure there’s a reason, but I can’t think of one. To get around this, as per Chapter 5 in “Pro Oracle Database 10g RAC on Linux” by Steve Shaw and Julian Dyke, I added a section to the tpcd.h for the ORACLE database:
#define GEN_QUERY_PLAN “”
#define START_TRAN “”
#define END_TRAN “”
#define SET_OUTPUT “”
#define SET_ROWCOUNT “”
#define SET_DBASE “”
I thought that was it – but it still would’t compile, giving the error:
config.h:213:2: #error Support for a 64-bit datatype is required in this release
Looking at the config.h – and bearing in mind I’m no C programmer – it struck me as odd that all bar the HP machine section, had stuff about DSS_HUGE and 64 bits…so I took a punt and copied some lines (in red) from the IBM section into the HP one to see if it worked…and it did. The HP section now looks like this:
#define DSS_HUGE long long
#define HUGE_FORMAT “%lld”
#define HUGE_DATE_FORMAT “%02lld”
#define RNG_A 6364136223846793005ull
#define RNG_C 1ull
#endif /* HP */
Typing make at the command prompt then compiles the code and produces the dbgen executable…which I then spent a few hours playing with to create a scale factor 1 TPC-H set of files.
My next problem was one of my own making really in that I copied the CREATE TABLE statements for the TPC-H target tables from HammerOra’ TCL script for TPC-H creation, but unfortunately, the column ordering is slightly different in those DDL statements as compared to the DBGEN output files…which meant that I created the tables OK, but since I’d copied the column ordering to make the SQL*Loader control files, I got errors when I tried to load some of the files as the column order is different in one or two cases.
I then downloaded the TPC-H specification document which has, amongst other things, the data model, from which I cross checked the column ordering of the data model against the columns in the output files and then managed to rerun the data in without any further issues.
Tomorrow I’ll try running HammerOra against the target TPC-H schema and make some shell scripts to try and automate most of the process so we can build different scale factor schemae and do so in a manually parallelised fashion – scripts the amiable Scotsman created for his parallel testing a while back should give me a good start with that.
Here’s a tale about an Oracle initialisation parameter…and a lesson we should all take note of…
For three days, my colleagues in the support team on one of the warehouses I’m involved in, were struggling with a piece of code which was exhausting the available temp space and after trying everything they could think of, they asked me to take a look. I must admit I was a little baffled at first because the piece of code in question had been happily running for some time now and every time I’d run it, I’d never noticed that TEMP was anywhere near being exhausted so, whilst I could tell the process had some kind of problem, I was in the dark as to exactly what that problem was.
After trying to break down the large query into several smaller steps, I realised that it was an early step in the query that was exhibiting the problem of temp exhaustion – the step being a pivot of around 300 million measure rows into a pivot target table.
This process runs monthly and it had run without issue on all of the previous 20 occurrences and for roughly the same, if not more rows to pivot…so it didn’t appear to be the volume that was causing the problem.
There had been no changes in the code itself for many months and the current version of the code had been run successfully in previous months, so it didn’t appear to be a code fault.
I obtained the actual execution path for the statement whilst it was running and it looked reasonable, although looking at it, triggered a thought in my mind…what if something had changed in the database configuration?
Why would I get that thought from looking at the execution path?
Well, a while back, we had received some advice that a line in our init.ora as follows, should be changed to set the feature to TRUE instead of FALSE, so that the feature became active:
_gby_hash_aggregation_enabled = FALSE
This results in a line in the plan that reads:
HASH GROUP BY
SORT GROUP BY
The parameter was set to FALSE due to a known bug and the issues we’d seen with it, however the recent advice we’d received, indicated that the bug had been resolved at the version level we were on and that by enabling the feature – which enables GROUP BY and Aggregation using a hash scheme – we’d gain a performance boost for certain queries.
So, the DBA team researched the advice and it appeared to be the case, that the bug (4604970) which led to the disabling of the feature was fixed at our version level (10.2.0.3 on HP-UX). We duly turned on the feature in a pre production environment and ran it for a while without noticing any issues. We then enabled it in production and again, for a while, we’ve not noticed any issues…until now.
After a check back through the logs, it appeared that since the parameter was first enabled, the queries which were now failing, had not been run at all…they had only run prior to the parameter change…so with my suspicions aroused further, I disabled the feature at the session level and reran the process. It completed in a normal time frame and used a small amount of TEMP – hooray!
So, now we have to go back to support to try and understand if the original bug is not quite fixed or whether this is a different scenario…in any event, we’re going to disable the feature for now, even though we’re only getting problems with the feature on 2 processes out of perhaps thousands.
So, what’s the lesson to learn?
Well, quite simply, that you need to have a thorough audit of what configuration changes you’ve made together with a good audit of the processes you’ve run so that you can work out what has changed since the last time you successfully ran a process. This gives you a fighting chance of spotting things like the above.
I really wanted to do this via AWR, but I’ve not been able to find out if this kind of information is stored and, if it is, how I’d get access to it…maybe someone else knows…hint hint?!
I have various queries I run whilst I’m online and processes are running, but what I really wanted, was to know the usage profile throughout the day…so, given that I couldn’t find an AWR way of tracking our use of TEMP on a database, I figured I’d use a more klunky method…
DROP TABLE mgmt_t_temp_use_history PURGE / CREATE TABLE temp_use_history(snap_date DATE NOT NULL ,sid NUMBER NOT NULL ,segtype VARCHAR2(9) NOT NULL ,qcsid NUMBER NULL ,username VARCHAR2(30) NULL ,osuser VARCHAR2(30) NULL ,contents VARCHAR2(9) NULL ,sqlhash NUMBER NULL ,sql_id VARCHAR2(13) NULL ,blocks NUMBER NULL ) PCTFREE 0 COMPRESS NOLOGGING / CREATE UNIQUE INDEX tuh_pk ON temp_use_history(snap_date,sid,segtype) PCTFREE 0 COMPRESS NOLOGGING / ALTER TABLE temp_use_history ADD CONSTRAINT tuh_pk PRIMARY KEY(snap_date,sid,segtype) USING INDEX / DECLARE l_program_action VARCHAR2(2000); l_27477 EXCEPTION; PRAGMA EXCEPTION_INIT(l_27477,-27477); BEGIN BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'MINUTELY_5M' ,start_date => SYSDATE ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5' ,comments =>'Daily schedule to run a job every five minutes.' ); EXCEPTION WHEN l_27477 THEN NULL; -- Ignore if the schedule exists END; l_program_action := 'DECLARE'; l_program_action := l_program_action||' l_date DATE := SYSDATE; '; l_program_action := l_program_action||'BEGIN'; l_program_action := l_program_action||' INSERT /*+ APPEND */ INTO temp_use_history(snap_date,sid,qcsid,username,osuser,contents,seg type,sqlhash,sql_id,blocks)'; l_program_action := l_program_action||' SELECT l_date,s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqlh ash,su.sql_id,sum(su.blocks)'; l_program_action := l_program_action||' FROM v$sort_usage su'; l_program_action := l_program_action||' , v$session s'; l_program_action := l_program_action||' , v$px_session ps'; l_program_action := l_program_action||' WHERE s.sid=ps.sid(+)'; l_program_action := l_program_action||' AND s.saddr = su.session_addr'; l_program_action := l_program_action||' AND s.serial# = su.session_num'; l_program_action := l_program_action||' GROUP BY s.sid,ps.qcsid,s.username,s.osuser,su.contents,su.segtype,su.sqladdr,su. sqlhash,su.sql_id;'; l_program_action := l_program_action||' COMMIT; '; l_program_action := l_program_action||'END;'; BEGIN DBMS_SCHEDULER.CREATE_PROGRAM( program_name => 'SNAP_TEMP_USAGE' ,program_type => 'PLSQL_BLOCK' ,program_action => l_program_action ,enabled => TRUE ,comments => 'Program to snap the temp usage into TEMP_USE_HISTORY' ); EXCEPTION WHEN l_27477 THEN NULL; -- Ignore if the program exists END; BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_SNAP_TEMP_USAGE' ,program_name => 'SNAP_TEMP_USAGE' ,schedule_name => 'MINUTELY_5M' ,enabled => TRUE ,auto_drop => FALSE ,comments => 'Job to snap the temp usage into TEMP_USE_HISTORY' ); EXCEPTION WHEN l_27477 THEN NULL; -- Ignore if the job exists END; END; /
I can now run queries against the TEMP_USE_HISTORY table to show how much TEMP has been used, when, by whom and for what use, e.g.
SQL> ed Wrote file afiedt.buf 1 select snap_date,round(sum(blocks)*32/1024/1024) gb 2 from temp_use_history 3 where snap_date > sysdate-1 4 group by snap_date 5 having round(sum(blocks)*32/1024/1024) > 50 6* order by 1 SQL> / SNAP_DATE GB -------------------- ---------- 26-FEB-2008 16:12:25 57 26-FEB-2008 16:17:25 65 26-FEB-2008 16:22:25 74 26-FEB-2008 16:27:25 86 26-FEB-2008 16:32:25 95 26-FEB-2008 16:37:25 107 26-FEB-2008 16:42:25 121 26-FEB-2008 16:47:25 127 26-FEB-2008 16:52:25 147 26-FEB-2008 16:57:25 160 26-FEB-2008 17:02:25 162 26-FEB-2008 17:07:25 179 26-FEB-2008 17:12:25 196 26-FEB-2008 17:17:25 208 26-FEB-2008 17:22:25 217 26-FEB-2008 17:27:25 233 26-FEB-2008 17:32:25 241 26-FEB-2008 17:37:25 251 26-FEB-2008 17:42:25 257 26-FEB-2008 17:47:25 262 26-FEB-2008 17:52:25 264 26-FEB-2008 17:57:25 267 26-FEB-2008 18:02:25 201 27-FEB-2008 00:27:25 59 27-FEB-2008 00:32:25 60 27-FEB-2008 00:37:25 69 27-FEB-2008 01:12:25 57 27-FEB-2008 02:22:25 53 27-FEB-2008 09:57:25 51 29 rows selected.
From the above, I can see that, during the last twenty four hours on the database in question, there was reasonably heavy use of the TEMP area between 4pm and 6pm yesterday, and that the load peaked at approximately 267Gb.
After Doug’s comment on my earlier blog on CALIBRATE_IO, and after seeing Kevin using Mr Poll to do a RAC poll, I’ve now created a poll for the results people are getting from this CALIBRATE_IO routine in 11g.
If you’d like to contribute please visit using the following link:
No prizes for the “biggest”…Kevin is bound to have something in his lab that beats us all! 😉
After reading how inadequate Doug was feeling over his IO subsystem, I decided to see how quick mine was…not that we’re getting into a “mine is better than yours” game, but rather to see how mine stacks up against Doug’s, bearing in mind his is a 5 disk stripe natively attached to his machine (I’m assuming) and mine is a logical disk attached to a VMWare machine…although admittedly, the PC underneath this logical disk is running, motherboard based, RAID striping, over two physical SATA disks…I just figured it would be interesting to compare.
Obviously, any experiment that goes flawlessly according to a preconceived plan is:
2. Less educational
3. Not normally one I’ve done – mine always have problems it seems!
I ran the calibration on my VMWare based OpenSuse 10 linux with Oracle 11g and it immediately came up with a problem:
SQL> @io SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat); 8 9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 11 dbms_output.put_line('max_mbps = ' || mbps); 12 end; 13 / DECLARE * ERROR at line 1: ORA-56708: Could not find any datafiles with asynchronous i/o capability ORA-06512: at "SYS.DBMS_RMIN", line 453 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1153 ORA-06512: at line 7
Of course, consulting the manual led me to run this query:
SELECT name, asynch_io FROM v$datafile f,v$iostat_file i WHERE f.file# = i.file_no AND filetype_name = 'Data File' /
SQL> / NAME ASYNCH_IO -------------------------------------------------- --------- /home/oracle/oradata/su11/system01.dbf ASYNC_OFF /home/oracle/oradata/su11/sysaux01.dbf ASYNC_OFF /home/oracle/oradata/su11/undotbs01.dbf ASYNC_OFF /home/oracle/oradata/su11/users01.dbf ASYNC_OFF /home/oracle/oradata/su11/example01.dbf ASYNC_OFF
…or in other words no asynchronous IO available – as the error message had said.
After altering the filesystemio_options parameter to “set_all” and bouncing the instance, a second run of the calibration process seemed to work fine…
SQL> @io SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat); 8 9 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 10 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 11 dbms_output.put_line('max_mbps = ' || mbps); 12 end; 13 / max_iops = 72 latency = 13 max_mbps = 26 PL/SQL procedure successfully completed.
So,my figures are considerably lower than those Doug achieved:
max_iops = 112 latency = 8 max_mbps = 32
…but not too bad I guess considering the fact that mine is a VM and the hardware I’m running is more humble…no seriously, size does not matter!