Feed aggregator

Flushing a single cursor

Fairlie Rego - Fri, 2007-10-12 19:33
In a very intensive OLTP environment plan stability is of utmost importance as a single sub optimal query can bring the system down to its knees.

Using sql outlines and sql profiles is always not possible if you have an application with more
than a million lines of code and any one of the thousands of sql statements could have a plan flip.

In 11g there is a new procedure in the DBMS_SHARED_POOL package which helps you flush out a single cursor.
There is a typo in the syntax portion of the documentation in
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_shpool.htm#sthref8578
which I will try to get corrected in the next week.

PURGE Procedure
This procedure purges the named object or specified heap(s) of the object.

Syntax

DBMS_SHARED_POOL.KEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)
Parameters

Table 118-4 KEEP Procedure Parameters

Parameter Description
name Name of the object to keep.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.

Currently, TABLE and VIEW objects may not be kept.

flag (Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

Set to 'T' or 't' to specify that the input is the name of a type.

Set to 'R' or 'r' to specify that the input is the name of a trigger.

Set to 'Q' or 'q' to specify that the input is the name of a sequence.

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

heaps Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged

This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.

E.g

SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6

PL/SQL procedure successfully completed.

This would actually not work against a cursor which is currently executing.(pinned)

Session 1:
=========
Do a massive Merge Join Cartesian
select * from dba_objects a, dba_objects b, dba_objects c;

Session 2:
=========
Identify the sql address and hash value and try to purge the cursor..

exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs

and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1

Session 3
==========
select event,p1,p2 from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1


The p1 value here is the Hash value of the cursor we are trying to flush.

From the short stack of the process which is executing the purge API a function called kxsPurgeCursor is called which would try to take a mutex (since _kks_use_mutex_pin is TRUE by default)
The purge completes only after you cancel the sql in session 1 and exit from the same
or kill the session executing the sql.

11g on Linux x86_64 is out!

Renaps' Blog - Fri, 2007-10-12 19:15

This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

It is now available here: Oracle 11g on Linux x86_64.

I have been waiting for this release for a while since two of my databases are stuck to 32 bits as they need to send and receive data using “hsodbc” (only available for 32bit) for an ERP software.

Heterogeneous systems are quite common, and I am pretty sure that many of the enhancement requests for Generic Connectivity were focused on making them available for 64-bit platforms.
The
Metalink Note:361676.1, Is Generic Connectivity Available On The LINUX Or Windows 64-bit Platforms? , explains that Generic Connectivity will be available on Linux Red Hat 64-bit and SUSE 64-bit with 11gR1.

Another reason that makes me appreciate this new release is that I ran through several issues while trying to run Oracle with larger SGA’s on 32 bits platforms. I used RAMFS and HugePages to enable the use of 8 to 10GB of SGAs. The following links helped me in this process:

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Metalink Note: 317141.1, How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages

I am hoping that “hsodbc” will work fine on 11g x86_64; I’ll keep you posted on my findings…


Categories: DBA Blogs

ORA-06502: PL/SQL: numeric or value error: host bind array too small

Mike Moore - Fri, 2007-10-12 16:12
ORA-06502: PL/SQL: numeric or value error: host bind array too small

With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.

So, it's probably not a good idea to drop those string chunking procedures until you are sure that everybody is off of the older clients.

Friends, forgive me

Fake Larry Ellison - Fri, 2007-10-12 14:51
You woke up this morning and saw the news about our bid for BEA Systems and you thought, what the fuck? Larry lied to us a couple weeks ago when he said he didn't care about those Beaturds. He tricked us, didn't he? Well, yes. And friends, I know what you're thinking right now. Listen, I'm sorry about misleading you. Just believe me when I say that I really want to tell you what we're doing with BEA Systems. But all I can say right now is that I can't say much. It's just one of those things. So have a good weekend and try not to toilet paper my house or egg my Karmann Ghia, okay?

As if three Google jets weren't enough...

Fake Larry Ellison - Thu, 2007-10-11 23:17

Larry and Sergey and Eric just had to get another one. See here. I called Eric this afternoon to verify. He said, yes, the rumors are true. Apparently Larry wanted all three of them to have their own planes so they could each fly one and have dogfights over LA, and he wanted this new 757 so he could fly it from home with a special version of Microsoft Flight Simulator X. The best part about the 757 is that it runs on a special hybrid blend of dollar bills and ethanol. Larry's convinced that inflation is going to get so high in a couple months that the dollar won't be worth anything, so he wants to burn green backs to reduce the number of bills in circulation which will in turn help decrease inflation. Eric also said that passengers can actually fuel the aircraft while it's flying by sticking bills into the little plastic box right next to the stripper pole.

In the face of adversity, Red Hat tries to act natural

Fake Larry Ellison - Thu, 2007-10-11 13:58

It's no secret that Shit for Brains thinks he owns Linux, or at least part of it. And it's also no secret that Red Hat thinks SFB is full of shit. But every couple months Stevie B. hints at an intellectual property lawsuit just to keep everybody on their toes. This time around, however, Red Hat says it isn't going to push back. They say they're just going to smile and nod and act like everything is normal. My lawyers are following the whole thing very closely. They say that Novell caving in was a good sign and that soon enough we'll be able to sue Microsoft for violating our patents. No joke. And we're talking large sums of money here, people. When you consider the fact that every computer running Windows has a little relational database inside, you'll see the scale of what I'm talking about. So we're almost ready to start entering into license negotiations with Microsoft. We think they should pay us about $10,000 per Windows installation. That's fair, right?

Jimbo's coming to town

Fake Larry Ellison - Thu, 2007-10-11 09:46

Actually he's already been here for a while. But now he's moving the whole Wikimedia enchilada out here to the Valley. See here. I guess this means Wikipedia is out of the woods now, eh? Because last time I checked Jimbo was thinking about selling ads on encyclopedia pages. I think the real problem is that the dude can't get away from Wikipedia. He wants to be the next Bill Gates or whatever, but he just can't shake this rinky-dink non-profit he started. I hear that people always come up to the guy and call him the Wikipedia man. That and his rag-tag band of college student contributers always want him to speak at their schools. He must just hate that. And who can blame him? When you want to live on a sailboat with beautiful women hanging off each arm, you need money. Serious money. Money you're not going to be able to make with a non-profit organization. So I'm thinking about calling the guy. Maybe I'll invite him golfing and sailing and then offer him a job at Oracle. Either way, I'd like another friend. The guy's obviously smart, even if he does insist on using MySQL.

ZDNet's Michael Krigsman calls me out

Fake Larry Ellison - Wed, 2007-10-10 14:27
Dude left a comment on one of my posts earlier today. Not sure, but I think he wanted to gently remind me of "Oracle's little payroll problem over at Arizona State University." Only it wasn't our problem, Mike. It was ASU's. Seriously. Because here's the thing. Sure we sell people software and support, but we aren't going to walk customers through every single itty-bitty little step. If they go and drop all of their tables by accident, that's not our fault. It's called operator error, Mike. Read our EULA. It doesn't cover mistakes made by blonde bimbos working as ASU interns who have freak-out moments whenever they break one of their nails. So you aren't going to get us on this one, bro. Nuh uh. Not going to happen. Talk to the hand, girlfriend.

This would make a great April Fools' Day joke

Fake Larry Ellison - Wed, 2007-10-10 14:02
See here. Tom Yager over at InfoWorld says that we should all use Solaris when we can't use Mac OS X. He says that Solaris has all of the bloat of Windows Vista and none of the headaches associated with ease of use, and that's what makes it a great choice for people who aren't quite Mac people but don't want to be assimilated by Microsoft. Here's the best quote: "Solaris and OS X are both Unix, and if that's not enough, know that PowerBook, MacBook, and MacBook Pro are practically de facto choices among Sun's engineers." So if you're not sold on Solaris, the fact that Sun's engineers use it will seal the deal for sure.

Yes, I know what you're thinking. It doesn't make any sense to me either. And honestly, I'm kind of wondering whether Steve paid to have the article planted. He loves to fuck with the enterprise people.

Who needs an education anyway?

Fake Larry Ellison - Wed, 2007-10-10 11:48

So by now you've probably read all about how SAP's shitty software basically shut down the entire Los Angeles school district. If not, see here. Apparently the administrators aren't yet sure whether the problem started with the MySQL database or the SAP installation. Anyway, now there's anarchy on the streets. Seriously. Kids are out of the classrooms and in the skate parks. They're listening to live punk bands instead of those dreary teachers. And thousands of these kids are talking to our Oracle recruiters. That's right people. As I write this, our dedicated employees are hitting the streets to find the little turd-droppers. We're calling it the A.S.S. (Application Support Services) initiative. Goes like this. We go out and find young tech-savvy individuals who want to make a difference. If they know about Linux, we give them our A.S.S. kit. It's a box full of things like condoms, Playboy magazines, O'Reilly programming books, a couple of marijuana joints to help them concentrate on the O'Reilly programming books, and a bunch of other information about open source projects. Because here's the thing. Those kids don't want to go to school their entire lives. They want to change the world and help people and do something meaningful. And what better way to change the world than to work on an open source project like Tomcat? Or maybe Fedora. Anything we can use and/or support.

Gartner Day 2

Peter Khos - Wed, 2007-10-10 07:27
Sorry about the lack of updates but been busy with the Gartner Symposium. Last evening (Tuesday), it was the "Attendee Appreciation Event" which was held at Disney's Animal Kingdom where the park was partially opened for the attendees to partake. Obviously the event was "pitiful" compared to Oracle OpenWorld where last year, Oracle had Elton John as the headliner and Billy Joel for this year's Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

I love Linus

Fake Larry Ellison - Tue, 2007-10-09 22:50

But sometimes the dude can be as stubborn as a pack mule walking through Siberia in the dead of winter. Kind of like last week when he said, "You security people are insane," and then reiterated that he will add Smack to the Linux kernel. And that's cool. I mean, Linux is his dog and pony show. The problem is that Smack will probably bring a bunch of security vulnerabilities along with it.

People, this is exactly why I run OpenBSD on all of my servers at home. Those suckers are locked down. I keep them off the network, and I also have all the totally crazy shit to protect them. Fingerprint identification. Retinal scanners. Man traps. I'm telling you: The NSA has nothing on Larry's servers. Some people wonder why I go to the insane lengths I've gone to. Well, I'll tell you why. The truth is that you can never be too careful with your digital black book. Women's phone numbers are very, very important.

What's that sound coming from Facebook?

Fake Larry Ellison - Tue, 2007-10-09 21:57

BusinessWeek thinks it's the cha-ching sound coming from the cash register as developers sell their Facebook applications and head for the door. But in reality it's just the sound of thousands of ambitious geeks trying to suck from Mark Zuckerberg's teat. Seriously. Sex-deprived brainiacs beware. This Facebook App stuff has pyramid scheme written all over it. Better to become an Oracle DBA and do serious work that can change the world and make you a bundle of money.

But let me tell you about this one app called Make a Baby. I really think it has potential. Check it out. You hop on Facebook and look at people's pictures until you get a hard-on or the female equivalent. Then you hook up and make a little virtual baby that looks like the two of you. Easy, right? The best part is that you don't even need to virtually procreate with a member of the opposite sex. Man oh man. Just think of the possible combos here. Bill Gates and Mark Zuckerberg. Steve Ballmer and Jonathan Schwartz. (Sorry. Bad image. Talk about some fugly offspring.) Peter Burrows and Dan Lyons. Marten Mickos and Zack Urlocker. Or maybe even Jason Maynard and Rick Sherlund. Yes. Friends, I can see the positive energy flowing as I write this. More on these couples as they develop.

Tuning Collections in Queries [Introduction]

Vlad Sadilovskiy - Tue, 2007-10-09 18:13

[To Next Article [1]] [To Performance Study]

If you never heard of using collections instead of regular IN-LIST expressions, you might want to read varying elements in IN list article from AskTom. It unfortunately has little information about the way CBO treats collections. So, if you have problems with tuning queries that employ nested tables or just evaluating what is in the way please read on. Here is one of the basic forms of SQL queries that employ collections.

select *
  from t1
 where t1.object_id in
     (
       select *
         from table(cast(:1 as table_of_number))
     );

There are several optimization issues related to tuning queries that use collections. Many of them can be attributed to the CBO statistical assumptions. The CBO calculations and figures are a little different in 9i and 10g. However the issue might have change the appearance, but not the essence. There will be few more posts on some basic forms of queries with collections.

Over the past three years I’ve engaged with the Oracle TS several times to find a good resolution for this nuisance. That generated couple bugs: 4484645, 4235962. One of them is closed as “NOT A BUG”.


Blog theme

Vlad Sadilovskiy - Tue, 2007-10-09 16:42

I know, I know… The theme looks exactly like the one Jonathan Lewis has for his Scratchpad. Sorry, about that. I’ve tried every possibility and this is the only one that is comfortable for posting Oracle trace or code snippets. The closest thing I found that wouldn’t wrap the code is a Sandbox theme. Others would have very small or otherwise irritating font.


“Undocumented?!” – Use it or not

Vlad Sadilovskiy - Tue, 2007-10-09 00:06

Rainy day… Thoughts arn’t well aligned… At the end of the day I was wondering on the Internet searching for an inspiration. For a moment I thought I found something interesting. It was on the Jonathan Lewis’s blog – something about Oracle traces. But just a second later after finished reading I grasped what the message was about. In few words there was confusion, doubt and something else, or so it seemed. The title was Trace Files. And here you go an inspiration came, and as usual from a direction you’d never expect.

Should we use undocumented or unofficial methods? Yes, no doubt. That is of course when they are judicially legitimate. I just wanted to point out few key elements of the documented versus undocumented approach as I understand and interpret it. In a different scope this might become discussion about supported versus unsupported and so on.

Documentation is a right place to go if it exists. If it doesn’t, “hacking” in the right meaning of the word, is the way to know the system better than it can possibly be documented. Consider Oracle tracing facility as free of charge and supported debugger. By using it you’d learn about the Oracle and application that uses Oracle, about specifics of different Oracle versions, the improvements in newer releases and deficiencies in former releases.

 What you would lack when you use Oracle tracing is official evidence. If you see a problem in the trace, but struggle to reproduce it using documented forms of proof, you might have some trouble building a case for an SR. You also will have certain difficulties explaining it to the Oracle TS what is that you are trying to solve. And final point against it, when filing SR, is that in Oracle R&D work people just like you. Reverse enginiring an issue from a trace file that you attached is the last thing they wanted to do. Similar to any other trace whether that is a stack trace from JVM or a core dump. It is always better for them to have a reproducible at will test case. However, many times by looking at traces I found things to build a simple test case that I wouldn’t have gotten that easily otherwise.

To summarize, as far as I’m concerned, if the Oracle traces, considering their generation can be done using supported API, allow me to find true problems and do right decisions I’ll keep using them even if their format is neither documented nor supported.


Henning Kagermann has his ass handed to him

Fake Larry Ellison - Mon, 2007-10-08 21:50

Today's articles were so negative that it really pained me to read them. And that's saying a lot coming from me. This article from MarketWatch says that SAP just destroyed its relationship with Microsoft. And this article from Forbes says that we're smiling over here at Oracle. Believe me, we are smiling. But put yourself in Kagermann's shoes for a minute. You're sitting there watching Oracle buy all of these companies and get further and further ahead. We're literally leapfrogging over your pathetic products. You look at SAP's numbers and then you look at Oracle's numbers and then you get all panicked and shit. Beads of sweat pop out on your forehead. You know you have to do something. But what? Your old tricks don't work anymore. The engineers are working as fast as they can and your R&D department is light years behind the competition. So you think, okay, if you can't beat them you'll join them. You'll take Oracle's consolidation strategy and apply it to SAP.

And that's the heart of the problem. Our consolidation strategy isn't going to work for SAP. They're just not that kind of company. And anyway, they're too late. They purchased a company that was being shopped around and they overpaid by a couple billion. Now Microsoft is going to turn against them. And guess who's going to benefit from that? We will, of course, but also MySQL and to a lesser extent Red Hat. After us, MySQL AB probably has the most to gain, because SAP will probably develop closer ties with them after they fall out of grace with Microsoft. Changes are coming, bros.

BusinessWeek : Screech can learn a lot from MySpace

Fake Larry Ellison - Mon, 2007-10-08 13:10
See here. For those too lazy to read, the article says that Facebook isn't doing all it can do to keep kids safe from sexual predators. The article cites a bunch of reputable off-duty police officers hanging out in strip clubs. Don't ask me where BusinessWeek finds these people, okay? I have no friggin idea. But anyway, according to the police officers, MySpace has some Perl script that flags profiles with words like "kill," "rape," "shoot," "gang," "Mark Zuckerberg," and "facebook." They take all the profiles that contain those words and they delete them. Facebook doesn't do that, so it's not as safe.

I know the article sounds stupid, but it's really not. And next month BusinessWeek is going to run another article about how Facebook needs to adopt MySpace's 1995-style colors and website design. The new article will also talk about how Facebook needs to let companies pose as people, let spammers create bogus profiles, and put advertisements for strip clubs at the top of every page. Then, according to the article, Facebook will be worth over $200 billion.

CmdrTaco celebrates ten years of bashing Oracle

Fake Larry Ellison - Mon, 2007-10-08 12:36

See here. We're actually sending CmdrTaco aka Rob Malda a big ass fruitcake to help him celebrate Slashdot's tenth birthday. Why not? Taco's given us one helluva ride. All of those "Oracle Sucks Ass" articles were priceless. Too bad it's all coming to an end. Now all I have to do is send out an email message to Oracle's 70,000 employees and ask them to digg this or that. And then, boom! It's up on Digg and we get like 45,000 unique visitors within two hours. Of course, as Taco himself points out in one of those well-duh moments, "Digg is not Slashdot." Of course it's not, Rob. Slashdot is you, dude. That's why it blows goats. And that's also why your Wired interview has that big "Reddit!" button at the bottom of the page and that link to "Digg Just Might Bury Slashdot." Time for a reality check, CmdrTurd. You're not going to float for much longer.

Pages

Subscribe to Oracle FAQ aggregator