This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Jul 24, 2013 10:41 AM by Shidu Go to original post RSS
  • 15. Re: HOW TO: Post a SQL statement tuning request - template posting
    680087 Pro
    Currently Being Moderated
    One thing should be added: linesize/pagesize configruation & plan_table_output format.
    set linesize 150 pagesize 1000
    column plan_table_output format a150
    Usually it's enough for readable output.
  • 16. Re: HOW TO: Post a SQL statement tuning request - template posting
    696382 Newbie
    Currently Being Moderated
    Hi all of you,

    the problem is i'm ride at the office and having another meeting. i have not read all your statements but your jugements are allready helpfull.
    As soon as our meetng is over i'll responds to everybody with the needed informations. Lazy is not for oracle people.

    Thanks and see you soon

    Sonson_01
  • 17. Re: HOW TO: Post a SQL statement tuning request - template posting
    696382 Newbie
    Currently Being Moderated
    Hi friends,

    i'm trying now to send you send you the needed information but facing another problem:
      ORA-00933: SQL command not properly ended 
    i'm looking a contact with the developper for correcting the statement.As soon as resolve this ora- problem you'll ear me

    Thanks in advance
  • 18. Re: HOW TO: Post a SQL statement tuning request - template posting
    BluShadow Guru Moderator
    Currently Being Moderated
    Ora_Genie,

    Please start your own thread with your question, rather than messing up this valuable thread.
  • 19. Re: HOW TO: Post a SQL statement tuning request - template posting
    696382 Newbie
    Currently Being Moderated
    Hi All of you,

    Waiting the result of my query to sent you the output back but since 2 and half hour the query is still running without end.

    Because i want to send you everything you need should i stop the execution or not?

    Best Regards

    Sonson_01
  • 20. Re: HOW TO: Post a SQL statement tuning request - template posting
    BluShadow Guru Moderator
    Currently Being Moderated
    Well hurry up, We're all sitting here waiting to help you. /sarcasm
  • 21. Re: HOW TO: Post a SQL statement tuning request - template posting
    696382 Newbie
    Currently Being Moderated
    Hi Mi friends,

    Executing thew action plan plan provide by Randolf Geist

    Here is the output you requested:
     SQL> show parameter optimizer
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    _optimizer_native_full_outer_join    string      FORCE
    optimizer_dynamic_sampling           integer     3
    optimizer_features_enable            string      10.2.0.4
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE 
    .......
    .......
    
    SQL> show parameter db_file_multi
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count        integer     32
    
    .....
    
    SQL> show parameter db_block_size
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192
    
    .....
    
    SQL> show parameter cursor_sharing
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    cursor_sharing                       string      EXACT
    
    ....
     
    
    SQL> column sname format a20
    SQL> column pname format a20
    SQL> column pval2 format a20
    SQL>  
    SQL>  select
      2   sname
      3                 , pname
      4                 , pval1
      5                 , pval2
      6      from
      7                 sys.aux_stats$;
    
    SNAME                PNAME                     PVAL1 PVAL2
    -------------------- -------------------- ---------- --------------------
    SYSSTATS_INFO        STATUS                          COMPLETED
    SYSSTATS_INFO        DSTART                          11-26-2008 23:46
    SYSSTATS_INFO        DSTOP                           11-26-2008 23:46
    SYSSTATS_INFO        FLAGS                         1
    SYSSTATS_MAIN        CPUSPEEDNW           1103.68846
    SYSSTATS_MAIN        IOSEEKTIM                    10
    SYSSTATS_MAIN        IOTFRSPEED                 4096
    SYSSTATS_MAIN        SREADTIM
    SYSSTATS_MAIN        MREADTIM
    SYSSTATS_MAIN        CPUSPEED
    SYSSTATS_MAIN        MBRC
    
    SNAME                PNAME                     PVAL1 PVAL2
    -------------------- -------------------- ---------- --------------------
    SYSSTATS_MAIN        MAXTHR
    SYSSTATS_MAIN        SLAVETHR
    
    13 rows selected.
    
    
    ...
    
    SQL_ID  2swg64madgw9b, child number 1
    -------------------------------------
    select * from v$session where username is not null and status = 'ACTIVE' order by 
    logon_time, sid
     
    Plan hash value: 84683711
     
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |                 |       |       |     1 (100)|          |
    |   1 |  SORT ORDER BY            |                 |     1 |  1134 |     1 (100)| 00:00:01 |
    |   2 |   NESTED LOOPS            |                 |     1 |  1134 |     0   (0)|          |
    |*  3 |    FIXED TABLE FULL       | X$KSUSE         |     1 |   925 |     0   (0)|          |
    |*  4 |    FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |   209 |     0   (0)|          |
    ---------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       3 - filter(("S"."KSUUDLNA" IS NOT NULL AND "S"."INST_ID"=USERENV('INSTANCE') AND 
                  BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND 
                  DECODE(BITAND("S"."KSUSEIDL",11),1,'ACTIVE',0,DECODE(BITAND("S"."KSUSEFLG",4096),0,'I
                  NACTIVE','CACHED'),2,'SNIPED',3,'SNIPED','KILLED')='ACTIVE'))
       4 - filter("S"."KSUSEOPC"="E"."INDX")
     
    
    But here i break the query because it run continually
    
    ...
    
    
    SQL> 
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Error: cannot fetch last explain plan from PLAN_TABLE
     
    Executed in 0,469 seconds
    
     .. 
    And up to here i cannot proceed agian because the Query still running.


    Sincerly

    Sonson_01


    Please Notice: Ora_Genie is my guy name Because i want to became one day one of the Best von Ortacle like you.

    Thanks
  • 22. Re: HOW TO: Post a SQL statement tuning request - template posting
    624104 Explorer
    Currently Being Moderated
    Ora_Genie wrote:
    Hi Mi friends,

    Executing thew action plan plan provide by Randolf Geist
    Dude move this to another, NEW thread, you're polluting a good thread.
  • 23. Re: HOW TO: Post a SQL statement tuning request - template posting
    696382 Newbie
    Currently Being Moderated
    Hi Randolf and everybody,

    Please how should i follow the Thread. Somebody told me this is move to another thread.

    But the problem still the long execution of the query who takes long, and because of that i canno't provide you a good
    Explain plan.

    Thanks a lot
    Sonson_01
  • 24. Re: HOW TO: Post a SQL statement tuning request - template posting
    BluShadow Guru Moderator
    Currently Being Moderated
    Ora_Genie wrote:
    Hi Randolf and everybody,

    Please how should i follow the Thread. Somebody told me this is move to another thread.

    But the problem still the long execution of the query who takes long, and because of that i canno't provide you a good
    Explain plan.
    No.
    You started a thread of your own. That was good. But on that thread you didn't provide sufficient information, so the person there provided you with a link to this thread which informs you of how to post your question and what information is needed.

    It's great that you're now able to obtain that information, but you should go back and post it on your own thread, not on this one.
  • 25. Re: HOW TO: Post a SQL statement tuning request - template posting
    709333 Newbie
    Currently Being Moderated
    hi Karthik,
    Thanks for ur reply & suggestion,I've formated it properly in my notepad,but when i posted here it was not in proper format.I think i 've format here. Any here is the statistics for the given query,



    Plan hash value: 1566236598

    ------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 26585 | 4231K| | 16826 (2)| 00:03:22 |
    |* 1 | HASH JOIN | | 26585 | 4231K| 4128K| 16826 (2)| 00:03:22 |
    |* 2 | HASH JOIN | | 26585 | 3816K| 1672K| 12509 (1)| 00:02:31 |
    |* 3 | TABLE ACCESS FULL | WORKORDER | 28903 | 1326K| | 3007 (3)| 00:00:37 |
    |* 4 | HASH JOIN | | 26584 | 2596K| | 9278 (1)| 00:01:52 |
    |* 5 | INDEX FAST FULL SCAN| RELATEDRECORD_NDX1 | 26584 | 467K| | 296 (1)| 00:00:04 |
    | 6 | TABLE ACCESS FULL | TICKET | 185K| 14M| | 8979 (1)| 00:01:48 |
    | 7 | VIEW | FINEGAS_OPEN_DATES | 231K| 3616K| | 3804 (4)| 00:00:46 |
    | 8 | SORT GROUP BY | | 231K| 2938K| 32M| 3804 (4)| 00:00:46 |
    | 9 | INDEX FAST FULL SCAN| WOSTATUS_NDX1 | 1180K| 14M| | 1019 (1)| 00:00:13 |
    ------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - access("FO"."WONUM"="W"."WONUM")
    2 - access("W"."WONUM"="RR"."RECORDKEY")
    3 - filter("W"."WOCLASS"='WORKORDER' AND "W"."ISTASK"=0)
    4 - access("RR"."RELATEDRECKEY"="T"."TICKETID")
    5 - filter("RR"."RELATEDRECCLASS"='SR')

    Please look on this when u r free..

    grace
  • 26. Re: HOW TO: Post a SQL statement tuning request - template posting
    818379 Newbie
    Currently Being Moderated
    Can you tell me know which query is faster? Explain why?

    Query 1:
    SELECT
    *
    FROM
    (
    SELECT
    SUM(
    CASE
    WHEN TYPE = '1' THEN Amount
    WHEN TYPE = '2' THEN Amount
    ELSE 0
    END
    ) Result
    FROM
    C_BPARTNER
    )
    WHERE Result > 0

    Query 2:
    SELECT
    SUM(Amount)
    FROM
    C_BPARTNER
    WHERE
    TYPE = '1'
    UNION ALL
    SELECT
    SUM(Amount)
    FROM
    C_BPARTNER
    WHERE
    TYPE = '2'
  • 27. Re: HOW TO: Post a SQL statement tuning request - template posting
    6363 Guru
    Currently Being Moderated
    No. You are supposed to read this thread and supply the information requested and not post the same question without the necessary information here again.
  • 28. Re: HOW TO: Post a SQL statement tuning request - template posting
    828706 Newbie
    Currently Being Moderated
    oracle 11g performance issue ( BITMAP CONVERSION TO ROWIDS)



    I have two instance of oracle 11g.
    in both instance i fired same query.
    one instance returns the result in 1sec but other instance returns the result in 10 sec

    following is explain plan for bot instance

    instance 1


    -------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 143 | 59 (2)| 00:00:01 |
    | 1 | HASH GROUP BY | | 1 | 143 | 59 (2)| 00:00:01 |
    | 2 | VIEW | VM_NWVW_2 | 1 | 143 | 59 (2)| 00:00:01 |
    | 3 | HASH UNIQUE | | 1 | 239 | 59 (2)| 00:00:01 |
    | 4 | NESTED LOOPS | | | | | |
    | 5 | NESTED LOOPS | | 1 | 239 | 58 (0)| 00:00:01 |

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    | 6 | NESTED LOOPS | | 1 | 221 | 57 (0)| 00:00:01 |
    | 7 | NESTED LOOPS | | 1 | 210 | 55 (0)| 00:00:01 |
    | 8 | NESTED LOOPS | | 1 | 184 | 54 (0)| 00:00:01 |
    | 9 | NESTED LOOPS | | 1 | 158 | 53 (0)| 00:00:01 |
    | 10 | NESTED LOOPS | | 1 | 139 | 52 (0)| 00:00:01 |
    | 11 | NESTED LOOPS | | 1 | 105 | 50 (0)| 00:00:01 |
    |* 12 | INDEX RANGE SCAN | year_field | 1 | 29 | 2 (0)| 00:00:01 |
    | 13 | SORT AGGREGATE | | 1 | 8 | | |
    | 14 | INDEX FULL SCAN (MIN/MAX)| idx_bf_creation_date | 1 | 8 | 2 (0)| 00:00:01 |
    |* 15 | TABLE ACCESS BY INDEX ROWID| OHRT_bugs_fact | 1 | 76 | 48 (0)| 00:00:01 |
    |* 16 | INDEX RANGE SCAN | idx_bf_creation_date | 76 | | 1 (0)| 00:00:01 |

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    |* 17 | TABLE ACCESS BY INDEX ROWID | OHRT_all_time_dimension | 1 | 34 | 2 (0)| 00:00:01 |
    |* 18 | INDEX UNIQUE SCAN | unique_alltime_bug_instance_id | 1 | | 1 (0)| 00:00:01 |
    | 19 | TABLE ACCESS BY INDEX ROWID | OHRT_all_time_dimension | 1 | 19 | 1 (0)| 00:00:01 |
    |* 20 | INDEX UNIQUE SCAN | unique_alltime_bug_instance_id | 1 | | 1 (0)| 00:00:01 |
    |* 21 | INDEX RANGE SCAN | bugseverity_instance_id_ref_id | 1 | 26 | 1 (0)| 00:00:01 |
    |* 22 | INDEX UNIQUE SCAN | unique_alltime_bug_instance_id | 1 | 26 | 1 (0)| 00:00:01 |
    | 23 | INLIST ITERATOR | | | | | |
    |* 24 | TABLE ACCESS BY INDEX ROWID | OHMT_ANL_BUCKET | 1 | 11 | 2 (0)| 00:00:01 |
    |* 25 | INDEX UNIQUE SCAN | SYS_C0053213 | 5 | | 1 (0)| 00:00:01 |
    |* 26 | INDEX RANGE SCAN | FK_BUCKET_TYPE | 6 | | 0 (0)| 00:00:01 |
    |* 27 | TABLE ACCESS BY INDEX ROWID | OHMT_ANL_BUCKET | 1 | 18 | 1 (0)| 00:00:01 |



    instance 2

    Plan
    SELECT STATEMENT ALL_ROWS Cost: 22 Bytes: 142 Cardinality: 1
    32 HASH GROUP BY Cost: 22 Bytes: 142 Cardinality: 1
    31 VIEW VIEW SYS.VM_NWVW_2 Cost: 22 Bytes: 142 Cardinality: 1
    30 HASH UNIQUE Cost: 22 Bytes: 237 Cardinality: 1
    29 NESTED LOOPS
    27 NESTED LOOPS Cost: 21 Bytes: 237 Cardinality: 1
    25 NESTED LOOPS Cost: 20 Bytes: 219 Cardinality: 1
    21 NESTED LOOPS Cost: 18 Bytes: 208 Cardinality: 1
    19 NESTED LOOPS Cost: 17 Bytes: 183 Cardinality: 1
    17 NESTED LOOPS Cost: 16 Bytes: 157 Cardinality: 1
    14 NESTED LOOPS Cost: 15 Bytes: 138 Cardinality: 1
    11 NESTED LOOPS Cost: 13 Bytes: 104 Cardinality: 1
    3 INDEX RANGE SCAN INDEX REPORTSDB.year_field Cost: 2 Bytes: 29 Cardinality: 1
    2 SORT AGGREGATE Bytes: 8 Cardinality: 1
    1 INDEX FULL SCAN (MIN/MAX) INDEX REPORTSDB.idx_bf_creation_date Cost: 3 Bytes: 8 Cardinality: 1
    10 TABLE ACCESS BY INDEX ROWID TABLE REPORTSDB.OHRT_bugs_fact Cost: 13 Bytes: 75 Cardinality: 1
    9 BITMAP CONVERSION TO ROWIDS
    8 BITMAP AND
    5 BITMAP CONVERSION FROM ROWIDS
    4 INDEX RANGE SCAN INDEX REPORTSDB.idx_OHRT_bugs_fact_2product Cost: 2 Cardinality: 85
    7 BITMAP CONVERSION FROM ROWIDS
    6 INDEX RANGE SCAN INDEX REPORTSDB.idx_bf_creation_date Cost: 2 Cardinality: 85
    13 TABLE ACCESS BY INDEX ROWID TABLE REPORTSDB.OHRT_all_time_dimension Cost: 2 Bytes: 34 Cardinality: 1
    12 INDEX UNIQUE SCAN INDEX (UNIQUE) REPORTSDB.unique_alltime_bug_instance_id Cost: 1 Cardinality: 1
    16 TABLE ACCESS BY INDEX ROWID TABLE REPORTSDB.OHRT_all_time_dimension Cost: 1 Bytes: 19 Cardinality: 1
    15 INDEX UNIQUE SCAN INDEX (UNIQUE) REPORTSDB.unique_alltime_bug_instance_id Cost: 1 Cardinality: 1
    18 INDEX UNIQUE SCAN INDEX (UNIQUE) REPORTSDB.unique_alltime_bug_instance_id Cost: 1 Bytes: 26 Cardinality: 1
    20 INDEX RANGE SCAN INDEX REPORTSDB.bugseverity_instance_id_ref_id Cost: 1 Bytes: 25 Cardinality: 1
    24 INLIST ITERATOR
    23 TABLE ACCESS BY INDEX ROWID TABLE OPSHUB.OHMT_ANL_BUCKET Cost: 2 Bytes: 11 Cardinality: 1
    22 INDEX UNIQUE SCAN INDEX (UNIQUE) OPSHUB.SYS_C0040939 Cost: 1 Cardinality: 5
    26 INDEX RANGE SCAN INDEX OPSHUB.FK_BUCKET_TYPE Cost: 0 Cardinality: 6
    28 TABLE ACCESS BY INDEX ROWID TABLE OPSHUB.OHMT_ANL_BUCKET Cost: 1 Bytes: 18 Cardinality: 1

    in both explain plan only difference is

    9 BITMAP CONVERSION TO ROWIDS
    8 BITMAP AND
    5 BITMAP CONVERSION FROM ROWIDS

    but is bitmap degrading performance lot?

    or suggest me what other parameter i can see so 2nd instance gives me better performace.
  • 29. Re: HOW TO: Post a SQL statement tuning request - template posting
    Shidu Newbie
    Currently Being Moderated

    Hi,

     

    I have written a query which collects some billing details from 1990 to till date and I like to add that I am using single table but it has about 10 crore of data. So the performance is tooooooo slooooooooowwwww.

     

    When I was tuning the query with 2 different tries that gives the exactly same result the explain plan was as follows,

     

    COSTCARDINALITYBYTES
    17214750334204
    16231350336719

     

    Can any of you let me know which is the better one in performance and also I like to know what exactly cost and bytes means?

     

    Thanks,

    Shidu

1 2 Previous Next