Down for the Count – Multiple Choice Quiz

8 08 2010

August 8, 2010 (Modified August 8, 2010, August 10, 2010)

I am not much of a supporter of True or False type questions, nor do I much care for multiple choice type questions.  It seems that essay questions are usually the only appropriate type of questions on exams.  Take, for example the following question that might appear on a SQL exam:

Three, and only three, user sessions are connected to the database.  Session 1 creates 2 tables using the following commands:

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE TABLE T1(COL1 NUMBER);
CREATE TABLE T2(COL1 NUMBER);

These are the only tables in the database named T1 and T2, as shown by the following:

SELECT
  TABLE_NAME
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('T1','T2');

TABLE_NAME
------------------------------
T1
T2

The following operations are performed in order:

In Session 1:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT;

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

In Session 2:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO T2
SELECT
  *
FROM
  T1;

COMMIT;

INSERT INTO T2
SELECT
  *
FROM
  T2;

In Session 3:

SELECT
  COUNT(*)
FROM
  T2;

What value is displayed when session 3 executes its query against table T2?
a.  600
b.  200
c.  100
d.  0
e.  All of the above
f.  None of the above

After you answer the multiple choice question, explain why your answer is correct.

————–
Edit roughly 4.5 hours after the initial publishing of this blog article: 7 people, including the first person to comment (Sean) saw a value of 1600 for answer a. – that value was modified within minutes of the first comment appearing in this article to the value 600.  The number 1600 was either a typo or an answer that I thought no one would ever select.  For fun, let’s add the following to the list of possible answers for the question… could it be the correct answer?:
a2.  1600

—————-

Edit August 10, 2010: The Test Answers:

As I stated a couple of days ago, I intend to reveal the correct answer to the question posed by this blog article.  As mentioned by Gary, just because a person (or certification board) designs a test question, that by default does not mean that the person knows the correct answer to the question.

Within roughly 15 minutes of this blog post appearing on Sunday Sean provided a very good answer with strong justification.  If I only had 60 seconds to answer the question, I would hope to be able to work out the same solution.  That said, the WordPress blog article category for this article is “Quiz – Whose Answer is it Anyway?” and answers E and F seem to be a bit of a clue that something is a bit strange about this question.  I had more than 60 seconds to think about the answer, so I will pick one of the other answers.

I suspect that several readers were a bit suspicious about the question in this blog article that just might appear on some sort of standardized test (I designed the question, so I hope that it does not).  Gary, Joel Garry, and Martin Berger stepped up to the challenge and offered suggestions regarding how there *might* be more than one answer to the provided question.  As you might guess, the numbers selected for the answers are not random.  I thought that I would share with you the thoughts that I had when putting together the potential test answers.

——

b. 200 – assumes that we are using SQL*Plus or some other tool that does not auto-commit after every DML call, have not modified the environment of the tool, and do not closed the tool after the script for each session was executed – SQL*Plus commits by default when exiting.  This is probably the right answer.

——

a. 600 – assumes that we are using a tool that auto-commits after every DML statement by default, or possibly using a database (other than Oracle) that auto-commits after every DML statement.  This could be a Visual Basic (or VBScript) program using ODBC or OLEDB (I might have stated ADO before) that does not start a transaction with a controllable end-point unless the BeginTrans method of the connection object is called first – or it could just as easily be a Java program using JDBC that does not call the setAutoCommit method of the connection object with a parameter of false.  Gary mentioned another possibility.

——

d. 0 – the question states that there are three and exactly three sessions connected to the database, so it probably is not reasonable to connect or disconnect from the database.  This is an instance where a pre-existing condition in one of the sessions might cause problems.  For example, assume that these three sessions had been connected for a while and one of the previous questions in the test asked about the serializable isolation level.  So, at some point in session 3 the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; was executed and a transaction started – maybe something as simple as: SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;  With this isolation level, once a transaction begins, the answers to all SQL statements are as of the start time for the transaction.  Session 3 will therefore see a count of 0 for table T2.

Another possibility is that either session 2 or (mutually exclusive) session 3 is connected as a different user.  The problem is that the test showed that there is only one table T1 and one table T2 in the database.  Suppose that one of the earlier test questions asked about synonyms and a table named TABLE2 was created in session 1 with a public synonym name of T2.  If session 2 is connected as a different user, it will actually insert into TABLE2 when attempting to insert into table T2, and session 3 will report the number of rows in the real table T2.  Note that the output does not show whether or not the inserts were successful, so it is possible that session 2 could not resolve the name T2 and returned an error.  If session 3 is connected as a different user, it will report the number of rows in table TABLE2, rather than T2.

Another possibility is that either session 2 or session 3 is connected as a different user and a public synonym points to a view created on table T2 that includes a WHERE clause of COL1=0.  Note that the output does not show whether or not the inserts were successful, so the view could have been created WITH CHECK OPTION.

——

c. 100 – the easiest way to obtain this value is if session 2 had at some point issued the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; and already had an in-process transaction (maybe SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;).

A second possibility of obtaining a value of 100 is if at some point in the past session 1 executed the following commands:

CREATE OR REPLACE VIEW VIEW_T2 AS
SELECT
  *
FROM
  T2
WHERE
  COL1<=50;
 
GRANT ALL ON VIEW_T2 TO PUBLIC;
 
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR VIEW_T2;

In this case, sessions 2 and 3 are logged in as the same user, which is different from session 1.

——

a2. 1600 – you might be able to guess how I obtained this number.  I performed the test several times, obtaining different results each time, not realizing that session 1 could not drop tables T1 and T2 because session 2 had an active transaction referencing one or both of those tables (maybe you could not guess how I obtained that number).

How else might you obtain a value of 1600?  Assume that at some point in the past session 1 executed the following commands:

CREATE TABLE TABLE2(COL1 NUMBER);
GRANT ALL ON TABLE2 TO PUBLIC;
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR TABLE2;
 
CREATE OR REPLACE TRIGGER T2_POPULATE AFTER INSERT ON TABLE2
BEGIN
  DELETE FROM T2;
 
  INSERT INTO
    T2
  SELECT
    ROWNUM
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1600;
END;
/

Now every time a transaction inserts into table TABLE2 all rows will be deleted from table T2 and 1600 new rows will be inserted into table T2.  Session 1 and session 3 are connected to the database as the same user.  Session 2 is connected to the database as a different user.

——
 
e. All of the Above – all answers were obtained without violating what the question asked, so I guess that e is the correct answer.

——

f. None of the Above – the count of table T2 will produce a single resulting value, not 5 values, and the value returned could very well be something other than the 5 values listed, depending on the prior state of the database.

————————

How else might you obtain the different potential answers listed for the test question?


Actions

Information

11 responses

8 08 2010
Sean

b.
because:
session 1 inserts into t1 100 rows, commits, inserts into t1 100 more, transactions still open
session 2 inserts into t1 100 rows, inserts into t2 its own 100 rows in t1 plus 100 committed rows by session 1 (committed read), then commits, inserts into t2 200 rows, transaction still open
session 3 counts 200 committed rows in t2.
I am assuming that inserted rows row locks will not be escalated to exclusive locks on t1,t2. I reserve the right to be wrong :)

8 08 2010
Charles Hooper

Sean,

That was a very quick answer. I will wait a couple of days before stating which answer is correct (I must have the correct answer if I designed the test question, right?).

This question seems to be very specific, yet ask yourself what the question does not state.

8 08 2010
Gary

Steven Feuerstein has been facing these with his PL/SQL Challenge. There’s a bunch of assumptions that go with it (no hidden triggers etc), and he still has to do the odd ‘fix’. And no, designing the question doesn’t always mean you know the answer.
IF you have done a SET AUTOCOMMIT ON in SQL*Plus then session 1 would have inserted 200 row into T1, followed by session 2 inserting another hundred. Those three hundred would have been copied to T2 twice, giving answer (A).

In 11gR2, with deferred segment creation, you can have the strange situation where you can successfully create a table (in your default tablespace) but not have any quota on it. In which case any attempt to insert will fail (when it tries to create the segment). So case (D) can be the answer. Oh, and in 8.0 (maybe 8i) the CONNECT BY LEVEL didn’t work, so that would give (D) as well

Haven’t worked out how to get (C) yet without some sort of constraint.

You can probably cheat with your own version

8 08 2010
joel garry

d. 0
b. 200

I noticed that if I didn’t have all the sessions going at the start of the test, it was 0. If I did, it was 200. But the question seems to say all three sessions would be going at the beginning of the test.

Oooooohhh… Statement level transaction consistency, each session sees the db as of the start of its own transaction. So that can depend on other unstated factors… like what-all you have in a global or local sql login file.

I would have answered b had I not tried it. But I still have a nagging feeling I’m forgetting about something else.

9 08 2010
Martin Berger

Charles, what’s your goal with this quiz? Do you want to show how useless are multiple choice questions if not all possible parameters are defined? Or do you want to get as many possible methods to create any of these numbers by additional setups not mentioned in your quiz?
I managed to get the numbers 0, 200 and 600 by playing around with the startup time of the sessions and ‘alter session set isolation_level=serializable;’. If you consider this statement directly applied before does not match the quiz rules, I’d could make it with a login.sql file or a login trigger. These should match the rules.
Other methods whould be different users (the quiz only talks about sessions) and views/synonyms which can make any result possible.
As next idea I’d like to play with FGAC. This could make even more possibilities!

9 08 2010
Charles Hooper

Martin,

Good question regarding the goal of this quiz. Several years ago I was state certified to teach mathematics and computer science classes. One of the interesting parts of teaching is discovering how an answer other than the “right” answer was obtained by the people taking a particular test – more or less by reviewing the work of the person answering the questions it is quite possible to discover that more than one answer is correct.

I read Cary Millsap’s recent blog articles about showing one’s work (the intermediate steps to the answer), heard about some of the difficulties with the PL/SQL Challenge mentioned by Gary, and I am currently reading Tom Kyte’s latest book – those were the inspirations for this blog article. I thought that this quiz would offer a chance to stop, think, and understand how the different answers might be obtained – that 100 number is hard to obtain.

All 3 of the sessions were connected at the start of the test script. What are some of the items that we do not know (that were not already mentioned):
* Were the sessions connected using SQL*Plus or something like JDBC, ODBC, or ADO that automatically commits by default
* Were the sessions, if SQL*Plus was used, closed after executing each set of commands
* You and Joel mentioned serializable – do we know if the sessions were just connected to the database, or could it be possible that the sessions had just completed another experiment that left an in-process transaction in place.
* What do we know about the pre-existing database – there might be views and public synonyms hanging around.

———————
Edit:
The original comment was posted using a netbook with a very small screen – I must have over-looked some of Martin’s response when typing the last bullet point. The question is more or less how is it possible to obtain different answers for this particular multiple choice question. Martin, I would like to see the answers using FGA and the other solutions that you develop.

If I had just 60 seconds to answer the question, I would try to produce an answer as clearly written as the first comment. If I had more than 60 seconds to answer the question I would consider picking one of the other answers…

What if… this is not an Oracle database? The CONNECT BY LEVEL syntax is probably Oracle Database specific, but what if it were not (or an equivalent syntax were used) and the database was actually SQL Server, Informix, or Sybase that commit by default after every DML statement (reference page 284 in Tom Kyte’s latest book).

9 08 2010
Flado

> What if… this is not an Oracle database?
Yeah, that was the first ‘gotcha’ I thought about… but the select from DBA_TABLES killed that idea even before I got to connect by
Cool question, though.

17 08 2010
Martin Berger

Charles,
sorry for the late reply.
I found some minutes to develop the FCA method:

assume (for whatever reason) this package and trigger exists in the sandbox-DB where you are running the tests:

CREATE OR REPLACE PACKAGE my_select_package AS
FUNCTION my_select_rownum (p_schema in varchar2,
                      p_object in varchar2 ) return varchar2;
END my_select_package;
/
 
CREATE OR REPLACE PACKAGE BODY my_select_package AS
function my_select_rownum( p_schema in varchar2,
                                p_object in varchar2 ) return varchar2
as
begin
return ' rownum  ora_dict_obj_owner,
    object_name     => ora_dict_obj_name,
    policy_name     => 'MY_SELECT_TRIGGER',
    function_schema => 'SYS',
    policy_function => 'MY_SELECT_PACKAGE.MY_SELECT_ROWNUM',
    statement_types => 'select' ,
    update_check    => FALSE );
  END IF;
 
END ddl_trig;
/

with this approach you can do nearly anything. With some more logic and a status table you can even give all answers in a row, for each repeated test run. ;-)

18 08 2010
Martin Berger

something went wrong during copy/paste.
there is a problem with ‘return rownum “less than” 101′.
here the correct code: (executed as sys before the rest of the testcase)

CREATE OR REPLACE PACKAGE my_select_package AS
FUNCTION my_select_rownum (p_schema in varchar2,
                      p_object in varchar2 ) return varchar2;
END my_select_package;
/

CREATE OR REPLACE PACKAGE BODY my_select_package AS
function my_select_rownum( p_schema in varchar2,
                                p_object in varchar2 ) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
return ' rownum < 101';
end;
END my_select_package;
/

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
if ora_sysevent = 'CREATE' THEN
  dbms_rls.add_policy
    ( object_schema   => ora_dict_obj_owner,
    object_name     => ora_dict_obj_name,
    policy_name     => 'MY_SELECT_TRIGGER',
    function_schema => 'SYS',
    policy_function => 'MY_SELECT_PACKAGE.MY_SELECT_ROWNUM',
    statement_types => 'select' ,
    update_check    => FALSE );
  END IF;
END ddl_trig;
/
18 08 2010
Charles Hooper

Martin,

Thank you for providing the code again – I was a little confused by the code (in your previous reply) that remained after WordPress deleted the code between the less than and greater than signs. This feature has damaged my comments a couple of times also. “& l t ;” without spaces will be understood as a less than sign, while “& g t ;” without spaces will be understood as greater than.

That code is impressive. If someone were to create the trigger with the rest of your code, every new table will automatically adopt the security policy – that is something that certainly could be forgotten.

Here is a demonstration of the fine-grain access control code in action:

SQL> create table t4 as select rownum rn from dual connect by level<=1000;
 
Table created.

SQL> select * from t4;

        RN
----------
         1
         2
         3
...
        97
        98
        99
        RN
----------
       100
 
100 rows selected.

A bonus side-effect is that the optimizer mode might change to FIRST_ROWS_100 when the SQL statement is excuted by a normal user, but remain unaffected when run by SYS. The optimizer mode did not change on 11.2.0.1, but that does not mean the same results will be achieved in older release versions.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
 
SQL_ID  dvrswdm4s3tuu, child number 0
-------------------------------------
select * from t4
 
Plan hash value: 1934969519
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |     2 (100)|          |
|   1 |  VIEW               | T4   |   100 |  1300 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T4   |  1000 | 13000 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<101)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
SQL> SELECT NAME,VALUE FROM V$SQL_OPTIMIZER_ENV WHERE SQL_ID='dvrswdm4s3tuu' ORDER BY NAME;
 
NAME                                     VALUE
---------------------------------------- -------------------------
_pga_max_size                            409600 KB
active_instance_count                    1
bitmap_merge_area_size                   1048576
cell_offload_compaction                  ADAPTIVE
cell_offload_plan_display                AUTO
cell_offload_processing                  true
cpu_count                                8
cursor_sharing                           exact
db_file_multiblock_read_count            128
dst_upgrade_insert_conv                  true
hash_area_size                           131072
is_recur_flags                           0
optimizer_capture_sql_plan_baselines     false
optimizer_dynamic_sampling               2
optimizer_features_enable                11.2.0.1
optimizer_index_caching                  0
optimizer_index_cost_adj                 100
optimizer_mode                           all_rows
optimizer_secure_view_merging            true
optimizer_use_invisible_indexes          false
optimizer_use_pending_statistics         false
optimizer_use_sql_plan_baselines         true
parallel_autodop                         0
parallel_ddl_mode                        enabled
parallel_ddldml                          0
parallel_degree                          0
parallel_degree_limit                    65535
parallel_degree_policy                   manual
parallel_dml_mode                        disabled
parallel_execution_enabled               true
parallel_force_local                     false
parallel_max_degree                      16
parallel_min_time_threshold              10
parallel_query_default_dop               0
parallel_query_mode                      enabled
parallel_threads_per_cpu                 2
pga_aggregate_target                     2048000 KB
query_rewrite_enabled                    true
query_rewrite_integrity                  enforced
result_cache_mode                        MANUAL
skip_unusable_indexes                    true
sort_area_retained_size                  0
sort_area_size                           65536
star_transformation_enabled              false
statistics_level                         typical
total_cpu_count                          8
transaction_isolation_level              read_commited
workarea_size_policy                     auto

I know that this was just a quick demo, but why can’t I create a new user now? :-)
Continuing immediately after creating the trigger:

Trigger created.

SQL> CREATE USER TESTUSER IDENTIFIED BY CRASH;
CREATE USER TESTUSER IDENTIFIED BY CRASH
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-28103: adding a policy to an object owned by SYS is not allowed
ORA-06512: at "SYS.DBMS_RLS", line 20
ORA-06512: at line 3


SQL> DROP TRIGGER DDL_TRIG;

Trigger dropped.

SQL> CREATE USER TESTUSER IDENTIFIED BY CRASH;

User created.

There is a way to check the type of object that is being created, so it would be possible to make certain that the policy is only automatically applied to tables that are being created. For those who decide to try this experiment, please make certain that you drop the trigger after the experiment ends.

10 08 2010
Charles Hooper

I have updated the blog article to include the reasons why the potential answers were selected. I am impressed with the different answers that were provided, and the justifications for those answers. There were definitely some justifications that I had not considered.

How else might you obtain the “wrong” answers for the test question?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: