ORAchk includes EXAchk’s functionality and replaces the popular RACcheck tool, extending the coverage based on prioritization of top issues reported by users, to proactively scan for known problems within:
- E-Business Suite Financials Accounts Payables
- Oracle Database
- Sun Systems
- Proactively scans for the most impactful known problems across your entire Engineered System as well as various layers of your stack
- Simplifies and streamlines how to investigate and analyze which known issues present a risk to you
- Lightweight tool runs within your environment; no data will be sent to Oracle
- High level reports show your system health risks with the ability to drill down into specific problems and understand their resolutions
- Can be configured to send email notifications when it detects problems
- Collection Manager, a companion Application Express web app, provides a single dashboard view of collections across your entire enterprise
ORAchk will expand in the future with more high impact checks in existing and additional product areas. If you have particular checks or product areas you would like to see covered, please post suggestions in the ORAchk community thread accessed from the support tab on the document below.
For more details about ORAchk see Document 1268927.1
The 20 Million Minds Foundation is starting a new blog, and for topics of mutual interest, Michael and I will be posting articles several times a month on their site and occasionally cross-posting here. The first article of mine went up last week.
Two Approaches to Watch in Remedial Education Innovation
It is no secret that the current approach to remedial (or developmental) courses does not work. According to a study by Complete College America (see original for footnotes documenting sources):
“The numbers tell a dispiriting story. Half of all undergraduates and 70 percent of community college students take at least one remedial course. Too many of these students never overcome being placed into a remedial course. Only about a quarter of community college students who take a remedial course graduate within eight years. In fact, most students who are referred to remedial education do not even complete the remedial sequence: One study found 46 percent of students completed the sequence in reading and only 33 percent completed it in math.”
Following the maxim that “if it doesn’t work, try something else”, two community colleges on opposite coasts – Miami-Dade College in Florida and Cerritos College in California – are trying to change the game. What can we learn from these two divergent examples?
Read the whole article here.
The post New post at 20MM: Two Approaches to Watch in Remedial Education Innovation appeared first on e-Literate.
One of the people attending my seminar in Munich last week has emailed me some details about a nasty little surprise you may get if you’re thinking about TSPITR (tablespace point in time recovery), and happen to have a few materialized views in your database.
You might have wanted to use the “duplicate target database” from rman with the “skip tablespace” option to create a duplicate database, excluding tablespaces that you’re not interested in, if you needed to recover a particular tablespace (or set of tablespaces) to a specific point in time. (Once you’ve done that you can extract the data that you want and then discard the duplicate database). But there’s a requirement for recovery is that the set of tablespaces should be “self-contained”; but what does “self-contained” mean ?
Amongst other things it means that none of the tablespace you skip should contain materialized views. Note carefully, that’s not saying the schema you’re interested in shouldn’t have created any materialized view, or the tablespace you want to duplicate contains a base table for a materialized view in another table; it really does mean – if you’ve got ANY materialized view ANYWHERE in the database, you have to duplicate those tablespaces as part of the process.
Here’s the restriction note from MoS (not the exclamation mark):
You MUST NOT exclude
- SYS-owned objects
- or tablespaces with rollback segments,
- nor tablespaces containing “MATERIALIZED VIEWS”!
Implementation suggestion – always put materialized views (and materialized view logs, and indexes on materialized views) in their own tablespace(s), just in case one day you want to do a tablespace point in time recovery and find you’ve got a few bits of materialized views scattered all around your database.Footnote:
When I first heard this comment I didn’t believe it (but take a look at MoS document ID: 1287276.1 if you’re finding it hard to believe). Naturally my engine of doom went into overdrive immediately after I was convinced and made me wonder what would happen in a 12c container database with several plugged databases; obviously the existence of a materialized view in one plugged database shouldn’t have any impact on TSPITR for another pluggable database – but I wouldn’t mind if someone tested the hypothesis and reported back what they found.
It’s just occurred to me that this type of TSPITR problem simply won’t exist if you’re using Delphix as part of your working environment.
Recently I have be working with Oracle Enterprise Data Quality (EDQ) combined with Oracle Data Integrator (ODI), we will tell you more about exactly what we have been doing in another blog, but for now I would like to revisit our earlier EDQ blogs. I am going to keep to the batch ETL paradigm and not really look at the Real-Time aspects of Oracle EDQ.
My colleague, Koen Vantomme, blogged about EDQ back in August 2012, and Mark Rittman wrote about EDQ and ODI in December 2012. New versions of ODI (12c) and EDQ (126.96.36.199) have been released and this has introduced a few changes in the way we use the two products together. Before discussing that I will give a brief overview of what EDQ can do and three typical use cases we may wish to use in a data integration / data warehousing project.
The product name says it all. “Oracle Enterprise Data Quality” is Oracle’s Data Quality toolset for Enterprises. It provides a comprehensive set of functionality for data stewardship, data profiling and data correction (both realtime by use of web services and, more traditionally, in batch operations). Additional, added-cost, components can be added to provide functionality such as address verification, CRM address services and watch-list screening.
Out-of-the-box we get a web front end giving access to various applets.
As developers, we will probably use the Director applet most often. Director is a web-launch Oracle ADF applet and is the IDE for creating and managing EDQ processes and jobs. One or more processes can be chained together in an EDQ job and this job can be executed from the Director GUI, the command line or, as I will cover later, from inside an ODI package (and hence from an ODI scenario or loadplan).
In the data warehousing world success is usually measured by whether we give the data users what they want. This usually boils down to being ’timely’ (are the data recent enough for their purposes?) and ‘correct’. Timely is about ETL process and load design. Correct, however, is mainly about data quality (although ETL logic flaws also play a part). Typically we see three kinds of issue:
- Incomplete: attributes or values that are missing. For example an order in process will not have a shipping date until the order is shipped; and in the case of a website data feed non-mandatory fields on a web form may be left blank.
- Incorrect: I have seen a e-retailer with a disproportionate number of customers from Albania, where Albania just happened to be the first country listed on the web-form country selector on the customer registration page.
- Inconsistent: the same data being presented in different ways so that we don’t always spot multiple records referring to the same entity. This can be simply an inconsistent use of letter case in a description or it can be more complex such as the use of synonyms and variant spellings; for example my father was William Scott, but he could appear as ‘Will Scott’, ‘Bill Scott’, ‘Wm Scott’, ‘W Scott’ or ‘W G Scott’ . However we can’t just blindly convert all cases of Billy to William as Billy could be a legitimate data value; we need to build more complex data rules about this. Sometimes inconsistent data is more physical in nature, for example one of our data sources may use a different character set and we see unexpected character conversions in our data.
Fortunately, EDQ gives us some great tools that we as ETL developers can use to investigate and handle these kinds of issue. Just dropping a data source onto the EDQ Director canvas prompts us if we want to auto profile the data; click ‘yes’ and we automatically generate a process that we can use to inspect our data for a host of anomalies. We click on the green arrow above the canvas to execute the profiling and then click on any of the pre-built Profiler processor icons to see the results. A nice touch is the ability to drill down on the blue coloured results to see the underlying data.
Having profiled our data we are in a position to decide whether our ETL processes need to be written to handle anticipated data anomalies (of course we should always code defensively) or that we go back to the data source owners for improved data feeds. If we adopt the “handle anomalies” route we can use the EDQ tool set to help with this. Notice I say handle and not “fix” I am a strong believer that a data warehouse is the single source of truth and not the “place where truth is created” If we receive data that needs fixing we should separate it from the data suitable for loading and ideally return it to the data owner for fixing. We may do some automatic fix ups but only if the rules can be firmly agreed with the business and that we always maintain the original source data along side so we can go back if we find our ‘rule’ was incorrect.
In the EDQ sample data (available on OTN for download) we find a customer CSV file where amongst other things the LastName column is highly inconsistent; some names in lowercase, some in uppercase, some in proper case (first character of words is uppercase). We can build out a simple process that does a proper case conversion on all of the data. We can be even smarter, and get it to ignore embedded uppercase so we don’t mess up names like McDonnell and DiCampo. In the real world we would possibly add an exceptions list to handle prefix words such as van, de and von which are traditionally in lower case. In my data correction task I have added another processor to add the current date to outward-bound data so we have a trail of when data was processed. The data written out also has both the original last name and the amended version.
Sometimes (I’d say “mostly!”) fixing data in the ETL is the wrong thing to do. In cases like these we can build a process using the Data Match processors and based on defined data criteria elect to process data, reject data or flag for further investigation. We can use these match operators as a trigger for instigating Oracle EDQ case management workflows or just use the inbuilt Director functionality to split good and rejected data and raise an email alert using the email processor. Using my customer data as an example we have several customers that are likely to be duplicates, with either variant versions of company name or address. We can build match rules and assign weights to the rule components so that certain conditions trigger matching, non-matching or review. Oracle EDQ supports some very sophisticated match algorithms, such as “within a given number of character substitutions” (aka typo matching) and “matching a percentage of words in a string”.
We can review our results from the data match tool, in my case I had one record that my rules considered to be an exact match and 52 records that were flagged for human review a likely matches. Note we can highlight the discrepancies
As I mentioned above we can call these EDQ jobs from the command line or as part of ODI packages. The simplest way is probably using the EDQ tool that has been available since ODI 188.8.131.52.
However, calling the EDQ command line interface from ODI is also feasible for earlier ODI versions or for more complex use cases that may not be supported by the tool (for example the use of execution parameters although this restriction can be worked around in other ways). To use the ODI tool we need to know some information about the EDQ instance, particularly the host, EDQ JMX server port (this is not the same as the browser url port) the user name and password, EDQ project and job names, and the EDQ domain. There are a few gotchas that are not clear from the documentation, but setting thus up is relatively easy. The biggest of these gotchas is that the EDQ domain name (that is the base mbean to which you connect) has changed in the current EDQ release from the default name provided by ODI, it is now edq. We can verify this base mbean name by using Java Console on the server and drilling into the EDQ server process. The JMX server port number has also changed (8090 is now the default on the EDQ server). We need to explicitly set these values on the ODI EDQ tool General Tab (the ODI defaults no longer work). There are two further restrictions we need be aware of, but these are probably unchanged from earlier releases; the execution user for EDQ must be a full EDQ Administration user and also that user must not be currently logged into EDQ on another connection. For my testing I created a Weblogic EDQ-USER user and an EDQ_ODI_GROUP group in the WebLogic Security Realm and mapped the WebLogic group to the EDQ Administrators role on the edq Administration web page. That is I have pushed user administration down to the WebLogic server.
Oracle EDQ installs on to an application server such as Apache Tomcat, IBM WebSphere and of course Oracle WebLogic. For this blog post I have downloaded the latest available versions of EDQ and ODI. It should be noted that WebLogic 12 is not supported by the current Oracle EDQ release. For my Application Server I am using a basic WebLogic 10.3.6 install. Download and run the EDQ Repository creation assistant to create required meta data and schemas on the repository database and then run the EDQ provided Oracle Universal Installer. When the base install is complete run the WebLogic configuration utility and extend the domain to include Oracle EDQ. Startup the services from the WebLogic console. If the EDQ url does not bring up the home page it may be that you need to make a fix to the node manager configuration (see MOS note 1617687.1) If you are using the latest Java 1.7 you will also probably need to change some Java security setting to allow the Java Web Start applets to launch. Do to this connect to the Java Control Panel and add the EDQ server and port to the exception site list.
In a follow-up post we will take a look at some of the Director functionality in detail.
In the last article (I hope) of this series I want to look at what happens when I change the parallel distribution method on the query that I’ve been using in my previous demonstrations. This was a query first introduced in a note on Bloom Filters (opens in a separate window) where I show two versions of a four-table parallel hash join, one using using the broadcast distribution mechanism throughout, the other using the hash distribution method. For reference you can review the table definitions and plan (with execution stats) for the serial join in this posting (also opens in a separate window).
To change distribution methods from the broadcast example to the hash example I’ve simply changed a few hints in my code. Here are two sets of hints showing what I’ve done; the first is a repeat from the third article showing the broadcast example, the second shows the small change needed to get the hash example:
/*+ leading(t4 t1 t2 t3) full(t4) parallel(t4, 2) use_hash(t1) swap_join_inputs(t1) pq_distribute(t1 none broadcast) full(t1) parallel(t1, 2) use_hash(t2) swap_join_inputs(t2) pq_distribute(t2 none broadcast) full(t2) parallel(t2, 2) use_hash(t3) swap_join_inputs(t3) pq_distribute(t3 none broadcast) full(t3) parallel(t3, 2) monitor */ /*+ leading(t4 t1 t2 t3) full(t4) parallel(t4, 2) use_hash(t1) swap_join_inputs(t1) pq_distribute(t1 hash hash) full(t1) parallel(t1, 2) use_hash(t2) swap_join_inputs(t2) pq_distribute(t2 hash hash) full(t2) parallel(t2, 2) use_hash(t3) swap_join_inputs(t3) pq_distribute(t3 hash hash) full(t3) parallel(t3, 2) monitor */
Because of the combination of leading() hint with the use_hash() and swap_join_inputs() hints the plan WILL still build in-memory hash tables from t1, t2, and t3 and it WILL still probe each hash table in turn with the rows (that survive) from t4; but the order of activity in the hash distribution plan will be dramatically different from the order in the serial and parallel broadcast plans where the order in which Oracle actually built the in-memory hash tables t3, t2, t1.
Here – with a little cosmetic adjustment – is the parallel execution plan using hash distribution on 184.108.40.206, captured from memory with rowsource execution stats enabled (the 12c plan would report PX SEND HYBRID HASH” operators with an associated “STATISTICS COLLECTOR” operator showing that adaptive execution was a possibility – with three points at which the plan might switch from hash distribtion to broadcast):
-------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 437 (100)| | | | | 1 |00:00:00.08 | 16 | 5 | | 1 | SORT AGGREGATE | | 1 | | | | | | 1 |00:00:00.08 | 16 | 5 | | 2 | PX COORDINATOR | | 1 | | | | | | 2 |00:00:00.08 | 16 | 5 | | 3 | PX SEND QC (RANDOM) | :TQ10006 | 0 | | | Q1,06 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 | | 4 | SORT AGGREGATE | | 2 | | | Q1,06 | PCWP | | 2 |00:00:00.01 | 0 | 0 | |* 5 | HASH JOIN | | 2 | 437 (3)| 00:00:03 | Q1,06 | PCWP | | 27 |00:00:00.01 | 0 | 0 | | 6 | JOIN FILTER CREATE | :BF0000 | 2 | 2 (0)| 00:00:01 | Q1,06 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 7 | PX RECEIVE | | 2 | 2 (0)| 00:00:01 | Q1,06 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 8 | PX SEND HASH | :TQ10004 | 0 | 2 (0)| 00:00:01 | Q1,04 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 9 | PX BLOCK ITERATOR | | 2 | 2 (0)| 00:00:01 | Q1,04 | PCWC | | 3 |00:00:00.01 | 4 | 2 | |* 10 | TABLE ACCESS FULL | T3 | 2 | 2 (0)| 00:00:01 | Q1,04 | PCWP | | 3 |00:00:00.01 | 4 | 2 | | 11 | PX RECEIVE | | 2 | 435 (3)| 00:00:03 | Q1,06 | PCWP | | 27 |00:00:00.01 | 0 | 0 | | 12 | PX SEND HASH | :TQ10005 | 0 | 435 (3)| 00:00:03 | Q1,05 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 13 | JOIN FILTER USE | :BF0000 | 2 | 435 (3)| 00:00:03 | Q1,05 | PCWP | | 27 |00:00:00.01 | 0 | 0 | |* 14 | HASH JOIN BUFFERED | | 2 | 435 (3)| 00:00:03 | Q1,05 | PCWP | | 630 |00:00:00.01 | 0 | 0 | | 15 | JOIN FILTER CREATE | :BF0001 | 2 | 2 (0)| 00:00:01 | Q1,05 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 16 | PX RECEIVE | | 2 | 2 (0)| 00:00:01 | Q1,05 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 17 | PX SEND HASH | :TQ10002 | 0 | 2 (0)| 00:00:01 | Q1,02 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 18 | PX BLOCK ITERATOR | | 2 | 2 (0)| 00:00:01 | Q1,02 | PCWC | | 3 |00:00:00.01 | 4 | 2 | |* 19 | TABLE ACCESS FULL | T2 | 2 | 2 (0)| 00:00:01 | Q1,02 | PCWP | | 3 |00:00:00.01 | 4 | 2 | | 20 | PX RECEIVE | | 2 | 432 (3)| 00:00:03 | Q1,05 | PCWP | | 632 |00:00:00.01 | 0 | 0 | | 21 | PX SEND HASH | :TQ10003 | 0 | 432 (3)| 00:00:03 | Q1,03 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 22 | JOIN FILTER USE | :BF0001 | 2 | 432 (3)| 00:00:03 | Q1,03 | PCWP | | 632 |00:00:00.09 | 0 | 0 | |* 23 | HASH JOIN BUFFERED | | 2 | 432 (3)| 00:00:03 | Q1,03 | PCWP | | 14700 |00:00:00.09 | 0 | 0 | | 24 | JOIN FILTER CREATE | :BF0002 | 2 | 2 (0)| 00:00:01 | Q1,03 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 25 | PX RECEIVE | | 2 | 2 (0)| 00:00:01 | Q1,03 | PCWP | | 3 |00:00:00.01 | 0 | 0 | | 26 | PX SEND HASH | :TQ10000 | 0 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 27 | PX BLOCK ITERATOR | | 2 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | 3 |00:00:00.01 | 4 | 2 | |* 28 | TABLE ACCESS FULL| T1 | 2 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | 3 |00:00:00.01 | 4 | 2 | | 29 | PX RECEIVE | | 2 | 427 (2)| 00:00:03 | Q1,03 | PCWP | | 14700 |00:00:00.08 | 0 | 0 | | 30 | PX SEND HASH | :TQ10001 | 0 | 427 (2)| 00:00:03 | Q1,01 | P->P | HASH | 0 |00:00:00.01 | 0 | 0 | | 31 | JOIN FILTER USE | :BF0002 | 2 | 427 (2)| 00:00:03 | Q1,01 | PCWP | | 14700 |00:00:00.05 | 6044 | 6018 | | 32 | PX BLOCK ITERATOR | | 2 | 427 (2)| 00:00:03 | Q1,01 | PCWC | | 14700 |00:00:00.04 | 6044 | 6018 | |* 33 | TABLE ACCESS FULL| T4 | 26 | 427 (2)| 00:00:03 | Q1,01 | PCWP | | 14700 |00:00:00.04 | 6044 | 6018 | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("T3"."ID"="T4"."ID3") 10 - access(:Z>=:Z AND :Z<=:Z) filter((TO_NUMBER("T3"."SMALL_VC")=1 OR TO_NUMBER("T3"."SMALL_VC")=2 OR TO_NUMBER("T3"."SMALL_VC")=3)) 14 - access("T2"."ID"="T4"."ID2") 19 - access(:Z>=:Z AND :Z<=:Z) filter((TO_NUMBER("T2"."SMALL_VC")=1 OR TO_NUMBER("T2"."SMALL_VC")=2 OR TO_NUMBER("T2"."SMALL_VC")=3)) 23 - access("T1"."ID"="T4"."ID1") 28 - access(:Z>=:Z AND :Z<=:Z) filter((TO_NUMBER("T1"."SMALL_VC")=1 OR TO_NUMBER("T1"."SMALL_VC")=2 OR TO_NUMBER("T1"."SMALL_VC")=3)) 33 - access(:Z>=:Z AND :Z<=:Z) filter(SYS_OP_BLOOM_FILTER(:BF0000,"T4"."ID1"))
There are a couple of significant points that are very easy to point out in this plan. First, we have a number of lines which are “BLOOM FILTER CREATE/USE” lines that did not appear in the broadcast plan; second that we can only see one sys_op_bloom_filter() in the predicate section rather than three (don’t worry, it’s – partly – a reporting defect); finally we have seven virtual tables (table queues :TQnnnnn) in this plan rather than four, and those virtual tables seems to be scattered rather more randomly around the plan.
To make it easier to understand what’s happened with a parallel execution plan, I usually also dump out the contents of v$pq_tqstat after running the query – so here’s the result after running the above:
DFO_NUMBER TQ_ID SERVER_TYPE INSTANCE PROCESS NUM_ROWS BYTES WAITS TIMEOUTS AVG_LATENCY ---------- ---------- --------------- -------- --------------- ---------- ---------- ---------- ---------- ----------- 1 0 Producer 1 P002 3 69 1 0 0 1 P003 0 48 0 0 0 Consumer 1 P000 2 62 30 16 0 1 P001 1 55 26 14 0 1 Producer 1 P002 1476 35520 2 1 0 1 P003 13224 317880 1 0 0 Consumer 1 P000 9800 235584 20 14 0 1 P001 4900 117816 20 14 0 2 Producer 1 P000 3 69 0 0 0 1 P001 0 48 0 0 0 Consumer 1 P002 2 62 33 19 0 1 P003 1 55 32 19 0 3 Producer 1 P000 422 9754 0 0 0 1 P001 210 4878 0 0 0 Consumer 1 P002 420 9708 33 19 0 1 P003 212 4924 32 18 0 4 Producer 1 P002 3 69 1 0 0 1 P003 0 48 0 0 0 Consumer 1 P000 2 62 42 20 0 1 P001 1 55 39 15 0 5 Producer 1 P002 18 444 0 0 0 1 P003 9 246 0 0 0 Consumer 1 P000 18 444 41 20 0 1 P001 9 246 39 16 0 6 Producer 1 P000 1 60 0 0 0 1 P001 1 60 0 0 0 Consumer 1 QC 2 120 1 0 0
So let’s work our way through the execution plan – if you want to put the plan and my comments side by side, this link will re-open this article in a second window.
Given the set of hints, and the intent I expressed at the start of the series, we hope to see Oracle building an in-memory hash table from each of t1, t2 and t3 in that order, following which it will scan t4, probe t3, t2, and t1 in that order, and then aggregate the result. Let’s check that using the parallel plan rule of “follow the table queues”.
Table queue 0 covers lines 26 – 28, we scan t1 and distribute it by hash. We can see from the A-Rows column we found 3 rows and distributed them and if we look at the output from v$pq_tqstat we find it matches - slaves 2 and 3 produced 3 rows, slaves 0 and 1 consumed 3 rows. Table queue 1 covers lines 30 – 33, we scan t4 and distribute it by hash. We can see from the A-rows column we found 14,700 rows and distributed them, and again we can see the match in v$pq_tqstat – slaves 2 and 3 produced 14,700 rows and distributed them to slaves 0 and 1. But there’s an oddity here, and things start to get messy: from the predicate section we can see that we applied a Bloom filter on the ID1 column on the data we got from the tablescan, and the plan itself shows a Bloom filter (:BF0002) being used at line 31, but that Bloom filter is created at line 24 of the plan and line 24 has been associated with table queue 3. Now I know (because I constructed the data) that a perfect filter has been created and used at that point because 14,700 rows is exactly the volume of data that should eventually join between tables t1 and t4. It’s reasonable, I think, to say that the boundary between table queues 0 and 3 is a little blurred at lines 24/25 – the slaves that are going to populate table queue 3 are the ones that created the Bloom filter, but they’re not going to populate table queue 3 just yet.
So let’s move on to table queue 2. This covers lines 17-19 (looking at the TQ column) except I’m going to assume the same blurring of boundaries I claimed for table queue 0 – I’m going to say that table queue 2 expands into lines 15-19 (bringing in the PX RECEIVE and JOIN FILTER CREATE (:BF001). So our next step is to scan and distribute table t2, and build a Bloom filter from it. Again we look at v$pq_tqstat and see that in this case it’s slaves 0 and 1 which scan the table and distribute 3 rows to slaves 2 and 3, and we assume that slaves 2 and 3 will send a Bloom filter back to salves 0 and 1.
Now we can move on to table queue 3: line 21 writes to table queue 3 by using lines 22, 23, 24, 25, and 29 according to the TQ column (but thanks to the blurring of the boundaries lines 24 and 25 were used “prematurely” to create the Bloom filter :BF002 describing the results from table t1). So lines 24/25 read table queue 0 and built an in-memory hash table, simultaneously creating a Bloom filter and sending it back to slaves 2 and 3; then line 23 did a HASH JOIN BUFFERED, which means it copied the incoming data from table queue 1 (slaves 2 and 3, table t4) into a buffer and then used that buffer to probe its in-memory hash table and do the join; then line 22 applied a Bloom filter (:BF001) to the result of the hash join although the filter won’t appear in the predicate section until version 220.127.116.11. Notice that line 23 (the join) produced 14,700 rows, demonstrating that our previous filter was a perfect filter, and then line 22 filtered out all but 632 rows. (Again, because I constructed the data I can tell you that the second Bloom filter has also worked with 100% accuracy – although v$pq_tqstat seems to show an extra 2 rows which I can’t account for and which don’t appear in the trace file).
So here’s another problem – we’re using another Bloom filter that we haven’t yet (apparently) created unless we accept my assumption of the blurring of the boundary at lines 15 and 16, where the plan shows two lines associated with table queue 5 even though I need them to be associated with table queue 2 so that they can produce the Bloom filter needed by table queue 3. Again, by the way, we can do the cross-check with the TQ_ID 3 of v$pq_tqstat abnd see slaves 0 and 1 produced 632 rows and sent them to slaves 2 and 3.
Before continuing, lets rewrite the action so far as a series of bullet points:
- Slaves 2,3 scan t1 and distribute to slaves 0,1
- Slaves 0,1 build an in-memory hash table and a Bloom filter (:BF002) for t1, and send the filter to slaves 2,3
- Slaves 2,3 scan t4, use the Bloom filter (:BF002) to eliminate data (luckily 100% perfectly) and distribute the remaining rows to slaves 0,1
- Slaves 0,1 buffer the incoming data
- Slaves 0,1 scan t2 and distribute to slaves 2,3
- Slaves 2,3 build an in-memory hash table for the results from t2 and a Bloom filter (:BF001) for t2, and send the filter to slaves 0,1
- Slaves 0,1 use the buffered t4 to probe the in-memory hash of t1 to do the join, testing join results against the Bloom filter (:BF001) for t2, and distributing the surviving rows to slaves 2,3
The pattern of the last four steps will then repeat for the next hash join – and for longer joins the patten will repeat up to, but excluding, the last join.
- Slaves 2,3 buffer the incoming data (the result of joining t4, t1 and t2) – the buffering is implied by line 4 (which is labelled as an input for table queue 5)
- Slaves 2,3 scan t3 and distribute to slaves 0,1 (reading lines 8,9,10 of the plan), cross-checking with TQ_ID 4 of v$pq_tqstat
- Slaves 0,1 build an in-memory hash table for the results from t3 and a Bloom filter (:BF000) for t3, and send the filter to slaves 2,3 (“sharing” lines 6 and 7 from table queue 6)
- Slaves 2,3 use the buffered results from (t4/t1) to probe the in-memory hash to t2 to do the join, testing join results against the Bloom filter (:BF000) for t3, and distributing the surviving rows to slaves 0,1.
Again, we can check row counts – the hash join buffered at line 14 shows 630 rows coming from the hash join (i.e. the previous Bloom filter was perfect), and line 13 shows 27 rows surviving the final Bloom filter. Again my knowledge of the data tells me that the Bloom filter was a perfect filter. Cross-checking to TQ_ID 5 of v$pq_tqstat we see slaves 2 and 3 producing 27 rows and slaves 0 and 1 consuming them.
So at this point slaves 0,1 have an in-memory hash table for t3, and are receiving the filtered results of the join between t4, t1, and t2; the slaves have to join and aggregate the the two data sets before forwarding a result to the query co-ordinator. Since the aggregation is a blocking operation (i.e. slaves 0,1 can send data to the co-ordinator until they’ve emptied virtual table 5 and aggregated all the incoming data) they don’t have to use the “hash join buffered” mechanism, so the pattern for the final part of the plan changes.
Lines 5, 6, 7, 11 show us the hash join (not buffered) with its two inputs (although lines 6 and 7 have, of course, been mentioned once already as the source of the Bloom filter used at line 13). Then line 4 shows slaves 0 and 1 aggregating their results; line 3 shows them forwarding the results to the query co-ordinator, line 2 shows the query co-ordinator receiving the results and line 1 shows it aggregating across the slave results ready to send to the end-user.
It’s a bit complicated, and the constant jumping back and fore through the execution plan lines (especially for the “shared” usage of the Bloom filter creation lines) makes it quite hard to follow, so I’ve drawn up a Powerpoint slide to capture the overall picture:
I’ve put the slaves 0 and 1 at the top of the picture, slaves 2 and 3 at the bottom, with the query co-ordinator in the middle at the right hand side. Time reads across the page from left to right, and that gives you the order in which data moves through table queues (and back, for Bloom filters). The annotation give you some idea of what data is moving. Note that I’ve used B1 to refer to the Bloom filter on table T1 (and ignored the numbering on Oracle’s :BFnnn entries). I’ve used red to highlight the data sets that are buffered, and put in curved arrows to show where the buffered data is subsequently brought back into play. I did try to add the various plan line numbers to the picture, but the volume of text made the whole thing incomprehensible – so I’ve left it with what I think is the best compromise of textual information and graphical flow.
I’ll just leave one final warning – if you want to reproduce my results, you’ll have to be careful about versions. I stuck with 18.104.22.168 as that’s the latest version of the most popular general release. There are differences in 22.214.171.124, and there are differences again if you try to emulate 126.96.36.199 by setting the optimizer_features_enable in 188.8.131.52 back to the earlier version.
Although pool safety is a serious issue that should not be discounted, many pool owners are surprised to learn just how easy it is to accomplish. To get you started: If you are a pool Resep Brownies Kukus owner, never let small children or inexperienced swimmers, even adults, swim in your pool alone. Always closely monitor those inside your pool. Children who do not know how to swim should be no more than an arms length away from an adult.
As it was previously stated, children who do not know how to swim should be within arms length of an adult. This is important to remember, as many parents have gained a false sense of confidence and security with floatation devices, such as arm floaties or swimsuits with built in floaters. Even children outfitted with lifejackets approved by the United States Coast Guard should never be left unattended.
Before a swimming pool is filled with water it should be surrounded by a strong and sturdy fence. All pool owners should consult with local officials, as law may require a fence. Pool fences are important because many children drown in accidents when the pool was otherwise not in use.
A cordless telephone should always be kept near the pool when it is in use. Cordless phones are recommended, as it enables adults to answer the phone without leaving the pool area. Remember, all it takes is a few seconds for a child to slip underwater. A close by telephone can also result in a quicker call for emergency services.
If you are a parent, there is a good chance that your children will want to invite friends over for a swim or even a full-fledge pool party. If so, it is vital that you speak to the parents of all guests. Make sure that all children in your pool have permission to be there and that they can swim.
If you have an inground pool, it is recommended that you section off the deep end of the pool from the shallow end. You may be provided with a small buoyed rope to do so. Inexperienced swimmers need to be able to see where the shallow waters end.
If your swimming pool is an aboveground pool, your ladder should be removable. You will want to remove it from the side of the pool after Brownies Kukus each swim. This is particularly important if there are small children in your home or in your neighborhood. With no ladder, most small children will not be able to go for an unauthorized and unmonitored swim.
The above mentioned summer pool safety tips can help to reduce the chance of a pool accident, but you always need to be prepared for one. That is why all swimming pool owners should know or learn CPR. For a small and reasonable fee, you may be able to save the life of a loved one.
I recently came across this project – http://nodyn.io/ which provides the ability to run node.js on a JVM – got me thinking – “Hey wait a sec ADF-Mobile has a JVM” true not latest and greatest but I’m sure its only a matter of time before Oracle upgrade ADF-Mobiles JVM.
So your probably all reading this going.. and.. .. why would I want to write JS on the device and not through the webview whats the point??
It’s not for the novelty of writing server/device side JS (although I personally really like this idea) or even importing prebuilt node.js packages onto the device; its about allowing node.js apps to work directly with existing Java apps or even apps that may also be running on the JVM.
Now, before you all get your hopes up if you write node.js packages you need to be aware that it isn’t a direct port of node.js not everything works; but I feel it is a step forward in the right direction with the backing of Redhat.
This might be a pipe dream of mine at the moment but the hope is in the next year or so we may be able to use node.js on ADF-Mobile.. It may even be possible to run it now although doubtful with the current JVM release running on ADF-Mobile.
Due to the fact that multiple organizations are shifting to different platforms, database administration support companies are being contacted by private enterprises and public agencies to conduct extensive reviews of their data hosting systems. Whether on-premise or in the cloud, amassing large amounts of data through PCs, interfaces, mobile devices and other machines has become the norm.
Keeping a lid on it
Harnessing digital information typically entails companies new to the process biting off more than they can chew. For this reason, database experts are consulted by these businesses in order to make sense of it all. Enterprise Apps Today reported that these professionals typically start by evaluating their clients' master data. Reviewing all data sources and how it's organized – all with best practices in mind – can help save enterprises from overlooking costly errors, such as misalignment of data for assets and customers.
"The key is to examine company data from an unbiased perspective with the most critical business processes in mind," noted the source.
Where database support services really benefit companies is their ability to identify data inaccuracies. Because the environment in which digital information is held spans entire departments, mistakes may accidentally be introduced by different machines.
Assessing mobile devices
Smartphones and tablets are becoming increasingly involved with database contribution, but monitoring and protecting these avenues has been an undervalued priority. Absolute Software recently surveyed 1,000 employees who use mobile phones for work. When questioned about penalties if they lose company data, 25.2 percent of respondents claimed that they shouldn't receive punishment because it wasn't their responsibility to practice safe security measures.
This apparent lack of interest would explain why many enterprises hire remote database services to monitor connections between machines and the the network. The survey also noted an overwhelming amount of apathy. Apparently, 75 percent of corporate employees felt that they should be reprimanded for the transgression, but the extent of their punishment should be mild.
"Although employees may feel the data on their device is only worth $500, when you consider that most data breach penalties can range into the millions of dollars, there's an obvious disconnect between an employee's perception and the risk to the organization," the source reported.
The lack of education in regard to the true value of company data is putting corporations severely at risk. Having a remote DBA service conduct surveillance is a good first step, but enlightening employees as to the dangers of negligent data access is absolutely necessary.
- The people here are almost desperate to provide their students with a 21st century experience. They feel the need to have their student systems match the excellence found in their academics and research.
- Many of the schools struggle to implement well. And it's not that they don't understand the technology. The common theme seems to be one of organizational change management.
- While total cost of ownership is obviously a concern, system quality seems to be an even great concern with these folks...a positive in my book.
- Prior point notwithstanding, a huge issue for many international schools seems to be infrastructure reliability. Things many of us take for granted (dependable electricity, broadband internet connections, physical hardware security) are huge issues in some parts of the world.
When performance is measured, performance improves. When performance is measured and reported, the rate of performance accelerates.
(LDS Conference Report, October 1970, p107)
Like everything in life, a SOA Suite installation that is monitored and tracked has a much better chance of performing well than one that is not measured. With that in mind I came up with tool to allow the measurement of the impact of configuration changes on database usage in SOA Suite. This tool can be used to assess the impact of different configurations on both database growth and database performance, helping to decide which optimizations offer real benefit to the composite under test.Basic Approach
The basic approach of the tool is to take a snapshot of the number of rows in the SOA tables before executing a composite. The composite is then executed. After the composite has completed another snapshot is taken of the SOA tables. This is illustrated in the diagram below:
An example of the data collected by the tool is shown below:Test Name Total Tables Changed Total Rows Added Notes AsyncTest1 13 15 Async Interaction with simple SOA composite, one retry to send response. AsyncTest2 12 13 Async interaction with simple SOA composite, no retries on sending response. AsyncTest3 12 13 Async interaction with simple SOA composite, no callback address provided. OneWayTest1 12 13 One-Way interaction with simple SOA composite. SyncTest1 7 7 Sync interaction with simple SOA composite.
Note that the first three columns are provided by the tool, the fourth column is just an aide-memoir to identify what the test name actually did. The tool also allows us to drill into the data to get a better look at what is actually changing as shown in the table below:Test Name Table Name Rows Added AsyncTest1 AUDIT_COUNTER 1 AsyncTest1 AUDIT_DETAILS 1 AsyncTest1 AUDIT_TRAIL 2 AsyncTest1 COMPOSITE_INSTANCE 1 AsyncTest1 CUBE_INSTANCE 1 AsyncTest1 CUBE_SCOPE 1 AsyncTest1 DLV_MESSAGE 1 AsyncTest1 DOCUMENT_CI_REF 1 AsyncTest1 DOCUMENT_DLV_MSG_REF 1 AsyncTest1 HEADERS_PROPERTIES 1 AsyncTest1 INSTANCE_PAYLOAD 1 AsyncTest1 WORK_ITEM 1 AsyncTest1 XML_DOCUMENT 2
Here we have drilled into the test case with the retry of the callback to see what tables are actually being written to.
Finally we can compare two tests to see difference in the number of rows written and the tables updated as shown below:Test Name Base Test Name Table Name Row Difference AsyncTest1 AsyncTest2 AUDIT_TRAIL 1
Here are the additional tables referenced by this testTest Name Base Test Name Additional Table Name Rows Added AsyncTest1 AsyncTest2 WORK_ROWS 1 How it Works
I created a database stored procedure, soa_snapshot.take_soa_snaphot(test_name, phase). that queries all the SOA tables and records the number of rows in each table. By running the stored procedure before and after the execution of a composite we can capture the number of rows in the SOA database before and after a composite executes. I then created a view that shows the difference in the number of rows before and after composite execution. This view has a number of sub-views that allow us to query specific items. The schema is shown below:
The different tables and views are:
- Used to track number of rows in SOA schema, each test case has two or more phases. Usually phase 1 is before execution and phase 2 is after execution.
- This only used by the stored procedure and the views.
- Used to track changes in number of rows in SOA database between phases of a test case. This is a view on CHANGE_TABLE. All other views are based off this view.
- Provides number of rows changed in each table.
- Provides a summary of total rows and tables changed.
- Provides a summary of differences in rows updated between test cases
- Provides a summary of the extra tables and rows used by a test case.
- This view makes use of a session context, soa_ctx, which holds the test case name and the baseline test case name. This context is initialized by calling the stored procedure soa_ctx_pkg.set(testCase, baseTestCase).
I created a web service wrapper to the take_soa_snapshot procedure so that I could use SoapUI to perform the tests.Sample Output How many rows and tables did a particular test use?
Here we can see how many rows in how many tables changed as a result of running a test:
-- Display the total number of rows and tables changed for each test
select * from summary_delta_view
order by test_name;
TEST_NAME TOTALDELTAROWS TOTALDELTASIZE TOTALTABLES
-------------------- -------------- -------------- -----------
AsyncTest1 15 0 13
AsyncTest1noCCIS 15 0 13
AsyncTest1off 8 0 8
AsyncTest1prod 13 0 12
AsyncTest2 13 0 12
AsyncTest2noCCIS 13 0 12
AsyncTest2off 7 0 7
AsyncTest2prod 11 0 11
AsyncTest3 13 0 12
AsyncTest3noCCIS 13 65536 12
AsyncTest3off 7 0 7
AsyncTest3prod 11 0 11
OneWayTest1 13 0 12
OneWayTest1noCCI 13 65536 12
OneWayTest1off 7 0 7
OneWayTest1prod 11 0 11
SyncTest1 7 0 7
SyncTest1noCCIS 7 0 7
SyncTest1off 2 0 2
SyncTest1prod 5 0 5
20 rows selectedWhich tables grew during a test?
Here for a given test we can see which tables had rows inserted.
-- Display the tables which grew and show the number of rows they grew by
select * from simple_delta_view
order by table_name;
TEST_NAME TABLE_NAME DELTAROWS DELTASIZE
-------------------- ------------------------------ ---------- ----------
AsyncTest1 AUDIT_COUNTER 1 0
AsyncTest1 AUDIT_DETAILS 1 0
AsyncTest1 AUDIT_TRAIL 2 0
AsyncTest1 COMPOSITE_INSTANCE 1 0
AsyncTest1 CUBE_INSTANCE 1 0
AsyncTest1 CUBE_SCOPE 1 0
AsyncTest1 DLV_MESSAGE 1 0
AsyncTest1 DOCUMENT_CI_REF 1 0
AsyncTest1 DOCUMENT_DLV_MSG_REF 1 0
AsyncTest1 HEADERS_PROPERTIES 1 0
AsyncTest1 INSTANCE_PAYLOAD 1 0
AsyncTest1 WORK_ITEM 1 0
AsyncTest1 XML_DOCUMENT 2 0
13 rows selected
Here we can see the differences in rows for two tests.
-- Return difference in rows updated (test1)
select * from different_rows_view
where test1='AsyncTest1' and test2='AsyncTest2';
TEST1 TEST2 TABLE_NAME DELTA
-------------------- -------------------- ------------------------------ ----------
AsyncTest1 AsyncTest2 AUDIT_TRAIL 1
Here we can see tables that were used by one test but not by the other test.
-- Register base test case for use in extra_tables_view
-- First parameter (test1) is test we expect to have extra rows/tables
begin soa_ctx_pkg.set('AsyncTest1', 'AsyncTest2'); end;
anonymous block completed
-- Return additional tables used by test1
column TEST2 FORMAT A20
select * from extra_tables_view;
TEST1 TEST2 TABLE_NAME DELTAROWS
-------------------- -------------------- ------------------------------ ----------
AsyncTest1 AsyncTest2 WORK_ITEM 1
I used the tool to find out the following. All tests were run using SOA Suite 184.108.40.206.
The following is based on a very simple composite as shown below:
Each BPEL process is basically the same as the one shown below:
Impact of Fault Policy Retry Being Executed Once Setting Total Rows Written Total Tables Updated No Retry 13 12 One Retry 15 13
When a fault policy causes a retry then the following additional database rows are written:Table Name Number of Rows AUDIT_TRAIL 1 WORK_ITEM 1 Impact of Setting Audit Level = Development Instead of Production Setting Total Rows Written Total Tables Updated Development 13 12 Production 11 11
When the audit level is set at development instead of production then the following additional database rows are written:Table Name Number of Rows AUDIT_TRAIL 1 WORK_ITEM 1 Impact of Setting Audit Level = Production Instead of Off Setting Total Rows Written Total Tables Updated Production 11 11 Off 7 7
When the audit level is set at production rather than off then the following additional database rows are written:Table Name Number of Rows AUDIT_COUNTER 1 AUDIT_DETAILS 1 AUDIT_TRAIL 1 COMPOSITE_INSTANCE 1 Impact of Setting Capture Composite Instance State Setting Total Rows Written Total Tables Updated On 13 12 Off 13 12
When capture composite instance state is on rather than off then no additional database rows are written, note that there are other activities that occur when composite instance state is captured:Impact of Setting oneWayDeliveryPolicy = async.cache or sync Setting Total Rows Written Total Tables Updated async.persist 13 12 async.cache 7 7 sync 7 7
When choosing async.persist (the default) instead of sync or async.cache then the following additional database rows are written:Table Name Number of Rows AUDIT_DETAILS 1 DLV_MESSAGE 1 DOCUMENT_CI_REF 1 DOCUMENT_DLV_MSG_REF 1 HEADERS_PROPERTIES 1 XML_DOCUMENT 1
As you would expect the sync mode behaves just as a regular synchronous (request/reply) interaction and creates the same number of rows in the database. The async.cache also creates the same number of rows as a sync interaction because it stores state in memory and provides no restart guarantee.Caveats & Warnings
The results above are based on a trivial test case. The numbers will be different for bigger and more complex composites. However by taking snapshots of different configurations you can produce the numbers that apply to your composites.
The capture procedure supports multiple steps in a test case, but the views only support two snapshots per test case.Code Download
The sample project I used us available here.
The scripts used to create the user (createUser.sql), create the schema (createSchema.sql) and sample queries (TableCardinality.sql) are available here.
The Web Service wrapper to the capture state stored procedure is available here.
The sample SoapUI project that I used to take a snapshot, perform the test and take a second snapshot is available here.
This is a content summary only. Visit my website http://onlineAppsDBA.com for full links, other content, and more!
Just a quick reminder that the second Oracle Midlands event will be taking place on 25th March 2014.
At this event you will get a chance to see Graham Wood (my Dad) speaking about “Advanced ASH Architecture and Usage” and Nikolay Manchev speaking about “Using Clusterware 11g to protect single-instance databases”.
These events live or die based on your support. Please spread the word and come along. See you there!
Tim…Oracle Midlands : Event #2 was first posted on March 9, 2014 at 4:34 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.
Once upon a time, working on an IT project in a large organisation meant reams of documentation, tangles of red-tape, and then burning the candle at both ends to get everything finished on time.
Then, someone discovered this Agile thing that dispensed with all of that.
The upshot ? Well, the documentation has been reduced ( although the red-tape is still problematic).
Many large organisations now adopt an approach that is almost – but not completely – unlike SCRUM.
It is at the business end of such a project I now find myself…burning the candle at both ends.
To pile on the excitement, Milton Keynes’ own Welsh enclave has become increasingly voiciferous in recent days.
The only SCRUM Deb is interested in is that of the Welsh Rugby Team grinding remorselessly over English bodies come Sunday.
She keeps giving me oh-so-subtle reminders of the result of last year’s game, such as when picking lottery numbers :
“Hmmm, three is supposed to be lucky…but not if your English. How about THIRTY !”
“But you’re married to an Englishman”, I pointed out during one of her more nationalistic moments.
“Which makes him half Welsh !”, came the retort.
At this point, I decided that discretion was the better part of logic and let the matter drop.
As a result of all this frenzied activity and feverish atmosphere, sometimes I’ve not been quite at the top of my game.
One particularly embarassing mishap occured late one evening and involved PL/SQL Tables – or Associative Arrays as they’re called these days – and the dreaded ORA-06531: Reference to uninitialized collection.
This particular post therefore, is mainly a reminder to myself of how to initialize and (just as importantly) clear down a Collection to prevent mysterious missing or, just as problematic, additional, records ( as well as less mysterious runtime errors).The Insanity Check
At some point, it would be nice to go away somewhere for a weekend of culture (which may or may not include extensive sampling of local alcoholic beverages).
I’ve got a table containing a list of possible destinations…and whether or not they are located in a Rugby playing nation :
CREATE TABLE getaway_plans( city_name VARCHAR2(50), country_name VARCHAR2(50), play_rugby VARCHAR2(1) ) / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('VIENNA', 'AUSTRIA', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('ROME', 'ITALY', 'Y') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('AUCKLAND', 'NEW ZEALAND', 'Y') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('CARDIFF', 'WALES', 'Y') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('BERLIN', 'GERMANY', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('WARSAW', 'POLAND', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('HAVANA', 'CUBA', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('COPENHAGEN', 'DENMARK', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('BRUGES', 'BELGIUM', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('AMSTERDAM', 'NETHERLANDS', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('BASEL', 'SWITZERLAND', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('PERTH', 'AUSTRALIA', 'N') / INSERT INTO getaway_plans( city_name, country_name, play_rugby) VALUES('NEW YORK', 'USA', 'Y') / COMMIT;
OK – so a “weekend away” would probably require me finally ironing out the bugs in my lottery number picker program ( it keeps selecting the wrong six numbers).
Using those whizzy associative arrays, I shall now attempt to come up with a shortlist…of places where Rugby is not widely popular.
I will concede that this could be done with far less hassle using a simple select statement. That would, however, defeat the object of the exercise…
CREATE TABLE shortlist_cities ( city_name VARCHAR2(50), country_name VARCHAR2(50) ) /
This should do the trick…
DECLARE CURSOR c_getaway IS SELECT city_name, country_name, play_rugby FROM getaway_plans; -- associatve array based on the cursor TYPE typ_all_cities is table of c_getaway%ROWTYPE; -- and another one based on the target table. TYPE typ_shortlist is table of shortlist_cities%ROWTYPE; tbl_all_cities typ_all_cities; tbl_short typ_shortlist; l_idx PLS_INTEGER := 0; BEGIN OPEN c_getaway; LOOP FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4; EXIT WHEN tbl_all_cities.COUNT = 0; DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration'); FOR i IN 1..tbl_all_cities.COUNT LOOP IF tbl_all_cities(i).play_rugby = 'N' THEN l_idx := l_idx + 1; tbl_short(l_idx).city_name := tbl_all_cities(i).city_name; tbl_short(l_idx).country_name := tbl_all_cities(i).country_name; END IF; END LOOP; FORALL j IN 1..tbl_short.COUNT INSERT INTO shortlist_cities VALUES tbl_short(j); l_idx := 0; END LOOP; CLOSE c_getaway; END; /
…Kick it off and…
DECLARE * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 23 SQL>
Yep, our first stumbling block. the TBL_SHORT collection has not been initialized.
Now at this point you may be a bit stumped as there is no obvious way to initialize this collection.
The other collection in the program – TBL_ALL_CITIES seems to have been automatically initialised by the FETCH.
We’ll come back to this in a bit.
In the meantime though, just how do you initialize an Associative Array without pointing a cursor at it ?
The refreshingly simple answer is : add an index clause to the type declaration …
set serveroutput on DECLARE TYPE typ_funny_shaped_balls IS TABLE OF shortlist_cities%ROWTYPE INDEX BY PLS_INTEGER; tbl_rugby typ_funny_shaped_balls; BEGIN DBMS_OUTPUT.PUT_LINE('There are '||tbl_rugby.COUNT||' elements in the collection.'); END; /
Run this and we get…
There are 0 elements in the collection. PL/SQL procedure successfully completed. SQL>
No nasty error when referencing the collection.
So, applying this to our code, we should be good to go, right ?
DECLARE CURSOR c_getaway IS SELECT city_name, country_name, play_rugby FROM getaway_plans; TYPE typ_all_cities is table of c_getaway%ROWTYPE; TYPE typ_shortlist is table of shortlist_cities%ROWTYPE INDEX BY PLS_INTEGER; tbl_all_cities typ_all_cities; tbl_short typ_shortlist; l_idx PLS_INTEGER := 0; BEGIN OPEN c_getaway; LOOP FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4; EXIT WHEN tbl_all_cities.COUNT = 0; DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration'); FOR i IN 1..tbl_all_cities.COUNT LOOP IF tbl_all_cities(i).play_rugby = 'N' THEN l_idx := l_idx + 1; tbl_short(l_idx).city_name := tbl_all_cities(i).city_name; tbl_short(l_idx).country_name := tbl_all_cities(i).country_name; END IF; END LOOP; FORALL j IN 1..tbl_short.COUNT INSERT INTO shortlist_cities VALUES tbl_short(j); l_idx := 0; END LOOP; CLOSE c_getaway; END; /
Run this and all seems well…
SQL> @madness2 Bulk Collect Loop Iteration Bulk Collect Loop Iteration Bulk Collect Loop Iteration Bulk Collect Loop Iteration PL/SQL procedure successfully completed. SQL>
…until you look at the resulting data…
SQL> select city_name from shortlist_cities order by 1; CITY_NAME -------------------------------------------------- AMSTERDAM AMSTERDAM BASEL BASEL BERLIN BRUGES BRUGES COPENHAGEN HAVANA PERTH PERTH CITY_NAME -------------------------------------------------- VIENNA WARSAW 13 rows selected.
Hmmm…not quite what we had in mind.
The problem here is that neither array is being cleared down between loop iterations.
The results are…interesting.
Let’s try and address that…
DECLARE CURSOR c_getaway IS SELECT city_name, country_name, play_rugby FROM getaway_plans; TYPE typ_all_cities is table of c_getaway%ROWTYPE; TYPE typ_shortlist is table of shortlist_cities%ROWTYPE INDEX BY PLS_INTEGER; tbl_all_cities typ_all_cities; tbl_short typ_shortlist; tbl_ac_empty typ_all_cities; tbl_short_empty typ_shortlist; l_idx PLS_INTEGER := 0; BEGIN OPEN c_getaway; LOOP FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4; EXIT WHEN tbl_all_cities.COUNT = 0; DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration'); FOR i IN 1..tbl_all_cities.COUNT LOOP IF tbl_all_cities(i).play_rugby = 'N' THEN l_idx := l_idx + 1; tbl_short(l_idx).city_name := tbl_all_cities(i).city_name; tbl_short(l_idx).country_name := tbl_all_cities(i).country_name; END IF; END LOOP; FORALL j IN 1..tbl_short.COUNT INSERT INTO shortlist_cities VALUES tbl_short(j); l_idx := 0; -- clear down the arrays between each iteration. tbl_all_cities := tbl_ac_empty; tbl_short := tbl_short_empty; END LOOP; CLOSE c_getaway; END; /
Here, we’ve simply assigned the arrays to an empty array of the same type at the bottom of the loop.
This seems to solve the problem…
SQL> select city_name from shortlist_cities order by 1; CITY_NAME -------------------------------------------------- AMSTERDAM BASEL BERLIN BRUGES COPENHAGEN HAVANA PERTH VIENNA WARSAW 9 rows selected. SQL>
At this point, you might think that there’s still a potential issue lurking in this code. OK, we’ve initialised the tbl_short array by including the INDEX BY clause in the type definition.
We haven’t done this for the tbl_all_cities array. OK, at the moment this array is intialised by the fetch from the cursor. What would happen if the cursor fetch didn’t return any rows …
DECLARE CURSOR c_getaway IS SELECT city_name, country_name, play_rugby FROM getaway_plans WHERE play_rugby = 'X'; TYPE typ_all_cities is table of c_getaway%ROWTYPE; TYPE typ_shortlist is table of shortlist_cities%ROWTYPE INDEX BY PLS_INTEGER; tbl_all_cities typ_all_cities; tbl_short typ_shortlist; tbl_ac_empty typ_all_cities; tbl_short_empty typ_shortlist; l_idx PLS_INTEGER := 0; BEGIN OPEN c_getaway; LOOP FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4; EXIT WHEN tbl_all_cities.COUNT = 0; DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration'); FOR i IN 1..tbl_all_cities.COUNT LOOP IF tbl_all_cities(i).play_rugby = 'N' THEN l_idx := l_idx + 1; tbl_short(l_idx).city_name := tbl_all_cities(i).city_name; tbl_short(l_idx).country_name := tbl_all_cities(i).country_name; END IF; END LOOP; FORALL j IN 1..tbl_short.COUNT INSERT INTO shortlist_cities VALUES tbl_short(j); l_idx := 0; tbl_all_cities := tbl_ac_empty; tbl_short := tbl_short_empty; END LOOP; CLOSE c_getaway; END; /
…er, it still works. Yep, as long as the Associative Array is the target of a fetch before you reference it, it will be initialised.
One final point to note. Whilst it’s common practice to cleardown your Associative Arrays by simply assigning them the value of an empty array of the same type, there is another way…
set serveroutput on DECLARE CURSOR c_getaway IS SELECT city_name, country_name, play_rugby FROM getaway_plans; TYPE typ_all_cities is table of c_getaway%ROWTYPE; TYPE typ_shortlist is table of shortlist_cities%ROWTYPE INDEX BY PLS_INTEGER; tbl_all_cities typ_all_cities; tbl_short typ_shortlist; l_idx PLS_INTEGER := 0; BEGIN OPEN c_getaway; LOOP FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4; EXIT WHEN tbl_all_cities.COUNT = 0; DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration'); FOR i IN 1..tbl_all_cities.COUNT LOOP IF tbl_all_cities(i).play_rugby = 'N' THEN l_idx := l_idx + 1; tbl_short(l_idx).city_name := tbl_all_cities(i).city_name; tbl_short(l_idx).country_name := tbl_all_cities(i).country_name; END IF; END LOOP; FORALL j IN 1..tbl_short.COUNT INSERT INTO shortlist_cities VALUES tbl_short(j); l_idx := 0; -- clear down the arrays between each iteration. DBMS_OUTPUT.PUT_LINE('Currently '||tbl_all_cities.COUNT||' records in tbl_all_cities.'); DBMS_OUTPUT.PUT_LINE('Currently '||tbl_short.COUNT||' records in tbl_short.'); tbl_all_cities.DELETE; tbl_short.DELETE; DBMS_OUTPUT.PUT_LINE('Now tbl_all_cities contains '||tbl_all_cities.COUNT||' records.'); DBMS_OUTPUT.PUT_LINE('...and tbl_short contains '||tbl_short.COUNT||' records.'); END LOOP; CLOSE c_getaway; END; /
Simply using the DELETE method will cleardown our arrays…
Bulk Collect Loop Iteration Currently 4 records in tbl_all_cities. Currently 1 records in tbl_short. Now tbl_all_cities contains 0 records. ...and tbl_short contains 0 records. Bulk Collect Loop Iteration Currently 4 records in tbl_all_cities. Currently 4 records in tbl_short. Now tbl_all_cities contains 0 records. ...and tbl_short contains 0 records. Bulk Collect Loop Iteration Currently 4 records in tbl_all_cities. Currently 4 records in tbl_short. Now tbl_all_cities contains 0 records. ...and tbl_short contains 0 records. Bulk Collect Loop Iteration Currently 1 records in tbl_all_cities. Currently 0 records in tbl_short. Now tbl_all_cities contains 0 records. ...and tbl_short contains 0 records. PL/SQL procedure successfully completed. SQL>
Right, six-pack of Brain’s bitter.Check. Inflatable Daffodil. Check. Deb humming Land of My Fathers, Check. The game must be about to start…
Filed under: Oracle, PL/SQL Tagged: associative array DELETE method, bulk collect, forall, index by, initializing an associative array, ora-06531, pl/sql associative arrays, PL/SQL Table
The fun thing about doing the hard yards to code up the algorithm is that you get a deeper level of understanding about what's going on. Take these lines:
v_in_str := utl_raw.cast_to_raw(i_username||':'||i_realm||':'||i_password); v_ha1 := lower(DBMS_OBFUSCATION_TOOLKIT.md5(input => v_in_raw));
Every time we build the "who we are" component for this site, we start with exactly the same hash made up of the username, realm (site) and password. This is a batch routine, which means somewhere we would store the username and password for the site - whether that is a parameter in a scheduling tool, coded into a shell script or OS file, or somewhere in the database. If you've got the security option for Oracle, you can use the Wallet, with its own security layers.
But digest authentication gives us another option. Since we actually use the hashed value of the user/site/password, we can store that instead. The receiving site has no idea the code doesn't actually know the REAL password.
Now turn that over in your head. We can call the web service as this user WITHOUT knowing the password, just by knowing the hash. I don't know about you, but it makes me a little bit more worried when I hear of user details being leaked or hacked from sites. It's all very well reassuring us the passwords are hashed and can't be reverse engineered (assuming your own password can't be brute-forced). But depending on the security mechanism, a leak of those hashes can be dangerous. If a hacked provider advises people to change their passwords, take their advice.
'Basic' authentication doesn't have the same weakness. In that environment the provider can store the password hash after applying their own 'secret sauce' mechanism (mostly a salt). When you authenticate, you send the password, they apply the secret sauce and compare the result. You can't get away without knowing the password, because all the work is done at their end.
There's no secret sauce for digest authentication, and there can't be. Even if the provider had the password in the clear, there's no way they can be sure the client has the password since all the client needs is the result of the hash. The provider must store, or be able to work out, the result of that hash because they need to replicate the final hash result using both the client and server nonces. They can store either that same user/realm/password hash as is, or they can encrypt it in a reversible manner, but a one-way hash wouldn't be usable.
In short, digest authentication means that our batch routine doesn't need to 'know' the actual password, just a hash. But it also makes those hashes a lot more dangerous.
I'm an amateur in this field. I checked around and it does seem this is a recognized limitation of digest authentication. EG: This Q&A and this comparison of Digest and Basic.
Hoping to do it at Open World 2014 so ... I guess this is the early version of that presentation eh?
You can find it here: Three approaches to shared pool monitoring Hotsos 2014
My guess is that there were about 100 people in the room and it looked like automatic "won" but not by much. Maybe 55 percent automatic and 45 manual ... so there are still a whole bunch of people nervous about using that functionality.
My presentation was updated ( after delivering it ) to add in some additional doc id's and warnings ( both pro and con ) about manual memory settings.
Specifically worth noting is the un obvious fact that EVEN IF you have disabled oracle from doing SGA resizing operations automatically ... well it may do them anyhow. Makes me a little grumpy eh?
So you can set SGA_TARGET to zero ( and/or MEMORY_TARGET to zero ) and still have oracle make decisions for you? It turns out that after some point you have to set yet another hidden underscore parameter to tell the oracle software "yeah thanks but I really really mean it" ...
Oracle doc id 1269139.1 ... a pretty good writeup here ... really turn off ammasmm
We are very excited to announce the release of Formspider Desktop 1.7. FS Desktop 1.7 includes one of the most requested features by our customers.Built-In Security RepositoryFS Desktop 1.7 comes with an integrated authentication and authorization repository. Formspider developers are now able to secure both data and UI elements and grant access rights to users directly from the Formspider IDE.Formspider Security Highlights:
- Security both at data and UI layer.
Grant read and write access at the query, column and row level. Control editable, enable, visible attributes of every UI component.
- Security aware UI components.
Formspider automatically detects the read&write privileges defined at the data layer and restricts access to data bound UI objects.
- Incredibly easy to manage.
Build and maintain your security model quickly with the point and click graphical user interface.
- Create reports on your security model easily with SQL.
No LDAP, no XML, no files. Formspider security model is implemented in relational database tables.
Yesterday I was informed about someone stealing my content again. I take a pretty hard line to this these days. I used to be a little unsure about how to approach it, but now I just hit them with a DMCA take-down notice straight away. I’ve not got time to explain to everyone and their dog about copyright law…
So the current thief has nicked 35+ of my articles. I went to the WordPress.com DMCA Notice page and they say the usual stuff, except that you have to file a separate notice per blog post. All other services I’ve encountered allow you to post a single notice, listing all the offending posts. Not WordPress.com! (see update below)
So now I’m faced with posting 35+ notices, or not bothering. I’m posting the notices and a shitty email to WordPress.com. By making this process significantly more clumsy than other service providers, they are playing into the hands of the thieves. I think this is an almighty fail on their part!
Update: WordPress.com have got back to me saying they have removed the offending content. They also said I should have posted a single DMCA notice for all the URLs on this blog. Easier said than done when the form prevents this! I think someone at WordPress.com needs to do some proper testing of their form.Copyright Theft: WordPress.com not making my life easy! was first posted on March 8, 2014 at 10:55 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.