Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    5

    Exclamation Unanswered: SQL PLUS Query Execution Time

    I am new to SQL and I am using ORACLE SQL PLUS - I need to report the query execution time. There are so many pages that I can not get to the end. When I change preferences to single page, it get a message that says my query is too large. Is there a statement I can use to get to the end of my query to report the execution time? My Set Timing On is on the first page of programming and I can view the lapse time on all of the pages before this one. Here is my coding:

    SELECT MY_PRODUCTS_CE2.PROD_ID, PROD_DESC, YEAR, MONTH, AMOUNT_SOLD, QUANTITY_SOLD, AVERAGE_SALE_PRICE, PROD_CATEGORY

    FROM MY_PRODUCTS_CE2,
    PRODUCT_SUMMARY_V

    WHERE PROD_CATEGORY IN ( 'Electronics' , 'Photo' ) ;

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    When MY_PRODUCTS_CE2 contains M rows and PRODUCT_SUMMARY_V contains N rows, your query will return M*N rows (all row combinations).
    It is because you did not join rows from both tables (specify, which rows shall be put together).
    As you did not post the table structures and the desired result, you shall find this condition yourself.
    As you did not post your Oracle version, you shall search the Oracle documentation for more details (SQL Reference).

    > When I change preferences to single page, it get a message that says my query is too large.
    What exactly did you do? Do you not use some reporting tool; as I am not aware about 'Preferences' whatever in SQL*Plus.

    > I can use to get to the end of my query to report the execution time?
    If the query resultset contains many rows, you shall wait till they are all written (may take a long while). Or do you want to see only the elapsed time, not the resultset?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Oct 2008
    Posts
    5
    Thank you flyboy. I was able to display the execution time by entering "show time" at the beginning of my coding. All I needed was the execution time to compare with a second run of the query.

    What are reporting tools? As I stated I am new to SQL, and my teacher is not much help. I am sorta learning on my own.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "Reporting tools" as Oracle's Reports Builder (part of the Developer Suite), or Crystal Reports, or ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •