Donnerstag, 30. Dezember 2010

Recently Charles Hooper posted a blog An Invalid, or Do You Just Not Want to Work. There he asked under which circumstances a SQL should be considered invalid? I tried to discuss this question in the comments there and I am still thinking about an other kind of answer. But today I'd like to show a statement, which is invalid without a hint!

here we go:(everything 11.2.0.1EE 64-bit on Linux)I was asked to provide a list of all views which have a hint (how ironic) in it. After some test I provided this one:

ERROR at line 9:ORA-31603: object "AAB_TEST" of type VIEW not found in schema "PSFT"ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105ORA-06512: at "SYS.DBMS_METADATA", line 3912ORA-06512: at "SYS.DBMS_METADATA", line 5678ORA-06512: at line 1

I did some explain plan with and without hints, so I got these 2 outputs:

1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))) 4 - access("O"."NAME"="O"."OBJECT_NAME") 7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608) 8 - access("S"."OBJ#"=:B1) 9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))) 13 - access("U"."NAME"='PSFT') 14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0) 15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL) filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL( (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW') 16 - access("O"."OWNER#"="U"."USER#") 17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) 18 - access("I"."OBJ#"=:B1) 20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit ion_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit ion_id'))) 21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#") 22 - filter(NULL IS NOT NULL) 25 - access("U"."NAME"='PSFT') 26 - access("L"."OWNER#"="U"."USER#") 29 - access("U"."NAME"='PSFT') 30 - access("O"."SPARE3"="U"."USER#") filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%') 31 - access("O"."OWNER#"="U"."USER#") 32 - access("O"."OBJ#"="V"."OBJ#") 34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit ion_id'))) filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit ion_id'))) 35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Donnerstag, 23. Dezember 2010

As a DBA, I'm not a big fan of Graphical User Interfaces.From my point of view they too often hiding more information than they visualize.As I am no GUI developer, please excuse me if I name some things not correct.

I'll show an example. Please take this screenshot. I do not own the tool myself but got this via email from a developer.

What can I see?In the top left corner I see 3 tabs, the active one is Sessions. This shows me a table with one line highlighted (sid 160). Below that table, there is something I would assume as a sub-tab. Especially the borders of that tab makes me think so, as it's 'above' the 'Sessions' tab.The sub-tab Waits is the active on. In it I see 2 Tables, Current Waits and Total Waits.At the bottom I can find a line Last refresh. I'm glad I have any timestamp anywhere.

This is all really fine. But I'm still missing something:

In general, I'd like to know the statement which generates the data I can see here. In the topmost table, I assume it will query v$session, v$process and some others to get the informations. But what's the query exactly? Even small changes can mean something different. Do you see the column CPU in the first table? I can not even imagine it's origin.

Many tools provide the ability to spool all SQLs they generate into a file. But as they do not associate the SQL to the visual representation, it's still guesswork.

I can not see which columns I do not see! Is the AUDSID important? Most of the time I'd say no. Until I have to deal with auditing

I can not adapt the query according to my needs. In the Current Waits table - I assume based on v$session_wait I am often interested in the P1, P2 and P3 values (sometimes even raw - it depends). Sometimes also the change of SEQ# is of some interest. This rapidly forces me to go to the command line, or SQL-window if you want.

But there are also clever examples of some GUIs. The one I like most (as it helps both blind people and others) is Providing Textual Descriptions of Charts. It's still not the SQL, but at least the content of a graph in re-useable form.

I have only considered of any kind of query, yet. no DML or DDL operations at the moment. Maybe it's worth another post in the future ;-)

We discussed some workarounds internally.I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.