1 2 Previous Next 29 Replies Latest reply: Jul 24, 2013 12:41 PM by Shidu Go to original post RSS
      • 15. Re: HOW TO: Post a SQL statement tuning request - template posting
        Timur Akhmadeev
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            mkr
                            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
                              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
                                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
                                  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

                                    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