This discussion is archived
1 2 Previous Next 26 Replies Latest reply: May 15, 2012 6:06 AM by mhouri RSS

select in current mode

AlleT Explorer
Currently Being Moderated
Hi,

is there a way to get rows performing a select in corrent mode (not consistent which implies using undo).

I nedd a fast way to retrieve rows as they are and I don't care about read consistency.

Is it possible?

Regards
  • 1. Re: select in current mode
    Aman.... Oracle ACE
    Currently Being Moderated
    AlleT wrote:
    Hi,

    is there a way to get rows performing a select in corrent mode (not consistent which implies using undo).

    I nedd a fast way to retrieve rows as they are and I don't care about read consistency.

    Is it possible?
    What's the db version we are talking about? Why you want to achieve "fastness" using this approach?

    Aman....
  • 2. Re: select in current mode
    AlleT Explorer
    Currently Being Moderated
    10.2.0.4 standard edition.

    I am performing a bulk insert into a table and I need to produce a report on that table, but when my select reaches the blocks that are being inserted it starts to access undo (db file sequential read) and it slows inexorably.

    Thanks
  • 3. Re: select in current mode
    damorgan Oracle ACE Director
    Currently Being Moderated
    I understand English is likely not your first language so, if you can, please find someone with a better knowledge of our language to help you clarify what you are asking.

    But in the event this is sufficient information to help you ... this is Oracle ... you do not have the option to not care about read consistency.

    If read consistency is not important to you ... perhaps you should consider something other than an enterprise class fully functional database environment.

    BTW: I highly doubt your analysis of your situation is correct. I doubt Oracle starts accessing undo as you describe and produces a measurable affect. I recommend you look for the real issue.
  • 4. Re: select in current mode
    Aman.... Oracle ACE
    Currently Being Moderated
    AlleT wrote:
    10.2.0.4 standard edition.

    I am performing a bulk insert into a table and I need to produce a report on that table, but when my select reaches the blocks that are being inserted it starts to access undo (db file sequential read) and it slows inexorably.
    So you want to include the newly inserted and I believe, not-yet-committed rows in the report too? Why? What good that's going to do? And how did you come to the conclusion that Undo access is making you slower?

    Aman....
  • 5. Re: select in current mode
    jgarry Guru
    Currently Being Moderated
    AlleT wrote:
    Hi,

    is there a way to get rows performing a select in corrent mode (not consistent which implies using undo).

    I nedd a fast way to retrieve rows as they are and I don't care about read consistency.

    Is it possible?
    [url http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#CNCPT1312]Not possible.
  • 6. Re: select in current mode
    damorgan Oracle ACE Director
    Currently Being Moderated
    AlleT

    I think, to be more explicit, we can clearly state a couple of things as fact.

    1. Your analysis of your issue is incorrect.
    2. Within a session Oracle always gives you access to your session's uncommited data. If you wish to see uncommitted data from a different session ... then you need to get yourself some lesser product where data integrity is not considered important. Might I suggest any number of companies offering freeware.
  • 7. Re: select in current mode
    AlleT Explorer
    Currently Being Moderated
    There are 4 sessions inserting into a table T. It is a bulk insert performed using INSERT INTO T SELECT ...

    Meanwhile I have to generate a report on the same table T. The select to create this report performs a full table scan on T. This session waits on db file scattered read for a certain period of time then it starts waiting on db file sequential read.

    I consider this sequential read waits as the I/O necessary to reconstruct the blocks involved in the insert: if my analysis is wrong please let me know.

    Thanks
  • 8. Re: select in current mode
    AlleT Explorer
    Currently Being Moderated
    Could you explain why my analysis is not correct?

    Thanks
  • 9. Re: select in current mode
    damorgan Oracle ACE Director
    Currently Being Moderated
    Ouch.

    BULK INSERT takes a lock. If four sessions are simultaneously using this technique they are undoubtedly blocking each other. My advice is to stop it. The chances, with BULK INSERT, of you needing more than a single session is a very small number rapidly approaching zero.

    Knock yourself out generating the report ... but you are never going to see uncommitted data ... especially with BULK INSERT ... as you are writing above the table's high water mark.

    Your analysis is fatally flawed.
  • 10. Re: select in current mode
    AlleT Explorer
    Currently Being Moderated
    ok I understood that I can't get uncommited data.

    But I would like some more explanation regarding my approach with bulk inserts.


    I get no rows if I query dba_blockers, I see no application contention, so why do you say that those 4 sessions are blocking each other?

    I know that I could have done a single insert but I chose multiple sessions so that I could eventually rollback one insert in less time.

    Thanks
  • 11. Re: select in current mode
    sb92075 Guru
    Currently Being Moderated
    AlleT wrote:
    10.2.0.4 standard edition.

    I am performing a bulk insert into a table
    Does INSERT INTO TABLE1 table have Primary Key(PK)?
    Is SEQUENCE all or part of the PK?
  • 12. Re: select in current mode
    jgarry Guru
    Currently Being Moderated
    I think Dan is saying there should be blocking if you are doing what you say you think you are doing. Perhaps you should show us your code and we can talk more sensibly. I would guess you are just doing standard single row inserts because you are using the values clause, and you should post some relevant tkprof, and statspacks to show us what it is really waiting on.

    Edit: noticed std edition, no awr there.

    Edited by: jgarry on May 14, 2012 10:54 AM
  • 13. Re: select in current mode
    rp0428 Guru
    Currently Being Moderated
    >
    There are 4 sessions inserting into a table T. It is a bulk insert performed using INSERT INTO T SELECT ...

    Meanwhile I have to generate a report on the same table T.
    >
    What mechanism are you using to do a bulk insert? Are you really doing a bulk insert or are you just inserting a lot of data at once?

    The term 'bulk insert' generally refers to a direct-path load and has a very specific meaning and implications.
    There is no APPEND hint in the query you show and you make no mention of PARALLEL so there is no indication that you are using direct-path loads unless you are using sql*loader.

    See 'Enabling Direct-Path INSERT' in the DBA guide
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables004.htm#ADMIN01509

    Is the report generation from a fifth session? Then, as damorgan mentioned, you need to consider the restrictions mentioned in the SQL Language doc in the 'Conventional and Direct-Path INSERT' section.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm

    Two of the ones that damorgan was alluding to are
    >
    Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
    If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
    >
    Those are another indication that you are not really using bulk inserts (direct-path load).
  • 14. Re: select in current mode
    AlleT Explorer
    Currently Being Moderated
    I used the term "bulk insert" as I am using INSERT INTO T SELECT * ... as I stated above.

    I can't find any piece of docs saying that "bulk" refers to direct path. Maybe I am wrong.

    I used "bulk" because I am not performing an "INSERT INTO T VALUES (....)".
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points