text:
Calling it the "most significant religious liberty decision in two decades," the New York Times announced the Supreme Court's decision to uphold the "ministerial exception" whereby churches and other religious organizations are exempt from governmental interference in their hiring and firing practices. In a unanimous 9-0 decision on January 11, the Court said that churches have an overriding "interest ... in choosing who will preach their beliefs, teach their faith and carry out their mission."
The case started when a teacher at a Lutheran school in Redford, Michigan, was fired for threatening to sue the school over an alleged discrimination violation. Cheryl Perich was diagnosed with narcolepsy and took a leave of absence. When she tried to return, she learned that the school had hired someone else to take her place. When she threatened a lawsuit under the Americans with Disabilities Act, she was fired for violating the church's doctrine by pursuing litigation rather than trying to resolve the situation internally.
The Equal Employment Opportunity Commission (EEOC) got involved and filed suit against the school. When the court ruled in favor of the school, the EEOC appealed. The appeals court reversed the decision, and the school took the case to the Supreme Court. At issue were core First Amendment rights: the Establishment Clause and the Free Exercise Clause, to wit: "Congress shall make no law respecting an establishment of religion, or prohibiting the free exercise thereof."
The government of Greece is catching flack over its decision to add some questionable categories to its list of recognized disabilities. As reported by the Associated Press, disability groups in the country were especially outraged over the government's decision to add pedophiles to its list of those the state recognizes as disabled individuals. Among the other "disabled" categories added to the list were exhibitionists, kleptomaniacs, pyromaniacs, compulsive gamblers, fetishists, and sadomasochists.
The U.S. Court of Appeals for the Tenth Circuit on Tuesday unanimously upheld a lower court ruling enjoining the enforcement of an amendment to the Oklahoma state constitution that barred state courts from taking Sharia law into consideration when deciding cases. Although the measure received overwhelming support from the citizens of the Sooner State, it was immediately challenged in court by the American Civil Liberties Union (ACLU) and the Council on American-Islamic Relations (CAIR).
As Minnesota voters gear up to vote on a state constitutional amendment defining marriage as only between a man and a woman, the Catholic Church's archbishop for Minneapolis and St. Paul has ordered priests in his diocese to show their support for the amendment effort — and the church's stand on the institution of marriage, which they promised to defend when they were ordained — or remain silent.
According to the Progressive Catholic Voice, a blog that supports same-sex marriage, the letter from Archbishop John Niensedt was addressed to the priests and deacons of the archdiocese and was originally published in the Archdiocesan Updates newsletter.
In the epistle to his fellow priests, Niensedt, who has been a vocal supporter of the marriage amendment, wrote, "I do not believe it is an exaggeration to say that in this movement to protect and defend the definition of marriage as a union between one man and one woman we are faced with one of the greatest challenges of our times." He warned that the goal of those who oppose passage of the marriage amendment "is not just to secure certain benefits for a particular minority, but, I believe, to eliminate the need for marriage altogether."
In what some legal analysts consider the most significant decision covering religious freedom in the last 20 years, the U.S. Supreme Court ruled January 11 that a religious organization has the right to fire an employee under the 1964 Civil Rights Act's "ministerial exception" clause.
The Obama administration's Justice Department has announced that it is expanding what it said is an outdated definition of rape found in the Uniform Crime Report, adding men as victims and stipulating that victims need not have physically resisted their attackers.
A Department of Justice press release, too graphic to quote verbatim, noted that the "longstanding, narrow definition of forcible rape, first established in 1927, is 'the carnal knowledge of a female, forcibly and against her will.'" That definition, the DOJ pointed out, excludes a whole host of crimes, including sexual violence of a victim by someone of the same sex, as well as "non-forcible rape." The new definition, specified in the press release, spells out all of the options.
"These long overdue updates to the definition of rape will help ensure justice for those whose lives have been devastated by sexual violence and reflect the Department of Justice's commitment to standing with rape victims," said Attorney General Eric Holder. "This new, more inclusive definition will provide us with a more accurate understanding of the scope and volume of these crimes."
The more than 400 contestants in the Miss California USA and Miss Teen California USA state pageant "will emphasize individuality and push the envelope even further," says Keith Lewis, co-executive of the annual flesh flash. "This year's event will be bigger and reflect the progressive attitudes of the contestants."
Last week's column started off asking: "What human motivation gets the most wonderful things done?" The answer is that human greed is what gets wonderful things done. I wasn't talking about fraud, theft, dishonesty, special privileges from government or other forms of despicable behavior. I was talking about people trying to get as much as they can for themselves.
Think about greed and racial discrimination. In 1947, when the Brooklyn Dodgers hired Jackie Robinson, why did racial discrimination by major league teams begin to drop like a hot potato? It wasn't feelings of guilt by white owners, affirmative action, or anti-discrimination laws. It turned out that there was a huge pool of black baseball talent in the Negro leagues. It became too costly for teams to allow the Dodgers to gain a monopoly on this talent. Black players won the National League's Most Valuable Player award for seven consecutive seasons. Had other teams not stepped in to hire black players, allowing the Dodgers to hire them, it might have given the Dodgers a virtual monopoly on world championships.
During South Africa's apartheid era, whites were in control, both economically and politically, and enacted some of the harshest racially discriminatory employment laws. There were job reservation laws that reserved certain jobs for whites only. Many white employers went to considerable lengths to contravene and violate those laws. White building trade unions complained to the South African government that laws reserving skilled jobs for whites had broken down.
The "top one percent" of American earners, who have become heated targets of Democrats, Occupy Wall Street protesters, and the Obama administration, is not limited to the isolated crowd of corporate executives who run America's financial institutions; it also includes top executives in organizations such as Planned Parenthood Federation of America (PPFA), the "family planning" group responsible for the deaths of millions of unborn babies.
According to Planned Parenthood's 2009-2010 annual report, the abortion business is booming, as the release shows the organization administering 329,455 abortions last year. These operations, along with other services the organization provides, have delivered PPFA more than a billion dollars in total net assets, a new record high.
However, spikes in business profits have not been the only contributors to the group's 2010 financial boon, but also the millions of dollars in government funding it harvests every year. "Planned Parenthood received $487.4 million in government money in 2010, a huge jump from the $363.2 million reported last year," reported LifeSiteNews. "At the same time, contributions to the organization from private sources dropped from $308.2 million the year before to $223.8 million, a fact that pro-life groups have pointed to as apparent evidence of declining support for the abortion giant."
As she heads into the last year of her tenure in the state of Washington, Democratic Governor Christine Gregoire has a handful of policy initiatives she would like to push through to pad her political legacy: balance the state's budget, raise state revenues, create some jobs to help the economy, improve education. And force through a bill legalizing homosexual marriage.
After seven years of waffling and "vague answers" on the issue, the Roman Catholic Governor, whose stand on homosexual marriage is at odds with her church's teaching, announced on January 4 that "she not only supports allowing gays and lesbians to marry, but will propose legislation to legalize it in Washington state," reported the Seattle Times.
"Our gay and lesbian families face the same hurdles as heterosexual families — making ends meet, choosing what school to send their kids to, finding someone to grow old with, standing in front of friends and family and making a lifetime commitment," Gregoire said in announcing her decision to support legalizing homosexual marriage. "For all couples, a state marriage license is very important. It gives them the right to enter into a marriage contract in which their legal interests, and those of their children if any, are protected by well-established civil law."


text:
Hand Tools
1 Result Found that Includes 2 Products
How can we improve the Search Experience?
Product availability based on selecting either shipping or pick up and a location. Dismiss
Ship or Pickup Enter Location
- Creative Permanent MarkerCreative Permanent Marker
Multiple product options available
Multiple product options available
Price $4.94
Note: Product availability is real-time basis and adjusted continuously. The product will be reserved for you when you complete your order. More

subset: commoncrawl


text:
Transition Guelph, its partner organizations and affiliates want to send out a huge THANK YOU!! to everyone who made this year's festival a huge success!!This year there were over 35 events on our schedule, organized and facilitated by 40 amazing individuals, organizations, businesses and groups. Easily the best Resilience Festival yet! We want to thank all of the over 40 sponsors and supporters listed on the Sponsors page who donated funds, in-kind donations, venues, time, energy, expertise and talent, the incredible army of volunteers who turned out to make the festival happen, all of the local businesses who donated food to the Earth Hour Potluck dinner, and those who gave prizes and gifts, all of our open mic performers, to our fantastic keynote speakers who gave such inspiring talks Friday night, and of course, to our fantastic festival planning committee, who managed a thousand-and-one details to turn the festival from a dream into reality!Last but not least, a big thank you goes out to everyone who came out to the festival events. We may have put the festival together, but you made it come alive!
Your feedback is important to us! Did you attend an event? If so, please give us your thoughts! Help us make future festivals even better. Please click on the button below.
What's the Resilience Festival all about? What are we celebrating? Learn all about community resilience, Transition Guelph, and the work of dozens of great organizations and businesses in the community!
Meet great people, have fun, and make a difference. Becoming a Resilience 2015 community festival volunteer is the best way to get in on the action. We need greeters, promoters, kitchen help... there are so many ways to be a part of the festivities!
Every little bit you contribute will add to making this year�s festival the best yet! Your small donation will help us spread the word about the fantastic workshops and activities we have planned, and enable us to print and distribute festival guides.
Although there may be limits to our natural resources, our collective creativity is boundless. We all have unique talents, knowledge and skills we use to ensure our community�s ability to endure and thrive. So we want to hear from you!
Presented by:Transition GuelphResilient Guelph 2030
The Guelph Resilience Festival takes place on the ancestral land of the Chonnoton/Attawandaron/Neutral Peoples. The colonial processes by which we came to live on this land were and continue to be to be a great injustice; we recognize that great harm has come to the original peoples of this land through colonization. We strive to be accountable to this in all aspects of our work by fostering positive relationships while challenging destructive ones. We welcome feedback and critique on our efforts.


text:
Your cart is empty!
Active Ingredients
Menthol (5%) (topical analgesic).
Inactive Ingredients
Alcohol, Aminomethyl Propanol, Camphor, Carbomer, Disteareth 75 IPDI, Ethylhexyl Isononanoate, FD&C Blue 1, Glycerin, Isopropyl Alcohol, PEG 7 Capric/Caprylic glycerides, Polysorbate 80, Water.
Never have any problems ordering or receiving my merchandise. Best place to buy from.
I've never had any problems getting my orders from myotcstore. The brand seems to be different this time, and I haven't actually used it yet, but I'm sure it's the same good quality as the other Traumeel they carried.
It is latex free, leaves the skin soft and it effectively treat the diaper rash. It is moderately thick ointment that soothes and protects the irritated area. It also helps the area to heal. It helps the skin protected.
This spray is great for back, feet and legs. It works well for stress aching muscles. It almost gives instant muscle pain relief. This is a powerful spray which provides me relief. Highly recommended one.
Perform gel provides fast relief and this is useful for temporary relief of minor muscle aches and joint pains. This gel also used to relieve severe leg and buttock cramps. I would recommend it tom any one who suffering from muscle pains. This is the best product for stress relieving.
Bengay Greaseless Deep Penetrating Pain Relief Cream - 2 Oz
Our Price:$3.90
Bengay Ultra Strength Pain Relieving Cream, Non-Greasy - 4 Oz
Our Price:$7.38
Capzasin - Hp Arthritis Pain Relief Cream, High Potency Formula - 1.5 Oz
Our Price:$10.70
Fruit Of The Earth Aloe Vera 100% Gel, Moisturizing Therapy For Dry Or Irritated Skin - 2 Oz
Our Price:$1.00
Neosporin Plus Pain Relief Maximum Strength Antibiotic Cream - 0.5 Oz
Our Price:$4.48
Neutrogena Oil-Free Facial Moisturizer With Uva And Uvb Sun Protection, Spf 15 - 4 Oz
Our Price:$7.47


text:
Industry News:
Most Popular in:
Company News
IFF and Amyris Advance Pact to Develop Ingredients for the F&F Market
Posted: February 25, 2014
International Flavors & Fragrances Inc. (IFF) and Amyris have completed the first phase of their collaboration to develop a specific set of renewable fragrance ingredients and agreed to move forward with the second phase of development.
"In our 125-year history, we have been market leaders in the area of innovation and this recent work with Amyris reflects our continuing commitment to lead in this area," said Nicolas Mirzayantz, IFF's group president, fragrances. "We believe that the creation of cost-effective molecules will have a positive impact on our fragrance ingredients business as well as the creative capabilities of our fragrance compounds business."
John Melo, Amyris president and CEO said: "We are extremely pleased with the success in our collaboration with IFF and are optimistic about the prospect for scaling up new fragrance compounds using our technology platform."
In April 2013, IFF and Amyris agreed to develop a sustainable, cost-effective and reliable source of key fragrance ingredients. The parties have agreed to pursue the second phase of development. As previously disclosed, under the terms of the multi-year agreement, IFF will have exclusive rights to the renewable fragrance ingredients developed for applications in the flavors and fragrances sector, and Amyris will have exclusive rights in other sectors.
World Perfumery Congress: June 10–12, 2014, Deauville, France
Join the fragrance world's thought leaders for three days of exhibitions, education and networking that will unite suppliers, marketers, retailers and other key industry stakeholders. Find out more!


text:
Testing Methodolgy – The Groundwork January 20, 2010Posted by mwidlake in Perceptions, Testing, Uncategorized.
Tags: data dictionary, knowledge, Testing
add a comment
I want to start learning about using SQL AUDIT, as I mentioned a couple of days ago.
First question. What do I want to know about SQL AUDIT? I might just have thought "well, I do not know much about this area and I think I should – so I want to know everything". That is OK, but personally I find it helps to make up a specific aim. Otherwise you can just flounder about {well, I do}. In this case I have decided I want to know:
- The options for auditing who is selecting key tables.
- How to audit when those key tables get changed.
- The impact on performance of that audit, and if it could be an issue.
- (4) follows on from (3), in that I want to find out how to control that performance impact.
For any of you who have been or are code developers, you hopefully appreciate test-driven coding. That is, you decide up front what the new code must achieve and design tests to ensure the code does it. You do not write any code until you have at least thought of the tests and written them down in a document. {Ideally you have written the tests and built some test data before you start, but then in an ideal world you would get paid more, have more holidays and the newspapers would tell the truth rather than sensational rubbish, but there you go :-) }
I do not think that learning stuff/testing as much different from developing code, thus the list above. I now know what I want to understand.
What next? I'm going to go and check the Oracle Documentation for the feature. And I am very careful to check the documentation for the version I will use. This is 10.2 for me. I know, the Oracle documentation can be pretty dry, it can be rather unhelpful and it is not very good at examples. But you can expect it to be 90% accurate in what it tells you. You can also expect it to be not-very-forthcoming about the issues, gotchas and bits that don't work. {I have this pet theory that the official documentation only mentions how things do not work once a feature has been out for a version and people have complained that the documentation should let on about the limitations}.
So, for SQL AUDIT I suggest you go and read:
- Concepts Manual, chapter 20 Database Security. If I am not rushed I would read the whole chapter, I might realise that what I want to do is better done with some other tool (If I wanted to see who had changed records months down the line, I think I would pick up that database triggers were a better bet, for example).
- SQL Reference, chapter 13 , the section on AUDIT (no surprises there). I do not do much more than read through the SQL manual once though, as frankly I find it pretty useless for explaining stuff, but it puts into your head what the parts of the command there are and pointers to other parts of the documentation. I'll read the concepts manual with more attention. In this case, the manual will lead me to:
- Database Security Guide chapter 8. Which is pretty good, actually.
- My next source of information, may not immediately spring to mind but I find it very valuable, is to find out which data dictionary objects are involved in the feature. In this case, the previous sources would lead me to go to the Database Reference and check out:
- DBA_AUDIT_TRAIL, DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS. And of course, SYS.AUD$. {I actually queried DBA_OBJECTS for anything with the word "AUDIT" in it, check out all the tables and also had a quick peak at the text for any views, which would have led me to SYS.AUD$ if I did not already know about it}.
Why do I go and look at the data dictionary objects and the reference guide? After all, is it not nerdy enough to have put myself through reading the SQL Reference manual? {and let us be honest, it is rarely enjoyable reading the SQL Reference manual}. Because I want to know how it works, not just how to use it. Seeing the table give me a lot of information and the description of the columns may well tell me a lot more. First thing, SYS.AUD$ only has one index, on a column SESSIONID# (I know, there is another column in the index, I need to get to a DB to check this). Any queries not via this index are going to scan the whole damned thing. Right off, I suspect this will be an issue.
I will DESC the tables, see if there is already any information in them. In this case, there was not. A clean sheet.
Why did I not just go to Google (Bing, Yahoo, whatever) and search? Because I can trust the manuals to tell me stuff which is mostly true and is valid for my release. Not stuff about older versions, about later versions, urban myths or just outright fallacies. The manuals certainly will not tell me everything, far from it, but what it does say is solid stuff. With this reliable start I can now go to other sources and have some chance of filtering all the other stuff that is Out There. Once filtered, it is probably a lot more informative and easier to digest than the manuals.
I'll ramble on about that next posting.
Ensuring Correlated Partition Exclusion #2 December 20, 2009Posted by mwidlake in performance, Uncategorized, VLDB.
Tags: partitions, performance, VLDB
2 comments
A few days ago {oh dear, it is now two weeks!} I showed how you could better control Correlated Partition Exclusion by creating a lookup table that showed, for each partition, the minimum and maximum ID (the partition key) and the minimum and maximum CRE_DATETIME (what you want to limit your query on). Using this range table in the way I described, you did not have to take an educated guess as to what range of IDs you used to include the partitions for the date range you were interested in, you could check the table.
But this only worked reliably where the ID and CRE_DATETIME increase directly in proportion to each other. It was a special case.
What about the more normal case, where there is a correlation between the two columns but it is not a perfect correlation? This would happen, for example, if the CRE_DATETIME is entered from another system, or from paper records, where the order of the records is not enforced. So some records from today get put into the system before some from yesterday. Or if the correlation is even loser than this. eg you are looking at orders for new customers. You "know" there are going to be no orders for these customers from 5 years ago but you are not sure how far back in the orders table you should go to find what you want.
You can still use the lookup table method. The lookup table in effect becomes a meta-index – an index of the segments where you will find data but not actually the rows.
To demonstrate this, I created a table where the ID and CRE_DATETIME increase in order:
create a partitioned table test_p4 (id number(10) not null ,cre_datetime date not null ,status number(1) not null ,num_1 number(4) not null -- random 20 ,num_2 number(4) -- random 500 ,num_3 number(5) -- cycle smoothly ,num_4 number(5) -- Random 10000 ,vc_1 varchar2(10) ,vc_2 varchar2(10) ,vc_pad varchar2(2000)) tablespace users partition by range (id) (partition id_01k values less than (1000) tablespace users ,partition id_02k values less than (2000) tablespace users ... ,partition id_45k values less than (45000) tablespace users ,partition id_max values less than (maxvalue) tablespace users ) -- -- local partitioned indexes on the table IND_NAME TAB_NAME PSN COL_NAME ------------------ ------------------ --------- ------------ TP4_CRE_DT TEST_P4 1 CRE_DATETIME TP4_PK TEST_P4 1 ID -- -- populate the table with data insert into test_p4(id,cre_datetime,status,num_1,num_2,num_3,num_4 ,vc_1,vc_2,vc_pad) select rownum ,to_date('01-JUL-2009','DD-MON-YYYY')+(rownum/360) ,decode(mod(rownum,100),0,1 ,0) ,trunc(dbms_random.value(1,20)) ,trunc(dbms_random.value(1,50)) ,mod(rownum,10)+1 ,trunc(dbms_random.value(1,10000)) ,dbms_random.string('U',5) ,lpad(chr(mod(rownum,6)+65),5,chr(mod(rownum,6)+65) ) ,lpad('A',1000,'A') from dba_objects where rownum <43000
I then messed with the data, updating 10% of records and setting the CRE_DATETIME to plus or minus a random amount up to 2 days different, so data in partitions would overlap. I then created a range table in the same way as I did for the previous post.
I ended up with a range table like the below:
You can see that the MIN_CRE_DATI-MAX_CRE_DATI from record to record (partition to partition) overlap but generally increase.
How do you find the start and end of the ID range to cover a date period you are interested in? I always have to sit down with a pen a paer to work this out. It is the classic "overlapping ranges" check, but my brain cannot hold on to it. So here goes. I want all records between the 1st of October and the 6th, this year.
You want to find the partition with the lowest ID range which has a record that falls into the date range you want. ie the Maximum CRE_DATETIME record is as late or later than the range you are interested in. If the maximum CRE_DATETIME is less than the date range you are interested in, no records will be in that partition.
Here is the code to find the lowest partition
select min(min_id) from tp4_range where MAX_CRE_DATI>=to_date('01-OCT-2009','DD-MON-YYYY') MIN(MIN_ID) ----------- 32000 Execution Plan ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 2 | 1 | SORT AGGREGATE | | 1 | 12 | | 2 | TABLE ACCESS BY INDEX ROWID| TP4_RANGE | 12 | 144 | 2 |* 3 | INDEX RANGE SCAN | TP4R_MACD_MIAD | 12 | | 1 ----------------------------------------------------------
To cover the whole of the partition of interest you need to look for records with and I greater than the minimum in that partition, thus the selection of the min(min_id)
Similarly, you only want partitions where the minimum CRE_DATETIME is before the end of the date range you want. If all records in the partition have a CRE_DATETIME beyond the range, you are not interested in it.
select max(max_id) from tp4_range where MIN_CRE_DATI<=to_date('06-OCT-2009','DD-MON-YYYY') MAX(MAX_ID) ----------- 35999 Execution Plan ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 | 1 | SORT AGGREGATE | | 1 | 13 | | 2 | TABLE ACCESS BY INDEX ROWID| TP4_RANGE | 37 | 481 | 2 |* 3 | INDEX RANGE SCAN | TP4R_MICD_MIID | 37 | | 1 ----------------------------------------------------------
Then you put these sub-queries into the query to select the data you want. Below I show the "original" code which does not use the partition range table and then using the partition range table, to prove the same number of records come back and to see the plan and statistics change:
select count(*) from test_p4 where cre_datetime between to_date('01-OCT-2009','DD-MON-YYYY') and to_date('06-OCT-2009','DD-MON-YYYY') and num_2 = 5 COUNT(*) ---------- 48 Execution Plan ------------------------------------------------------ | Id | Operation | Name | R ows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------ ------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 11 | 356 (1)| 00:00:02 | | | | 1 | SORT AGGREGATE | | 1 | 11 | | | | | | 2 | PARTITION RANGE ALL | | 36 | 396 | 356 (1)| 00:00:02 | 1 | 46 | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P4 | 36 | 396 | 356 (1)| 00:00:02 | 1 | 46 | |* 4 | INDEX RANGE SCAN | TP4_CRE_DT | 1788 | | 52 (0)| 00:00:01 | 1 | 46 | ------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 676 consistent gets 0 physical reads
Note the 676 consistent gets and the Pstart/Pstop of 1-46
select count(*) from test_p4 where cre_datetime between to_date('01-OCT-2009','DD-MON-YYYY') and to_date('06-OCT-2009','DD-MON-YYYY') and id> (select min(min_id) from tp4_range where MAX_CRE_DATI >=to_date('01-OCT-2009','DD-MON-YYYY')) and id < (select max(max_id) from tp4_range where MIN_CRE_DATI <=to_date('06-OCT-2009','DD-MON-YYYY')) and num_2 = 5 COUNT(*) ---------- 48 Execution Plan ----------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------ ----------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 | 66 (5)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 16 | | | | | | 2 | PARTITION RANGE ITERATOR | | 1 | 16 | 62 (5)| 00:00:01 | KEY | KEY | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST_P4 | 1 | 16 | 62 (5)| 00:00:01 | KEY | KEY | | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | 5 | BITMAP AND | | | | | | | | | 6 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | 7 | SORT ORDER BY | | | | | | | | |* 8 | INDEX RANGE SCAN | TP4_PK | 1788 | | 2 (0)| 00:00:01 | KEY | KEY | | 9 | SORT AGGREGATE | | 1 | 12 | | | | | | 10 | TABLE ACCESS BY INDEX ROWID| TP4_RANGE | 12 | 144 | 2 (0)| 00:00:01 | | | |* 11 | INDEX RANGE SCAN | TP4R_MACD_MIA D | 12 | | 1 (0)| 00:00:01 | | | | 12 | SORT AGGREGATE | | 1 | 13 | | | | | | 13 | TABLE ACCESS BY INDEX ROWID| TP4_RANGE | 37 | 481 | 2 (0)| 00:00:01 | | | |* 14 | INDEX RANGE SCAN | TP4R_MICD_MII D | 37 | | 1 (0)| 00:00:01 | | | | 15 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | 16 | SORT ORDER BY | | | | | | | | |* 17 | INDEX RANGE SCAN | TP4_CRE_DT | 1788 | | 52 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 350 consistent gets 0 physical reads
The plan is admittedly more complex and the SQL can be tricky to understand if you are not used to code that looks for overlap of ranges. But Consistent gets is down to 350 and the Pstart/Pstop values are KEY-KEY. The CBO cannot tell you WHAT the ranges will be when the code is parsed {I think it should check but in 10.2.0.3 at least, the CBO is not that smart at parse time}, but it knows there will be a start and stop.
I am using tiny partitions for my example and only 45 of them for the table. When the partitions are for millions of rows and there are a couple of thousand of them, excluding partitions in a manner you can rely on is a powerful tool. Which leads onto a final word of caution.
Nothing is enforcing that the range table is maintained.
You could do things with triggers or regular re-calculation of the ranges table but this will be something you need to consider if you use ranges tables to help partition exclusion. Flipping tablespaces to read-only can help the worry go away though… :-)
As an example of the issue of needing to maintain the ranges table and also a demonstration that the ranges table does work correctly if maintainted, I'll update a record well outside of the "expected window", show that it does not appear in my range-check controlled code, then update the ranges table and try again.
update test_p4
set cre_datetime=to_date('02-OCT-2009 11:15′,'DD-MON-YYYY HH24:MI')
where id=22100
3> select count(*) from test_p4
where cre_datetime between to_date('01-OCT-2009′,'DD-MON-YYYY')
and to_date('06-OCT-2009′,'DD-MON-YYYY')
and id> (select min(min_id)
from tp4_range where MAX_CRE_DATI>=to_date('01-OCT-2009′,'DD-MON-YYYY'))
and id < (select max(max_id)
from tp4_range where MIN_CRE_DATI (select min(min_id)
from tp4_range where MAX_CRE_DATI>=to_date('01-OCT-2009′,'DD-MON-YYYY'))
and id < (select max(max_id)
from tp4_range where MIN_CRE_DATI<=to_date('06-OCT-2009','DD-MON-YYYY'))
and num_2 = 5
— success
I wonder if I will do the next posting on this topic in less than two weeks!
Scalar Functions Not Behaving as Scalar Functions December 8, 2009Posted by mwidlake in performance, Uncategorized.
Tags: performance, PL/SQL
3 comments
My thanks go to Graeme Hobbs, who I currently have the pleasure of working with, for explaining this to me.
This is all about scalar functions that do not act like scalar functions.
Where I am spending much of my time at the moment, we have an issue with a very simple sql statement. It takes on average 2 buffer gets per execution. How could that be an issue? Well, it is being executed a few thousand times an hour. By "a few thousand" read 50,000 times. Even a simple statement being executed 15 times a second is not good if it is not needed. Especially, as in this case, there is a context switch.
I got a look at the code and it is doing something like this:
HAVING SUM( col.vol_gbp ) >=
my_user.pkg_application_settings.get_number_value( 'rp157_eric' )
I quickly batted an email to my esteemed colleague Mr Hobbs saying "but this is a scalar. It will be executed once for the statement and the value passed into each execution of the whole query, it can't be the source of the 50,000 executions an hour".
Oh woe, for my ignorance has undone me. But it's OK, Graeme not only took time to explain, but knocked up the following nice demo of WHY this is a problem. And said I could put it on my Blog. What a nice chap.
From Graeme:
Consider package:
CREATE OR REPLACE PACKAGE BODY ghtest IS l_called_count NUMBER := 0; -- Function and procedure implementations FUNCTION f_get_called_count RETURN NUMBER IS BEGIN RETURN( l_called_count ); END; FUNCTION f_call RETURN NUMBER IS BEGIN l_called_count := l_called_count + 1; RETURN 1; END; FUNCTION f_reset RETURN NUMBER IS BEGIN l_called_count := 0; RETURN 1; END; END ghtest;
— nothing up my sleeve
SELECT ghtest.f_get_called_count
ie no calls made yet
SQL> SELECT id , COUNT( * ) FROM ( SELECT * FROM MY_USR.tbl_SOME_DATA WHERE ROWNUM < 10 ) GROUP BY id HAVING COUNT( * ) > ghtest.f_call; ID COUNT(*) ---------- ---------- 1624361594 3 SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 7
NOTE the counter has gone up to 7 as there were 7 IDs found from that code -6 IDs with one record and one with 3.
SELECT ghtest.f_reset
That just blanks the counter (see the package).
-- nothing up my sleeve SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 0 SELECT id , COUNT( * ) FROM ( SELECT * FROM my_usr.tbl_some_data WHERE ROWNUM < 10 ) GROUP BY id HAVING COUNT( * ) > (SELECT ghtest.f_call FROM dual); ID COUNT(*) ---------- ---------- 1624361594 3 SELECT ghtest.f_get_called_count FROM DUAL; F_GET_CALLED_COUNT ------------------ 1
You can see that the scalar subquery
HAVING COUNT( * ) > (SELECT ghtest.f_call FROM dual);
calls f_call once.
HAVING COUNT( * ) > ghtest.f_call;
results in it being called 7 times – the number of rows returned from the group by
That is precisely the problem.
Apparently our developers should know this, as it is a "known issue". But not known to me until now (unless I knew it and forgot it)
So, hats of to Graeme.
{apologies, wordpress utterly screwed the layout/truncated text when I pasted in the example, have fixed now}
Ensuring Correlated Partition Exclusion December 7, 2009Posted by mwidlake in performance, Uncategorized, VLDB.
Tags: partitions, performance, VLDB
9 comments
<Previous Post…Next Pos >
I've posted a couple of times recently about a tuning method with partition exclusion where you infer a relationship between the value you want to limit a query on and the partition key. It takes a while to explain the theory so I am going to give it a name, in the vain hope it catches on {forgive me if someone has already done this – in fact, just flame me with pointers if you already know a name for this}. At the very least, for my own postings, I can use this name from now on and link back to a single posting explaining it.
I'm going to call it Correlated Partition Exclusion. In essence, you have partitioned the table on a key, in my case the primary key ID, which is an ascending numeric probably sourced from a sequence.
You have a second column, in my case CRE_DATETIME, which increases in line with the PK. If you limit your query on the CRE_DATETIME partition exclusion is not possible as there is no guarantee which CRE_DATETIME values appear in which partition. But, as a human, you understand that if you create 10,000 records a day, if you want to look at the last week's date you can use:
to exclude partitions with an id more than 7 days worth ago.
So, you have your Correlated Partition Exclusion.
How can you be sure that going back 70,000 IDs is going to safely cover one week of data and how can you maximise your efficiency of including only partitions that cover the date range? {note, I am using a numeric ID as my partition range key and a datetime as my correlated column, this principle works just as well if you partition on datetime and want to correlate to a (generally) ascending numeric key}
Here is my test table :-
create table test_p3 (id number(10) not null ,cre_datetime date not null ,status number(1) not null ,num_1 number(4) not null -- random 20 ,num_2 number(4) -- random 500 ,num_3 number(5) -- cycle smoothly ,num_4 number(5) -- Random 10000 ,vc_1 varchar2(10) ,vc_2 varchar2(10) ,vc_pad varchar2(2000)) tablespace users partition by range (id) (partition id_01k values less than (1000) tablespace users ,partition id_02k values less than (2000) tablespace users ,partition id_03k values less than (3000) tablespace users ,partition id_04k values less than (4000) tablespace users ... ,partition id_45k values less than (45000) tablespace users -- ,partition id_max values less than (maxvalue) tablespace users ) / --@ind_cols IND_NAME TAB_NAME PSN COL_NAME ------------------ ------------------ --------- -------------------- TP3_PK TEST_P3 1 ID TP_CRE_DT TEST_P3 1 CRE_DATETIME
If I want to look for all records between two dates I could use code like the below (based in that suggested by Bernard Polarski, any mistakes are mine).
with get_min_id as (select max(id) min_id from test_p3 where cre_datetime >= TO_DATE('18-OCT_2009','DD-MON-YYYY') ) ,get_max_id as (select min(id) max_id from test_p3 where cre_datetime <= TO_DATE('20-OCT_2009','DD-MON-YYYY') ) select count(*) from test_p3 ,get_min_id ,get_max_id where id >get_min_id.min_id and id < get_max_id.max_id and cre_datetime between TO_DATE('18-OCT_2009','DD-MON-YYYY') and TO_DATE('20-OCT_2009','DD-MON-YYYY') / COUNT(*) ---------- 721 1 row selected.
ie find the minimum ID for the start date and the maximum ID for the end date and query between them.
This works fine. Unfortunately, you get a plan like the below
------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------ ------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 887 (4)| 00:00:04 | | | | 1 | SORT AGGREGATE | | 1 | 39 | | | | | | 2 | NESTED LOOPS | | 2 | 78 | 887 (4)| 00:00:04 | | | | 3 | NESTED LOOPS | | 1 | 26 | 717 (5)| 00:00:03 | | | | 4 | VIEW | | 1 | 13 | 505 (5)| 00:00:02 | | | |* 5 | FILTER | | | | | | | | | 6 | SORT AGGREGATE | | 1 | 26 | | | | | |* 7 | VIEW | index$_join$_001 | 38393 | 974K| 505 (5)| 00:00:02 | | | |* 8 | HASH JOIN | | | | | | | | | 9 | PARTITION RANGE ALL | | 38393 | 974K| 418 (6)| 00:00:02 | 1 | 46 | |* 10 | INDEX RANGE SCAN | TP_CRE_DT | 38393 | 974K| 418 (6)| 00:00:02 | 1 | 46 | | 11 | PARTITION RANGE ALL | | 38393 | 974K| 152 (4)| 00:00:01 | 1 | 46 | | 12 | INDEX FAST FULL SCAN | TP3_PK | 38393 | 974K| 152 (4)| 00:00:01 | 1 | 46 | | 13 | VIEW | | 1 | 13 | 212 (5)| 00:00:01 | | | | 14 | SORT AGGREGATE | | 1 | 26 | | | | | |* 15 | VIEW | index$_join$_002 | 2972 | 77272 | 212 (5)| 00:00:01 | | | |* 16 | HASH JOIN | | | | | | | | | 17 | PARTITION RANGE ALL | | 2972 | 77272 | 59 (6)| 00:00:01 | 1 | 46 | |* 18 | INDEX RANGE SCAN | TP_CRE_DT | 2972 | 77272 | 59 (6)| 00:00:01 | 1 | 46 | | 19 | PARTITION RANGE ALL | | 2972 | 77272 | 152 (4)| 00:00:01 | 1 | 46 | | 20 | INDEX FAST FULL SCAN | TP3_PK | 2972 | 77272 | 152 (4)| 00:00:01 | 1 | 46 | | 21 | PARTITION RANGE ITERATOR | | 2 | 26 | 170 (1)| 00:00:01 | KEY | KEY | |* 22 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_P3 | 2 | 26 | 170 (1)| 00:00:01 | KEY | KEY | |* 23 | INDEX RANGE SCAN | TP_CRE_DT | 707 | | 48 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 892 consistent gets 0 physical reads
If you look at the plan {towards the end, and sorry about the naff layout, my blog is not wide enough for this sort of printout} there are two checks on the TP_CRE_DT indexes that scan all partitions of the index – Pstart/Pstop are 1-46. This is the CBO looking for the partitions where the stated CRE_DATETIME records occur in the whole table. Cost is 892 consistent gets, much of which is the checking of local index partitions that will hold no relevant entries.
Bernard also spotted that the code was considering all partitions and was not as efficient as it could be but did not know how to get around it except with a Global index, which brings it's own issues.
One way is to just say "well, I know how many records per day I get so I will fake up the ID limits based on this". The problem is, and this is why the CBO cannot make the decision for you, is that there is no guarantee, no fixed rule, saying that CRE_DATETIME will always increment with the ID. In fact, there is nothing stopping you altering a record which has an ID from yesterday having a CRE_DATETIME from 10 years ago {but forget I said that until tomorrow's post}. Now, in my example the CRE_DATETIME is going to increment with ID. We will use this special case for now, I know it is flawed and will address that flaw {tomorrow}.
So to ensure yo do not miss data you end up making your ID window pretty large to endure you do not miss records. Say you want all records for the last day, you process 1500 records a day, so you consider a window covering all samples with an ID within the last 10,000. It will still be more efficient than scanning all partitions and should be safe. Fairly safe.
The way out of this is to have a ranges table. Something that tells you, for each partition, which is the maximum and minimum CRE_DATE and the IDs covered by that range. You can then use that to identify the partitions that cover the date range you are interested in.
Here is an example. I will create a simple table to hold the ranges:
create table tp3_range (min_cre_dati date ,max_cre_dati date ,min_id number ,max_id number)
Now you have to populate it.
The below code will create the first record for the first partition.
insert into tp3_range SELECT MIN(CRE_DATETIME) ,MAX(CRE_DATETIME) ,MIN(ID) ,MAX(ID) FROM TEST_P3 PARTITION (ID_01K) / Execution Plan ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ( %CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------ | 0 | INSERT STATEMENT | | 1 | 12 | 102 (1)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 12 | | | | | | 2 | PARTITION RANGE SINGLE| | 999 | 11988 | 102 (1)| 00:00:01 | 1 | 1 | | 3 | TABLE ACCESS FULL | TEST_P3 | 999 | 11988 | 102 (1)| 00:00:01 | 1 | 1 | ------------------------------------------------------------------ Statistics ---------------------------------------------------------- 1 recursive calls 3 db block gets 195 consistent gets 0 physical reads
As you can see from the plan and cost, this is not very efficient as it has to scan the whole partition. Maybe not a problem if you do this once, but there are indexes on both these columns, can't this be done more efficiently? Yes, if you split up the code into four in-line selects (if you want more details about it being more performant to do MIN and MAX on their own than in one statement then see this post on the topic ):
insert into tp3_range SELECT (SELECT MIN(CRE_DATETIME) FROM TEST_P3 PARTITION (ID_04K)) ,(SELECT MAX(CRE_DATETIME) FROM TEST_P3 PARTITION (ID_04K)) ,(SELECT MIN(id ) FROM TEST_P3 PARTITION (ID_04K)) ,(SELECT MAX(ID ) FROM TEST_P3 PARTITION (ID_04K)) FROM DUAL PLAN --------------------------------------------------------- SORT AGGREGATE PARTITION RANGE SINGLE cst:2 rws:1000 INDEX FULL SCAN (MIN/MAX) TP_CRE_DT cst:2 rws:1000 Statistics ---------------------------------------------------------- 1 recursive calls 5 db block gets 9 consistent gets 0 physical reads
{You may wonder why the Explain Plan section above has a different look. This is because there seems to be a bug in 10.2.0.3 where the autotrace plan for the insert statement comes out wrong, as a FAST DUAL access, so I had to Explain the statement in a different way}
You would run one of the above statements against each partition. Probably, the "Best Practice" way would be to generate a SQL script from a query against DBA_TAB_PARTITIONS.
To populate my table I cheated – I just assumed all my partitions are of the same size (1000 records) and used:-
insert into tp3_range
select min(cre_datetime),max(cre_datetime),min(id),max(id)
from test_p3
— where id between 10000 and 20000
group by trunc(id/1000)
Note I left in a commented line. You could run the above against the whole table and then limit it to just new partitions as you add them. This is a tad risky though, you are relying on the partitioning being perfect and it would not scale to hundreds of very large partitions. You would be better off with the partition-by-partition methods above.
You end up with a table like the below:-
select * from tp3_range order by min_cre_dati MIN_CRE_DATI MAX_CRE_DATI MIN_ID MAX_ID ----------------- ----------------- ---------- ---------- 01-JUL-2009 00:04 03-JUL-2009 18:36 1 999 03-JUL-2009 18:40 06-JUL-2009 13:16 1000 1999 06-JUL-2009 13:20 09-JUL-2009 07:56 2000 2999 09-JUL-2009 08:00 12-JUL-2009 02:36 3000 3999 12-JUL-2009 02:40 14-JUL-2009 21:16 4000 4999 14-JUL-2009 21:20 17-JUL-2009 15:56 5000 5999 17-JUL-2009 16:00 20-JUL-2009 10:36 6000 6999 20-JUL-2009 10:40 23-JUL-2009 05:16 7000 7999 23-JUL-2009 05:20 25-JUL-2009 23:56 8000 8999 26-JUL-2009 00:00 28-JUL-2009 18:36 9000 9999 28-JUL-2009 18:40 31-JUL-2009 13:16 10000 10999 31-JUL-2009 13:20 03-AUG-2009 07:56 11000 11999 ... 14-OCT-2009 13:20 17-OCT-2009 07:56 38000 38999 17-OCT-2009 08:00 20-OCT-2009 02:36 39000 39999 20-OCT-2009 02:40 22-OCT-2009 21:16 40000 40999 22-OCT-2009 21:20 25-OCT-2009 15:56 41000 41999 25-OCT-2009 16:00 28-OCT-2009 10:36 42000 42999
add the two below indexes:
create index tp3r_micd_miid on tp3_range(min_cre_dati,min_id);
create index tp3r_macd_miad on tp3_range(max_cre_dati,max_id);
And now you can find the upper and lower ID bounds for a date range with the following code:
select min_id from tp3_range where min_cre_dati = (select max(min_cre_dati) from tp3_range where min_cre_dati <TO_DATE('18-OCT_2009','DD-MON-YYYY') ) MIN_ID ---------- 39000 Execution Plan --------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost --------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 2 |* 1 | INDEX RANGE SCAN | TP3R_MICD_MIID | 1 | 22 | 1 | 2 | SORT AGGREGATE | | 1 | 9 | | 3 | FIRST ROW | | 40 | 360 | 1 |* 4 | INDEX RANGE SCAN (MIN/MAX)| TP3R_MICD_MIID | 40 | 360 | 1 --------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads select max_id from tp3_range where max_cre_dati = (select min(max_cre_dati) from tp3_range where max_cre_dati >to_date('20-OCT_2009','DD-MON-YYYY') ) MAX_ID ---------- 39999 Execution Plan ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 22 | 2 |* 1 | INDEX RANGE SCAN | TP3R_MACD_MIAD | 1 | 22 | 1 | 2 | SORT AGGREGATE | | 1 | 9 | | 3 | FIRST ROW | | 4 | 36 | 1 |* 4 | INDEX RANGE SCAN (MIN/MAX)| TP3R_MACD_MIAD | 4 | 36 | 1 ---------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads
Put it all together into one statement and let's see how much it costs:
with get_min_id as (select min_id from tp3_range where min_cre_dati = (select max(min_cre_dati) from tp3_range where min_cre_dati <TO_DATE('18-OCT_2009','DD-MON-YYYY') ) ) ,get_max_id as (select max_id from tp3_range where max_cre_dati = (select min(max_cre_dati) from tp3_range where max_cre_dati >to_date('20-OCT_2009','DD-MON-YYYY') ) ) select count(*) from test_p3 ,get_min_id ,get_max_id where id >get_min_id.min_id and id < get_max_id.max_id and cre_datetime between TO_DATE('18-OCT_2009','DD-MON-YYYY') and TO_DATE('20-OCT_2009','DD-MON-YYYY') COUNT(*) ---------- 721 1 row selected. Execution Plan ---------------------------------------------------------- | Id | Operation | Name | Row s | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------ ---------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 57 | 67 (5)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 57 | | | | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | TEST_P3 | 2 | 26 | 65 (5)| 00:00:01 | | | | 3 | NESTED LOOPS | | 2 | 114 | 65 (5)| 00:00:01 | | | | 4 | MERGE JOIN CARTESIAN | | 1 | 44 | 2 (0)| 00:00:01 | | | |* 5 | INDEX RANGE SCAN | TP3R_MICD_MIID | 1 | 22 | 1 (0)| 00:00:01 | | | | 6 | SORT AGGREGATE | | 1 | 9 | | | | | | 7 | FIRST ROW | | 40 | 360 | 1 (0)| 00:00:01 | | | |* 8 | INDEX RANGE SCAN (MIN/MAX) | TP3R_MICD_MIID | 40 | 360 | 1 (0)| 00:00:01 | | | | 9 | BUFFER SORT | | 1 | 22 | 1 (0)| 00:00:01 | | | |* 10 | INDEX RANGE SCAN | TP3R_MACD_MIAD | 1 | 22 | 1 (0)| 00:00:01 | | | | 11 | SORT AGGREGATE | | 1 | 9 | | | | | | 12 | FIRST ROW | | 4 | 36 | 1 (0)| 00:00:01 | | | |* 13 | INDEX RANGE SCAN (MIN/MAX) | TP3R_MACD_MIAD | 4 | 36 | 1 (0)| 00:00:01 | | | | 14 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY | | 15 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | 16 | BITMAP AND | | | | | | | | | 17 | BITMAP CONVERSION FROM ROWIDS| | | | | | | | | 18 | SORT ORDER BY | | | | | | | | |* 19 | INDEX RANGE SCAN | TP3_PK | 7 07 | | 6 (0)| 00:00:01 | KEY | KEY | | 20 | BITMAP CONVERSION FROM ROWIDS| | | | | | | | | 21 | SORT ORDER BY | | | | | | | | |* 22 | INDEX RANGE SCAN | TP_CRE_DT | 7 07 | | 48 (0)| 00:00:01 | KEY | KEY | ------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 133 consistent gets 0 physical reads
If you look way back up this post, you will see that the number of records selected by the above is the same as from the code indicated by Bernard (721 records) and for a lot less cost – 133 consistent gets compared to 892.
So I have hopefully explained the issue (having to visit all the index partitions to check the date range), shown how a ranges table can help, given some simple and a less-simple-but-more-efficient examples of code to populate the table and finally shown that using the range table can be more efficient.
And I arrogantly gave it a name – Correlated Partition Exclusion :-)
Some of you may remember tha this example has been a special case, as there is no overlap between the dates; the relationship between the CRE_DATETIME and ID is perfect. You are unlikely to have that in real life. Also, dome of you may also be tired of reading this post. So I will cover the general case in the NEXT post.
Depth of Indexes on VLDBs November 18, 2009Posted by mwidlake in performance, Uncategorized, VLDB.
Tags: Architecture, partitions, statistics
3 comments
In a couple of recent posts on partitions, on single row and range scan access I have started exploring the performance of partitions. I will continue on the main theme soon, honest.
I feel a little bad that I have not given concrete examples so far {creating very large objects for the sake of demonstrations and proof-of-concepts is a bit of an issue with VLDBs, due to the space and time requirements to do it}. So, here is just some real-world information on index depth, or BLEVEL of indexes on Very Large DataBases.
The BLEVEL is the number of levels in the index. Minus one. See this post for a full description, but in brief, if an index has a BLEVEL of 3, that means there is a root node, a level of branch nodes, a second level of branch nodes and then the leaf nodes. 4 levels. 4 IOs to check if the index holds the value you are looking for. And then one or more IOs against the table to get the actual records from the table, if required (one block if there is only one row, one or more blocks if there are several rows).
The below is from a 25TB datawarehouse system. I am looking for the maximum index depth on the database:-
select max(blevel) from dba_indexes
OK, how many indexes are this deep?…
select owner,index_name from dba_indexes
where blevel=4
—————————— ———————-
1 row selected.
Oh. I have a pretty massive database and the deepest index I have is BLEVEL 4 and there is one of them. So the index height is 5. And access to that table for a given referral ID is going to take 5 IOs to work down the index and one against the table.
What is more, the index is an IOT
—————————— —————————
so, in fact, to get to the table record it is just the 5 IOs as the table record is held in the IOT.
So with a 25TB data warehouse, the deepest normal indexes are BLEVEL 3.
select index_name,num_rows/leaf_blocks from dba_indexes where BLEVEL =3 order by num_rows desc INDEX_NAME NUM_ROWS LEAF_BLOCKS ------------------------------ ---------------- ------------ W_WL_SH_API_XIIIIIIIIIII_ID 4,585,108,192 13,406,015 W_WL_SHIIIIIIIIIIIXXXX_ID 4,564,350,002 16,892,898 W_WL_SIIIIIIIIIIIIIIIIIIIIII_ID 4,422,775,820 17,189,536 W_WL_SHIIIIIIIIIIIIIIIIIIIIII_ID 4,182,087,545 12,243,438 W_WL_GX_RESIIIIIIIIIII_LOG_ID1 3,690,374,216 14,613,388 IDX_W_WL_LIIIIIIIIIIIESS_ID 351,507,905 1,958,109 WL_LOGIN_SUIIIIIIIIIIIIE_ID 348,004,861 1,550,180 IND_IIII_LEG_ID 312,727,000 972,690 IND_TMLR_MARIIIIIIIIIII_ID 306,988,247 1,121,592 PK_TBL_IIIIIIIIIIIUNNER 305,566,240 939,775 PK_IIIIIIIIIIIGS 284,208,000 863,000
I asked an old friend, Tony Webb, to check out the depth of indexes on the largest databases they have, which range from a few TB to several 10s of TB. {one is 100TB plus, but a lot of the data is in LOBs, so the actual number of records is only billions, not 10's of billions :-) } No index had a BLEVEL greater than 3.
Hi Martin,
I ran the first query on DAVE, DEE(our biggest db), DOZY and MICK. The max depth results were 3,2,3 and 3. Our largest database only had 2. In fact some of the other largish dbs also only have a max depth of 2.
That enough for your blog Martin?
Now, some of you may be smelling a Rat, and you would be right. At both sites, the VLDBs have been designed with partitioning as a key part of the Physical Implementation. All the largest objects are partitioned. What impact does partitioning have on BLEVEL?
The depth of the index segment is dependent on the number of keys per block.
Let us say you have 8k blocks {this is a questionably low block size for a data warehouse, but it is what some of these systems have} and your index is on a couple of columns, totalling 20 bytes per key on average, including rowid. Allowing block overhead, that is approx 8000/20 entries = 400 max….
Blow that, that's whooly theory, I'll look at my real 25TB database for number of rows per leaf block…
select leaf_blocks, num_rows\leaf_blocks rows_per_leaf from dba_ind_statistics where num_rows > 100000000 order by leaf_blocks desc LEAF_BLOCKS ROWS_PER_LEAF ------------ ------------- 30,211,949 325.602273 28,717,447 262.762331 26,308,608 382.505959 25,429,862 385.047045 24,054,626 309.92864 23,660,747 382.571434 22,458,058 411.063205 22,316,976 346.620625 18,875,827 379.952198 17,451,901 338.600909 17,189,536 257.29466
From the above let us take a low figure of 300 average enteries per block. For a BLEVEL 0 , a one block index, that would be 300 rows that are referenced. For a level 1 index, 300 entries in the root block will point to 300 leaf blocks, so that will be 300*300 rows…90,000 entires.
Go down another BLEVEL to 2 (and a height of 3) and you have a root node referencing 300 Branch nodes, referenceing 300 Leaf nodes each referencing 300 records. 300*300*300 = 27 million.
Another level (BLEVEL 3), another factor of 300 and that is 8.1 billion.
BLEVEL 4 = 6,480,000,000,000 (6,480 billion entries).
You can see that with an 8k block size and being very pessimistic about the number of entries per block (I used a low average to calculate enttries per block where as in reality most indexes will fill the block at the root and branch nodes to close to full before throwing a new level) your have to increase your data volume by amost 300 to throw a new level of index.
On average, knowing nothing about the actual number of records in an index, you would have to reduce your number of indexed rows by 150 times (half of 300) to have a 50% chance of lowering the BLEVEL of a locally partitioned index by one. ie 150 or more partitions.
with a 32k block size, you would have to reduce your data volume by more like 620 times to drop the BLEVEL (not 600 as you have less wastage from block overhead with larger blocks).
To reduce it by two it would be 300*150 I think, wiht 8k block size {can any statisticians, like Mr Lewis, help me out on this one?}. If I am right, that is 45,000 partitions. In that 25TB datawarehouse, no table has more than 4,000 partitions.
That is why I say, when you partition a table and have locally partitioned indexes, you might reduce the index level by 1. Might.
However, none of these systems has more than 4000 partitions per table. That might sound a lot, but it is only going to reduce an index BLEVEL by 1. Almost certainly it will, but if you have read the previous postings, that is not going to really help index lookups be that more efficient :-)
I'll just add a couple of last comments.
- If you have an index BLEVEL of 4 or 5 or more and do not have a VLDB, you might want to look at why {hints, it could be an IOT, it could be an index on several concatenated VARCHAR2 columns, it could be you are using 2k block size, it could be that you regularly delete a large pecentage of your data and then re-insert more data, it could be you have a one-table database, hehe.}.
- Point one does not mean you should rebuild said index. I almost never rebuild indexes. The need to do so is one of those Oracle myths. There are situations where index rebuilds are advantageous, but they are pretty damned rare. If you regularly rebuild all your indexes or you are thinking of doing so, can I suggest you book a "meeting" for an afternoon and sit down with a cup of tea and google/bing/yahoo or whatever and check out index rebuilding first? Ignore any sites you land on offering database health checks or training courses on cruise liners.
That's all for now.
Friday Philosophy – Memory (of the Human Kind) November 14, 2009Posted by mwidlake in Perceptions, Uncategorized.
Tags: knowledge
7 comments
One of the Joys of having been around for a while (I've hit that point where I occasionally find myself working with people who were not born when I got my first job in IT) is that you start to re-learn things. Well, I do. {I have to point out that, this being a Friday Philosophy, you will learn nothing yourself, especially not about Oracle. from this opost. If you want Oracle knowledge you might want to pop by Dion cho, Tanel Poder or Richard foote}
Several times over the last couple of weeks I have learnt something "new" and realised a little while later that I already knew it, but had forgotton (BLEVEL is one, consistent gets being impacted by array fetch size is another) . This is not something to be proud of – not knowing something you once did is far worse, and slightly worrying, then never having known it…
I like to think that it is all the new stuff I am learning, pushing previous knowledge out of my brain's buffer cache {if it is not warmed by re-using it}, but I suspect that it might just be an uptime-related memory leak. A bit like having your database open for a few months and doing a lot of work that includes massive PL/SQL in-memory tables and hash joins (pickler fetches). No one seems sure of the exact mechanics, but after a while you have a hell of a lot less spare memory in your serever than you started with :-)
Maybe the memory loss is closer to the "pickler query" analogy than I thought, you can preserve soft tissue for a long time in alcohol. I'll have another glass of wine and think on that.
This Forgetting Stuff was actually a major factor to my starting a blog. I was in the process of putting a load of notes on Oracle Odditites and things I had learnt from years past onto a web site so I could get at it from wherever I was in the world, and a friend told me I was being stupid – I should put them on a blog. So I did. There are only two little issues with this.
- I can't remember what I have already put on my blog.
- I've forgotten where I put the ZIP disk with the notes on.
So I was contemplating this drift of knowledge and two things struck me.
1) I reckon that the very best people, in any area of expertise, are blessed with excellent memories. There is a comedian in the UK called Stephen Fry, and he is renowned for being stunningly clever. I think he probably is stunningly clever, but he also remembers everything he has learnt (Rory McGrath is another UK comedian with a perfect memory, but somehow he lacks the charm of Stephen Fry, and he has a beard, so not so highly renowned).
2) My poor memory is not maybe such a bad thing. I don't have to get so upset when a whole chunk of my Oracle knowledge becomes obsolete. I used to be really good at sizing objects and utilizing space, taking into account the 5-block minimum, getting the extents to fit nicely into the datafiles, little scripts to resize segments into a small but sensibele number of extents to reduce wasted space, considering initrans, pctfree, pctused… Pretty much all pointless now :-)
Big Discs are Bad September 27, 2009Posted by mwidlake in development, performance, Uncategorized.
Tags: performance, Storage, system development, VLDB
8 comments
I recently came across this article on large discs for database by Paul Vallee. The article is over 3 years old but is still incredibly valid. It's a very good description of why big discs are a problem for Oracle Database Performance. {Paul also introduces the BAHD-DB campaign – Battle Against Huge Disks for Databases, which I quite like}.
To summarise the article, and the problem in general, IT managers will buy big discs as they provide more GB per pound sterling. It saves money.
However, less discs is Bad For Performance. As an extreme example, you can now buy a single disc that is a TB in size, so you could put a 1TB Oracle database on one such disc. This one disc can only transfer so much data per second and it takes this one disc say 10ms to search for any piece of data. If you want the index entry from one place and the table row from another, that is at least two seeks. This will not be a fast database {and I am not even touching on the consideration of disc resilience}.
Now spread the data over 10 discs. In theory these 10 discs can transfer 10 times the total data volume and one disc can be looking for information while the others are satisfying IO requests {This is a gross over-simplification, but it is the general idea}.
IT Managers will understand this 1-to-10 argument when you go through it.
Kind of.
But then discussions about how many modern "fast" discs are need to replace the old "slow" discs ensure. It can be very, very hard to get the message through that modern discs are not much faster. A 1TB disc compared to a 4-year-old 100GB disc will not have a transfer speed 10 times faster and it will certainly not have a seek time ten times less, chances are the seek time is the same. And then there are the discussion of how much impact the larger memory caches of modern storage units have. Answer,(a) quite a lot so long as it is caching what you want and (b) even if it is perfectly caching what you want, as soon as you have read a cache-sized set of data, you are back to disc IO speed.
Bottom line. Disc Drives are now slower in proportion to the disc acerage than they used to be.
Anyway, I am certainly not the only person to have had these discussions, though I have had them for longer than most {due to my accidental work history of having worked on VLDBs for so long}. There are certainly practitioners of Oracle Arts who understand all of this far better than I and one of them, James Morle, recently started blogging. It's looking good so far. If he keeps it up for a month, I'll put him on my blog roll :-)
There is, however, one aspect of the Big Disc Performance issue that does not seem to get much mention but is something I have suffered from more than a couple of times.
As a Database Performance person you have had the argument about needing spindles not disc acreage and won. The IT manager buys enough spindles to provide the I/O performance your system needs. Success.
However, the success has left a situation behind. You need 10 spindles over a couple of RAID 10 arrays to give you the IO you need. 250GB discs were the smallest you could buy. So you have 1.25TB of available storage (RAID 10 halves the storage) and have a 500GB database sitting on it. There is 750GB of empty storage there…
That 750GB of empty storage will not be left inviolate. Someone will use it. Someone will need "a bit of temporary storage" and that nice chunk of fast storage will be too inviting. Especially if it IS fast storage. It will be used.
Now your database, who's storage you specified to support said database, is sharing it's storage with another app. An app that steals some of your IO and potentially {heck, let's say this straight WILL} impact your database performance. And the galling thing? Twice, I had no idea my storage had become shared until I started getting odd IO latency issues on the database.
You may be able to make a logical argument for the spindles you need at design time. But you have almost no chance of protecting those spindles in the future. But who said working life was easy? :-)
Friday Philosophy – Friends who Know Stuff August 29, 2009Posted by mwidlake in Uncategorized.
Tags: ethics, knowledge
2 comments
Being a consultant/contractor can be hard work. You are expected to turn up on-site and become useful before the second cup of coffee has been drunk. Then, if you manage that, there is an expectation that you will continue to pull IT Rabbits out of computer Hats for as long as you are there. Which is actually reasonable, we are after all usually paid well for being consultants/contractors.
This need to always have an answer can become quite hard, especially as those of us who chose not to be permanent staff have a bit of a disadvantage, one which those who have normal jobs may not appreciate.
Permies have a group around them called "the team" who they can call upon and talk about issues. Permies tend to stay in an organisation for many years and build up strong contacts with people, who they can call upon years after they have moved on. For us nomads, it can be far harder to make strong links to people you can call upon. That is not to say most teams are unfriendly when you go on site, it is just that by the nature of starting off as a temporary member of the group and moving on after a year, 6 months, even a week or two, developing strong ties to people so that you feel able to badger them 2 years later is less likely to happen.
Don't under estimate the benefit of being able to call on old friends to get a second opinion (or of being called yourself to help assist some old friend who has got to get to grips with some section of IT knowledge that you had to deal with for 2 years). It really helps.
Some of you are probably thinking "well, you Consultant types just ask each other, the other experts that you all know". Well, sometimes, but we tend not to work with each other much. Contractors rarely get to work with each other repeatedly on different projects unless you find yourself in a position where the client needs someone with X skills and you have a friend with X skills who is looking for a new position.
This is something I have become very aware of, having gone from Contractor for half a decade, to Permy for 6 years and back again to contractor/consultant. I miss having a stable team of collegues to discuss things with.
So, Friends with Skills are important. And it is a two-way thing, if you expect to be able to call on some old collegues for help, then you need to be helpful when they call on you.
Is this a case of "who you know not what you know"? Yes and no. It is not about contacts getting you a leg up. It's about developing and keeping a group of work-related friends where you all help each other when there is a need. Proper friendship is about sharing, not using.
Learning stuff you know you don't know July 17, 2009Posted by mwidlake in Uncategorized.
6 comments
A Friday, off-topic post :-)
OK, how many people remember the much maligned speech by Donald Rumsfeld about {and I paraphrase} "things we know, things we don't know, things we know we don't know and things we don't know we don't know".
If you want, you can watch the dear chap himself saying it here. {I'm afraid this is a slightly maligning version, but not as bad as most I found}.
Leaving aside it was Mr Rumsfeld who said it and thus accounted for some of the endless scorn poured upon the speech, I think it was a very, very valid observation by his speech writers. I always think that people laughing at the "idiocy" of the speech have missed something. The idea is nothing new, Henry David Therou for example said
"To know that we know what we know, and that we do not know what we do not know, that is true knowledge."
{an excellent quote, for which I thank Wikipedia and Google – I knew I did not know any good quotes but I knew they existed and I just did not know them}.
I think it is very valid to realise you don't know stuff in your chosen area of knowledge but that you recognise that lack and that there is thus more to learn. It also means that people who do already know an awful lot in one area of knowledge, they probably have gaps you can help fill. So speak up when you think you know something, even to acknowledged experts. It gives you a really warm glow inside when you do teach somone you regard as an expert something new. And you know what? Most of those experts will be really appreciative for you even trying, let alone succeeding.
I could of course be saying all this to give my utterences about not knowing stuff a veneer of philosphical respectability, but I really do believe and have done for many years now, that we should be able to say we do not know something and it be respected that we acknowledge that gap. I don't take glory in my stupidity {for even my ego could not hold up under all that amount of glory :-) } but I like to feel that if I can be honest about my stupidity I can thus be honest about my knowledge, and be quietly happy with what I know and what I can do.
OK, so none of this is specific to Oracle but it is no less valid for being applicable to all knowledge. I guess I'm trying to say that I think it is OK {and should be publically acceptable} to admit not knowing stuff and it is equally OK and good for all of us to try and teach others, even if we feel individually that we maybe do not have that much to add.
What prompted this philosophical outpouring? I found out today that I don't even know how to peel a banana. And probably most of you don't either. But Monkeys do.
Go on, watch the video, the boss is in a meeting. It makes you think….
Cost of full table scans #2 June 30, 2009Posted by mwidlake in performance, Uncategorized.
Tags: cost, performance, SQL
2 comments
This is the second post in a series on Full Table Scans. Sorry to anyone waiting for part two, I got distracted {I suspect very few people were actually waiting :-)} I make up for it by making posting this veeeery long.
Post One – I got several comments, mostly pre-empting what I planned to say next {I am not complaining as such, I like to get feedback and Randolf Geist in particular was being very helpful}, but I'm going to say what I wanted to say anyway, especially as I was being purposefully misleading in my first post…
I'm attempting to show three things in this series of posts.
- How table scans are costed by the CBO under 10g and 11g, as it is quite different from before.
- That simple test cases of what you knew once can fail to work and leave you confused and questioning your knowledge.
- How levels of "truth" help you understand more as you dig deeper.
- As a fourth I might touch on how Oracle's calculated cost is not actually always reflecting how it executes sql, but I need to work on that a little.
If you thought that db_file_mutliblock_read_count is the number of blocks that oracle reads in one go when scanning tables and indexes and that this improves performance, that will help you understand what is going on inside your Oracle database.
If you think that increasing the value will make full table scans look more efficient to Oracle and thus it will favour them, you are again right and it may well help you tune a system.
However, you may know more – you may know that Oracle modifies the value of the MBRC to make it more realistic. As the MBRC gets larger, things like the size of the segment, hitting the end of individual extents and the physical time taken to read data off disk and chuck it across the network makes the efficiency gains of these larger values less significant. Each layer of knowledge helps a little more. Then the technology underneath shifts and you may not realise this as the older knowledge still kind of helps.
I'm not trying to be smart or "I know better" here, I've posted before that none of us knows this stuff until we are told and I certainly did not know all of what is in these posts when I started preparing them 3 or 4 weeks ago. Some of what I plan to say I am still not sure of. But I'll postpone the philosphy now for a later blog post, though as I have a rough version already written, here it is if you want.
Back to the technology and the costing of full table scans.
Here is my test script again.
It creates a couple of tables, the one of interest is TEST_BIG, holding 40,000 rows. TEST_BIG is 2304 blocks big but only 2267 blocks are occupied. As nothing has been deleted from the table, this is also the high water mark, ie the highest point in the table into which data has been inserted. Oracle keeps track of this high water mark. The table is in a locally managed, autoallocate tablespace with automatic segment space management. All the defaults.
This is my environment:-
—————————— ——————–
compatible 126.96.36.199.0
cpu_count 2
db_block_size 8192
db_file_multiblock_read_count 128
optimizer_mode ALL_ROWS
sga_target 0
sort_area_size 65536
Just to re-itterate, I have not gathered system statistics -the system is "out of the box".
From Oracle 10 onwards, there are some default system statistics set. You can see these by looking at the sys.aux_stats$ table (click on "view plain" to see it properly laid out):-
select sname,pname,pval1,pval2 from sys.aux_stats$ order by sname,pname SNAME PNAME PVAL1 PVAL2 ----------------------------------------------------- SYSSTATS_INFO DSTART 10-15-2007 11:32 SYSSTATS_INFO DSTOP 10-15-2007 11:32 SYSSTATS_INFO FLAGS 1 SYSSTATS_INFO STATUS COMPLETED SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN CPUSPEEDNW 1845.90945 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM
There are only 3 values set {on both version 11g and 10g},
- CPUSPEEDNW – an estimate of the speed of the CPU. This value is different on different servers. Maybe. Both my old V10 windows test boxes have a value of 484, even though one is a truely old laptop, yet the linux servers I have access to all have different values, ranging from 713 to 1778.
- IOSEEKTIME – This is the time for the IO subsystem to locate the start of an item. In both v10 and 11, this is 10 milliseconds
- IOTFRSPEED – This is the volume of data that can be read from the io subsystem in 1 millisecond – 4096 bytes or 4k, again in V10 and 11
These default figures for IOSEEKTIME and IOTFRSPEED are fairly conservative, especially as they have not changed since V10, but then seek time has not really improved (especially on single discs).
In my experience, and looking around on the net, it seems that many sites on V10 upwards have not gathered system stats {BTW see the end of this post for some clarification on system statistics}. This seems to be due to one of several factors:
- Some sites do not have a DBA or one who knows about system statistics {I'm not criticising, if you have been labelled a DBA and thrown the whole of the Oracle stack to look after, there is a lot to get your head around}.
- Some DBAs think the system stats are gathered by the automatic stats job that {sort of} runs every night .
- Changing to using system statistics would change execution plans and thus need full regression testing, some re-tuning, which there is no time or money for.
- Hey, it works, why change things {this can also be viewed as "I am fundamentally scared of change" :-) }.
I can understand all of the above, and as a result of them many of us benefit from understanding how the default system statistics work. I would suggest it is better to gather system statitics than not as you are giving the oracle CBO optimizer more accurate information about the actual speed of your cpu, your IO and the difference between single block reads and multi-block reads, but life is not perfect. {NB I've heard on the grapevine that you should not gather system stats if you run Oracle Apps 11i but I am no expert on Oracle Apps!}
So what does Oracle do with the "out of the box" information? It calculate values for SREADTIME {time to read a single block} and MREADTIM {the time taken to do a multi-block read. If you think about how discs work, to read something the head of the disc must locate where the data is and physically move to it – this is the seek time – and then read the data.
So for a single block (8k) the time taken is
IOSEEKTIME+(size of one block/IOTFRSPEED).
ie 10+(8192bytes/4096 bytes) = 12.
For a multi block read, then the disc head will seek once and then read db_file_multiblock_read_count number of blocks at once {just accept this for now, please}. In my case, 128*8k.
IOSEEKTIME+( (MBRC * Size of one block) / IOTFRSPEED)
ie 10+( (128*8192) / 4096)
= 10+(1048576/4096)
=10+(1024k/4k) =10+256 =236
I don't know about you but I can't divide 1048576 by 4096 just like that but 1024/4, yep easier.
The CBO can only cost something if it can treat all parts in the same units, so it converts those multiblock read counts to equivalent single block read counts by dividing the time for the multi block read by the time of the single read.
236/12 = 19.6667.
Oracle then rounds the figure to the next whole number, so 20.
{Incidentally, it does this with the CPU cost as well.
Oracle makes a guess at how much cpu effort an activity will take and then divides it by the CPU speed and the time for a single block read – I'm being a little simplistic here, but it will do for now}.
So, a single block read is cost 1
A multi block read of 128 is cost 20, and reads 128 times as much.
In previous versions of Oracle this was all different. In 8i, single block reads and multi block reads were costed the same. In 9i without cpu costing {no cpu costing being the default and I can't remember seeing it changed on a live system I saw} a "fudge factor" was introduced where values for MBRC were reduced down to better match reality. And in my first post I misleadingly referred to a Fudge Factor and gave a table. Anyone who was used to this fudge factor may have noticed my numbers did not match, but then who of us remembers that the fudge factor for 8 as the multi block read count is 6.19? :-)
{That table is still useful, it shows an effective ratio for the MBRC, IF you have gatherd no stats AND you have an 8k block size. OK, maybe it is not so useful…}
Anyway, as you can now see from the above formulae, Oracle on 10g and 11g now uses a fairly sensible piece of logic to decide on the relative costs of single and multi block reads. The CBO formula is basically saying you save the cost of multiple seek times when reading multiple blocks, but the time to transfer data is directly related to the volumeof data read. This is probably reasonable on a system with a simple IO setup (like single disks or very basic RAID) but will break down with high-end storage which has read-ahead and SAN caches and the like.
OK, I have to address one more issue before I demonstrate that the formula is correct. And it is something that I did not know and confused the hell out of me when I decided to do a "simple test" of what I thought I knew, and prompted me to go on endlessly about how doing a simple test to prove what you have learned can go wrong and send you crazy with confusion. And thanks again to Randolf Geist for pointing me in the right direction.
At the end of the last post I had shown that my multiblock read count was 128 but acted like it was 8.
ie I got a cost of 617 for scanning TEST_BIG. When I manually set my MBRC to 8 I also got a cost of 617. When I manually set a value for MBRC of 128, it gave a cost of 395. Huh?!
Coskan alluded to this bit, but I missed it – I was ignorant about what he was saying.
On a newly created Oralce 11.1 test database, created as part of a simple instal on windows vista, you have db_file_multiblock_read_count set to:-
show parameter db_file_multiblock_read
NAME Value
db_file_multiblock_read_count 128
But there is a hidden parameter:-
—————————- —– —–
_db_file_optimizer_read_count 8
multiblock read count for regular clients
{To see hidden parameters you need to be logged in as sys or use a trick like intermediate views – here is a script you can run as sys to see them. Warning, there are a LOT of them! I'll do yet another post about hidden parameters sometime soon.}
How the hell are we supposed to know this???! Do Oracle do this on purposed to keep us awake at night worrying about this???.
On Oracle 10, this hidden initialization parameter defaults to 8 and MBRC defaults to 8 {usually – I just recreated a new test 10.2.0.1 database and both are set to 16 "out of the box"}. If you change MBRC, the hidden parameter alters too, to the same value, so you do not notice it so much.
On 11, MBRC is set to 128 and the hidden parameter is set to 8 initially. Once you change MBRC, the hidden _db_file_optimizer_read_count is also changed to match.
So, as my first paragraph on the first posting said, you go on to a test database to check something and you get confused as the very first "simple" test does not give you the results you expected.
OK, does the formula work?
In the below table I list:
- The MBRC that I set by altering my session
- The cost as reported by Explain Plan
- The calculated MREADTIM = IOSEEKTIM+ (MBRC*blocksize/IOTFRSPEED)
- The calculated SREADTIM = IOSEEKTIM+(blocksize/IOTFRSPEED)
- MREATIME/SREADTIM { I'll call it MT/ST}
- Blocks in table/MBRC {I'll call it blks/MBRC
- (blks/MBRC) * (MT/ST) to get a calculated cost in single block reads
MBRC Cost MRT SRT MT/ST blks/ Calc cost mbrc 2 1325 14 12 1.167 1133.5 1323 3 1010 16 12 1.333 755.667 1008 4 853 18 12 1.5 566.75 851 6 695 22 12 1.833 377.833 693 8 617 26 12 2.167 283.375 615 10 569 30 12 2.5 226.7 567 12 538 34 12 2.833 188.917 536 16 499 42 12 3.5 141.688 496 20 475 50 12 4.167 113.35 473 24 459 58 12 4.833 94.458 457 32 440 74 12 6.167 70.844 437 33 438 76 12 6.333 68.697 436 64 410 138 12 11.5 35.422 408 128 395 266 12 22.167 17.711 393
Yes :-)
The calculated cost is always 2 less than the cost given by the CBO. I know that under Oracle 10 1 is added to the cost for reading the table header. I am presuming that an additional 1 has been added for some reason under 11.
That will do for now.
Oh, as promised, some comments on system statistics
Oracle System statistics are NOT statistics collected on the tables and indexes owned by users SYS or SYSTEM. These would be "Dictionary Statistics". These can be collected via specific calls to the DBMS_STATS.GATHER_DICTIONARY_STATISTICS and {if you have not turned off the automatics gathering of object statistics} are gathered automatically during weekday nights and over the weekend by the automated job, which gathers table and index stats when it feels they are needed. I'll be posting about that {probably at inordinate length} in a month or so.
System Statistics are statistics about how the underlying computer system works, specifically cpu speed and IO subsystem performance.
Even Oracle Support can get confused over this. You might like to check out this unfortunate experience on the topic. I have to say, I have much sympathy with Piet as I had very similar issues trying to discuss automated stats gathering with Oracle a couple of years ago. Having said that, I in the end got good support on the topic.


text:
Trading Companies and Distributors
Company Overview of Univar USA Inc.
Company Overview
3075 Highland Parkway
Suite 200
Downers Grove, IL 60515
United States
Founded in 1924
Key Executives for Univar USA Inc.
Univar USA Inc. Key Developments
Univar USA Inc. has completed the issuance of its $2.05 billion USD Term Loan B and €250 million EUR First Lien Term Loan and that, separately, Univar USA has closed its offering of $400 million aggregate principal amount of 6.75% Senior Notes due July 15, 2023. The New Term Loan Facilities mature in July 2022 and bear interest based on, at Univar USA's option, LIBOR plus 3.25%, subject to a LIBOR floor of 1.00%, or Base Rate plus 2.25%. There was no material change to Univar's total or secured leverage as a result of the borrowings under the New Term Loan Facilities and the issuance of the Notes. Merrill Lynch, Pierce, Fenner & Smith Incorporated, acted as lead arranger of the New Term Loan Facilities, together with additional arrangers. The company intends to use the net proceeds of the Notes offering, together with the borrowings under the New Term Loan Facilities and cash on hand, to refinance certain existing indebtedness under its existing senior secured credit facilities and pay related transaction costs. The Notes were offered in a private offering exempt from the registration requirements of the United States Securities Act of 1933, as amended. The Notes were offered and sold only to qualified institutional buyers pursuant to Rule 144A and to certain persons outside the United States pursuant to Regulation S, each under the Securities Act.
Univar USA Inc. has priced $400,000,000 aggregate principal amount of 6.75% Senior Notes due 2023. The Notes will mature on July 15, 2023, will have an interest rate of 6.75% and will be issued at 100% of par value, providing a yield to maturity to investors of 6.75%. Interest will be paid semi-annually on the 15th day of January and July beginning January 15, 2016. The offering is expected to close on July 1, 2015, subject to customary closing conditions.
Univar USA Inc. announced it has reached an agreement with Penford Products Co. to become the exclusive commercial distributor of PenCare DP cationic polymer. PenCare DP is a proprietary, naturally based cationic polymer with optimal deposition and enhanced conditioning properties to leave hair and skin soft and smooth.
Similar Private Companies By Industry
|Company Name
|Alliance Barter, Inc.
|United States
|Green Light Energy Services, Inc.
|United States
|Polsinello Fuels Inc.
|United States
|Robert Yost Enterprises, Inc.
|United States
|ITP Rail Associates, Inc.
|United States
Recent Private Companies Transactions
July 16, 2015
|Chemical Associates, Inc.
April 10, 2015
|Key Chemical, Inc.
Most Searched Private Companies
|Company Name
|Geographic Region
|Lawyers Committee for Civil Rights Under Law
|United States
|Bertelsmann AG
|NYC2012, Inc.
|United States
|Rush University
|United States
|Citizens Budget Commission
|United States
Sponsored Financial Commentaries
Sponsored Links
To contact Univar USA Inc., please visit www.univar.com/us. Company data is provided by Capital IQ. Please use this form to report any data issues.
Our data partners will research the update request and update the information on this page if necessary. Research and follow-up could take several weeks. If you have questions, you can contact them at email@example.com.


text:
Because they were domesticated, the horses supplied meat year-round and vitamin-rich mare's milk from spring through fall. "No one in their right mind would try to milk a wild mare," said Olsen.
There is also evidence that the Botai were carrying a lot of heavy material, like rocks and large skulls, over long distances. That is a lot more practical and explicable if they used pack horses.
Later people of the same region adopted shepherding and cattle raising, said Olsen. That created a more nomadic culture, since sheep and cattle are not well suited for sub-zero climates and therefore needed to be taken south in winter. The tradeoff, she says, was that cows and sheep give far fattier milk year round, which can be made into yogurt and cheese. Sheep also provide wool.
Kazakh people today still eat horsemeat. They were forced to abandon their nomadic lifestyle during the Soviet era and have returned to small village pastoralism, Olsen says.


text:
BPL Community Profile
Dustin Short (upalachango) - BPL Lifetime Member
You cannot send this user a personal message (PM) because they have not specified a PM email address yet.
All Forum Posts by Dustin Short
Showing 161 - 170 of 1235 | Page: 1 ...8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 ...124
Re: GoLITE Bitterroot IS ON SALE RIGHT NOW
Not XS in W's, of which I wanted two =/
Oh yeah, the Grand Canyon is also great this time of year. Sure there'll be snow on the rim most likely, but once you descend a bit the weather should be actually really nice, if not warm.
Re: AZ or NM winter hikes...no snow
The Highline Trail near Payson may be snow free. It follows the bottom of the Mogollon Rim so stays below 6K for a lot, with a ton of SW exposure to stay warm. Other than that you could make some l...
Re: montbell
Basically any jacket of this weight will only be warm to around the freezing point depending on layering (my UL parka was good to about 40 usually). The MB UL in medium should fit you well and ...
Re: 0F Bags
Fill power is simply a measure of the quality of down. Specifically it's the volume of space that an ounce of down occupies after being weighted. So a higher fill power (say 800+ vs 650) will r...
Re: Re: Re: Re: Re: Eddie Bauer Peak XV specs
Yeah, I've been drooling over the Neutrino Plus for a few years now and I bought the trigger when I saw it on sale recently (not the usual deep discount but I'm employed again...). Still at...
Re: Backwoods.com 25% off including WM
So I bought a WM Ultralite a few years back on one of the backwoods sale and was very pleased with the purchase. Well that bag was stolen from my car recently and now I get to replace it again on s...
Re: Re: Re: Eddie Bauer Peak XV specs
Dang you Stephen, now you're giving me a touch of buyer's remorse on my new Rab Neutrino Plus!
Re: Fit
I agree with much the previous posters but I also want to add that I wouldn't trust the 5R rating of the sleeping pad. Maybe in ideal lab situations but not with someone actually laying on the ...
Re: lightweight fleece jacket suggestions?
If you can get a Cap 4 on sale, do it. I just got a hoody today and it's definitely a special garment compared to standard grid fleeces! I foresee mine getting lots of double duty and may event...
All Reader Reviews by Dustin Short
Dustin Short has posted no reviews.


text:
December 10, 2013
Quaker Steak & Lube announced it signed an area development agreement with Bosselman Food Services Inc. to open two locations in Nebraska and one in Iowa.
Bosselman Food Services Inc. plans to open one Quaker Steak & Lube restaurant per year in 2014, 2015 and 2016. Located in Grand Island, Neb., Bosselman Food Services Inc. has targeted the areas of North Platte, Lincoln, Grand Island-Hastings-Kearney, Omaha, Neb. and Des Moines, Iowa as potential development sites of their Quaker Steak & Lube restaurants.
We offer e-newsletters that deliver targeted news and information for the entire fleet industry.
The expert, Mark Betner from CITGO, will answer your questions
The expert, Rence Oliphant from Hendrickson, will answer your questions
Set of Tools Cleans Male Pins on Terminals
SecureFuel Provides Transparent Fuel Payments
McLeod Offers 'Branded' Driver App for Fleets
Paccar Announces MX-11 Diesel for North America


text:
Leave a Review
I really liked this recipe. Fresh and flavorful! I took the advice of "milong11" and used 3 Tablespoons olive oil. I didn't want to give up all the lemon juice, though, so I used 2 T. red wine vinegar and 1 T. lemon juice. I also substituted cilantro for parsley. I'd definitely make again.flag if inappropriate
No garlic? hmm.flag if inappropriate
I make this all the time. However, mine is a little different. I use Red Bell Peppers not only for color but to sweeten the taste. I broil till slightly chared, cool and peel the skin off. Then follow the recipe. The other thing I do is replace PArsley with Cilantro, again for flavor. Reduce the Olive Oil to 3 tablespoons and then replace the Lemon Juice with Red Wine Vinegar. Let sit overnight mix occasionally and serve. It is the best, more Spanish like my mom makes but with my twist.flag if inappropriate
I did add salt and pepper to my dressing, and I added a yellow pepper as well for color. I took it on a picnic with 7 adults and everyone enjoyed it.flag if inappropriate
By another name this is a Greek salad without the feta and with a simple lemon juice dressing instead of red vinegar. Perhaps an over-simplified recipe -- not even salt or pepper recommended. The outcome reflects the simplicity -- nothing memorable. In fact, basically forgettable.flag if inappropriate
This is really nice with good quality fresh vegetables. (I also tried some with harissa as suggested by the previous reviewer and it is excellent. It does, however, completely change the dish - it gives depth but takes away the bright, refreshing quality.) It was a perfect side for a chicken tajine.flag if inappropriate
The only thing I did to change this is that I used an ordinary cucumber...added a little celery...and a little Harissa. My husband loved it!flag if inappropriate


text:
Dallas, TX (PRWEB) July 31, 2013
CityVoter announced the 150 winners of its online "Dallas A-List" Awards last week, and Park Cities Pet Sitter, Inc. was named as the winner of the Best Pet Sitting Company in North Texas.
The "Dallas A-List" Awards is an online-voting contest that lists 6600 different nominees over 150 different categories. Local businesses first nominate themselves to be considered in their category, and then encourage their patrons to go online to vote for them over a multi-week voting period.
Over 40,000 votes were cast to determine the 150 winners of their respective categories. Park Cities Pet Sitter President, Joette White, was delighted to learn of her company's win in the Best Pet Sitting Company category. "We are thrilled to learn that our fans voted us as Best Pet Sitting Company in Dallas. All of our sitters work so hard every day of the year to provide the best pet care to our clients, and it is wonderful to receive this validation from them."
Park Cities Pet Sitter has won a variety of "Best Pet Sitting Company" awards in its twenty year history. In addition to its recent Dallas A-List Awards win, Park Cities Pet Sitter has also won Best Pet Sitting Company in Dallas awards from The Dallas Observer, D Magazine and Dallas Voice publications, as well as "Super Service" awards from Angie's List for both Pet Sitting and Dog Walking.
Ms. White says that winning these awards continues to motivate her and her staff to provide the best possible service to their clients. "Everyone loves to be recognized when they do a great job, and I am proud to say that my pet sitters have been providing great service for ma
|Source: PRWeb
Copyright©2012 Vocus, Inc.
All rights reserved


text:
Black Patches on the noseband??
What is the black patch on the noseband:
a) called
b) for?
Thank you! :D
Sorry the massive size of the picture, i'm no computer geek and dont know how to make it smaller! :oops:
It's a nasal strip, like a Breathe Right for humans, to keep the airways open and respiration efficient during hard aerobic exercise.
^ They work remarkably well, by the way. I ride a gelding that has some roaring problems in his older age and with a nasal strip his breathing sounds almost 100% normal even right after a cross country run.
And just to add to bubba and strange...
The patches are NOT on the nose band, but were applied directly to the horse. The bridle just sits over top of it.
Brill :-) thanks everyone!!
|All times are GMT -4. The time now is 06:50 AM.
Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.6.0