We have a 2-node RAC installation on 64-bit Linux. Our applications have a number of SQLPlus reports that do not specify column formats, but depend on the default column widths. The reports are in Linux script files, run as dbms_scheduler jobs. The default widths were the maximum character string size of all output rows. An example report query is:
select
lpad(incident_no,8,' ') a,
lpad(detn_dtm,10,' ') b,
'QQQ' as filter
from ti
where stts_set_dtm >= to_date('01-APR-2009','DD-MON-YYYY')
and stts_icws_id = 20
and rej_reason_id = 132
order by incident_noRecently I changed the cursor_sharing parameter from EXACT to SIMILAR, and was very surprised to see that the computation of the default column widths had changed as a result. After the parameter change, the default width was equal to the linesize, so the output contained one value per line. Even more strange was that I temporarily changed this parameter on only one of the 2 RAC instances, but the default column widths changed for jobs running on either node. Going back to EXACT immediately reverted the reports to the original format.
I know that these reports should be modified to specify the column widths, and I will put in this request, but I am very curious why the cursor_sharing parameter changed the default SQLPlus column formatting, and why both instances were affected. Where does SQLPlus get its defaults? These columns are not specified in glogin.sql or login.sql.
Thanks for clearing up this mystery.
This is easy and should be almost obvious - after thinking about it... :)
select lpad( column_that_is_8_characters, 8, ' ' ) from table;
what is the length of that output? Since the column is 8 characters at most and the lpad is for 8 characters - we know that it must be 8 characters...
select lpad( column_that_is_8_characters, :x, :y ) from table;
what is the length of that output? Hmmmm. Well, :x could be 8, but it could be 18. It could be lots of values.
Consider:
ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;
Session altered.
ops$tkyte%ORA10GR2> create table t
2 as
3 select *
4 from all_users
5 where rownum = 1;
Table created.
ops$tkyte%ORA10GR2> select substr(username,1,10) uname,
2 to_char(user_id,'999,999') u_id,
3 to_char(created,'Dy Mon DD, YYYY' ) created
4 from t cs_exact;
UNAME U_ID CREATED
---------- -------- ----------------
BIG_TABLE 58 Wed Dec 14, 2005
nicely formated, we know that uname is 10 characters. We know that u_id is 8 charaters (leading sign, six digits, a comma). We know that created is 15 - the format dictates that.
But....
ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;
Session altered.
ops$tkyte%ORA10GR2> select substr(username,1,10) uname,
2 to_char(user_id,'999,999') u_id,
3 to_char(created,'Dy Mon DD, YYYY' ) created
4 from t cs_force;
UNAME
------------------------------
U_ID
-------------------------------------------------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
BIG_TABLE
58
Wed Dec 14, 2005
goes wacky - and when we query v$sql, we see why:
ops$tkyte%ORA10GR2> alter session set cursor_sharing=exact;
Session altered.
ops$tkyte%ORA10GR2> column sql_text format a80
ops$tkyte%ORA10GR2> select sql_text
2 from v$sql
3 where sql_text like 'select substr(username%';
SQL_TEXT
--------------------------------------------------------------------------------
select substr(username,1,10) uname, to_char(user_id,'999,999') u_id,
to_char(created,'Dy Mon DD, YYYY' ) created from t cs_exact
select substr(username,:"SYS_B_0",:"SYS_B_1") uname, to_char(user_id,:"SY
S_B_2") u_id, to_char(created,:"SYS_B_3" ) created from t cs_force
Look at the overbinding happening there - ouch.
Reason #4313413412 to not use cursor sharing.
Bottom line, if you have to use cursor sharing force or similar, the following statements are true:
a) the developers of your application have a bug and need to fix it.
b) you have a security hole the size of a space shuttle (google up sql injection)
c) you can only turn a hard parse problem into a soft parse problem - you cannot FIX the underlying problem which is the developer parse like mad (making it so you cannot scale - at all - ever)