Feed aggregator
UK Oracle User Group Election for Directors
Voting is on-line, and instructions will be sent to the main contact for each membership, but if they have a colleague who is better placed to make that judgement, such as an active SIG member, then the vote can be passed to them. As with any election, it's important that each membership takes the opportunity to vote.
By voting, you are ensuring that your community is represented on the Board of Directors and you are helping to shape the future of the UKOUG.
I am standing as a candidate for election this year. Regular readers of my ramblings on this blog and my website will know that I have worked with both PeopleSoft and the Oracle database for many years. Most of my presentations have been given at UKOUG meetings and conferences. I have also been involved with UKOUG as a volunteer for mainy years. I have been chair of the UNIX SIG, and am currently a deputy chair of the PeopleSoft Technology SIG. I have also previously been a director of UKOUG.
I think it is vitally important that the PeopleSoft community in the UK is represented on the board of UKOUG by a director whose day job includes PeopleSoft. That is currently not the case.
I also want to see the user group better serve its traditional core community; Database Server Technology. I believe that the current array of Server Technology SIGs are not well aligned with the needs of the members. They have given rise to duplication and some confusion. I think they need to be reorganised, but without watering down the offering.
If you're a member of the UKOUG then please do vote, or liaise with the main contact for your vote, and hopefully vote for me!©David Kurtz, Go-Faster Consultancy Ltd.
ADF Coding Ninja
Want to know the inner secret of being an ADF Ninja. Awesome video!
Random Things: Volume #13
Admittedly I have gotten into the habit, finally, of traveling, so it's a little strange to be home (never thought I would say that). There is no shortage of respect from me for those who travel all the time. It's a hard life.
OBIEE Performance
Our Usage Tracking reports are a tad slow and I've been looking into the logs trying to decipher them. Unlike the database which has a multitude of resources, OBIEE has hardly any. Christian did point me to this Mark Rittman article, which is good, but not great (not because the author is lacking...there just isn't that much to go on). rnm1978 suggested MOS, but I don't have access right now...besides, it seems to be having problems again anyway.
While I'm on the subject of performance and rnm1978, I should link up to this article which highlights 3 recent posts by Cary Millsap. All 3 are excellent reads and require your immediate attention.
Kate
The reason I am home this week is so that I can go to a Doctor's visit with Kate. We finally found a place where they might be able to help diagnose her. Most of her doctors have been more concerned with keeping her alive (healthy) and haven't worried too much about her developmental delays (still not talking...but she can sign "daddy"). Anyway, the place is called The Tridas Center and we're excited/nervous. Excited about finding her better help and nervous about the possibilities (more specifically, what a diagnosis would mean).
Oracle extends BTM and SOA Mgt through Amberpoint acquisition
Read the following resources for more info
- Benefits of the combination from Richard Sarwal, Sr VP, Product Development, Oracle
- General Presentation on Amberpoint
- FAQ
The AmberPoint solution will provide several critical capabilities requested by customers.
• Application Discovery – Automatically discovers components and interactions and ensures visibility of the entire heterogeneous SOA environment
• Application Performance Management – Tracks end-to-end performance and availability
• Business Transaction Management – Ensures reliability of individual business transactions and tracks the progress in real time to pinpoint any issues
• SOA Governance – Provides closed-loop governance by reporting run-time results to design-time governance solutions
Premier Support for Oracle Application Server 10g ends December 2010
The Java Commercial that Didn't Made it to the Super Bowl
Value is in the Eyes of the Beholder: Timeliness
In my last post, I introduced six Key Value Factors that I consider to be drivers of high service value perception:
- Timeliness
- Efficiency
- Effectiveness
- Responsiveness
- Quality
- Integrity
In this post, I will expand upon the first factor: Timeliness. I will do so by defining what it means and discussing what it entails. While the focus of this post is based on my experience and approach at Remote DBA Experts, these factors are universal and thus applicable in multiple individual and business contexts. Many companies include several of these factors, if not all of them, in performance management systems. In companies that apply the internal customer concept, these factors are used to rate intra-customer relations and performance. Regardless of context, framing your individual or business performance strategy around these factors is smart. Being timely, efficient, effective, responsive, and delivering quality output with high integrity will score big on anyone’s value scorecard. That is why these six factors are so universal and thus powerful value drivers!
One final point before delving into timeliness, keep in mind an important point I am trying to make with the title of this blog post series: Value is in the Eyes of the Beholder. This means that the ultimate meaning of each of the Key Value Factors is determined by the recipient of the output based upon the results of a complex mental process. They consider what they expected to get versus what they perceived getting according to a ranked set of the factors. I said it was complex! Furthermore, conditions and circumstances change and affect priorities and perception accordingly. This is why individuals and service providers must continually improve what they do. We are as good as our last service. What was good last time may be perceived as mediocre next time. You have to keep getting better and better!!!
What is Timeliness?
Simply speaking, timeliness means the state of being timely. However, for the purpose of value perception, being timely is not good enough, you must be timely within a range of expectations and applicable in several contexts.
What does being timely entail?
The quick and smart answer is to ask your customers. However, since this is a post and I am supposed to write something, please indulge me. Let me give you some of my thoughts on timeliness. Nevertheless, no matter what I say, timeliness will always be in the eyes of the beholder. Their value system, conditions and circumstances will dictate the meaning and value they assign to this factor. Generally, timeliness is high on most people’s list of value factors. And most of us have a range of tolerance for this factor. When we are under the gun or in a hurry for whatever reason, our sensitivity to timeliness is heightened. Some of us are in-time and others are through-time types. If we are always late or behind, we are through-time types and are typically less sensitive to lateness than the in-time types who are always punctual. Timeliness involves many contexts:
- Arriving, leaving, starting, and/or ending meetings or events
- Asking timely questions and making timely comments
- Making timely requests
- Delivering in a timely fashion
Timeliness perception also involves three key dimensions:
1. Frequency
2. Magnitude
3. Importance
Frequency involves how often you perform within the expectation ranges for timely delivery in different contexts. If you arrive late once in a blue moon and you have built a good timeliness reputation, it may not be as bad as if you are often late.
Magnitude involves how far outside the tolerance range each performance falls. If you arrive one hour late, it may have a different effect than if you arrive five minutes late. Arriving one hour before may also result in perception issues in some situations and contexts.
Importance involves the impact of your timeliness. Arriving late to meet a CEO may have a bigger impact than being late to other meetings. Delivering a critical document late will have a bigger impact that delivering FYI-type information.
You must take these three dimensions into account when devising your timeliness strategy. High value perception from this and all Value Factors requires careful expectation management. That means you need to carefully uncover and set expectations. Make sure you have a good understanding of all the dimensional aspects for all contexts under which your timeliness value perception will be based. This is a never ending process. Conditions, circumstances, and contexts are always changing. Also, the bar is always rising. Once you perform at a certain level, expectations are reset. Others also influence perception. Competitors, peers, etc. also influence expectations. To reach and maintain a high value perception, you have to remain on your tiptoes ready to move and get better and better, if you want to survive.
The BEST is Yet to Come!
Epi Torres, CEO
Value is in the Eyes of the Beholder: Timeliness is a post from: Remote DBA Experts
UKOUG - Northern Server Tech Day 2010
ADF Code Corner on Twitter !
Just in case …
- you are worried about missing bits of information about Oracle JDeveloper and ADF
- you feel uncomfortable saying “If I had known this before it would have saved me …”
- you don’t have time to frequently browse OTN
- you enjoy technical stuff more than knowing what Paris Hilton did last week
- you want to know more than others do
ADF Code Corner “twitters” interesting updates and information about Oracle JDeveloper, ADF and related topics.
Get More, Learn More: http://twitter.com/fnimphiu
Frank
Eat Food. Not too much. Mostly plants.
I feel very strongly that both Dad and Laurie had health problems contributing to their deaths that came from the foods they ate and the toxic environments in which they, we all, lived (any urban environment saturates us with toxins). Those same foods are likely causing problems for the rest of us, too. I have lately been struck by how ridiculous it is that food has become such a complicated and treacherous affair, that we seem to need nutritionists and scientists and politicians to decide what is good for us to eat. Isn't that strange? Humans have been finding, growing, preparing and eating food for thousands of years - and surviving quite well, thank you - without all this "assistance." And in the US, where the nutritionists reign supreme, we are getting fatter and sicker with each year. Something is so deeply wrong with this picture.
Fortunately, I think the solution to this problem is very simple: "Eat Food. Not too much. Mostly plants."
That is the mantra of a man named Michael Pollan. He has just published a book called Food Rules, which I urge you to purchase and read immediately (only $5 from Amazon!). This small, very accessible book offers simple, practical advice regarding food that can help all of us regain control of our bodies and our health.
Here is what I have decided so far from reading this book: I am going to stop purchasing packed, processed food products (not the same as food) as much as possible. No more Ritz crackers (absolutely yummy with PBJ), no more ramen noodles (I love those with a generous helping of sauteed brussel sprouts).
NO MORE HIGH FRUCTOSE CORN SYRUP at all. I will check every package. I am now convinced more than ever that that stuff is a poison (or at the very least a drug) that operates at a very deep level on our physiology, making us fat and sick.
If you are sick or you have a family member who is sick; if you are affected by any of the four "Western diseases" (obesity, high blood pressure, diabetes or cancer); if you simply don't feel all that great (low energy, acid reflux, etc.) - then please, please, take a serious look at a serious change to your diet. It could make all the difference.
Data Warehouse Fault Tolerance Part 1: Resuming
In the introduction to this series of posts, I spoke briefly about data warehouse fault tolerance and the unique challenges resulting from high data volumes combined the batch load window required to create them. I then defined the goal: a layered approach allowing simple errors to be caught early before they turn in to serious conditions.
Resuming is the ability to continue effortlessly after an error. The important thing is that there should be no aftermath from the error: our process should pause gracefully until the error is corrected. The Oracle Database has offered out of the box functionality for resuming since version 9i in the form of Resumable Space Allocation. Resumable operations are supported for SELECT queries, DML and DDL, and can be enabled at either the system or the session level. To enable at the system level, the RESUMABLE_TIMEOUT database parameter should have a non-zero value.
SQL> alter system set resumable_timeout=3600; System altered. SQL>
To enable resumable operations at the session level, the statement follows this basic syntax, with the TIMEOUT and NAME clauses being optional:
ALTER SESSION ENABLE RESUMABLE <TIMEOUT n> <NAME string>;
The TIMEOUT value is specified in seconds, and if omitted, the default value of 7200 is used, or 2 hours. The NAME clause gives the resumable session a user-friendly name for when we are monitoring for resumable sessions (as we will see later) to see which of our processes is suspended. Enabling resumable operations for the session level requires that the RESUMABLE permission has been granted:
SQL> grant resumable to stewart; Grant succeeded. SQL>
Resumable operations can also be enabled with the Oracle utilities… such as SQL-Loader, Export/Import and Datapump. The command-line parameters RESUMABLE, RESUMABLE_NAME and RESUMABLE_TIMEOUT exist to mimic the functionality mentioned above.
Now for a demonstration. I’ll create a situation that is ripe for a space allocation error: I’ll put an empty copy of the SALES fact table from the SH schema in a tablespace with only 250K of space:
SQL> create tablespace target datafile '/oracle/oradata/bidw1/target01.dbf' size 250K; Tablespace created. SQL> create table target.sales tablespace target as select * from sh.sales where 1=0; Table created. SQL>
Now I’ll load some records into the table, which should cause it to suspend. To prepare my session, I need to enable resumable operations. Since I always instrument my code, I’ll register my process with the database. After that, I have an easy way to guarantee consistency when referring to processes. Now, I can use the registered name for my resumable session as well:
SQL> exec dbms_application_info.set_module('SALES fact load','insert some rows');
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_module VARCHAR2(48) := sys_context('USERENV','MODULE');
3 BEGIN
4 EXECUTE IMMEDIATE
5 'alter session enable resumable timeout 18000 name '''||l_module||'''';
6 END;
7 /
PL/SQL procedure successfully completed.
SQL>
I start loading the records in hopes of a suspended session:
SQL> insert into target.sales select * from sh.sales;
So now, I open up another session, and I start another transaction against the TARGET.SALES table, just to pile on the TARGET tablespace:
SQL> exec dbms_application_info.set_module('SALES fact load2','insert more rows');
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_module VARCHAR2(48) := sys_context('USERENV','MODULE');
3 BEGIN
4 EXECUTE IMMEDIATE
5 'alter session enable resumable timeout 18000 name '''||l_module||'''';
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> insert into target.sales select * from sh.sales;
I’ll have a look in the DBA_RESUMABLE view (there is also a USER_RESUMABLE version) for my suspended sessions. Even though I could get all the following information with a single SQL statement, I broke it up for better visibility on the blog:
SQL> select name, start_time, suspend_time, status from dba_resumable; NAME | START_TIME | SUSPEND_TIME | STATUS ----------------- | -------------------- | -------------------- | ------------ SALES fact load2 | 02/06/10 10:33:33 | 02/06/10 10:33:33 | SUSPENDED SALES fact load | 02/06/10 10:29:03 | 02/06/10 10:29:03 | SUSPENDED 2 rows selected. Elapsed: 00:00:00.07 SQL> select name, sql_text from dba_resumable; NAME | SQL_TEXT ----------------- | ----------------------------------------------- SALES fact load2 | insert into target.sales select * from sh.sales SALES fact load | insert into target.sales select * from sh.sales 2 rows selected. SQL> select name, error_msg from dba_resumable; NAME | ERROR_MSG ----------------- | ------------------------------------------------------------------------ SALES fact load2 | ORA-01653: unable to extend table TARGET.SALES by 8 in tablespace TARGET SALES fact load | ORA-01653: unable to extend table TARGET.SALES by 8 in tablespace TARGET 2 rows selected. SQL>
The Oracle Database also publishes server alerts concerning suspended transactions using the Server-Generated Alerts infrastructure. This infrastructure uses the AWR toolset, the server package DBMS_SERVER_ALERT for getting and setting metric threshholds, and the queue table ALERT_QUE to hold alerts that have been published from AWR. Custom processes could be written to mine ALERT_QUE for these alerts, but the easiest way to configure and view server alerts is using Oracle Enterprise Manager (OEM). On the Alerts section of the main OEM page, we can see three different alerts generated by the Oracle Database:
If we click on the “Session Suspended” link, we can see the multiple alerts generated in this category:
Another alert generated indirectly by the suspended transaction is the “Configuration” class event caused by our session “waiting” to proceed. The Oracle wait event interface can show us information about the suspend waits on the system:
SQL> SELECT event, 2 SUM(time_waited) time_waited, 3 SUM(total_waits) total_waits, 4 AVG(average_wait) average_wait 5 FROM gv$session_event 6 WHERE lower(event) LIKE '%suspend%' 7 GROUP BY event 8 ORDER BY time_waited ASC 9 / EVENT | TIME_WAITED | TOTAL_WAITS | AVERAGE_WAIT ---------------------------------------------- | ----------- | ----------- | ------------ statement suspended, wait error to be cleared | 305373 | 1377 | 221.78 1 row selected. SQL>
To free up the space issue, I’ll enable autoextend on the TARGET tablespace. Then, I’ll take a look and see if anything has changed:
SQL> alter database datafile '/oracle/oradata/bidw1/target01.dbf' 2 autoextend on next 10M maxsize 1000M; Database altered. SQL> select status, resume_time, name from dba_resumable; STATUS | RESUME_TIME | NAME ------------ | -------------------- | ----------------- NORMAL | 02/06/10 10:56:49 | SALES fact load2 NORMAL | 02/06/10 10:56:49 | SALES fact load 2 rows selected. SQL>
The Resumable Space Allocation features includes the AFTER SUSPEND trigger, which allows the specification of a system-wide trigger that will fire whenever a transaction is suspended. The typical use for this functionality is alerting as suspended operations don’t write anything to the alert log.
UPDATE: I made a mistake here… suspended transactions do in fact cause entries in the alert log, and so does the RESUME process detailed below.
There are some features in the DBMS_RESUMABLE package that may make sense when writing an AFTER SUSPEND trigger:
SQL> desc dbms_resumable PROCEDURE ABORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SESSIONID NUMBER IN FUNCTION GET_SESSION_TIMEOUT RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SESSIONID NUMBER IN FUNCTION GET_TIMEOUT RETURNS NUMBER PROCEDURE SET_SESSION_TIMEOUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SESSIONID NUMBER IN TIMEOUT NUMBER IN PROCEDURE SET_TIMEOUT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TIMEOUT NUMBER IN FUNCTION SPACE_ERROR_INFO RETURNS BOOLEAN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- ERROR_TYPE VARCHAR2 OUT OBJECT_TYPE VARCHAR2 OUT OBJECT_OWNER VARCHAR2 OUT TABLE_SPACE_NAME VARCHAR2 OUT OBJECT_NAME VARCHAR2 OUT SUB_OBJECT_NAME VARCHAR2 OUT SQL>
This package adds functionality for writing custom processes in the AFTER SUSPEND trigger. The SPACE_ERROR_INFO function returns specifics about the table and tablespace affected by the space error. A series of checks could be coded enabling specific actions depending on which objects were affected. A suspended process can be ended prematurely with the ABORT procedure, or more time can be added using the SET_TIMEOUT procedure. I actually had one client explain how she had written an AFTER SUSPEND trigger that compiled information about the tablespace affected so that an “ALTER DATABASE… RESIZE…” command could be issued to add more space to the affected datafile. I didn’t have the heart to tell her that she had basically written a feature that already existed in the database: AUTOEXTEND.
So what are the best practices to take away from this? Quite simply… all ETL mappings and flows, as well as database maintenance processes, should use Resumable Space Allocation, preferably using the NAME clause in conjunction with DBMS_APPLICATION_INFO. Setting a RESUMABLE_TIMEOUT value at the system level can be scary, because a single suspended transaction could cause locks that reverberate all the way through the system. But is this really a concern in a BI/DW environment? Are there any processes in our batch load window or with any of our operational maintenance processes that we wouldn’t want to enable for resumable operations, no matter how many processes back up waiting for them to complete? It could spell bad news if we used any kind of synchronous replication technology to move data to the DW instance, but short of that, I can’t think of any. Please let me know if you have alternative viewpoints.
I’ve never found much reason to use the AFTER SUSPEND trigger though. Data warehouses should have production-type monitoring running already, just like other production systems. OEM is more than satisfactory for basic monitoring and alerting, and with the Server-Generated Alerts introduced in 10g, forms a complete product for Oracle environments. But regardless of which monitoring solution is used, it should be able to issue simple queries against the database and alert based on the results of those queries. A select against the DBA_RESUMABLE table provides all the information required to send out an alert, and with features such as AUTOEXTEND, I just can’t see a requirement for the ability to issue procedural code because a transaction is suspended.
UPDATE: as pointed out above, since suspended transactions do in fact show up in the alert log, this is good news for integrating Resumable Space Allocation into an existing environment. Assuming that there’s proper alert log monitoring with paging functionality already in place, implementing resumable operations can simply use that infrastructure already in place.
Keep your eyes open for the next of the “Three R’s” in BI/DW fault tolerance: Restarting.
Oracle acquires Amberpoint
Oracle just announced that it has purchased Amberpoint. If you have ever been interested in Web services management, then you surely know about Amberpoint. The company has long led the pack of best-of-breed vendors for Web services and SOA Management. My history with them goes back to the old days of the OASIS WSDM technical committee, where their engineers brought to the group a unique level of experience and practical-mindedness.
The official page has more details. In short, Amberpoint is going to reinforce Oracle Enterprise Manager, especially in these areas:
- Business Transaction Management
- SOA Management
- Application Performance Management
- SOA Governance (BTW, Oracle Enterprise Repository 11g was released just over a week ago)
I am looking forward to working with my new colleagues from Amberpoint.
Daily Roundup of News, Tips and Tricks for 2010-02-08
- Oracle loses some MySQL mojo – Ken Jacobs Resigns On Friday, Ken Jacobs announced his resignation from Oracle to key members of the MySQL team via e-mail. Jacobs, a 28-year Oracle veteran and one of its first 20 hires, has been Oracle's liaison with the MySQL community for the past several years, ever since Oracle acquired the popular MySQL storage engine, InnoDB.
- SAP CEO Apotheker Resigns in Wake of Poor 2009 Earnings Report SAP CEO Leo Apotheker resigned on Feb. 7 after he and the SAP Supervisory Board "reached a mutual agreement" not to extend Apotheker’s contract as a member of the SAP Executive Board, SAP named Bill McDermott, head of SAP’s field organization and Jim Hagermann Snabe, head of product development as co-CEOs replacing Apotheker as sole CEO.
- Oracle versus IBM and DB2 Oracle’s Larry Ellison threw down the gauntlet recently when he made claims about the superiority of Oracle over DB2. IBM has, of course, responded. Here are the Oracle claims and the IBM rebuttals.
- Securing Java In Oracle [PDF] It is an interesting case of coincidental timing that within a week of the Oracle Sun deal being finalised by the EU1 that the Blackhat conference in DC should publish David Litchfield’s research for NGS Software 2, on how to escalate privilege using the Java functionality built into the Oracle DB 3. David’s research is not patched yet so would normally have only been made privy to the Administrators at Oracle’s SecAlert, but it has been made “Public” so it is now risk mitigation time. Thankfully these Java related Oracle vulnerabilities have been discussed privately for a number of months beforehand, thus giving the Author time to fix them with a provably low risk of affecting other Oracle functionality. In the absence of a patch from Oracle this paper provides information on how to fix the Java related vulnerabilities in both 10g and 11g which were detailed in David’s Blackhat presentation on February 2nd 2010.
- Excel – Charting the Results of Oracle Analytic Functions This is a somewhat complicated example that builds a couple of sample tables, uses a SQL statement with the Oracle analytic function LEAD submitted through ADO in an Excel macro, and then presents the information on an Excel worksheet. When the user clicks one of three buttons on the Excel worksheet, an Excel macro executes that then build charts using disconnected row sources – a disconnected ADO recordset is used to sort the data categories before pushing that data into the charts that are built on the fly.
Related articles:
- Mini Posts Roundup from Twitterland (2007-04-08)
- Mini Posts Roundup from Twitterland (2007-07-24)
- Mini Posts Roundup from Twitterland (2007-07-30)
Index Block Dumps and Index Tree Dumps Part I: (Knock On Wood)
Sample Dataset for Oracle
Interestingly the dataset can be generated directly on the website itself and the maximum rows that will be generated is 5000.
The result output can be on different forms like excel,csv,html,xml and sql.
SQL was the best part , the page gives option to select the database type and then the tablename.
By default it creates the script with the create tablename and then the insert statements.
I hope this will help people who need sample dataset.
Append Values and how not to break the database
Firstly, direct path inserts work by inserting data at the end of existing data, above the high water mark. If you have two conventional inserts throwing data at a table, they can each move the high-water mark as required. Session 1 can move it out by 10 blocks, then session 2 can move it out another 2, then session 1 moves it again. The high-water mark is metadata about the table. It will be stored in one place and there is the potential for contention by multiple sessions wanting to change it at once. For conventional inserts, as soon as the session has adjusted it, it releases its hold and other sessions can do their adjustments. It doesn't need to wait on session commits, and contention generally isn't a problem.
In direct-path inserts, the insert moves the HWM but cannot release its hold on that information. That's because the data it is writing between the old and new HWM is 'dodgy'. It isn't yet committed and it shouldn't be read into the buffer cache. [I suspect it is written to the data file as if it were committed rather than with lock flags and transaction identifiers. That way it avoids the need for a delayed block cleanout when it is subsequently read.] If another insert (or even an update or merge) on the table needs to move the HWM, it has to wait until the direct path transaction is committed or rolled back. That could happen with conventional insert/update/merge, but will ALWAYS happen with another direct path insert.
Try this in one session
drop table TEST_IA purge;
create table TEST_IA (id number, val varchar2(4000));
insert /*+ APPEND */ into TEST_IA
select rownum rn, 'b' val from dual connect by level <= 1000;
And then this in another
insert /*+ APPEND */ into TEST_IA
select rownum rn, 'b' val from dual connect by level <= 1000;
See what I mean ?
If you have 11gR2, insert /*+ APPEND_VALUES */ into TEST_IA values (1,'b'); will have the same effect.
So the first failure pattern I predict will be multiple sessions all trying APPEND_VALUES into the same table and knocking heads together. This would most likely happen when developers have tried to parallelise operations outside the database. Knocking heads together may be the appropriate solution too.
I suspect this will be somewhat mitigated by the second failure pattern. Once you've done a direct path insert, if you try anything else on that table in the same session, you'll get a "ORA-12838: cannot read/modify an object after modifying it in parallel" error message. I'd say the error message was somewhat mis-leading, but a quick google will tell them that the solution is to do a commit after the insert. You can split coders into two groups, the first who understand the concept of a transaction, and the second who don't. I think the number of the latter are increasing. Even if it is okay to commit, you could still have log sync waits.
The final failure pattern I predict will be those who think "Ah, I can do inserts without generating log data. That should be faster.". The problem is that the metadata changes, moving the HWM, are logged and it is only the creation of content data that might be unlogged. In the following script, I compare several approaches to inserting a single record in a loop. When compared with a conventional part insert, it is apparent that a lot more redo is generated for the single row direct path insert with a small record size. When I used a larger record size (padding the value to several thousand characters) the redo size was comparable but there were still fewer redo entries in the conventional path inserts. I do concede logging is not the only performance impact and performance may still improve due to bypassing of the buffer cache, no need to locate free space in the table etc.
drop table TEST_ROW_IAV purge;
drop table TEST_ROW_IA purge;
drop table TEST_ROW_IV purge;
drop table TEST_ROW_I purge;
create table TEST_ROW_IAV (id number, val varchar2(4000));
create table TEST_ROW_IA (id number, val varchar2(4000));
create table TEST_ROW_IV (id number, val varchar2(4000));
create table TEST_ROW_I (id number, val varchar2(4000));
clear screen
declare
cursor c_1 is
select rownum rn, 'b' val from dual connect by level <= 10000;
--
procedure rep_ext (p_seg in varchar2)
is
cursor c_e is
select rpad(segment_name,20) segment_name, tablespace_name,
count(extent_id) cnt, sum(round(bytes/1024)) kb, sum(blocks) blocks
from user_extents
where segment_name = p_seg
group by segment_name, tablespace_name;
begin
for c_out in c_e loop
dbms_output.put_line(to_char(c_out.segment_name)||
' '||c_out.tablespace_name||' '||to_char(c_out.cnt,'9990')||
' '||to_char(c_out.kb,'999,990.00')||' '||to_char(c_out.blocks,'999,990'));
end loop;
end rep_ext;
--
procedure rep_redo (p_text in varchar2)
is
cursor c_r is
select sum(case when name = 'redo entries' then value end) redo_entries,
sum(case when name = 'redo size' then value end) redo_size
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where name in ('redo entries','redo size');
begin
dbms_output.put(rpad(p_text,20)||' at '||to_char(sysdate,'hh24:mi:ss')||' ');
for c_rec in c_r loop
dbms_output.put_line('Entries:'||to_char(c_rec.redo_entries,'9,999,990')||
' Size:'||to_char(c_rec.redo_size,'999,999,990'));
end loop;
end rep_redo;
--
begin
commit;
rep_redo('Start');
for c_rec in c_1 loop
insert /*+ APPEND_VALUES */ into TEST_ROW_IAV values (c_rec.rn, c_rec.val);
commit;
end loop;
rep_redo('After /*+ APPEND_VALUES */');
--
for c_rec in c_1 loop
insert /*+ APPEND */ into TEST_ROW_IA select c_rec.rn, c_rec.val from dual;
commit;
end loop;
rep_redo('After /*+ APPEND */');
--
for c_rec in c_1 loop
insert into TEST_ROW_IV values (c_rec.rn, c_rec.val);
commit;
end loop;
rep_redo('After insert values');
--
for c_rec in c_1 loop
insert into TEST_ROW_I select c_rec.rn, c_rec.val from dual;
commit;
end loop;
rep_redo('After insert select');
--
rep_ext('TEST_ROW_IAV');
rep_ext('TEST_ROW_IA');
rep_ext('TEST_ROW_IV');
rep_ext('TEST_ROW_I');
--
end;
/And my results:
Start at 14:10:59 Entries: 912 Size: 125,628
After /*+ APPEND_VAL at 14:11:02 Entries: 112,547 Size: 15,995,632
After /*+ APPEND */ at 14:11:08 Entries: 224,184 Size: 31,863,240
After insert values at 14:11:09 Entries: 234,409 Size: 36,723,128
After insert select at 14:11:11 Entries: 244,634 Size: 41,422,384
TEST_ROW_IAV USERS 81 81,920.00 10,240
TEST_ROW_IA USERS 81 81,920.00 10,240
TEST_ROW_IV USERS 3 192.00 24
TEST_ROW_I USERS 3 192.00 24
APPEND_VALUES (and indeed APPEND) are not intended for single row inserts, but even small arrays will demonstrate similar problems. Ideally you want each insert to leave full blocks to minimize unused (and probably unusable) space. If you target your array size to a single block, you may find that in practice you get variations between 75% of a block and 1 and a bit blocks (more empty space). However if each insert creates one hundred blocks, you don't mind if the last one is a bit empty. With this in mind you probably want to think of arrays of at least thousands of rows, and maybe in the tens or hundreds of thousands of rows depending on both row size and block size
One more script, looking at how things should be done. Firstly the classical insert with append hint from a select. Secondly, with an array of a hundred thousand rows inserted with the append_values hint. Finally, the '10g' way of a direct path insert from a PL/SQL variable, using SQL types. What you see is that, for this data set, there's not much to choose between the three.
drop table TEST_FORALL_IAV purge;
drop table TEST_IA purge;
drop table TEST_TYPE_IA purge;
drop type type_test_ia;
drop type type_tab_test_ia;
create type type_test_ia is object (id number, val varchar2(4000));
/
create type type_tab_test_ia is table of type_test_ia;
/
create table TEST_FORALL_IAV (id number, val varchar2(4000));
create table TEST_IA (id number, val varchar2(4000));
create table TEST_TYPE_IA (id number, val varchar2(4000));
clear screen
declare
cursor c_1 is
select rownum rn, 'b' val from dual connect by level <= 100000;
TYPE tab_1 is table of c_1%rowtype index by pls_integer;
t_1 tab_1;
t_tab type_tab_test_ia;
--
procedure rep_ext (p_seg in varchar2)
is
cursor c_e is
select rpad(segment_name,20) segment_name, tablespace_name,
count(extent_id) cnt, sum(round(bytes/1024)) kb, sum(blocks) blocks
from user_extents
where segment_name = p_seg
group by segment_name, tablespace_name;
begin
for c_out in c_e loop
dbms_output.put_line(to_char(c_out.segment_name)||
' '||c_out.tablespace_name||' '||to_char(c_out.cnt,'9990')||
' '||to_char(c_out.kb,'999,990.00')||
' '||to_char(c_out.blocks,'999,990'));
end loop;
end rep_ext;
--
procedure rep_redo (p_text in varchar2)
is
cursor c_r is
select sum(case when name = 'redo entries' then value end) redo_entries,
sum(case when name = 'redo size' then value end) redo_size
from v$mystat s join v$statname n on n.statistic# = s.statistic#
where name in ('redo entries','redo size');
begin
dbms_output.put(rpad(p_text,20)||' at '||to_char(sysdate,'hh24:mi:ss')||' ');
for c_rec in c_r loop
dbms_output.put_line('Entries:'||to_char(c_rec.redo_entries,'9,999,990')||
' Size:'||to_char(c_rec.redo_size,'999,999,990'));
end loop;
end rep_redo;
--
begin
commit;
rep_redo('Start');
insert /*+ APPEND */ into TEST_IA
select rownum rn, 'b' val from dual connect by level <= 100000;
rep_redo('After Append dual');
--
open c_1;
fetch c_1 bulk collect into t_1;
close c_1;
forall i in 1..t_1.count
insert /*+ APPEND_VALUES */ into TEST_FORALL_IAV values t_1(i);
rep_redo('After Append values');
--
select cast(collect(type_test_ia(rn,val)) as type_tab_test_ia)
into t_tab
from (select rownum rn, 'b' val from dual connect by level <= 100000);
--
insert /*+ APPEND */ into TEST_TYPE_IA
select * from table(t_tab);
rep_redo('After Append type');
--
rep_ext ('TEST_IA');
rep_ext ('TEST_FORALL_IAV');
rep_ext ('TEST_TYPE_IA');
end loop;
/
And again, my results
Start at 14:33:04 Entries: 580 Size: 112,392
After Append dual at 14:33:04 Entries: 850 Size: 153,468
After Append values at 14:33:04 Entries: 1,116 Size: 193,836
After Append type at 14:33:05 Entries: 1,383 Size: 234,432
TEST_IA USERS 17 2,048.00 256
TEST_FORALL_IAV USERS 17 2,048.00 256
TEST_TYPE_IA USERS 17 2,048.00 256
Oracle Forms Look and Feel version 1.5
See a live demo here.
Get it now
Custom Authentication Scheme In APEX
I have been playing around with Apex 4.0 on http://tryapexnow.com. When playing around with some ideas I have for application I decided to use the logging mechanism I created a while ago, to see what’s going on in the custom code.
Since this is a hosted environment it is not too easy to check the records in the log. There were a couple of queries needed to check the log. I usually run these from my IDE, but the is not possible now. They can be easily run using the SQL Workshop, but I have APEX at my disposal. So why not create a simple application to display the information in the log table. But if I create an application and start using it, it becomes publicly available and everybody can see what’s being logged (including the values of parameters). I need some sort of authentication here.
Luckily APEX provides the possibility to add your own authentication schemes. Since I am running this on a hosted environment I cannot use ‘Database’ or ‘Database Account’. So I need to create my own, custom authentication. This is described in detail in the book Pro Oracle Application Express especially in chapter 3 . So I decided to follow the steps here.
The steps include examples of creating a user table and a custom authorization function in a package. It also includes some pointers on how to handle passwords. I did everything exactly as described in the book, changed the authorization scheme to the newly created one and tried to log in to my application. But it failed. Even with the correct credentials. What is going on here? Must be something I did wrong. Let’s try to create the schema again (dispose of the ‘old’ one) and now follow all the steps in the wizard.
In the book they said to create the scheme from here (with minimal information) and then change the properties later. But I want to go through the entire wizard so I decided to click the ‘Next>’ button.
The 6th page of the wizard is where the real action takes place. I want to use my own custom function to authenticate the session.
The function should return a boolean and take username and password as input.
The function is declared in the package as follows:
FUNCTION authenticate(p_username IN VARCHAR2
,p_password IN VARCHAR2) RETURN BOOLEAN;Now it’s time to test the new scheme. Change the current authorization scheme to the one just created.
Then try to run the application again.
Login with some faulty credentials and notice that I am not authenticated.
When trying to login with correct credentials I am allowed access to the application.
Now what is the difference between this authorization scheme and the one I created earlier. I think it has something to do with the session not valid part of the authentication. When I created it by filling in just the name and the description of the scheme it used the –BUILTIN- ‘Not Valid URL’. In my new scheme it says here to use the login page instead. It seems that did the trick. Checking the Application Express scheme shows me roughly the same settings. I think I nailed this one. It took me a lot of time and I want to thank my friends Jorrit and Alex for helping me where they could (although they had not needed this yet) especially where to look or provide me with more documentation.