Search

Top 60 Oracle Blogs

Recent comments

February 2009

Dad, do I really need math?

My kids are pretty good about their math homework. They seem to enjoy it for the most part. It wasn't always that way. When the going gets tough, the natural human response, it seems, is to quit. So at times in our kids' school careers, their Mom and I have had to hang tough with them to try to make them do their homework. (The credit here belongs to their Mom.)

I remember when I was in school, the prevailing attitude in the classroom was, "When are we ever going to need to know this?" The much sadder one was, "My Mom and Dad said that I'm never going to need to know this stuff."

I couldn't have told you, when I was 10 years old, that I'd need to understand queueing theory one day in order to finish an Oracle project I had to do for Fidelity Investments. Or that I'd be able to win a Jim Sundberg autographed World Series baseball by using the distributive law of multiplication in my head while he was showing 400 people how Gaylord Perry liked his signs on the mound. It didn't matter to me, because I just had faith that there was a good reason I was supposed to learn everything I could in school. Having that particular faith was no accident.

I don't remember my Mom and Dad ever forcing me into doing math. I knew, of course, that it was My Job to do as well as I could in school ('A's are loafing unless they're '100's). But I don't remember ever feeling forced.

Security, Forecasting Oracle Performance and Some stuff to post… soon…

I’ve been busy this February “playing around/studying” on the following:

1) Oracle Security products (Advance Security Option, Database Vault, Audit Vault, Data Masking, etc. etc.). Well, every organization must guard their digital assets against any threat (external/internal) because once compromised it could lead to negative publicity, lost revenue, litigation, lost of trust.. and the list goes on.. I’m telling you, Oracle has a lot to offer (breadth of products and features, some of them are even free!) on this area and you just need to have the knowledge to stitch them..

60000 bind variables?! Maybe it’s time to use a temporary table instead…

I just noticed a bug 8277300 filed in Metalink with following description:
ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES. Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)
I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

60000 bind variables?! Maybe it’s time to use a temporary table instead…

I just noticed a bug 8277300 filed in Metalink with following description:
ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES. Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)
I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

60000 bind variables?! Maybe it’s time to use a temporary table instead…

I just noticed a bug 8277300 filed in Metalink with following description:
ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES. Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)
I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

60000 bind variables?! Maybe it’s time to use a temporary table instead…

I just noticed a bug 8277300 filed in Metalink with following description:
ORA-7445[XTYQBCB] OCCURS DURING EXECUTING SQL THAT USES 60000 BIND VARIABLES. Wow! That’s about 100 times more bind variables in a single query than what I’ve seen in past. And I thought that query was bad!!! :)
I suspect this is a massive IN list passed to a query. Maybe it’s time to use a temporary table or a collection for passing in the IN values instead?

SQL_ID is just a fancy representation of hash value

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969


So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)

SQL_ID is just a fancy representation of hash value

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969


So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)

SQL_ID is just a fancy representation of hash value

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969


So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)

SQL_ID is just a fancy representation of hash value

…Or in other words, how to translate SQL_ID to a hash value :)

I once wrote a script to demo this in my Advanced Oracle Troubleshooting class.

Check this, I’ll run a query and then check what is its SQL_ID and HASH_VALUE from V$SQL:

SQL> select * from dual;

D
-
X

SQL> select sql_id, hash_value from v$sql
  2  where sql_text = 'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969


So, V$SQL reports the real SQL_ID and HASH_VALUE above.

Now lets use my i2h.sql script ( i2h stands for: sql Id to Hash value ):

SQL> @i2h a5ks9fhw2v9s1

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

It works! :)