IOUG Collaborate 2012: The art of database sharding
Thanks everyone, who attended my presentation about database sharding at IOUG Collaborate. That was a lot of fun!
Sharding is an awesome technique and I’ll blog separately on it, but for now see my presentation and white paper.
Cheers
How to find if your SQL is using SPM baseline
I’ve always wondered how many of my SQLs are NOT using baselines.
Of course, when you run DBMS_XPLAN.DISPLAY_CURSOR for the statement, it can put a nicely formatted note for you, something like:
-----
- SQL plan baseline "SQL_PLAN_01yu884fpund494ecae5c" used FOR this statement
But can you find it globally for ALL SQLs in the shared pool ?
How to add a hint to ORACLE query without touching its text
If you’ve been a DBA long enough, you’ve probably seen multiple cases where certain SQL queries just refuse to cooperate.
I.e. you KNOW that the query is supposed to use index IDX1, but ORACLE stubbornly decides to take IDX2. Rats!
The usual suspect here is bad statistics and thus the usual advise is: start re-collecting them. But while this might help, the success is far from guaranteed! Moreover, with a ‘real production’ data statistics collection could take hours (days?) and you already have people screaming about slow performance … In other words, you need to fix the problem NOW and waiting until stats collection maybe fixes the problem a few hours down the road is just not an option!
How to find SPM baseline by sql_id
When you start working with SQL Plan baselines, one of the annoying things that you might find is that the main “baseline” dictionary view dba_sql_plan_baselines does not have sql_id column.
How to become regular ORACLE user when you do NOT know the password
This is a fairly common situation for a DBA: you can always connect as SYSDBA, but sometimes you really need to connect as a regular database user (i.e. to have proper environment for your queries).
If you are root in UNIX, this would be extremely easy to do:
Unfortunately, ORACLE does not have an equivalent su command and, even if you are SYS, to connect to any regular database user you need to know its password (which could be a problem).
Still, there is a simple workaround that can be applied here. Essentially, you can save user’s old password, change it to some dummy value and quickly change it back after your connection is established.
While user password hashes are no longer exposed in DBA_USERS view (starting with 11g, I believe), they are still available in SYS.USER$.
Below is the script that does exactly that (full credit goes to Matt Parker who showed me this trick).
COLUMN user_pwd new_value SZ_PWD
SET termout off
SELECT password AS user_pwd FROM USER$ WHERE UPPER(name)='&BECOME_USER';
spool '/tmp/.&BECOME_USER..CHANGE'
prompt SET termout off
prompt CONNECT / AS sysdba
prompt ALTER USER &BECOME_USER IDENTIFIED BY VALUES '&SZ_PWD';;
prompt exit
spool off
ALTER USER &BECOME_USER IDENTIFIED BY TEMPORARY;
CONNECT &BECOME_USER/TEMPORARY
host sqlplus -S /nolog @/tmp/.&BECOME_USER..CHANGE
host rm /tmp/.&BECOME_USER..CHANGE
SET termout ON
SHOW USER
undef BECOME_USER
The troubling global nature of SQL Profiles and SPM Baselines
Did you know that SQL profiles and SPM baselines collected for SQL statements in one schema can apply to “the same” SQL statements in another schema ?
They do ! And, besides, objects that these SQLs apply to do NOT need to be exactly the same … And I’m not just talking about different data values or different number of records … The objects can have different structure! Or, they can actually be of different type, i.e. views instead of tables …
The only things that matter are: SQL text and object names.
This strange behavior is summarized in the table below:
| BEHAVIOR | SQL Profile | SPM Baseline |
|---|---|---|
| “Fire” for object in a different schema | YES | YES |
| “Fire” for object with a different DATA | YES | YES |
| “Fire” for object with a different STRUCTURE | YES | YES |
| “Fire” when indexes are different | YES | MAYBE, will “fire” if the same execution plan is still produced |
| “Fire” when indexes are missing | YES | NO |
| “Fire” when TABLE is replaced by VIEW | YES | NO |
| “Fire” when TABLE is replaced by MVIEW | YES | NO |
And, if you do not believe me, read on …
What are SQL Profiles and why do we need them ?
If you use DBMS_XPLAN package to analyze execution plans for your SQL statements (and you really should these days), you might have noticed that at times the following line might be displayed along with your execution plan:
-----
- SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement
This seems to mean that:
- An external ‘helper’ object was used during evaluation of your SQL statement
- This ‘helper’ object changed (or, at least, influenced) its execution plan
While having some extra help is nice, one has to wonder: what exactly is this “profile” object ? What does it do ? And, in a bigger picture, why do we need “external” help evaluating SQL statements at all ?
Can ORACLE 11g memory_target work with AIX large pages ?
One of my readers recently asked me if ORACLE 11g can use memory_target along with AIX large pages … (Thanks Randolf!)
At the first blush this seems to be impossible. The main reason, of course, is that memory_target and large pages are used for the purposes that are, in fact, completely opposite!
- The goal of 11g memory_target is to flow memory efficiently between ORACLE SGA and PGA to the place where it is most needed. This means that SGA may reduce its size from time to time, releasing memory to the operating system
- AIX large pages, on the other hand, are designed to never leave physical memory
I hope, you can see a contradiction here …
ORACLE 11g XML alert logs. Surprise: They are actually more convenient to work with
I have to admit, I was initially very skeptical about ORACLE 11g Automated Diagnostic Repository or ADR.
Why do I need to use “special” tool to access database alert log ? (and not vi) Who decided to move trace directories to some weird location ?? (why not keep them where they’ve always been?) Why store alert records as XML ??!! (it is meant for humans, you know …)
These things have “inconvenience” and “pain in the a%%.” written all over them … And, of course, the usual ORACLE attitude: “we decided to change it, so suck it up and learn how it works” did not help much …
Old habits die hard, but lately, I’ve started to come around using ADR. It turns out that with a bit of adrci knowledge and a few tweaks in the environment, working with alert logs from a regular UNIX shell becomes not only manageable but actually (gulp!) much more convenient …
ORACLE 11g SQL Plan Management: The Dark Side of SPM. Part 4
With SQL Plan Management being relatively new, it is inevitable that many people run into problems using it.
Most of those problems are caused by our (relative) ignorance: SPM does change the way how ORACLE runs SQL statements and it simply takes time to get used to how it works. Yet, some of the consequences of using SPM are truly bizarre and will surprise many people (including, probably, some ORACLE developers).
In this post I will describe the 3 scenarios where we have recently run into problems using SPM and you can judge for yourself …