1 2 3 Previous Next 34 Replies Latest reply: Jan 7, 2014 1:50 AM by Chrisjenkins-Oracle RSS

    Passthough count increasing

    user10366531

      Hi ,

       

      I am checking passthrough count in monitor command. as per my observation it is increasing so high.

       

        PASSTHROUGH_COUNT:     

      15000866

        LOCK_GRANTS_IMMED:      2060856012

      Can you please share why this such behaviour ?

       

      I am getting below message in timesten ttmess.log file.  can you please share this as well .

       

      16:37:56.79 Warn:: 11374: 12261/0x2f654cc0: ConnId=54 (java) waiting for latch "Perm Block[34262408352]"(34262408448), Holder=51 (java) PID 12261, now 50 secs
      16:38:06.97 Warn:: 11374: 12261/0x2f8de590: ConnId=59 (java) waiting for latch "Perm Block[34262408352]"(34262408448), Holder=51 (java) PID 12261, now 60 secs
      16:38:07.04 Warn:: 11374: 12261/0x2f7da6b0: ConnId=57 (java) waiting for latch "Perm Block[34262408352]"(34262408448), Holder=51 (java) PID 12261, now 60 secs
        • 1. Re: Passthough count increasing
          Tim Vincent

          Hi,


          You've not mentioned if you're actually using PassThrough or not?


          However, PASSTHROUGH_COUNT counts the number of 'passthrough' executions against Oracle. Note that the cache agent and the replication agent regularly execute passthrough operations even when there is no application load on the system so just seeing this increase does not mean that your query ran with passthrough. Do you have the cache agent or replication running?


          On the warning in the message log, what is PID 12261 actually doing? Look to see if it's holding any locks with ttXactAdmin. Please let us know what version this is and on what platform?


          Tim



          • 2. Re: Passthough count increasing
            user10366531

            [orattadmin@racdb2 bin]$ ./ttversion

            TimesTen Release 11.2.2.6.0 (64 bit Linux/x86_64) (y:53396) 2013-11-21T02:09:30Z

              Instance admin: orattadmin

              Instance home directory: /opt/TimesTen/y

              Group owner: timesten

              Daemon home directory: /opt/TimesTen/y/info

              PL/SQL enabled.

             

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

            Command> call ttrepstart;

            8191: This store (TT_1122 on RACDB2) is not involved in a replication scheme

            The command failed.

            Please  help to resolve this immediate 

            • 3. Re: Passthough count increasing
              Tim Vincent

              This error could be for a number of reasons:

              Check the error messages manual Warnings and Errors

               

              If still a problem please provide the following:

              1. The DSN definitions all the TimesTen databases involved

              2. The exact create replication / create active standby pair statement you are using.

              3. The output of the O/S hostname command on each machine involved

               

              Also you may like to double check your setup against the example in the quick start:

              Configuring Replication Between TimesTen Databaseshttp://download.oracle.com/otn_hosted_doc/timesten/1122/quickstart/html/admin/rep.html

              • 4. Re: Passthough count increasing
                user10366531

                [tttest]

                Driver=/opt/TimesTen/y/lib/libtten.so

                DataStore=/data3/Timesten/TT_1122

                DatabaseCharacterSet=WE8MSWIN1252

                LogBufMB=512

                LogFileSize=512

                LogBufParallelism=16

                PermSize=51920

                TempSize=10240

                #Temporary=1

                MemoryLock=4

                PassThrough=1

                oracleNetServiceName=orcl

                 

                 

                . 2) i am not using any replication .

                3) Host name = dbserver

                • 5. Re: Passthough count increasing
                  Tim Vincent

                  OK, perhaps you could provide a bit more information around what it is that you are trying to achieve? If you are not using replication then I'm guessing your using AWT cache groups? Please describe the steps you have performed up to when you get the error.

                   

                  Thanks

                  • 6. Re: Passthough count increasing
                    user10366531

                    Hi tim,,

                    I have solved this error. now please guide me how can i improve perfomance in timesten. i have observed that timesten log files and passthrough are increasing.

                     

                    please guide me how can i solve this

                    • 7. Re: Passthough count increasing
                      Tim Vincent

                      You have PassThrough=1 In your DSN so a SQL statement that references a table that does not exist in the TimesTen database is passed through to the Oracle database for execution. The fact that you have PASSTHROUGH_COUNT increasing means this is happening quite a lot. timesten transaction log files are purged when not needed, by the checkpointing process. I see you don't have a checkpoint interval defined in your DSN so it will use the default of 600 seconds. Review CkptFrequency and CkptLogVolume to see which is appropriate for you. You can manually call a checkpoint in ttIsql with call ttCkpt; issue it twice to purge all log files.


                      With regards to general performance please review the best practices and quickstart.

                      TimesTen Best Practices


                      If you provide further details on what it is you are trying to achieve, why for example do you need Passthrough=1 we can help you further?

                      • 8. Re: Passthough count increasing
                        user10366531

                        thanks Time for your suggestion,

                         

                        we are having OLTP environment. where we need to required other objects while application up.such  as synonyms.jboss caches some query

                        while  we start it. so we have set passtrhough 1.

                         

                        how ckptfrequnecy and ckptlogvolume will help me in improving performance??

                         

                        How can i monitor that time based aging is applied on cach groups or not. is there any audit from where i can check that this much RAM is free and this much is in Used..command , Free -g will help me in this case ?

                        • 9. Re: Passthough count increasing
                          user10366531
                          23:24:38.56 Warn::  8871: 18600/0xb54da50: ConnId=29 (java) waiting for latch "Perm Block[548103064]"(548103160), Holder=23 (java) PID 18600, now 40 secs
                          23:24:38.59 Warn::  8871: 18600/0xb6518f0: ConnId=31 (java) waiting for latch "Perm Block[548103064]"(548103160), Holder=23 (java) PID 18600, now 40 secs
                          23:24:38.62 Warn::  8871: 18600/0xb243460: ConnId=23 (java) waiting for latch "Log Strand Insertion[0]"(85908644616), Holder=132 (Log Marker) PID 8877, now 40 secs
                          23:24:38.64 Warn::  8871: 18600/0xb345f30: ConnId=25 (java) waiting for latch "Perm Block[548103064]"(548103160), Holder=23 (java) PID 18600, now 40 secs
                          23:24:38.64 Warn::  8871: 18600/0xb1cf080: ConnId=22 (java) waiting for latch "Perm Block[548103064]"(548103160), Holder=23 (java) PID 18600, now 40 secs
                          23:24:38.68 Warn::  8871: 18600/0xb5cf9a0: ConnId=30 (java) waiting for latch "Perm Block[548103064]"(548103160), Holder=23 (java) PID 18600, now 40 secs
                          23:24:38.71 Warn::  8871: 18600/0xb2c4200: ConnId=24 (java) waiting for latch "Heap[10] - Perm"(37200), Holder=84 (java) PID 18784, now 40 secs
                          23:24:39.35 Warn::  8871: 18688/0x2aab07038310: ConnId=59 (java) waiting for latch "Log Strand Insertion[15]"(85908657576), Holder=60 (java) PID 18688, now 40 secs
                          23:24:39.37 Warn::  8871: 18688/0x2aab06d2c690: ConnId=53 (java) waiting for latch "Table CRESTELRNCPRD611.TBLCDRPREPAID"(952976), Holder=23 (java) PID 18600, now 40 secs
                          23:24:39.38 Warn::  8871: 18688/0x2aab06dae5e0: ConnId=54 (java) waiting for latch "Table CRESTELRNCPRD611.TBLCDRPREPAID"(952976), Holder=23 (java) PID 18600, now 40 secs
                          23:24:39.39 Warn::  8871: 18688/0x2aab06f34430: ConnId=57 (java) waiting for latch "Table CRESTELRNCPRD611.TBLCDRPREPAID"(952976), Holder=23 (java) PID 18600, now 40 secs
                          23:24:39.45 Warn::  8871: 18688/0x2aab06eb24c0: ConnId=56 (java) waiting for latch "Log Strand Insertion[15]"(85908657576), Holder=60 (java) PID 18688, now 40 secs
                          23:24:39.45 Warn::  8871: 18688/0x2aab06fb63a0: ConnId=58 (java) waiting for latch "Log Strand Insertion[15]"(85908657576), Holder=60 (java) PID 18688, now 40 secs
                          23:24:39.48 Warn::  8871: 18688/0x2aab06e30550: ConnId=55 (java) waiting for latch "Log Strand Insertion[15]"(85908657576), Holder=60 (java) PID 18688, now 40 secs
                          23:24:39.55 Warn::  8871: 18688/0x2aab06caa740: ConnId=52 (java) waiting for latch "Log Strand Insertion[15]"(85908657576), Holder=60 (java) PID 18688, now 40 secs
                          23:24:39.76 Warn::  8871: 23099/0xaea8b50: ConnId=128 (TRANSMITTER(M):1115306304) waiting for latch "Log Strand Insertion[0]"(85908644616), Holder=132 (Log Marker) PID 8877, now 40 secs
                          • 10. Re: Passthough count increasing
                            user10366531

                            n@dbserver info]$ ps -ef| grep 18688

                            501      18688     1 99 21:00 pts/14   08:46:48 /usr/java/jdk1.6.0_12//jre/bin/java -server -Dlog4j.debug=true -Dlog4j.configuration=log4j.xml -DOCSRMIEngine1 -verbose:gc -Doracle.jdbc.Trace=true -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=8874 -Dcom.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authenticate=false -server -Xms512m -Xmx1g -Djava.awt.headless=true -Dsun.rmi.dgc.server.gcInterval=36000000 -Djava.rmi.server.hostname=192.168.0.149 -Djava.rmi.activation.port=3231 -Dcom.sun.management.jmxremote.ssl=false -classpath .:../lib/crestel-coreserver.jar:../lib/expression-lib.jar:../lib/hornetq-core-client-java5.jar:../lib/hornetq-jms-client-java5.jar:../lib/jboss-javaee.jar:../lib/jdmkrt.jar:../lib/jline-0.9.94.jar:../lib/jnpserver.jar:../lib/log4j.jar:../lib/messageserviceinterface-utils.jar:../lib/netty.jar:../lib/ocs-remoting.jar:../lib/ttcp.jar:../lib/ttjdbc6.jar:../jars/elite-account-agents.jar:../jars/elite-account-core.jar:../jars/elite-account-protocols-diameter.jar:../jars/elite-account-protocols-memory.jar:../jars/elite-account-protocols-rmi.jar:../jars/elite-charging-agents.jar:../jars/elite-charging-common-agent.jar:../jars/elite-charging-common-core.jar:../jars/elite-charging-common-fw.jar:../jars/elite-charging-common-util.jar:../jars/elite-charging-core.jar:../jars/elite-charging-protocols-diameter.jar:../jars/elite-charging-protocols-jmx.jar:../jars/elite-charging-protocols-memory.jar:../jars/elite-charging-protocols-rmi.jar:../jars/elite-offline-account-core.jar:../jars/elite-offline-agents.jar:../jars/elite-offline-charging-core.jar:../jars/elite-offline-protocols-jmx.jar:../jars/elite-offline-protocols-memory.jar:../jars/elite-offline-protocols-rmi.jar:../jars/elite-offline-rating-core.jar:../jars/elite-rating-core.jar:../jars/elite-rating-protocols-diameter.jar:../jars/elite-rating-protocols-memory.jar:../jars/elite-rating-protocols-rmi.jar:../config/. com.elitecore.crestelocs.fw.server.Main com.elitecore.crestelocs.fw.server.OCSServer

                            504      24259 23252  0 23:26 pts/9    00:00:00 grep 18688

                            [orattadmin@dbserver info]$

                            • 11. Re: Passthough count increasing
                              user10366531

                              how frequently timesten data are commited to oracle ? . For example if i have AWT cache groups, when and at what time timesten data are  commite to oracle . ??

                              • 12. Re: Passthough count increasing
                                user10366531

                                can you please help us to resolve the same ?

                                • 13. Re: Passthough count increasing
                                  user10366531

                                  We have following strategy :

                                  On 2 tables , only select query has been executed & for that CacheGroup = Read only

                                  On 2 tables, Insert & update queries have been executed & for that cachegroup=AWT

                                  On 1 table only update query has been executed & for that cachegroup=AWT

                                  On 1 table only insert query has been executed & for that cachegroup=AWT

                                   

                                  i have taken output of monitor command while load run. attaching here with same .

                                   

                                  following are my concerns .

                                   

                                  1)  PASSTHROUGH_COUNT: 90299012 .

                                  as per above tables specifications, six queries are executed per request. each table is containing cachgroups in timesten only.we have set passthrough=1 in our DS file. we are processing almost 20 millions of requests .  we are not able to undestand why passthough count is increasing this much.

                                   

                                  2) as per our understanding below parameters play a role in performance tuning.

                                  -Durablecommit

                                  -LogFlushMethod

                                  - CkptFrequency

                                  - CkptLogVolume

                                   

                                  We didn't set any of above parameters yet. right now these parameters carry default values so please guide us to set values of these parameters as per above specifications. as we need to generate 50 millions of requests.

                                   

                                  3) for 20 million Requests, we have around 680 log files of 512 MB size each.

                                  So we have doubted, major performance bottleneck for this disk I/O.

                                  and also problem of DISK size full.we need to minimize the log file generation.

                                   

                                  4) it has been observed that timesten keeps two ds file (datafile ) for replication. can we keep only one file to avoid disk space

                                  So kindly guide us to tune this condition.

                                  • 14. Re: Passthough count increasing
                                    Tim Vincent

                                    Q: how ckptfrequnecy and ckptlogvolume will help me in improving performance??

                                    A: It doesn't directly but you asked me why the timesten log files were increasing, and these are the parameters that control the purging of log files via checkpointing.

                                     

                                    Q How can i monitor that time based aging is applied on cach groups or not. is there any audit from where i can check that this much RAM is free and this much is in Used..command , Free -g will help me in this case ?

                                    A: Use the ttIsql command 'cachegorups' to determine if time based ageing is applied to a cache group. Use the ttIsql command 'dssize' to check how much RAM is allocated to TimesTen and how much of that RAM is in use. For the OS there are various tools to monitor the RAM usage such as 'cat /proc/meminfo' and 'free -m'

                                     

                                    For performance please review TimesTen Best Practices and apply the recommendations here. In particular put the checkpoint files and transaction log files on different physical disks to avoid disk contention. Increase LogBufMB and LogFileSize to both be 1024. Then tune individual queries with the Index Advisor to ensure they are optimised correctly, making sure you're updating statistics.

                                     

                                    It looks like you have a lot of contention going on on the system. What is the characteristic of the workload? How many inserts/updates/deletes/reads? How many of your transaction are passed through to be executed in the oracle database, by the look of the passthrough count it's a lot? What is pid 18688 doing in terms of SQL workload, is it a 'passthrough' transaction?

                                     

                                    Q: how frequently timesten data are commited to oracle ? . For example if i have AWT cache groups, when and at what time timesten data are  commite to oracle . ??

                                    A: You can monitor this with 'ttRepAdmin -showstatus -awtmoninfo' please see the doc first at Managing a Caching Environment

                                    1 2 3 Previous Next