Wednesday, July 30, 2008
Scripts for loading up DBGEN TPC-H data
If you're interested in creating a TPC-H schema for testing purposes, then the following scripts may be of use to you:
Unix Scripts:
Multi Load TPCH
Load TPCH Stream
SQL*Loader Control files:
REGION Table
NATION Table
SUPPLIER Table
CUSTOMER Table
PART Table
PARTSUPP Table
ORDERS Table
LINEITEM Table
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.
Caveats:
Bugs, issues or questions, please get in touch...enjoy.
Unix Scripts:
Multi Load TPCH
Load TPCH Stream
SQL*Loader Control files:
REGION Table
NATION Table
SUPPLIER Table
CUSTOMER Table
PART Table
PARTSUPP Table
ORDERS Table
LINEITEM Table
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.
Caveats:
- 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.
Labels: benchmarking, code, dba, DW, tuning
Monday, July 21, 2008
Creating a TPC-H schema with DBGEN on HP-UX
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
DATABASE= ORACLE
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:
#ifdef ORACLE
#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:
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.
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
DATABASE= ORACLE
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:
#ifdef ORACLE
#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:
#ifdef HP
#define _INCLUDE_POSIX_SOURCE
#define STDLIB_HAS_GETOPT
#define DSS_HUGE long long
#define HUGE_FORMAT "%lld"
#define HUGE_DATE_FORMAT "%02lld"
#define RNG_A 6364136223846793005ull
#define RNG_C 1ull
#endif /* HP */
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.
Labels: benchmarking, bugs, code, dba, tuning
Tuesday, February 26, 2008
Tracking TEMP usage throughout the day
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...
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.
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.
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.
Thursday, February 21, 2008
Getting multi row text items from Designer repository
I've mentioned Lucas Jellema before when talking about Oracle Designer - he's helped me out again today via this post on the AMIS blog.
What I wanted, was to be able to extract, using a SQL query against the designer repository, the "Description" attribute from our Tables so that I could create table comments in the database which had the description from the table in Designer.
Extracting scalar attributes is simple enough, however, description is a multi row text property so it needed a bit more thought...and rather than reinvent the wheel, I had a look at Lucas' stuff and sure enough found the post above. It was almost what I wanted, only I had to change it to look for table stuff rather than entities and attributes.
I used the same TYPE and sum_string_table function as Lucas so if you're trying to use this stuff you'll probably need to read Lucas' article first.
The query I ended up with is below...it's been "sanitized" somewhat, but I'm sure you'd get the picture, if retrieving stuff out of the designer repository is a requirement of yours.
Thanks Lucas!
On another note, regular visitors may realise I've now got my own oramoss dot com domain and that my blogger blog is published on that domain now.
Thanks to Andreas Viklund for the template.
If anyone sees anything untoward with the new site please feel free to drop me a note. It's a bit thin on content but I'll work on that over time.
What I wanted, was to be able to extract, using a SQL query against the designer repository, the "Description" attribute from our Tables so that I could create table comments in the database which had the description from the table in Designer.
Extracting scalar attributes is simple enough, however, description is a multi row text property so it needed a bit more thought...and rather than reinvent the wheel, I had a look at Lucas' stuff and sure enough found the post above. It was almost what I wanted, only I had to change it to look for table stuff rather than entities and attributes.
I used the same TYPE and sum_string_table function as Lucas so if you're trying to use this stuff you'll probably need to read Lucas' article first.
The query I ended up with is below...it's been "sanitized" somewhat, but I'm sure you'd get the picture, if retrieving stuff out of the designer repository is a requirement of yours.
WITH dsc AS
(
SELECT txt.txt_text
, txt.txt_ref
FROM cdi_text txt
WHERE txt.txt_type = 'CDIDSC'
)
, add_cast AS
(
SELECT appsys.name application_system_name
, b.name table_name
, b.alias
, CAST(COLLECT(dsc.txt_text) AS string_table) tab_description
FROM dsc
, designer.ci_application_systems appsys
, designer.ci_app_sys_tables a
, designer.ci_table_definitions b
WHERE dsc.txt_ref = a.table_reference
AND b.irid = a.table_reference
AND a.parent_ivid = appsys.ivid
GROUP BY appsys.name
, b.name
, b.alias
)
SELECT application_system_name
, table_name
, alias
, sum_string_table(tab_description)
FROM add_cast
WHERE application_system_name = 'MY_APP_SYS'
and table_name = 'MY_TABLE_NAME'
/
Thanks Lucas!
On another note, regular visitors may realise I've now got my own oramoss dot com domain and that my blogger blog is published on that domain now.
Thanks to Andreas Viklund for the template.
If anyone sees anything untoward with the new site please feel free to drop me a note. It's a bit thin on content but I'll work on that over time.
Monday, August 13, 2007
ORA-07455 and EXPLAIN PLAN...and statements which, perhaps, shouldn't run
I encountered a scenario today which I thought was strange in a number of ways...hence, irresistible to a quick blog post.
The scenario started with an end user of my warehouse emailing me a query that was returning an error message dialog box, warning the user before they ran the query, that they had insufficient resources to run said query - ORA-07455 to be precise.
I figured, either the query is one requiring significant resources - more resources than the user has access to, or the query has a suboptimal plan, whereby it thinks it will require more resources than they have access to.
To try and determine which, I logged into the same Oracle user as my end user and tried to get an explain plan of the query - so I could perhaps gauge whether there were any problems with the choice of execution path and whether the query was one which would indeed require significant resources.
The result was that it came back with the same error - which quite surprised me at first.
In using EXPLAIN PLAN, I wasn't asking the database to actually run the query - merely to tell me what the likely execution path was for the query and yet, it appears to still do the checks on resource usage. At first, that seemed strange to me, in the sense that I wouldn't be requiring those resources since, I'm not actually executing the statement, yet perhaps it does makes sense - or at least is consistent, because, for example, you don't need access to all the objects in the query if you're not going to actually execute it, yet quite rightly, the optimizer does checks as to whether you have the appropriate access permissions to each object as part of the EXPLAIN PLAN process.
That was educational point number one for me.
After logging in as another user with unlimited resource usage, I then reran the EXPLAIN PLAN and the statement was accepted and the plan returned...indicating an unpleasant rewrite of the query, and a very high anticipated cost - in excess of the limit for that end user.
That explained why the ORA-07455 was appearing for them, but highlighted an altogether different issue which perplexed me further. There follows a simple reconstruction of the query and explain plan results:
First the obligatory test script...
SET TIMING OFF
DROP TABLE tab1 PURGE
/
CREATE TABLE tab1
(col1 VARCHAR2(1))
/
DROP TABLE tab2 PURGE
/
CREATE TABLE tab2
(col2 VARCHAR2(1))
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
,tabname => 'TAB1'
);
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER
,tabname => 'TAB2'
);
END;
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab1 VALUES('A')
/
INSERT INTO tab1 VALUES('B')
/
INSERT INTO tab2 VALUES('C')
/
INSERT INTO tab2 VALUES('D')
/
COMMIT
/
SET AUTOTRACE ON
SELECT *
FROM tab1
WHERE col1 IN (SELECT col1 FROM tab2)
/
SET AUTOTRACE OFF
Now the results...
Table dropped.
Connected.
Table dropped.
Table created.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
C
-
A
B
A
B
4 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4220095845
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)|00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | TAB1 | 1 | 2 | 2 (0)|00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| TAB2 | 1 | | 2 (0)|00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "TAB2" "TAB2" WHERE
:B1=:B2))
3 - filter(:B1=:B2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
458 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
Now, when I first saw the query I thought, hang on a minute, COL1 does not exist in table TAB2 so this query should not even execute...but it does! I don't think it should execute personally but according to the documentation, "Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.", so it is operating as described in the manuals- even if, in my view, it's a little odd since without a rewrite, the query is incapable of executing.
The query has been rewritten with an EXISTS approach - note the first FILTER statement in the "Predicate Information" section of the autotrace output. A bit like this:
SELECT a.*
FROM tab1 a
WHERE EXISTS (SELECT 0
FROM tab2 b
WHERE a.col1 = a.col1
)
/
The subquery is always going to return a row, hence, for any row we select in the containing query, we will always get that row back, because the EXISTS will always find a match - it's a bit like saying "WHERE TRUE" I guess.
Interestingly, my friend Jon first brought this scenario to my attention last week in various discussions with him and another of my colleagues, who is far more experienced than myself. To be fair, the experienced colleague is the source of a number of my blogging posts, but he's painfully shy and will therefore remain nameless.
I was educated during that discussion, that this functionality is as advertised in the manuals - even if it doesn't sit well with me. My closing line to my fellow debaters at the time, was that nobody would ever write SQL like that and if they did I'd tell them to rewrite it using aliases and so that it made sense - as is often the case in life though, the very next week, a real life user comes up with exactly that scenario - at least I was prepared!
Labels: cbo, code, errors, tuning
Thursday, February 08, 2007
Using AWR to summarise SQL operations activity
I was asked recently how much "use" a database had - a non too specific question but basically the person asking it wanted to know how many queries, DMLs, DDLs etc.. were run against the database on a daily basis...kind of like the queries per hour metrics that are sometimes quoted on TPC benchmarks I guess.
I didn't have an answer as to where to get this information so I figured it warranted a ramble through the AWR tables to try and come up with something...
This isn't going to be a description of how the AWR works - there are manuals for that. Instead, I'll just give you the salient points from my exploration.
The AWR data, including it's history, is accessible by looking at a collection of DBA Views named DBA_HIST%.
Part of the AWR processing captures Active Session History (ASH) data which is then accessible via the DBA_HIST_ACTIVE_SESS_HISTORY view. From this view we can obtain, for any given snap point in time, the sessions which were active, together with details of what they were doing and the statistics relating to the performance of that activity.
On this view is a column called SQL_OPCODE which tells us the type of SQL operation being performed by the session, with various codes indicating different things. I didn't manage to find a definitive list of these SQL_OPCODES in a table/view anywhere so, by trial and error I worked them out as follows:
...I'm sure this isn't definitive...someone really smart out there will probably know where this list is actually stored and will, perhaps, put it on a comment to this post ;-)
OK, now to code up something that goes back over this AWR data for the last, say seven days, and group it up by SQL operation so we can see what our activity has been - I used a ROW_NUMBER analytic (cos they rock) to make sure we only get the latest snapshot row from the AWR table - if a SQL operation is running for a long time then it may appear in the AWR view more than once...and we don't want to double count it as far as executions go.
I've grouped up those sessions which don't have an active SQL statement associated with them (i.e. SQL_ID is NULL) into groups based on their Object Type and I've then used ROLLUP to get the results for each SQL operation by day and also the summed up totals by operation across the seven days and a grand total.
I'm not actually sure why some rows in DBA_HIST_ACTIVE_SESS_HISTORY don't have a SQL_ID against them - if they were not actually running a statement in the session then that sounds fair enough...but some of them had values in PLSQL_ENTRY_OBJECT_ID indicating (I think) that they were running a specific package (DBMS_STATS or DBMS_SPACE in many cases) so the fact they didn't have a SQL_ID was confusing to me - perhaps it means they're in that package but not actually running a query at the time of the snap - either way, they're grouped up separately.
Here is a SQL*Plus spool of the code and results from a test system:
It was just an idea...if anyone has anything to add/improve it feel free to comment.
I didn't have an answer as to where to get this information so I figured it warranted a ramble through the AWR tables to try and come up with something...
This isn't going to be a description of how the AWR works - there are manuals for that. Instead, I'll just give you the salient points from my exploration.
The AWR data, including it's history, is accessible by looking at a collection of DBA Views named DBA_HIST%.
Part of the AWR processing captures Active Session History (ASH) data which is then accessible via the DBA_HIST_ACTIVE_SESS_HISTORY view. From this view we can obtain, for any given snap point in time, the sessions which were active, together with details of what they were doing and the statistics relating to the performance of that activity.
On this view is a column called SQL_OPCODE which tells us the type of SQL operation being performed by the session, with various codes indicating different things. I didn't manage to find a definitive list of these SQL_OPCODES in a table/view anywhere so, by trial and error I worked them out as follows:
SQL_OPCODE SQL Operation
1 DDL
2 INSERT
3 Query
6 UPDATE
7 DELETE
47 PL/SQL package call
50 Explain Plan
189 MERGE
...I'm sure this isn't definitive...someone really smart out there will probably know where this list is actually stored and will, perhaps, put it on a comment to this post ;-)
OK, now to code up something that goes back over this AWR data for the last, say seven days, and group it up by SQL operation so we can see what our activity has been - I used a ROW_NUMBER analytic (cos they rock) to make sure we only get the latest snapshot row from the AWR table - if a SQL operation is running for a long time then it may appear in the AWR view more than once...and we don't want to double count it as far as executions go.
I've grouped up those sessions which don't have an active SQL statement associated with them (i.e. SQL_ID is NULL) into groups based on their Object Type and I've then used ROLLUP to get the results for each SQL operation by day and also the summed up totals by operation across the seven days and a grand total.
I'm not actually sure why some rows in DBA_HIST_ACTIVE_SESS_HISTORY don't have a SQL_ID against them - if they were not actually running a statement in the session then that sounds fair enough...but some of them had values in PLSQL_ENTRY_OBJECT_ID indicating (I think) that they were running a specific package (DBMS_STATS or DBMS_SPACE in many cases) so the fact they didn't have a SQL_ID was confusing to me - perhaps it means they're in that package but not actually running a query at the time of the snap - either way, they're grouped up separately.
Here is a SQL*Plus spool of the code and results from a test system:
x_j4134[543/9757]@AED52> l
1 WITH ash AS
2 (
3 SELECT TRUNC(ash.sample_time) sample_day
4 , (CASE WHEN ash.sql_opcode = 47
5 THEN 'PL/SQL'
6 WHEN ash.sql_opcode IN(1)
7 THEN 'DDL'
8 WHEN ash.sql_opcode IN(2,6,7,189)
9 THEN 'DML'
10 WHEN ash.sql_opcode IN(50)
11 THEN 'Explain Plan'
12 WHEN ash.sql_opcode IN(3)
13 THEN 'Query'
14 ELSE 'No Statement ID; In object type: 'NVL(o.object_type,'Not Specified')
15 END) statement_type
16 , ROW_NUMBER() OVER(PARTITION BY ash.sql_id,ash.sql_child_number ORDER BY ash.sample_time DESC) rn
17 FROM dba_hist_snapshot s
18 , dba_hist_active_sess_history ash
19 , dba_objects o
20 WHERE s.snap_id = ash.snap_id(+)
21 AND s.dbid = ash.dbid(+)
22 AND s.instance_number = ash.instance_number(+)
23 AND ash.plsql_entry_object_id = o.object_id(+)
24 AND TRUNC(ash.sample_time) BETWEEN TRUNC(SYSDATE-6) AND TRUNC(SYSDATE+1) -- all within last 7 days
25 )
26 SELECT sample_day
27 , statement_type
28 , COUNT(1)
29 FROM ash
30 WHERE rn = 1
31 GROUP BY ROLLUP(sample_day)
32 , ROLLUP(statement_type)
33 ORDER BY sample_day
34* , statement_type
x_j4134[543/9757]@AED52> /
SAMPLE_DAY STATEMENT_TYPE COUNT(1)
-------------------- ---------------------------------------------------- ----------
02-FEB-2007 00:00:00 DDL 112
02-FEB-2007 00:00:00 DML 49
02-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 292
02-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 7
02-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 131
02-FEB-2007 00:00:00 PL/SQL 301
02-FEB-2007 00:00:00 Query 181
02-FEB-2007 00:00:00 1073
03-FEB-2007 00:00:00 DDL 20
03-FEB-2007 00:00:00 DML 26
03-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 91
03-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 2
03-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 84
03-FEB-2007 00:00:00 PL/SQL 166
03-FEB-2007 00:00:00 Query 12
03-FEB-2007 00:00:00 401
04-FEB-2007 00:00:00 DDL 127
04-FEB-2007 00:00:00 DML 14
04-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 410
04-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 305
04-FEB-2007 00:00:00 PL/SQL 306
04-FEB-2007 00:00:00 Query 14
04-FEB-2007 00:00:00 1176
05-FEB-2007 00:00:00 DDL 115
05-FEB-2007 00:00:00 DML 81
05-FEB-2007 00:00:00 Explain Plan 1
05-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 261
05-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 16
05-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 161
05-FEB-2007 00:00:00 PL/SQL 315
05-FEB-2007 00:00:00 Query 360
05-FEB-2007 00:00:00 1310
06-FEB-2007 00:00:00 DDL 98
06-FEB-2007 00:00:00 DML 86
06-FEB-2007 00:00:00 Explain Plan 2
06-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 212
06-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 16
06-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 108
06-FEB-2007 00:00:00 PL/SQL 299
06-FEB-2007 00:00:00 Query 439
06-FEB-2007 00:00:00 1260
07-FEB-2007 00:00:00 DDL 98
07-FEB-2007 00:00:00 DML 162
07-FEB-2007 00:00:00 Explain Plan 1
07-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 210
07-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 24
07-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 96
07-FEB-2007 00:00:00 PL/SQL 337
07-FEB-2007 00:00:00 Query 348
07-FEB-2007 00:00:00 1276
08-FEB-2007 00:00:00 DDL 112
08-FEB-2007 00:00:00 DML 420
08-FEB-2007 00:00:00 Explain Plan 1
08-FEB-2007 00:00:00 No Statement ID; In object type: Not Specified 311
08-FEB-2007 00:00:00 No Statement ID; In object type: PACKAGE 25
08-FEB-2007 00:00:00 No Statement ID; In object type: PROCEDURE 119
08-FEB-2007 00:00:00 PL/SQL 572
08-FEB-2007 00:00:00 Query 493
08-FEB-2007 00:00:00 2053
DDL 682
DML 838
Explain Plan 5
No Statement ID; In object type: Not Specified 1787
No Statement ID; In object type: PACKAGE 90
No Statement ID; In object type: PROCEDURE 1004
PL/SQL 2296
Query 1847
8549
68 rows selected.
Elapsed: 00:09:49.44
It was just an idea...if anyone has anything to add/improve it feel free to comment.
Thursday, February 01, 2007
Good to be back in the 21st Century...and identifying PEL mismatches
Well, I can honestly say, that moving house over the last few months has been an experience I don't wish to repeat for a good few years - if not decades! I've only just got my broadband enabled for the new place - hence my first post in ages - and that was a miracle given the state of what they very loosely call "customer service" at BT.
A few things I've learned in the process of moving house:
Now, I'm not going to just post a social thing as I quite understand the points being made recently on other blogs about "Sorry I've not posted in ages but...", so I thought I'd say something about problems when you're partition exchange loading. I built this routine a while back to help diagnose why we were unable to exchange partitions during a Partition Exchange Load process - it doesn't check every scenario but does cover a number of common ones such as:
I'm sure it's not perfect but if anyone spots any issues with it then feel free to let me know and I'll check and fix the code.
We've found it quite useful - your mileage, as they say, may vary.
There are a number of links on this blog site that were pointing at my old web hosting site (www.oramoss.demon.co.uk) - that's dead now - I made the mistake of cancelling that and moving to BT - I mentioned that right? Anyway, I've fixed some of the posts and the presentation links - I'll fix anything else as I come across it.
A few things I've learned in the process of moving house:
- Never choose BT to assist you with any communications infrastructure
- - phone line, VOIP, Broadband etc...the list of problems I've encountered whilst getting the simple task of telephone line and broadband installed has been literally staggering.
- Never move in with your in laws when you are "between houses"...unless they are a nice bunch of people in which case for a short period of time (we spent 5 weeks there) it can be quite workable, even if there were some opinions at polar extremes to negotiate around at times - how exactly do you get a pro hunting person and a vegetarian to live happily under one roof?! Thankfully the Christmas spirit got us all through it and we're happily esconced in or new place.
- Don't even think about using BT to get a phone line installed - it will take at least 3 weeks and they will probably lose your order at least twice.
- Never trust Estate Agents - ours misinformed us about one particular aspect of our purchase and very nearly stopped it happening because of it - miscommunication is a highly dangerous thing.
- Never ask BT to install your broadband - I won't go into massive details but basically they lost my first order and then handled my second one in an inept, dismissive and unprofessional manner...and believe me, if you've ever tried to use their IVR system to get through to the right person you'll understand just how frustrating it can be to be passed from pillar to post, explain your situation for five minutes only to be told that you have been talking to the wrong department (Exactly how I managed to get through to a Residential Sales person when I called a Business Sales line is quite beyond me!)
- Take more care when you pack stuff away...otherwise you'll end up turning the new house upside down looking for things you can't find only to find they are tucked away in an incorrectly marked box...it would have helped if we'd been more involved with the packing people from the removals company in hindsight.
Now, I'm not going to just post a social thing as I quite understand the points being made recently on other blogs about "Sorry I've not posted in ages but...", so I thought I'd say something about problems when you're partition exchange loading. I built this routine a while back to help diagnose why we were unable to exchange partitions during a Partition Exchange Load process - it doesn't check every scenario but does cover a number of common ones such as:
- IOT's with different overflow characteristics can't be exchanged
- Hakan factors must be the same
- Columns must be the same - in my view, that includes column names - this script will find issues like this one - even though it won't actually stop the PEL.
- Constraints must be the same
- Indexes must be the same - at least if they do if you are INCLUDING INDEXES in the PEL operation.
I'm sure it's not perfect but if anyone spots any issues with it then feel free to let me know and I'll check and fix the code.
We've found it quite useful - your mileage, as they say, may vary.
There are a number of links on this blog site that were pointing at my old web hosting site (www.oramoss.demon.co.uk) - that's dead now - I made the mistake of cancelling that and moving to BT - I mentioned that right? Anyway, I've fixed some of the posts and the presentation links - I'll fix anything else as I come across it.
Wednesday, September 20, 2006
Using a trigger to grant access on new objects in end user schemae
End users on our warehouse log in using their own, personal oracle account and have the ability to create objects in their own schema - so they can test out analysis ideas without harming anyone else. I had to tune one of the pieces of SQL a user was running the other day and it involved links between some tables the user had created in their own schema and those in the public warehouse schemae and unfortunately, when I went to do get an execution plan using EXPLAIN PLAN it stopped me in my tracks because I couldn't see the objects in the end user schema - I don't have SELECT ANY TABLE privileges in my personal Oracle account.
So, the options I thought of were:
1. Get SELECT ANY TABLE privilege granted to me so that I could do the tuning for this query and, indeed, any query that I will come across. Not something the security people would be happy about which I can understand.
2. Get the end user to grant me SELECT access privilege on the objects - also possible but a bit painful if there are lots of objects and I'd need to go through this hassle every time I had a tuning requirement
3. Get the login details of the user and log in as them - a security minefield and not really practical.
So, no brilliant solutions there, until my teamleader Tank suggested that we create a trigger AFTER CREATE ON SCHEMA that would do the necessary grant(s). Phil from the DBA team had a look at it and found some code from Tom to do this and it works great.
It interested me as a solution because I remember reading recently on Tom's blog how much he hates triggers and that he'd love to have them removed from the database. He did say that "triggers are so abused - and used so inappropriately" but there are some occasions when they are useful and perhaps this is a good example.
So, the options I thought of were:
1. Get SELECT ANY TABLE privilege granted to me so that I could do the tuning for this query and, indeed, any query that I will come across. Not something the security people would be happy about which I can understand.
2. Get the end user to grant me SELECT access privilege on the objects - also possible but a bit painful if there are lots of objects and I'd need to go through this hassle every time I had a tuning requirement
3. Get the login details of the user and log in as them - a security minefield and not really practical.
So, no brilliant solutions there, until my teamleader Tank suggested that we create a trigger AFTER CREATE ON SCHEMA that would do the necessary grant(s). Phil from the DBA team had a look at it and found some code from Tom to do this and it works great.
It interested me as a solution because I remember reading recently on Tom's blog how much he hates triggers and that he'd love to have them removed from the database. He did say that "triggers are so abused - and used so inappropriately" but there are some occasions when they are useful and perhaps this is a good example.
Labels: code
Friday, July 14, 2006
TRUNCATE command marks previously UNUSABLE indexes as USABLE
Yes, I'm back...but on an adhoc basis as I still don't have enough time for this really! Yeah - I know, you're all in the same boat ;-)
I came across this problem on the warehouse the other day and was slightly surprised by what was going on...
We have an OWB mapping which does a TRUNCATE and then INSERT APPEND into a target table. The target table has some bitmap indexes on it. We run a little preprocess before calling the mapping to mark the bitmaps as unusable so it doesn't maintain them and then we (hope to) get better performance during the INSERT APPEND...we then rebuild the indexes afterwards more efficiently than maintaining them on the fly...sounds simple enough...except that performance was dragging...so I had a quick shufty and observed that the indexes were still marked as USABLE. I asked the guy running the stuff to check the run script and he said "No, we marked them as UNUSABLE before we ran the mapping - I'm sure of it".
That's strange I thought...time for some debug...which proved that we were setting the indexes UNUSABLE but somehow by the time the mapping was running they had miraculously been marked as USABLE.
I decided to create a simple test script to run in SQL*Plus which would be doing the same things as the OWB mapping - to try and repeat it at the SQL level and also to prove/disprove whether OWB was having some affect.
It's a useful thing to do this in any case as it's helpful if you're going to end up:
A) Testing it on other platforms/versions
B) Sending it to Oracle Support via an SR.
(Excuse formatting - either Blogger is crap or I don't understand how to get this to look nice -take your pick)
drop table j4134_test_bmi;
create table j4134_test_bmi(col1 number not null
,col2 number not null);
create unique index jtb_pk_i on j4134_test_bmi(col1);
alter table j4134_test_bmi add constraint jtb_pk primary key(col1) using index;
create bitmap index jtb_bmi on j4134_test_bmi(col2);
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
alter index jtb_bmi unusable;
alter table j4134_test_bmi disable constraint jtb_pk;
alter index jtb_pk_i unusable;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
truncate table j4134_test_bmi;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
insert /*+ append */ into j4134_test_bmi(col1,col2)
select rownum
, 100
from (select level l from dual connect by level <>
commit;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
A section of the output of the script follows:
Index IndexName Type STATUS PAR
------------------------------ ------ -------- ---
JTB_PK_I NORMAL UNUSABLE NO
JTB_BMI BITMAP UNUSABLE NO
2 rows selected.
Elapsed: 00:00:00.06
Table truncated.
Elapsed: 00:00:00.11
Index IndexName Type STATUS PAR
------------------------------ ------ -------- ---
JTB_PK_I NORMAL VALID NO
JTB_BMI BITMAP VALID NO
Which shows that immediately after the TRUNCATE command was issued, the previously UNUSABLE indexes were suddenly marked as USABLE!
Why ? It's not documented anywhere that this would happen...so I created an SR with Oracle Support who initially created a Bug for it...but Development came back to say that it is "Expected behaviour". The reasoning Support give for this being expected is that when the Table is truncated, the associated Index segments are also truncated...and if they have no data in them then they can't possibly be UNUSABLE so they get marked as USABLE as a side effect of the process.
It's actually a reasonable assumption for them (Oracle development) to make to be fair - but it would have helped if it was documented - they're going to create a Metalink support note to document the issue.
Thanks to Doug for pushing me to blog this - I'll have to buy you a drink at the OUG Scotland Conference 2006!
I came across this problem on the warehouse the other day and was slightly surprised by what was going on...
We have an OWB mapping which does a TRUNCATE and then INSERT APPEND into a target table. The target table has some bitmap indexes on it. We run a little preprocess before calling the mapping to mark the bitmaps as unusable so it doesn't maintain them and then we (hope to) get better performance during the INSERT APPEND...we then rebuild the indexes afterwards more efficiently than maintaining them on the fly...sounds simple enough...except that performance was dragging...so I had a quick shufty and observed that the indexes were still marked as USABLE. I asked the guy running the stuff to check the run script and he said "No, we marked them as UNUSABLE before we ran the mapping - I'm sure of it".
That's strange I thought...time for some debug...which proved that we were setting the indexes UNUSABLE but somehow by the time the mapping was running they had miraculously been marked as USABLE.
I decided to create a simple test script to run in SQL*Plus which would be doing the same things as the OWB mapping - to try and repeat it at the SQL level and also to prove/disprove whether OWB was having some affect.
It's a useful thing to do this in any case as it's helpful if you're going to end up:
A) Testing it on other platforms/versions
B) Sending it to Oracle Support via an SR.
(Excuse formatting - either Blogger is crap or I don't understand how to get this to look nice -take your pick)
drop table j4134_test_bmi;
create table j4134_test_bmi(col1 number not null
,col2 number not null);
create unique index jtb_pk_i on j4134_test_bmi(col1);
alter table j4134_test_bmi add constraint jtb_pk primary key(col1) using index;
create bitmap index jtb_bmi on j4134_test_bmi(col2);
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
alter index jtb_bmi unusable;
alter table j4134_test_bmi disable constraint jtb_pk;
alter index jtb_pk_i unusable;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
truncate table j4134_test_bmi;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
insert /*+ append */ into j4134_test_bmi(col1,col2)
select rownum
, 100
from (select level l from dual connect by level <>
commit;
select index_name,index_type,status,partitioned from user_indexes where table_name='J4134_TEST_BMI';
A section of the output of the script follows:
Index IndexName Type STATUS PAR
------------------------------ ------ -------- ---
JTB_PK_I NORMAL UNUSABLE NO
JTB_BMI BITMAP UNUSABLE NO
2 rows selected.
Elapsed: 00:00:00.06
Table truncated.
Elapsed: 00:00:00.11
Index IndexName Type STATUS PAR
------------------------------ ------ -------- ---
JTB_PK_I NORMAL VALID NO
JTB_BMI BITMAP VALID NO
Which shows that immediately after the TRUNCATE command was issued, the previously UNUSABLE indexes were suddenly marked as USABLE!
Why ? It's not documented anywhere that this would happen...so I created an SR with Oracle Support who initially created a Bug for it...but Development came back to say that it is "Expected behaviour". The reasoning Support give for this being expected is that when the Table is truncated, the associated Index segments are also truncated...and if they have no data in them then they can't possibly be UNUSABLE so they get marked as USABLE as a side effect of the process.
It's actually a reasonable assumption for them (Oracle development) to make to be fair - but it would have helped if it was documented - they're going to create a Metalink support note to document the issue.
Thanks to Doug for pushing me to blog this - I'll have to buy you a drink at the OUG Scotland Conference 2006!
Friday, February 17, 2006
Obtaining the value of an XML element in a VARCHAR2
We've got some XML data stored in the comments against tables on a system I'm working on - things like "Table Short Name (alias)", "Legacy Key" and "SCD Type", e.g.
select table_name,comments
from dba_tab_comments
where table_name='BDM_T_CUSTOMER';
Table
Name COMMENTS
------------------------------ ---------------------
BDM_T_CUSTOMER <alias>CUS</alias>
We're storing extra metadata in the comments for things we can't easily store anywhere else.
Now how do we get the value of a specific element, e.g. alias ?
I figured this would be easy...but it seems that all the examples I come across are for extracting such elements when they are in an XMLType column not a VARCHAR2.
Looking in Chapter 5 of Building Oracle XML Applications by Steve Muench I found an example showing how to get the value of an XML attribute. Steve gave some sample code to get the attribute value and explained that getting the element value was also possible but a little more tricky...he explained the general method but unfortunately there was no example or code for that so I had to play around with Steve' code for the attribute stuff until I came up with this for an element...
CREATE OR REPLACE PACKAGE xml_utils AS
FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument;
FUNCTION get_element_value(p_xmldoc VARCHAR2
,p_element VARCHAR2) RETURN VARCHAR2;
END xml_utils;
/
CREATE OR REPLACE PACKAGE BODY xml_utils AS
FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument IS
l_return_document xmldom.DOMDocument;
parser xmlparser.Parser;
e_parse_error EXCEPTION;
PRAGMA EXCEPTION_INIT(e_parse_error,-20100);
BEGIN
parser := xmlparser.newParser;
xmlparser.parseBuffer(parser,p_xml);
l_return_document := xmlparser.getDocument(parser);
xmlparser.freeParser(parser);
RETURN l_return_document;
EXCEPTION
WHEN e_parse_error THEN
xmlparser.freeParser(parser);
RETURN l_return_document;
END parse_xml;
FUNCTION get_element_value(p_xmldoc VARCHAR2
,p_element VARCHAR2) RETURN VARCHAR2 IS
xd_xmldoc xmldom.DOMDocument;
l_return_value VARCHAR2(4000);
l_node_list xmldom.DOMNodeList;
l_doc_node xmldom.DOMNode;
l_text_node xmldom.DOMNode;
BEGIN
IF LENGTH(LTRIM(RTRIM(p_xmldoc))) > 0 THEN
xd_xmldoc := parse_xml(p_xmldoc);
IF NOT xmldom.IsNull(xd_xmldoc) THEN
l_node_list := xmldom.getElementsByTagName(xd_xmldoc,p_element);
l_doc_node := xmldom.item(l_node_list, 0);
l_text_node := xmldom.getfirstchild(l_doc_node);
l_return_value := xmldom.getNodeValue(l_text_node);
xmldom.freeDocument(xd_xmldoc);
RETURN l_return_value;
ELSE
xmldom.freeDocument(xd_xmldoc);
RETURN NULL;
END IF;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SQL Error occured:'||SQLERRM);
END get_element_value;
END xml_utils;
/
Now testing for the above XML fragment...
select table_name
, xml_utils.get_element_value(comments,'ALIAS') as table_alias
from all_tab_comments
where table_name='BDM_T_CUSTOMER'
/
Table
Name TABLE_ALIAS
------------------------------ ------------
BDM_T_CUSTOMER CUS
Seems to work reasonably well although I'd be the first to admit I'm an XML novice so I'm sure there are ways to improve it.
Monday, January 09, 2006
Hex to decimal conversion and vice versa
Here’s an interesting quick tip…
I was building something earlier today and wanted to convert hex numbers to decimal and vice versa…figuring it must be easy I had a quick scan of the online manuals but couldn’t find anything of use so I resorted to the internet where I found several references to PL/SQL functions which convert such things – this was probably the best of the bunch thanks Tom.
My colleague Anthony Evans had found a better alternative on his travels in the Oracle world and it was far simpler…
Here’s a Raptor shot of converting decimal to hex using a simple TO_CHAR call with a format mask of enough ‘x’s to cover the number of characters in the decimal number to be converted.
Here’s another Raptor shot of converting hex to decimal using a TO_NUMBER call with the same format mask approach.
Thanks to Anthony for that one.
I was building something earlier today and wanted to convert hex numbers to decimal and vice versa…figuring it must be easy I had a quick scan of the online manuals but couldn’t find anything of use so I resorted to the internet where I found several references to PL/SQL functions which convert such things – this was probably the best of the bunch thanks Tom.
My colleague Anthony Evans had found a better alternative on his travels in the Oracle world and it was far simpler…
Here’s a Raptor shot of converting decimal to hex using a simple TO_CHAR call with a format mask of enough ‘x’s to cover the number of characters in the decimal number to be converted.
Here’s another Raptor shot of converting hex to decimal using a TO_NUMBER call with the same format mask approach.
Thanks to Anthony for that one.
Labels: code
Wednesday, September 21, 2005
10g Recycle Bin
Much like holding the SHIFT key when you delete a file in Windows Explorer, if you use the PURGE keyword when dropping a Table you can ensure that it doesn’t go into the Recycle bin that Oracle 10g maintains.
e.g.
DROP TABLE emp PURGE;
NOTE – You can’t roll back a PURGE statement…so be absolutely sure you want it
To see the contents of the Recycle Bin use:
SELECT * FROM USER_RECYCLEBIN;
To remove the entire contents of the Recycle Bin use:
PURGE RECYCLEBIN;
To retrieve Tables from the Recycle Bin use Flashback Table.
Thanks to Anthony Evans for introducing me to this feature.
Labels: code
Subscribe to Posts [Atom]