1 2 3 Previous Next 31 Replies Latest reply: Sep 1, 2013 10:20 PM by sb92075 Go to original post RSS
      • 15. Re: Slow Performance
        yxes2013

        Thanks all,

         

        I want to give the sql statement that is causing waits or locks or performance issues to the developer for investigation, but she insist to see the entire sql statement.

        Should I tell them that sqltxt table has limited column length and can not store long sql statements?

         

        Thanks

        • 16. Re: Slow Performance
          sb92075

          yxes2013 wrote:

           

          Thanks all,

           

          I want to give the sql statement that is causing waits or locks or performance issues to the developer for investigation, but she insist to see the entire sql statement.

          Should I tell them that sqltxt table has limited column length and can not store long sql statements?

           

          Thanks

          V$SQL.SQL_FULLTEXT  contains the whole SQL statement

          • 17. Re: Slow Performance
            yxes2013

            It does not exist

             

            connect / as sysdba

            SQL> desc v$sql_fulltext

            ERROR:

            ORA-04043: object v$sql_fulltext does not exist

             

             

             

             

            SQL> desc v$sql.sql_fulltext

            ERROR:

            ORA-04043: object v$sql.sql_fulltext does not exist

            • 18. Re: Slow Performance
              sb92075

              Please NOTE the second column below

               

              It appears you are incapable of using DESC command

               

              SQL> desc v$sql

              Name                                      Null?    Type

              ----------------------------------------- -------- ----------------------------

              SQL_TEXT                                           VARCHAR2(1000)

              SQL_FULLTEXT                                       CLOB

              SQL_ID                                             VARCHAR2(13)

              SHARABLE_MEM                                       NUMBER

              PERSISTENT_MEM                                     NUMBER

              RUNTIME_MEM                                        NUMBER

              SORTS                                              NUMBER

              LOADED_VERSIONS                                    NUMBER

              OPEN_VERSIONS                                      NUMBER

              USERS_OPENING                                      NUMBER

              FETCHES                                            NUMBER

              EXECUTIONS                                         NUMBER

              PX_SERVERS_EXECUTIONS                              NUMBER

              END_OF_FETCH_COUNT                                 NUMBER

              USERS_EXECUTING                                    NUMBER

              LOADS                                              NUMBER

              FIRST_LOAD_TIME                                    VARCHAR2(76)

              INVALIDATIONS                                      NUMBER

              PARSE_CALLS                                        NUMBER

              DISK_READS                                         NUMBER

              DIRECT_WRITES                                      NUMBER

              BUFFER_GETS                                        NUMBER

              APPLICATION_WAIT_TIME                              NUMBER

              CONCURRENCY_WAIT_TIME                              NUMBER

              CLUSTER_WAIT_TIME                                  NUMBER

              USER_IO_WAIT_TIME                                  NUMBER

              PLSQL_EXEC_TIME                                    NUMBER

              JAVA_EXEC_TIME                                     NUMBER

              ROWS_PROCESSED                                     NUMBER

              COMMAND_TYPE                                       NUMBER

              OPTIMIZER_MODE                                     VARCHAR2(10)

              OPTIMIZER_COST                                     NUMBER

              OPTIMIZER_ENV                                      RAW(2000)

              OPTIMIZER_ENV_HASH_VALUE                           NUMBER

              PARSING_USER_ID                                    NUMBER

              PARSING_SCHEMA_ID                                  NUMBER

              PARSING_SCHEMA_NAME                                VARCHAR2(30)

              KEPT_VERSIONS                                      NUMBER

              ADDRESS                                            RAW(4)

              TYPE_CHK_HEAP                                      RAW(4)

              HASH_VALUE                                         NUMBER

              OLD_HASH_VALUE                                     NUMBER

              PLAN_HASH_VALUE                                    NUMBER

              CHILD_NUMBER                                       NUMBER

              SERVICE                                            VARCHAR2(64)

              SERVICE_HASH                                       NUMBER

              MODULE                                             VARCHAR2(64)

              MODULE_HASH                                        NUMBER

              ACTION                                             VARCHAR2(64)

              ACTION_HASH                                        NUMBER

              SERIALIZABLE_ABORTS                                NUMBER

              OUTLINE_CATEGORY                                   VARCHAR2(64)

              CPU_TIME                                           NUMBER

              ELAPSED_TIME                                       NUMBER

              OUTLINE_SID                                        NUMBER

              CHILD_ADDRESS                                      RAW(4)

              SQLTYPE                                            NUMBER

              REMOTE                                             VARCHAR2(1)

              OBJECT_STATUS                                      VARCHAR2(19)

              LITERAL_HASH_VALUE                                 NUMBER

              LAST_LOAD_TIME                                     VARCHAR2(76)

              IS_OBSOLETE                                        VARCHAR2(1)

              IS_BIND_SENSITIVE                                  VARCHAR2(1)

              IS_BIND_AWARE                                      VARCHAR2(1)

              IS_SHAREABLE                                       VARCHAR2(1)

              CHILD_LATCH                                        NUMBER

              SQL_PROFILE                                        VARCHAR2(64)

              SQL_PATCH                                          VARCHAR2(30)

              SQL_PLAN_BASELINE                                  VARCHAR2(30)

              PROGRAM_ID                                         NUMBER

              PROGRAM_LINE#                                      NUMBER

              EXACT_MATCHING_SIGNATURE                           NUMBER

              FORCE_MATCHING_SIGNATURE                           NUMBER

              LAST_ACTIVE_TIME                                   DATE

              BIND_DATA                                          RAW(2000)

              TYPECHECK_MEM                                      NUMBER

              IO_CELL_OFFLOAD_ELIGIBLE_BYTES                     NUMBER

              IO_INTERCONNECT_BYTES                              NUMBER

              PHYSICAL_READ_REQUESTS                             NUMBER

              PHYSICAL_READ_BYTES                                NUMBER

              PHYSICAL_WRITE_REQUESTS                            NUMBER

              PHYSICAL_WRITE_BYTES                               NUMBER

              OPTIMIZED_PHY_READ_REQUESTS                        NUMBER

              LOCKED_TOTAL                                       NUMBER

              PINNED_TOTAL                                       NUMBER

              IO_CELL_UNCOMPRESSED_BYTES                         NUMBER

              IO_CELL_OFFLOAD_RETURNED_BYTES                     NUMBER

               

               

              SQL>

              • 19. Re: Slow Performance
                yxes2013

                Thanks nagulan,

                 

                Check the underlying indexes of the tables involved in the query and gather stats if they are old. Did the tables had a mass delete/insert?

                Run SQL Tuning Advisor on the SELECT statement with SQL_ID "38frh2ms0wmd6" as per the recommendation. This will give you some clue.

                 

                The batch program is again ok today. So the culprit would not be the sql. The tables have are gathered stats everyday as it has mas delete.

                • 20. Re: Slow Performance
                  yxes2013

                  Hi all,

                   

                  Since the diagnostics tool does not point out what is causing the performance issue of our batch program,

                  I have a wild guess that it could be accounted to some memory held by runaway processes, which needs to be cleared without rebooting the server.

                  In this regard, Is there a utility that will clear the cache in AIX? It is somehow equivalent to linux cache dropping (http://unix-rootvg.blogspot.com/2011/08/clear-filesystem-memory-cache.html)?  We recently encountered problem with our oracle db server and based on their findings, they recommend to do regular cache clearing if we do not regularly reboot our system.


                  Thanks

                  • 21. Re: Slow Performance
                    Nicolas.Gasparotto

                    yxes2013 wrote:

                     

                    Hi all,

                     

                    Since the diagnostics tool does not point out what is causing the performance issue of our batch program,

                    I have a wild guess that it could be accounted to some memory held by runaway processes, ...

                    Would it not be because the weather is bad today that your program is slowing down ?

                     

                    Nicolas.

                    • 22. Re: Slow Performance
                      yxes2013

                      Well, if it is the weather the awrrpt would have told me so

                      • 23. Re: Slow Performance
                        sb92075

                        yxes2013 wrote:

                         

                        Hi all,

                         

                        Since the diagnostics tool does not point out what is causing the performance issue of our batch program,

                        I have a wild guess that it could be accounted to some memory held by runaway processes, which needs to be cleared without rebooting the server.

                        In this regard, Is there a utility that will clear the cache in AIX? It is somehow equivalent to linux cache dropping (http://unix-rootvg.blogspot.com/2011/08/clear-filesystem-memory-cache.html)?  We recently encountered problem with our oracle db server and based on their findings, they recommend to do regular cache clearing if we do not regularly reboot our system.


                        Thanks

                         

                        I don't know what is more sad, lame & pathetic; the recommendation to "do regular cache cleanings" or you accepting such utter nonsense.

                        Understand & realize that Oracle DB does not  know about or care about the contents (or lack thereof) of the OS filesystem cache.

                        Why does this situation only impact AIX?

                        Why do you report so MANY different & ongoing challenges involving the databases you are supposedly responsible for?

                        What is common to all these problems?

                        • 25. Re: Slow Performance
                          sybrand_b

                          They hired someone who is 100 percent incompetent in both OS and database software, and who doesn't have industry to learn anything.

                           

                          -----------------

                          Sybrand Bakker

                          Senior Oracle DBA

                          • 26. Re: Slow Performance
                            Hemant K Chitale

                            Oracle Support Note 316533.1  covers AIX Memory Management tuning for Oracle.    However, that is extremely unlikely as the cause for the problem that you describe in this thread.  For this problem, you still need to identify the "other" session that is executing DML and locking row(s).

                             

                             

                            Hemant K Chitale


                            • 27. Re: Slow Performance
                              yxes2013

                              Thanks hemant sir,

                               

                              Since last friday's job was good as compared to last thursday bad job . I want to gather all sql statement being run that day on that specified time range and compare them with each other. This is just to identify the difference of their sql statements that may have caused the problem.

                               

                              I this available in dba_session_sql_history? or any other views?

                               

                              Thanks

                              • 28. Re: Slow Performance
                                Hemant K Chitale

                                No, there is no view that shows "all sql statement being run that day".   You'd have to write your own code to query V$SQL or V$SQLSTATS periodically and write to a custom table.  Even then, there is no guarantee that you'd get every  SQL as an SQL might be executed and aged out of the shared pool between two collections by your monitoring script.

                                 

                                 

                                Hemant K Chitale

                                • 29. Re: Slow Performance
                                  sb92075

                                  I already said that the GUARANTEED way to capture every SQL is via SQL_TRACE=TRUE

                                  but this was rejected as being too bothersome so you continue to live with what you got (which is a MESS!)